January 30, 2009 at 11:36 am
I setup a linked server, "MyLink". I want to run a function located in a database on the remote linked server, "RemoteDB" called "TimeOnly". When I try and run the function using the fully qualified name,
SELECT MyLink.RemoteDB.dbo.TimeOnly('1/1/09 23:00:15')
I receive and error :"Invalid Column Name MyLink".
When I run the same query using OLE DB "OpenQuery",
SELECT * FROM OPENQUERY(MyLink,
'SELECT RemoteDB.dbo.TimeOnly(''1/1/09 23:00:15'')')
The results are returned as expected.
According to BOL for SQL Server 2005 (both the local and remote servers are 2005 Standard) for stored procedures (not sure if it also applies to functions?):
In SQL Server 2000 and later versions, OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name.
I can run other, higher level stored procedures, i.e.,
EXEC MyLink.Master.dbo.sp_who
without any problems.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 30, 2009 at 5:54 pm
This doesn't answer your question, but I want to point out you're going a really long way to do something really simple. I'm assuming your function just returns the time part of a date (dbo.TimeOnly('1/1/09 23:00:15') = '1/1/1900 23:00:15'). If that is the case why don't you just create the function locally or embed the logic into the code itself? Otherwise you might as well call GetDate() from a linked server as well.
My 2 cents...
June 1, 2011 at 5:18 pm
same problem here. Ever figure out what the issue was?
My queries run fine, pulling data from the linked server, except where there is a funtion call.
"Msg 207, Level 16, State 1, Line 26
Invalid column name 'LINK_XXX'."
June 1, 2011 at 5:27 pm
It's simply not supported:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 2, 2011 at 2:06 pm
Craig Farrell (6/1/2011)
It's simply not supported:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138
Thanks Craig.
JM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply