January 8, 2009 at 1:17 pm
if i create a function:
CREATE FUNCTION dbo.MyFunction ()
RETURNS INT AS
BEGIN
RETURN 1
END
SELECT dbo.MyFunction works
SELECT DBWhereTheFunctionIsLocated.dbo.MyFunction works
SELECT CurrentServerName.DBWhereTheFunctionIsLocated.dbo.MyFunction fails
why? and any way to get around it? i know it seems redundant to include the current server name in the SELECT, but i need to (if i can).
January 8, 2009 at 1:45 pm
I believe you must have a linked server to actually do this. This is why it is failing when you are trying this locally on your machine.
What you can do is simply remove the 4th part, or add a linked server to the server you want to connect, to actually be able to call it.
Cheers,
J-F
Cheers,
J-F
January 8, 2009 at 1:48 pm
actually, i did try a linked server... that's how i found this in the first place. i know, a linked server pointing to the local server seems redundant, but we're renaming machines soon and i'm trying to cut down on downtime... kind of a preemptive strike.
so it doesn't work WITH the linked server and it doesn't work with the server's name.
January 8, 2009 at 1:54 pm
What is the failure message you are getting?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 1:57 pm
Invalid Column Name 'CurrentServerName' (or linked server name).
i could swear i posted the error in my first post... i must be losing my marbles...
January 8, 2009 at 2:13 pm
I seem to remember having trouble with UDFs on remote servers before. I think I ended up having to convert to procs to get them to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2009 at 2:17 pm
I missed what you are trying to do - and, you can't do that. You can call a table-valued function through a linked server, but you cannot reference a scalar function that way.
So, this would work:
SELECT {columns}
FROM LinkedServer.db.dbo.function(...) AS table;
Will not work:
SELECT LinkedServer.db.dbo.function(...) AS column;
In fact, using three-part naming to reference a column is deprecated in the column list (SQL Server 2008).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 2:18 pm
i can't even do select servername.dbname.dbo.MyFunction() from ssms much less a proc or view...
January 8, 2009 at 2:21 pm
jeffrey williams:
thanks for the info... where is that written and how'd you find it?
it's not that i don't believe you (i do believe you)... i just want to find out how i missed finding that...
January 8, 2009 at 2:23 pm
That's the point. You can't do scalar UDFs that way. Have to convert it to a proc (or create a parallel proc that locally calls the function), and return the value as an output parameter. Then execute the proc across the linked server connection.
create dbo.MyProc
(@Output_Variable datatype)
as
select @Output_Variable = dbo.MyFunction();
And then:
declare @MyVariable datatype
exec MyRemoteServer.MyDatabase.dbo.MyProc @MyVariable output
select @MyVariable
Something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2009 at 2:26 pm
gsquared:
i'm with you now... read your post too fast and didn't get your meaning. doing too many things at once... sigh.
January 8, 2009 at 2:27 pm
As far as being able to call a scalar function - not sure I found that anywhere. But, as far as the deprecated features - it's in the documentation for SQL Server 2008.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply