0

I have an ODBC connection to a db2 system from SQL Server. I can use OPENQUERY to select, insert, and delete records from my intended table.

But, I need to routinely delete all records from a table and when trying to delete the entire contents I'm getting errors about isolation levels, but I think it actually has to do with the the number of rows I'm attempting to delete at one time.

I can loop thru and delete about 600 at a time, but that's a hack for now.

Any suggestions on how to more effectively do this?

Error I'm getting:

OLE DB provider "MSDASQL" for linked server "MY_SERVER" returned message "Neither the isolation level nor a strengthening of it is supported.".

Msg 7392, Level 16, State 2, Line 32
Cannot start a transaction for OLE DB provider "MSDASQL" for linked server "MY_SERVER".

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tod Meinke
  • 116
  • 1
  • 1
  • 7

1 Answers1

0

If you really want to delete ALL records in the DB2 table why don't you try:

TRUNCATE TABLE <mytable>

I think you can run it via EXEC in OLE DB (this is not ODBC) using something like this:

EXEC <mylinkedserver.db>sp_executesql 'TRUNCATE TABLE <mytable>'
mauro
  • 5,730
  • 2
  • 26
  • 25
  • I tried that but I don't have the privs for it. Also, at least in sql server, truncate won't work if there are any tables with a FK to the table. That's not the case here, but it's a common reason why truncate isn't always an available solution. – Tod Meinke Jun 17 '17 at 02:38