• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1035
  • Last Modified:

Linked Server to MySQL update or delete data errors

I have set up a linked server from  SQL2000 to a MySQL database. I am able to select data from the linked sever but I am unable to update or delete data from the linked server. I have posted select code that works and delete codes that does NOT, along with the error that occurs.
query code that works:
select * from  openquery(BEEF19_MYSQL_DEV, 'select * from stgpackprd')
 
delete code that does NOT work:
 delete from  OPENQUERY(BEEF19_MYSQL_DEV, 'SELECT  UNIQUE_ID, PLANT_NUM, PRODUCT_CD from stgpackprd')
	where  UNIQUE_ID = (SELECT UNIQUE_ID FROM  Sparoe.dbo.TRANSACTIONS_FOR_PROD_MASTERS WHERE UPDATED_FLAG  = '2' )
 
Server: Msg 7333, Level 16, State 2, Line 1
Could not fetch a row using a bookmark from OLE DB provider 'MSDASQL'. 
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetLocate::GetRowsByBookmark returned 0x80040e21:  ].

Open in new window

0
everythingbutthemoo
Asked:
everythingbutthemoo
  • 2
  • 2
1 Solution
 
everythingbutthemooAuthor Commented:
I hae found a solution to my problem,  the following code allows me to delete records

-- cursor
declare @UNIQUE_ID VARCHAR(15)
declare @PLANT_NUM INT
declare @PRODUCT_CD VARCHAR(10)
declare update_single_record_cursor cursor for  select  UNIQUE_ID, PLANT_NUM, PRODUCT_CD from TRANSACTIONS_FOR_PROD_MASTERS
      where  UPDATED_FLAG = '2'
open update_single_record_cursor
fetch next from update_single_record_cursor INTO @UNIQUE_ID, @PLANT_NUM, @PRODUCT_CD

WHILE (@@FETCH_STATUS = 0)
BEGIN
  delete from  OPENQUERY(BEEF19_MYSQL_DEV, 'SELECT  UNIQUE_ID, PLANT_NUM, PRODUCT_CD from stgpackprd')
      where UNIQUE_ID =  @UNIQUE_ID
        AND PLANT_NUM =  @PLANT_NUM
        AND PRODUCT_CD = @PRODUCT_CD

  fetch next from update_single_record_cursor INTO @UNIQUE_ID, @PLANT_NUM, @PRODUCT_CD
END

CLOSE update_single_record_cursor
DEALLOCATE update_single_record_cursor

0
 
ZberteocCommented:
Your problem was the fact that (SELECT UNIQUE_ID FROM  Sparoe.dbo.TRANSACTIONS_FOR_PROD_MASTERS WHERE UPDATED_FLAG  = '2' ) query probably  returned more than one row so you can't delete with =, in that case you need to use IN. Probably the following would work as well:

 delete from  OPENQUERY(BEEF19_MYSQL_DEV, 'SELECT  UNIQUE_ID, PLANT_NUM, PRODUCT_CD from stgpackprd')
        where  UNIQUE_ID IN (SELECT UNIQUE_ID FROM  Sparoe.dbo.TRANSACTIONS_FOR_PROD_MASTERS WHERE UPDATED_FLAG  = '2' )

If that works you don't need a cursor. Cursors are to be avoided especially in a context with linked server because will give real performance problems. On the other hand I would avoid linked servers alltogehter for the same very reason, bad performance. There are other methods to implement that functionality

I know the question is closed but I wanted to let you know.

0
 
andhit_rCommented:
@Zberteoc :
How bout if I using composite keys. Can I use EXISTS?
0
 
ZberteocCommented:
You can use whatever keys you want. The number of rows returned by a query has nothing to do with the keys. And yes you can use EXISTS.
0
 
andhit_rCommented:
Thx @Zberteoc
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now