CODEWITHSUNDEEP
sqlsql-server
Serve Laurijssen
Serve Laurijssen

Reputation: 9763

calling an insert stored procedure on another machine using TSQL

I am trying to insert data from a table at one machine into a table on another machine if that record does not exist but Im not sure how to do that.

To clarify

There's a table named tblAssortments on server X.

There's a table named Assortments on server Y.

server y has a stored procedure named RegisterAssortment looking like this:

BEGIN
    SET NOCOUNT ON;

    if not exists (select 1 from AssortmentRegEx where id = @id and code = '^' + @ProductCode + '$')
    begin
        insert into AssortmentRegEx
        values (newid(), 1, @id, '', '1,2', '^' + @ProductCode + '$', '', @ProductCode)
    end
END

On machine X there's a stored procedure named SynchronizeAssortments. It is supposed to open a cursor and loops through all records. Then it calls RegisterAssortment for every record.

But that's where I get stuck. I tried using openquery to call the sproc on the other machine but it's not working for insert statements. Something like this:

while @@fetch_status = 0
begin
  openquery(ws422, 'exec yy.dbo.RegisterAssortment ', @ProductCode, @id)

  fetch next from assortmentCursor
end

but openquery doesn't work like this. What is a good way to solve this problem?

Upvotes: 0

Views: 57

Answers (1)

SAS
SAS

Reputation: 4045

Try adding a linked server. Use sp_addlinkedserver or in SSMS it's found under Serverver Objects. Then you can access the linked server from the server where you added the link, just prefix with server name: server.database.dbo.table.

Upvotes: 1

Related Questions