Calling Remote database user defined functions

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 2000 Forums

SQL Server Development (2000)

Calling Remote database user defined functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-10 : 08:40:44

Rajesh writes "Hi,

I created one user defined function in pubs database at 172.28.6.161 server. The function accepts state as input parameter and returns table data type data. the function looks like as follows

server:172.28.6.161
Database:pubs

CREATE function getauthors(@strState varchar(3))
returns @ca_authors table (au_id varchar(20), au_lname varchar(50), au_fname varchar(50))
as
begin

insert into @ca_authors select au_id, au_lname, au_fname from authors
where state= @strState
return
end

select * from dbo.getauthors('CA')

the above query is executing perfectly when i am in same server.

Now i connected to server 172.28.8.185 and tempdb database.
Created linked server to 172.28.6.161

select * from [172.28.6.161].pubs.dbo.getauthors('CA')

When i am executing the above query i am getting the following error

Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('

My question is how to call remote database user defined functions?

Thank you,
Rajesh"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-10 : 16:05:45

Try openquery or sp_executesql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources