Insert into MySQL table with an autoincrement ID using SQL Server Linked Server Openquery

barkome
barkome used Ask the Experts™
on
Hello I'm trying to insert records into a MySQL DB table, and i get the following error message.

The MySQL db table has an auto-increment ID column

OLE DB provider "MSDASQL" for linked server "test_db" returned message "Query cannot be updated because it contains no searchable columns to use as a key.".
Msg 7343, Level 16, State 2, Line 5
The OLE DB provider "MSDASQL" for linked server "test_db" could not INSERT INTO table "[MSDASQL]".

The query im using is:

INSERT INTO OPENQUERY (test_db, 'SELECT id, name, create_date, create_user_id FROM testers')
VALUES (0, 'tester_a', getdate(), 'sqlagent')

Open in new window


can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
F IgorDeveloper

Commented:
Do not use values for auto-increment fields, they are filled automatically by mysql.
If "id" is the auto-increment field you could put:

INSERT INTO OPENQUERY (test_db, 'SELECT name, create_date, create_user_id FROM testers')
VALUES ( 'tester_a', getdate(), 'sqlagent')

Open in new window


Why using 0 as value? By default, mysql cannot insert an autoincrement value of 0.
Try changing to a different value (1) if you nned to put a fixed value, but cant be duplicated

Author

Commented:
Tried that and still got the same error message.
F IgorDeveloper

Commented:
Things to try and check:

Some field in your sql data could be in a unexpected format,for example the date field "create_date", the format from getdate() must match the format mysql expects to receive as a date field, try using a date format function (output formay 'yyyy-mm-dd' instead of raw getdate() result.

The mysql table probably needs a unique key to let the sql provider check if that row exists before insert (Is any of the fields, or a couple of them, a unique combination?, create an unique index for this columns, eg: name and  create_date)
Commented:
Turned out that the wrong ODBC driver was installed....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial