Calling a function from linked SQL Server

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You normally have to specify the user name (e.g. dbo) when calling a UDF.

Have you tried......

SELECT * FROM TSTCMDDB1..dbo.usysprocess(51)

....only a guess!

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

You need to include all parts of the 4 part name in the call i.e. server.database.user.object

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

Mkumari Hall of Fame Points: 3498 More actions · Answer 3

Hall of Fame

Points: 3498

November 13, 2002 at 1:25 pm

I agree with Simon Sabin.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Try SELECT * FROM TSTCMDDB1.master.dbo.usysprocess(51) but it still doesn't work. Same error message.

Edited by - Allen_Cui on 11/14/2002 12:28:14 PM

Antares686 SSC Guru Points: 125444 More actions · Answer 5

I remember somewhere someone stateing they can only use it with OPENQUERY.

I think there is a limitation in the ability to return UDF with table returns that prevents them. Or you have to turn something else on.

However, try this to see if works.

SELECT * FROM TSTCMDDB1.master.dbo.[usysprocess(51)]

I don't know what will happen since I haven't tested.

Now the other question I have is why didn't you just select from the sysprocesses table yourself? Or make an Procedure instead?

dj_meier SSCrazy Points: 2732 More actions · Answer 6

Antares is right...you must call it like this:

select * from OPENQUERY(TSTCMDDB1,'select * from master.dbo.usysprocess(51)')

-Dan

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 7

SSCertifiable

Points: 7891

November 14, 2002 at 4:52 pm

The openquery method is what we use. Works fine.