cant 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
MNJCan 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|||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|||EXECUTE linkedserver.master.dbo.sp_who

Works just fine for me. Does anyone else have trouble with it?

-PatP|||This is working for me:

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

but not working:

select * from linkedserver.testDB2.dbo.testfunction()|||Pat,

I get

Server: Msg 7411, Level 16, State 1, Line 1
Server 'QA' is not configured for RPC.|||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...|||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|||I think you can't use the next for a function

select * from any_function(params)

Shouldn't this be:

select any_function(params)

Maybe this will solve the problem?|||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|||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.|||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 '('.|||Pat: an off-topic question: what's a table valued function?|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp) documentation, or an article at SQL Team (http://www.sqlteam.com/item.asp?ItemID=1955).

-PatP|||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.php?p=3673457#post3673457

Thanks
MNJ