Openquery insert not working

Question

I have a linked MySQL -server to MSSQL-server and I am trying to INSERT data to the table admin_user on the MySQL -server, but end up getting the error:

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

This works fine:

SELECT * FROM openquery([MYDB], 'SELECT * FROM admin_user')

This gets the error:

INSERT into openquery([MYDB], 'dbo.admin_user') values ('Testi','Testaaja','me@google.com','koe','','','','','','1','N;','','')

Here are the rights of the user whom I used for creating the ODBC-connection

| xx.xxx.xxx.xx | me | *qweqweqwdq2edqdadasd| Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y | Y | | | |
| 0 | 0 | 0 | 0 | | NULL |
>     | %             | me | *asdasadasdsadasdasdsad| Y           | Y           | Y           | Y           | Y           |
> Y         | Y           | Y             | Y            | Y         | Y
> | Y               | Y          | Y          | Y            | Y        
> | Y                     | Y                | Y            | Y         
> | Y                | Y                | Y              | Y            
> | Y                  | Y                | Y          | Y            |
> Y                      |          |            |             |        
> |             0 |           0 |               0 |                    0
> |        | NULL                  |

My catalog is bitnami_magento, I have the provider string configured with

DRIVER=(MySQL ODBC 5.3 ANSI Driver); SERVER= XX.XXX.XXX.XXX;PORT=3306;DATABASE=bitnami_magento; USER=me;PASSWORD=mypass;OPTION=3;

Also I have unchecked the "Level zero only" box from Provider Options (MSDASQL) and made sure that ad_hoc queries are allowed. What I am doing wrong?

There are the instructions that I followed http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

I tried inserting as root@localhost from MySQL -server and it worked fine. User me has all the rights so I do not know why it can not insert with openquery — user1054844, Jun 12 '14 at 09:05

score 1 · Answer 1 · answered Jun 12 '14 at 09:45

1

You have an error in your query:

In the OPENQUERY() you have to use the MySQL table name instead of the MSSQL one (if you want to insert into the MySQL table).

The following syntax should work

INSERT INTO OPENQUERY([MYDB], 'SELECT * FROM mysqlDbName.mysqlTableName') VALUES
('Testi','Testaaja','me@google.com','koe','','','','','','1','N;','','')

Please change the mysqlDbName.mysqlTableName to you MySQL database and table name accordingly.

answered Jun 12 '14 at 09:45

Pred

8,789
3
26
46

Actually you just need the MYDB (with or without brackets), since it is the name of linked database defined in Linked server properties as "Linked server:" the provider string will take care that you will use the "right" database – user1054844 Jun 12 '14 at 10:00
Thats true, this is the full syntax. Also please note, that I added the `SELECT * FROM` part to the `OPENQUERY` – Pred Jun 12 '14 at 10:02
1

Your query still needs the dummy where clause otherwise it will be slow, since it will get a result set. You should set it so that it will not get any results e.g where 1=0 – user1054844 Jun 12 '14 at 10:26

score 1 · Answer 2 · answered Jun 12 '14 at 09:55

The problem was I am an idiot. The syntax for Openquery expects a result set to be returned.

So it apparently needs a "dummy query" to be incorporated as a part of the actual query so it will get the result set in response. Writing "where 1=0" makes the query faster as it will not get any actual results in response.

Working example:

 insert openquery(MYDB, 'select firstname from admin_user where 1=0') values ('3','Testi','Testaaja','me@google.com','koe12','koe22','','','','0','0','1','','','')

OpenQuery requires a result set to be returned, but UPDATE, DELETE, and INSERT statements that are used with OpenQuery do not return a result set.

http://support.microsoft.com/kb/270119/fi

Openquery insert not working

2 Answers2