Linked Server - Delete rows

Question

Post reply

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Justin-14543 SSCrazy Points: 2017 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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)