Linked Server UDF Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server UDF Problem

Author

Topic

Fergus McGee
Starting Member

2 Posts

Posted - 2007-04-24 : 12:28:31

I have a problem trying to include a UDF in the select list of a view.

My View looks like ...
Create View dbo.View1
AS
SELECT
a,
b,
c,
d,
dbname.dbo.udfName(e)
From
LinkedServer.dbname.dbo.tablename

This used to work just fine in SQL2000 but I get an error in 2005 -
Cannot find either column "dbname" or the user-defined function or aggregate "dbname.dbo.udfName", or the name is ambiguous.

I've tried using 4 part naming but that doesn't work either.

Any suggestions would be very gratefully recieved.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-24 : 13:37:06

Have you tried the 2 part naming convention: dbo.udfName(e)?

Does the select work if you run it ad-hoc rather than in a view?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-24 : 13:43:48

Is the function table-valued or scalar?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-24 : 13:47:42

quote:
Originally posted by cvraghu

Is the function table-valued or scalar?

Since it is being used in the column list of a select plus we have the error message, we can assume it is scalar-valued.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-24 : 21:59:04

Stupid Question #1: When you installed SQL2005, did you re-create the linked server?
Stupid Question #2: Did you verify the linked server works in an ad-hoc query?

I don't intend any offense - sometimes the tv doesn't work because it got unplugged.

/jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-24 : 22:42:40

If function dbname.dbo.udfName is in database dbname on the server where you are creating the view, it should work.

If dbname.dbo.udfName is a function in database dbname on the linked server, that will not work.


Create View dbo.View1
as
SELECT 
	a,
	b,
	c,
	d,
	dbname.dbo.udfName(e)
From
	LinkedServer.dbname.dbo.tablename

CODO ERGO SUM

Fergus McGee
Starting Member

2 Posts

Posted - 2007-04-25 : 04:27:10

Hi,

Thanks for the responses.

It is a scalar function. The function is on the linked server. I've tried every naming convention. The linked server does work in other queries.

It looks like it doesn't work in 2005, I guess I'm going to have to find another solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources