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