remote stored procedures - 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

Transact-SQL (2000)

remote stored procedures

Author

Topic

vanity
Starting Member

2 Posts

Posted - 2006-08-01 : 11:01:33

Hi Guys, we are using sql server 2000 and we have a few servers. I need to pipe the resultset from a remote stored procedure (which ends with a select) result set into a temporary table.

the remote querry does say:

select 'hello world';

and the way i'm trying to use it is

insert into #temp
exec remotequerry;

However this starts a infinite loop. (as in the querry doesnt end and it takes a long time to abort) How do i get functionality similar to this ?

I have initiated the select that runs off the remote database inside a local stored procedure but that was 4x slower then getting it done remote side.

Any suggestions ?

Cheers

Hassan

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-01 : 11:17:40

exec remotequerry

s.b.
exec linkedserver.database.dbo.remotequerry

What happens if you execute it without the insert?

If it's an infinite loop then the problem is with the SP not with your call.

==========================================
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.

vanity
Starting Member

2 Posts

Posted - 2006-08-01 : 11:22:34

remote procedure deffinition on db8 :

CREATE PROCEDURE dbo.test AS

select 'hassan'
GO

remote procedure run from local server

create table #temp(string varchar(20))

insert into #temp
exec db8.[ukplc web].dbo.test;
drop table #temp

I tried the remote procedure before the insert and it worked fine :D returned the atomic value 'hassan'

Hope this clarifies any ambiguity

Cheers

Hassan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources