0

I'm trying to run a delete query from Micrsoft SQL server to clear out a table in a remote mySQL table.

Query is: 

delete from OPENQUERY(WEB_DB,'select id,university_name,college_name from table_name');

This is returning the error:

OLE DB provider "MSDASQL" for linked server "WEB_DB" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.". Msg 7345, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "WEB_DB" could not delete from table "select id,university_name,college_name from table_name". Updating did not meet the schema requirements.

The SQL used to create the table is as follows:

CREATE TABLE IF NOT EXISTS `table_name` (
  `pk` int(11) AUTO_INCREMENT,
  `studentid` int(11) default NULL,
  `university_name` varchar(255) default NULL,
  `college_name` varchar(255) default NULL,
  CONSTRAINT PRIMARY KEY(`pk`)
);

Interestingly it does appear to do delete some portion of the remote database records, there should be ~900, running the delete command for the first time reduces this to ~700 so it seems like the limit is about 200 rows?!

Any help much appreciated.

Thanks Jona

Jona
  • 305
  • 1
  • 4
  • 15
  • Does your table have a primary key? – Mark Byers May 21 '12 at 16:19
  • Not currently, would need to create a new column as id can have duplicate values. – Jona May 21 '12 at 16:32
  • 1
    Wow... very misleading name for a column that can both be null and contain duplicates. I'm glad it's you and not me that has to deal with that mess. – Mark Byers May 21 '12 at 16:42
  • 1
    I'd start with creating primary key, as @Mark suggested, even if you are not going to use it. Otherwise sql engine may get confused identifying which rows to delete. Just for clarification, if ms sql suspect that target linked server not supporting something, it tends to pull all entries on his side, create queries and push them back with 'real' data. So, I guess it is where he got stuck with his duplicate check. – Val Bakhtin May 21 '12 at 16:43
  • Hi both, Have added a primary key and it dosn't seem to have made any difference. The id is actually a studentid that is the many end of a one->many join from the students table. Have updated the question with the new SQL. – Jona May 21 '12 at 16:48
  • Did you check table to see if `pk` was populated? And try including `pk` column in openquery query. – Val Bakhtin May 21 '12 at 17:24
  • And another though - Identity column spec does not define column as Primary key automatically. It just specifies how values are generated. – Val Bakhtin May 21 '12 at 17:31
  • Thanks @ValBakhtin the problem seems to be that I wasn't selecting the primary key in the delete query. – Jona May 22 '12 at 07:47

3 Answers3

3

If you are trying to delete all the rows in the remote table, why not:

DELETE WEB_DB...table_name;

Or if this is something you're doing often, why not put a stored procedure on the other end to call?

You might also try:

EXEC ('delete table_name') AT WEB_DB;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks for your answer, any chance you could like a page showing that form of delete syntax in use on a Linked server, I can't seem to make it work. A stored procedure might be the way to go, but seems like it should work this way. – Jona May 21 '12 at 16:51
  • I don't have any MySQL set up, sorry. It would maybe have to be `WEB_DB.dbname..table_name` or `WEB_DB.dbname.table_name`... – Aaron Bertrand May 21 '12 at 16:58
0

To clear out the table in mysql using openquery, you should try this:

EXEC('TRUNCATE TABLE table_name') AT WEB_DB
ncfuncion
  • 227
  • 1
  • 3
  • 11
0

The syntax below resolved it for me:

EXEC ('delete table_name where a in (1,2,3)') AT linked_server
user1403869
  • 37
  • 2