Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

call a SQL Server User Defined Function from Access query Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:36 AM
Points: 21, Visits: 229
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
Post #1463125
Posted Thursday, June 13, 2013 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:36 AM
Points: 21, Visits: 229
solution found

SELECT dbo.udf_GetStuff (44, 22) AS retVal



--
candide
________Panta rhei
Post #1463149
Posted Friday, June 14, 2013 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:48 PM
Points: 100, Visits: 780
What does dbo represent in the example you're using?
Post #1463541
Posted Friday, June 14, 2013 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:36 AM
Points: 21, Visits: 229
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
Post #1463583
Posted Friday, June 14, 2013 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:48 PM
Points: 100, Visits: 780
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
Post #1463639
Posted Friday, June 14, 2013 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:36 AM
Points: 21, Visits: 229
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
Post #1463691
Posted Friday, June 14, 2013 11:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 3:24 AM
Points: 59, Visits: 928
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).
Post #1463714
Posted Saturday, June 15, 2013 5:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:48 PM
Points: 100, Visits: 780
Thanks Candide, in your VBA procedure you have the line:
[color=996699]strSQL = "SELECT dbo.udf_GetStuff (44, 22) AS retVal"[/color]
What's the code behind udf_GetStuff?
By the way, why isn't the hexadecimal colour code accepted?
Post #1463842
Posted Wednesday, June 19, 2013 2:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:36 AM
Points: 21, Visits: 229
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
Post #1464985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse