call a SQL Server User Defined Function from Access query

• candide

  Ten Centuries

  Points: 1164

  More actions

  June 13, 2013 at 8:33 am

  #73378

  Hi,

  can I call a UDF (Scalar-valued function) from Access2007 in a query window? I know it works with Stored Procedures, but how about UDF?

  EXEC dbo.udf_GetStuff 44, 22

  thx

  --
  candide
  ________Panta rhei

• candide

  Ten Centuries

  Points: 1164

  More actions

  June 13, 2013 at 9:16 am

  #1623763

  solution found:-)

  SELECT dbo.udf_GetStuff (44, 22) AS retVal

  --
  candide
  ________Panta rhei

• grovelli-262555

  SSCrazy

  Points: 2633

  More actions

  June 14, 2013 at 6:59 am

  #1624089

  What does dbo represent in the example you're using?

• candide

  Ten Centuries

  Points: 1164

  More actions

  June 14, 2013 at 8:02 am

  #1624124

  Hi,

  dbo is the schema owner, as all objects in SQL Server are owned by a schema (dbo = database owner). It's the usual syntax for every object in SQL Server, more info you find in BOL.

  Btw, the query didn't run with

  SELECT dbo.udf_GetStuff (44, 22) AS retVal

  and I don't know whats the issue.

  What I really needed was a VBA solution which works this way:

  Public Function fctRunUDF_GetStuff(ByVal inlng_arIdnr As Long _

  , ByVal lngSKey As long _

  ) As Currency

  '===

  ' calling a SQL Server UDF (User defined function) from Access VBA

  '

  '===

  Dim currDB As DAO.Database

  Dim strSQL As String

  Dim varReturn As Variant

  Const cstrODBC as string = "ODBC;DSN=hubba;Description=hubba;UID=Administrator;DATABASE=hubba;Trusted_Connection=Yes"

  Set currDB = CurrentDb()

  strSQL = "SELECT dbo.udf_GetStuff (44, 22) AS retVal"

  With currDB.CreateQueryDef("", strSQL)

  .Connect = cstrODBC

  varReturn = .OpenRecordset.Fields(0)

  End With

  fctRunUDF_GetStuff = varReturn

  currDB.Close

  Set currDB = Nothing

  and it works:-)

  --
  candide
  ________Panta rhei

• grovelli-262555

  SSCrazy

  Points: 2633

  More actions

  June 14, 2013 at 9:16 am

  #1624170

  if dbo is the schema owner, I don't think in Access you can call the udf_GetStuff function using the syntax

  dbo.udf_GetStuff

• candide

  Ten Centuries

  Points: 1164

  More actions

  June 14, 2013 at 10:50 am

  #1624207

  hey grovelli-262555

  look at the VBA-code, it works! I develop on SQL SERVER with an account at Windows Security with admin rights.

  Calling

  SELECT dbo.udf_GetStuff (44, 22) AS retVal

  inside an Access query (in SQL view) doesn't work, but I don't care cause VBA is what I needed

  --
  candide
  ________Panta rhei

• rf44

  SSCommitted

  Points: 1535

  More actions

  June 14, 2013 at 11:42 am

  #1624227

  In the VBA procedure, I would try:

  strSQL = "SELECT dbo.udf_GetStuff (44, 22)"

  Then:

  varReturn = .OpenRecordset.GetRows

  And finally retrieve the value returned by the udf from varReturn(0, 0).

• grovelli-262555

  SSCrazy

  Points: 2633

  More actions

  June 15, 2013 at 5:41 am

  #1624336

  Thanks Candide, in your VBA procedure you have the line:

  strSQL = "SELECT dbo.udf_GetStuff (44, 22) AS retVal"

  What's the code behind udf_GetStuff?

  By the way, why isn't the hexadecimal colour code accepted?

• candide

  Ten Centuries

  Points: 1164

  More actions

  June 19, 2013 at 2:05 am

  #1625309

  Hi,

  @grovelli-262555

  here's a simple code example to add 2 values:

  USE [Test01]

  GO

  /****** Object: UserDefinedFunction [dbo].[udf_GetStuff] Script Date: 06/19/2013 09:54:19 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:its me

  -- Create date: 2013-06-19

  -- Description:add 2 values

  -- =============================================

  CREATE FUNCTION [dbo].[udf_GetStuff]

  (

  -- Add the parameters for the function here

  @p1 int = 0

  , @p2 int = 0

  )

  RETURNS int

  AS

  BEGIN

  -- Declare the return variable here

  DECLARE @Result int

  -- Add the T-SQL statements to compute the return value here

  SET @Result = @p1 + @p2

  -- Return the result of the function

  RETURN @Result

  END

  Call the udf in SSMS:

  DECLARE @RET int

  EXEC @RET = dbo.udf_GetStuff 44, 22

  Select 'Sum= ', @RET

  @rf44

  varReturn = .OpenRecordset.GetRows

  is really an improvement because returning a Table Valued UDF returning a table can be handled. thanx for this hint:-)

  --
  candide
  ________Panta rhei