Solved

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

Posted on 2016-07-15
5
64 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
5 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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