INSERT - EXEC never returns

[dublyner]

I am having an issue with linked servers on Windows 2000 running SQL 2005.

I created the following test case to simplify an issue. I created a small procedure that selects the max site_no from a table called site on one server and called it from another. It runs fine.

However when I try to insert it into a table ( # or permanent ) using insert-exec it never comes back.

I ran a profiler and I was not even able to trap the call, so I don't think it is even getting access to the other server.

If I try to insert the data directly for the site table using a direct select over the linked server it works fine.


---- Calling Code -----

drop table #test_red
go
create table #test_red ( site_no int)

insert #test_red
exec LINKEDSERVER.DEVDB.dbo.mm_test

select * from #test_red
go


---Procedure Code -----
drop procedure mm_test
go
create procedure mm_test
as
select max(site_no) from site
go


Any help you canprovide would be great.

Mark

[satane]

HI

create table #test_red ( site_no int)
insert #test_red
exec dbo.mm_test

create table
site(sites int)

insert site(sites) values (25)

create procedure mm_test

begin
select max(sites) from site
end
select * from #test_red

this code is working.it may help u.

I am having an issue with linked servers on Windows 2000 running SQL 2005.

I created the following test case to simplify an issue. I created a small procedure that selects the max site_no from a table called site on one server and called it from another. It runs fine.

However when I try to insert it into a table ( # or permanent ) using insert-exec it never comes back.

I ran a profiler and I was not even able to trap the call, so I don't think it is even getting access to the other server.

If I try to insert the data directly for the site table using a direct select over the linked server it works fine.


---- Calling Code -----

drop table #test_red
go
create table #test_red ( site_no int)

insert #test_red
exec LINKEDSERVER.DEVDB.dbo.mm_test

select * from #test_red
go


---Procedure Code -----
drop procedure mm_test
go
create procedure mm_test
as
select max(site_no) from site
go


Any help you canprovide would be great.

Mark

[dublyner]

Thanks but the problem is running the procedure accross the linked server.
The code below works because the SP is local.

insert #test_red
exec dbo.mm_test

Any other ideas?