June 13, 2013 at 8:33 am
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
June 13, 2013 at 9:16 am
solution found:-)
SELECT dbo.udf_GetStuff (44, 22) AS retVal
--
candide
________Panta rhei
June 14, 2013 at 6:59 am
What does dbo represent in the example you're using?
June 14, 2013 at 8:02 am
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
June 14, 2013 at 9:16 am
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
June 14, 2013 at 10:50 am
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
June 14, 2013 at 11:42 am
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).
June 15, 2013 at 5:41 am
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?
June 19, 2013 at 2:05 am
Hi,
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
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply