UDF's and Distributed queries

[Jeremy]

Anyone out there running distributed queries within UDF's (User Defined Functions)?
Anyone know if it is possible?

Thanks
Jeremy

[Jeremy]

So I guess thats why I am not getting data back for this function

CREATE FUNCTION Test (@pSys_Of_Rec varchar(128))
RETURNS datetime
AS

BEGIN


DECLARE @max_extract_window_date datetime
SET @max_extract_window_date = NULL

IF @pSys_Of_Rec = 'Something'
BEGIN
Select @max_extract_window_date = Max(SysTime)
-- SysTime = max time stamp entry
FROM DServerName.dbName.dbo.table
END

RETURN (@max_extract_window_date)
END

Thank you

Jeremy

[DaveSatz]

I have not done it, but this is what BOL says:
The following statements are allowed in the body of a multi-statement
function. Statements not in this list are not allowed in the body of a
function:
Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the
function.
SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements modifying table variables local to the
function.
EXECUTE statements calling an extended stored procedures.

--

HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
-----------------------------------------------------------------

"Jeremy" <jwadzin@wpsr.com> wrote in message
news:3c0e2ece$1@147.208.176.211...
>
> Anyone out there running distributed queries within UDF's (User Defined
Functions)?
> Anyone know if it is possible?
>
> Thanks
> Jeremy

[DaveSatz]

I would think you are covered under:
"SELECT statements containing select lists with expressions that assign
values to variables that are local to the function."

If you do
Select @max_extract_window_date = Max(SysTime)
FROM DServerName.dbName.dbo.table
you get the expected result ?

and I expect you are passing in @pSys_Of_Rec = 'Something'


"Jeremy" <jwadzin@wpsr.com> wrote in message
news:3c0e3775$1@147.208.176.211...
>
> So I guess thats why I am not getting data back for this function
>
> CREATE FUNCTION Test (@pSys_Of_Rec varchar(128))
> RETURNS datetime
> AS
>
> BEGIN
>
>
> DECLARE @max_extract_window_date datetime
> SET @max_extract_window_date = NULL
>
> IF @pSys_Of_Rec = 'Something'
> BEGIN
> Select @max_extract_window_date = Max(SysTime)
> -- SysTime = max time stamp entry
> FROM DServerName.dbName.dbo.table
> END
>
> RETURN (@max_extract_window_date)
> END
>
> Thank you
>
> Jeremy
>
>
>
>
>
>
>
>
>
>
>
>
>

[jeremy]

No - Do not recieve a result set at all - but it does run error free. If
I run the same query in a SQL statement result is returned correctly IE -
Select Max(SysTime)FROM DServerName.dbName.dbo.table. I also found that I
was having problems with the ANSI_NULLS option when I attempted to create
the UDF (running the Distributed Query) using the wizard provided by SQL
Server. I profiled the wizard and it seems the wizard attempts to turn ANSI_NULLS
OFF before creation of the UDF but I find in BO that 'SET ANSI_NULLS should
be set to ON for executing distributed queries' The UDF creation would fail
returning an error that said 'SET ANSI_NULLS must be set to ON'. I found
that if I run the UDF creation script in Query Analyzer (With SET ANSI_NULLS
ON) it is created without error - so if anyone out there is having problems
creating a UDF that will run a distributed query try creating it by not using
the wizard. Anyway back to my other problem - is there any other reason that
anyone knows of that would cause and error free function not to return a
specified result?

Thanks again
Jeremy