Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.
Solved
Insert into MySQL table with an autoincrement ID using SQL Server Linked Server Openquery
Posted on 2016-07-15
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:
can anyone help?
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')
can anyone help?
[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
Do not use values for auto-increment fields, they are filled automatically by mysql.
If "id" is the auto-increment field you could put:
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
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')
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
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)
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)
Featured Post
![[Webinar] Lessons on Recovering from Petya](https://filedb.experts-exchange.com/files/public/2017/7/11/dcb3272a-62a8-4274-a34e-d1771610ec3b.png)
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 ClickHouse performs in a general analytical workload using the star schema benchmark test.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll
656 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.