Solved

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

Posted on 2016-07-15
5
143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

705 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