?
Solved

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

Posted on 2016-07-15
5
Medium Priority
?
242 Views
Last Modified: 2016-08-28
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?
0
Comment
Question by:barkome
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:F Igor
ID: 41713485
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
0
 

Author Comment

by:barkome
ID: 41713578
Tried that and still got the same error message.
0
 
LVL 13

Expert Comment

by:F Igor
ID: 41716217
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)
0
 

Accepted Solution

by:
barkome earned 0 total points
ID: 41719372
Turned out that the wrong ODBC driver was installed....
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question