Linked Server - Delete rows

• gsolomon

  Mr or Mrs. 500

  Points: 599

  More actions

  April 30, 2002 at 10:13 am

  #158321

  I have a stored procedure that cursors through data in a db2 table and does some processing based upon these 'triggers'. The question is, is there a way that I can delete these rows in DB2 once I have processed the rows? I am using Openquery for selects.

• Justin-14543

  SSCrazy

  Points: 2017

  More actions

  April 30, 2002 at 10:24 am

  #428125

  Can't you issue a Delete stmt to DB2 thru the same way you issue a Select stmt?

  Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

• gsolomon

  Mr or Mrs. 500

  Points: 599

  More actions

  April 30, 2002 at 10:32 am

  #428128

  negative...for example to select, my query looks like this...

  Select * from Openquery(db2, 'select * from mytable')

  to delete, I've tried,

  Delete * from Openquery(db2, 'select * from mytable')

  and

  Delete from Openquery(db2, 'select * from mytable')

  and

  Select * from Openquery(db2, 'Delete * from mytable')

  all fail syntactically.

• gsolomon

  Mr or Mrs. 500

  Points: 599

  More actions

  May 2, 2002 at 8:53 am

  #428315

  Anyone else have any thoughts here? I've banked on getting this work and the bank now has insufficient funds. Thanks in advance.

  Edited by - gsolomon on 05/02/2002 09:16:35 AM

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 2, 2002 at 9:33 am

  #428326

  I believe this is the correct syntax.

  Delete Openquery(db2, 'select * from mytable')

  If not then look at http://search.support.microsoft.com/search/default.aspx?Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&Product=sql&Query=openquery%2520delete&Queryc=openquery+delete&withinResults=false&srchstep=0&KeywordType=ALL&Titles=false&numDays=&maxResults=25 and see if anything helps answer the question.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• gsolomon

  Mr or Mrs. 500

  Points: 599

  More actions

  May 2, 2002 at 1:57 pm

  #428378

  quote:

  ---

  I believe this is the correct syntax.

  Delete Openquery(db2, 'select * from mytable')

  If not then look at http://search.support.microsoft.com/search/default.aspx?Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&Product=sql&Query=openquery%2520delete&Queryc=openquery+delete&withinResults=false&srchstep=0&KeywordType=ALL&Titles=false&numDays=&maxResults=25 and see if anything helps answer the question.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  ---

  I tried that syntax and got the following error...

  Server: Msg 7357, Level 16, State 1, Line 1

  Could not process object 'select id_case from yccfssxf.case where id_case = 131'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

  The 7537 error is documented in the link that you sent me, 4-part names are not supported in my environment and the other options presented fail as well

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 2, 2002 at 3:08 pm

  #428383

  I have done this before but the server I tested with is no longer around so I a little lost. These are my only 2 other thoughts. Try

  Select * from Openquery(db2, 'delete from yccfssxf.case where id_case = 131')

  or

  Select * from Openquery(db2, 'Select null delete from yccfssxf.case where id_case = 131')

  You may need to try the last way and these with this first

  SET ANSI_NULLS ON

  go

  SET ANSI_WARNINGS ON

  go

  if I can find my notes on the testing we did I will pass along but I think they are gone.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)