Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Posts
    27

    Question Unanswered: can't use functions on linked servers???

    Hello

    I'm having some problems with a couple of linked SQL servers. Basically I can get queries and SPs to work just fine, aklthough there is a little overhead. But I can't get functions to work! Can it really be that linked servers don't support functions??? And if so, just out of curiosity, why on earth is it so? Linked servers only handle result sets, not scalar values?

    I hope somebody can help me with this
    MNJ

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you explain "can't get functions to work" in more detail? I've never had it fail, so I'm probably missing something really basic here.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    I thought you could never access the SPs and UDFs through a linked server. If you could, I would be interested to know how. Is this syntax valid. Can I execute this from Server 2
    linkedserver1.database1.owner1.SP1

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    PHP Code:
    EXECUTE linkedserver.master.dbo.sp_who 
    Works just fine for me. Does anyone else have trouble with it?

    -PatP

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    This is working for me:

    select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

    but not working:

    select * from linkedserver.testDB2.dbo.testfunction()

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat,

    I get

    Server: Msg 7411, Level 16, State 1, Line 1
    Server 'QA' is not configured for RPC.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that was easy..

    Just go to properties in EM and select that you want to do RPC's

    Why would that even be an option....

    Wonder what the code is to enable it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by snail
    This is working for me:

    select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

    but not working:

    select * from linkedserver.testDB2.dbo.testfunction()
    Can you elaborate just a bit on what constitutes "not working" ???

    -PatP

  9. #9
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    I think you can't use the next for a function
    Code:
    select * from any_function(params)
    Shouldn't this be:
    Code:
    select any_function(params)
    Maybe this will solve the problem?
    Johan

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Welcome Johan!

    It depends. If they are using a table valued function, the original syntax would be fine as long as it had at least a two-part name.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Just one question from me ...

    though from your post it seems the linked server is to a SQL server ...

    is it really to a SQL server or some other db.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Pat Phelan
    Can you elaborate just a bit on what constitutes "not working" ???

    -PatP
    I got:

    Server: Msg 170, Level 15, State 31, Line 1
    Line 2: Incorrect syntax near '('.

  13. #13
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Pat: an off-topic question: what's a table valued function?
    Johan

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by jora
    Pat: an off-topic question: what's a table valued function?
    You can think of a table valued function as a view that takes parameters. You can read about them in the CREATE FUNCTION documentation, or an article at SQL Team.

    -PatP

  15. #15
    Join Date
    Aug 2003
    Posts
    27

    Thumbs up

    Thanks for the input everybody, and sorry I didn't respond until now. I've been looking further into the thing, and has come to the conclusion that my problem is really about linked servers and INSERTS.

    I've started a new thread here:
    http://www.dbforums.com/showthread.p...57#post3673457

    Thanks
    MNJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •