Using Temp table across Linked Servers
-
I have a procedure say :
create table #data
(keyfield int null,
valuefiled char(5) null)
go
create proc proc1
as
declare @keyfield int
select @keyfield = 0
while 0 = 0
begin
select @keyfield = min(keyfield)
from #data
where keyfield > @keyfield
if @keyfield is NULL break
update table set value = valuefiled from #data
where keyfield = @keyfield
end
This procedure is compiled in Remote Server
I have a procedure in Local Server which creates a #data table and populates the data.
How do I call the Store Procedure so that I need not change the Store Procedure in the remote server.
I currently call the procedure by
exec @ret_status = [linkedserver\instancename].dbname.username.proc1
This gives me invalid #proc.
Can anyone help me to find a solution.
Thanks in advance
-
If you need to avoid creating object on the remote server, you can use sp_executesql.
exec remote.master.dbo.sp_executesql @sql
@sql contains statements to create #data, get the data, insert it into #data, and exec proc1.
If there's little data, you can just use simple insert statements in @sql to fill #data. If there's a lot of data, use a distributed query in @sql to get the data into #data from the local server.
-
Thank you for the suggestion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply