UDF's and Distributed queries


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: UDF's and Distributed queries

  1. #1
    Jeremy Guest

    UDF's and Distributed queries


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

    Thanks
    Jeremy

  2. #2
    Jeremy Guest

    Re: UDF's and Distributed queries


    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














  3. #3
    DaveSatz Guest

    Re: UDF's and Distributed queries

    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




  4. #4
    DaveSatz Guest

    Re: UDF's and Distributed queries

    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
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >




  5. #5
    jeremy Guest

    Re: UDF's and Distributed queries



    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center