I've a problem on getting resultset from remote stored procedure from the local server.
The stored procedure on the remote server:
create procedure sp_test
begin
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from abc
select * from #temp
end
on the local server, the command is
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from openquery(LINKEDSERVER,'LINKEDSERVER.DBNAME.dbo.sp_test')
/*****/
the server prompt that table #temp not found
if I simply run LINKEDSERVER.DBNAME.dbo.sp_test
it works.
Actually I need to prepare a stored procedure on the local server to execute sp_temp on remote server.
I tried to use other method to do the task, such as
insert into #temp
exec LINKEDSERVER.DBNAME.dbo.sp_test
but the server prompt that MSDTC service not found in the server.
as i'm not quite familer on MSDTC service, I tried to use other method.
for doing this task, is it a must to start the MSDTC service.
if I start it, what effect will be (performance, loading etc)
please help!! thanks!!
Yeah, for the most part just start DTC and let it go its merry way. It will not have a negative impact on performance. Can somebody check me on this please?|||
Yes, I just afraid the loading and performance would be affected after starting MSDTC.
No comments:
Post a Comment