call remote stored proc - 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 2000 Forums

SQL Server Development (2000)

call remote stored proc

Author

Topic

beyonder422
Posting Yak Master

124 Posts

Posted - 2005-09-20 : 10:55:24

anyone know how to call a proc on a remote server and return it's recordset as a local recordset?

i thought something like:
EXEC remote-server-name.remote-db-name.dbo.usp_remote_proc

but get incorrect syntax errors. Is this the correct syntax, but my db name is causing issues?

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-20 : 11:37:18

have you tried setting up a linked server?

====================================================
Regards,
Sean Roussy

aka the merciless black hearted capitalist troublemaker

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 11:39:17

Looks alright to me

Try

EXEC RemoteServer.RemoteDB.dbo.sp_helpdb

also check that the RemoteServer name is in the list from:

EXEC sp_linkedservers

Kristen

beyonder422
Posting Yak Master

124 Posts

Posted - 2005-09-20 : 11:45:38

I knew about linked servers, but wasn't sure about the specifics of calling stored procedures through linked server.

Thought there would be a way to do it with straight syntax only and no linked server.

{ CALL [LINKEDDBNAME].[remote_db_name].[dbo].[st_proc] ('123','123') }

Had to enable RPC Out option in linked server settings.

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 11:48:10

"I thought there would be a way to do it with straight syntax only and no linked server"

You could probably open an ADO object and run it through that, but I don't know whether you'd be able to get any resultset back again.

Preferable to use a LinkedServer IMHO 'coz if the Server "moves" then only the LinkedServer has to be changed, and not each query that uses it.

Kristen

beyonder422
Posting Yak Master

124 Posts

Posted - 2005-09-20 : 11:55:07

You know how to script the enable RPC Out option?

sp_alter...linkedserver @rpc_out = true

etc..

think I got it... - sp_serveroption

exec sp_serveroption @server='linkedbname', @optname='rpc out', @optvalue=true
exec sp_serveroption 'linkedbname', 'rpc out', false

beyonder422
Posting Yak Master

124 Posts

Posted - 2005-09-20 : 11:59:22

Thanx for the input guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources