Syntax for delete statment using a linked server

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  October 20, 2006 at 2:04 pm

  #170121

  Hoping someone can point me in the right direction.  I did a search and this topic is mentioned, but I never see the correct syntax posted and verification from the original poster.

  I need to delete data in a linked server.  As suggested in other threads, I used the four part name in the delete statment.  But, it seems to me the delete is taking place on the base server, not the linked server.

  delete from INTERFLOW02.inventory.dbo.transmissions --9 rows deleted

  --Verify the data got deleted

  select * from INTERFLOW02.inventory.dbo.transmissions --0 rows returned

  --Now to double check run in QA on linked server

  select * from inventory.dbo.transmissions --9 rows ?!?

  I have some scripts below to illustrate/duplicate what I have done.

  Thanks! Kim

  --Add the linked server from teh base database

  EXEC sp_addlinkedserver

       @server = 'INTERFLOW02'

     , @provider = 'MSDASQL'

     , @provstr = 'DRIVER={SQL Server};SERVER=INTERFLOW02;UID=<*****>;PWD=<*****>;'-- substite IP for OtherServerName if needed

     , @srvproduct = 'any'

  EXEC sp_addlinkedsrvlogin 'INTERFLOW02', 'false', '<****>', '<*****>', '<******>'

  --Create the table on both servers

  CREATE TABLE [Transmissions] (

   [TransmissionId] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,

   [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

   [SortOrder] [int] NOT NULL ,

   [Active] [int] NOT NULL ,

   CONSTRAINT [PK_Transmissions] PRIMARY KEY  CLUSTERED

   ([TransmissionId])  ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  --Insert the data on both servers

  Insert into transmissions (description,sortorder,active) values ('None',0,1)

  Insert into transmissions (description,sortorder,active) values ('Automatic',1,1)

  Insert into transmissions (description,sortorder,active) values ('5 Speed Manual',2,1)

  Insert into transmissions (description,sortorder,active) values ('4 Speed Manual',3,1)

  Insert into transmissions (description,sortorder,active) values ('3-Speed Manual',4,1)

  Insert into transmissions (description,sortorder,active) values ('6-Speed Manual',5,1)

  Insert into transmissions (description,sortorder,active) values ('Allison Automatic',6,1)

  Insert into transmissions (description,sortorder,active) values ('Allison Manual',7,1)

  Insert into transmissions (description,sortorder,active) values ('Automatic Overdrive',8,1)

  --Verify the data on both servers.  Run this is QA on each server.

  select * from inventory.dbo.transmissions --9 rows each

  --Execute the delete to reference the linked server.

  delete from INTERFLOW02.inventory.dbo.transmissions --9 rows deleted

  --Verify the data got deleted

  select * from INTERFLOW02.inventory.dbo.transmissions --0 rows returned

  --Now to double check run in QA on linked server

  select * from inventory.dbo.transmissions --9 rows ?!?

  --It seems to me the delete is deleting from the base server table

  --not the remote even though I have specified the linked server

  --How is this possible?

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  October 20, 2006 at 3:05 pm

  #667226

  The proper syntax:

  delete openquery(INTERFLOW02, 'select * from inventory.dbo.transmissions')

  As illustrated in this article:

  http://support.microsoft.com/kb/270119