0

I am trying to learn openquery. I have MSSQL server that has a linked server MySQL_testing. Now I have been fighting with the openquery syntax all morning and figured here are probably much smarter people who know how it works.

So what is the working syntax for the following 

  INSERT INTO OPENQUERY
(test_MYSQL, 'INSERT INTO test.catalog_category_product VALUES (1, 1, 1)')

The error is

The OLE DB provider "MSDASQL" for linked server indicates that either the object has no columns or the current user does not have permissions on that object.

I checked that the "root" user on xx.xxx.xxx.xxx has insert, update, etc rights

What could cause this?

Thank you in advance!

user1054844
  • 922
  • 5
  • 17
  • 34
  • It's not clear what are you trying to do. Do you want to insert data into MSSQL from MySQL_testing, or vice versa? – Tomas Jun 05 '14 at 11:28

1 Answers1

0

You need to move the 'VALUES' clause out of your open query and select the 'COLUMNS' you want to insert into, see example below

INSERT OPENQUERY(test_MYSQL, 'SELECT COLUMN1, COLUMN2, COLUMN2 FROM test.catalog_category_product')
VALUES (1, 1, 1)

Here is the article on OPENQUERY

Hope this helps

Geewers
  • 206
  • 1
  • 8