Special statements with Openquery

0
votes

I would like to transfer data from a MS SQL Server database to a MySQL database. So, I added a linked server to MS SQL so that I can use Openquery to insert the data in the MySQL database. I want to optimize the performance of the data transfer and I found the guidelines for improving performance of data loading in MySQL.

One optimization consists of disabling AUTOCOMMIT mode, however I was not able to do it using Openquery.

I tried both ways:

SELECT * from openquery(MYSQL,'SET autocommit=0') 

exec openquery(MYSQL,'SET autocommit=0')

and I got:

Cannot process the object "SET autocommit=0". The OLE DB provider "MSDASQL" for linked server "MYSQL" indicates that either the object has no columns or the current user does not have permissions on that object.

Is it possible to execute such statements through openquery?

Thanks, Mickael

2
mysqlsql-serveropenquery

2 Answers

1
votes

OPENDATASOURCE() and OPENROWSET() allow for add-hoc server connections. You do not need to define a linked server ahead of time.

The OPENQUERY() depends upon a static linked server being defined ahead of time.

Here is the MSDN reference.

http://technet.microsoft.com/en-us/library/ms188427.aspx

Most of the examples show a DML (SELECT, UPDATE, DELETE, INSERT) using the OPENQUERY() as the source or destination of the command. What you are trying to do is execute a session command. Therefore it will fail. Also, you might not even know if the session stays open for the next call.

Why not package up the logic on the MYSQL server as a stored procedure. The stored procedure can be executed on a linked server by using a four-part name?

For example:

INSERT INTO #results EXEC server.database..stored-proc

This assumes MYSQL has the same object structure as ORACLE. Since I am not a MYSQL person, I can not comment. I allow you to research this little item.

But this should work. It will allow you to package any type of logic in the MYSQL database.

0
votes

If you want to use SSIS to transfer data from SQL Server to MYSQL.

For the ADO.NET Destination to work properly, the MySQL database needs to have the ANSI_QUOTES SQL_MODE option enabled. This option can be enabled globally, or for a particular session. To enable it for a single session:

1 - Create an ADO.NET Connection Manager which uses the ODBC driver
2 - Set the connection manager’s RetainSameConnection property to True
3 - Add an Execute SQL Task before your data flow to set the SQL_MODE – Ex. set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'
4 - Make sure that your Execute SQL Task and your ADO.NET Destination are using the same connection manager.

Matt Mason did this on a reply.. The key is item #2, use the same connection.

http://blogs.msdn.com/b/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx#comments

Also, CozyRoc has a custom ODBC driver that might be faster / more reliable than the free one from MYSQL.

http://cozyroc.com/ssis/odbc-destination