How to caal a UDF on a linked server
-
EXEC sp_addlinkedserver '192.168.1.1'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '192.168.1.1', @useself = 'false',
@locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'password'
SELECT A.Column1, [192.168.1.1].MyDatabase.dbo.MyFunction(A.Columns1)
FROM [192.168.1.1].MyDatabase.dbo.MyTable A
EXEC sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.1', @locallogin = 'sa'
EXEC sp_dropserver '192.168.1.1'
Above gives me:
Server: Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.
When I'm on the Linked server,
the following runs fine.
SELECT A.Column1, dbo.MyFunction(A.Columns1)
FROM MyTable A
How do I call a UDF on a linked server?
Please help.
Regards,
K. Matsumura
-
A function call is limited to the 3 part naming convention... See Books Online.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Jeff.
Does that mean a UDF cannot be called from a linked server?
-
Pretty sure that's what it means...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, Jeff.
I will do without UDF.
-
If this is more of a "Is it in any way possible, no matter what hoops I have to jump through?" type question, I'm thinking there is a way. I wouldn't recommend it as a good solution by any stretch of the imagination, but it should be possible (can't test at the moment).
Using your linked server, find the udf in question in the INFORMATION_SCHEMA.Routines view, and using dynamic SQL, create it locally. When done, drop it like a hot potato. This assumes that you have the necessary permissions everywhere that is needed, and that you really can't use one of the more aesthetically pleasing methods of solving your problem, for whatever reason.
-
Thank you, David, for your interesting idea.
But I would have to change all object names in the original function.
I mean I have to add [192.168.1.1].MyDatabase.
It would be to difficult for me to find all object names using TSQL.
K. Matsumura
-
Ah, I reread your code, and you're wanting to use the remote udf on remote data, not local data. There are likely ways of accomplishing what you want, if you give us more detail about what you're trying to do, and why (not because we're curious, although we often are, but because that helps us determine what will and won't fit your needs). Off the top of my head, calling remote stored procs, or even starting jobs remotely might or might not do what you want.
-
For some reason this is a common question and I know of almost no one who knows how to do this. It's simple really but you have to make yourself understand that functions work a little differently in SQL Server, why Microsoft deemed it necessary to make them unique is beyond me. Anyway you would do something like the following:
EXEC sp_addlinkedserver '192.168.1.1'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '192.168.1.1', @useself = 'false',
@locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'password'
EXECUTE [192.168.1.1].master.dbo.sp_executesql 'SELECT A.Column1, [192.168.1.1].MyDatabase.dbo.MyFunction(A.Columns1)
FROM [192.168.1.1].MyDatabase.dbo.MyTable A'
EXEC sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.1', @locallogin = 'sa'
EXEC sp_dropserver '192.168.1.1'
By making that change you are getting around the three part function naming convention functions use by having your linked server actually execute the code.
-
Aren't you still using the 4 part naming in the dynamic SQL? I don't think that's gonna work unless you try it like this... (although, I certainly could be wrong)...
EXEC sp_addlinkedserver '192.168.1.1'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '192.168.1.1', @useself = 'false',
@locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'password'
EXECUTE [192.168.1.1].master.dbo.sp_executesql 'SELECT A.Column1,
[192.168.1.1].MyDatabase.dbo.MyFunction(A.Columns1)FROM [192.168.1.1].MyDatabase.dbo.MyTable A'
EXEC sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.1', @locallogin = 'sa'
EXEC sp_dropserver '192.168.1.1'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you all,
It worked in the following way:
EXEC sp_addlinkedserver '192.168.1.1'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '192.168.1.1', @useself = 'false',
@locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'password'
EXECUTE [192.168.1.1].master.dbo.sp_executesql
'SELECT A.Column1, MyDatabase.dbo.MyFunction(A.Columns1)
FROM MyDatabase.dbo.MyTable A'
EXEC sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.1', @locallogin = 'sa'
EXEC sp_dropserver '192.168.1.1'
Regards,
K. Matsumura
-
"Aren't you still using the 4 part naming in the dynamic SQL? I don't think that's gonna work unless you try it like this... (although, I certainly could be wrong)..."
Thanks Jeff, typo on my part. Anyway looks like the problem is solved.
-
Heh... know wurrees, Ed... A've bin nown too meke na tyyp-oh hear ond their, meself
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Koji,
Thanks for posting your solution... THAT's what we like to see when folks get things working!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Setup a Linked-Server via Enterprise Manager or with the system sprocs.
Then call the scalar UDF via OpenQuery as such:
Select * from OpenQuery(LnkSrv,'select dbname.dbo.udf(parms)')
or for table-valued UDF as such:
Select * from OpenQuery(LnkSrv,'select * from dbname.dbo.udf(parms)')
Enjoy...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply