0

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

manash
  • 6,985
  • 12
  • 65
  • 125

2 Answers2

1

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.

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
  • Thank you. The problem is that I need to pass a lot of data to the stored procedure, is it possible? I need to insert millions of rows, is it a good idea to pass all this data as parameter to the SP? – manash Jan 20 '14 at 07:52
  • Please write an ETL process. If you own SQL Server, you have SSIS. Write a package. If not, look at powershell. Both solutions will move data efficiently. Linked servers are usually slower than ETL. – CRAFTY DBA Jan 20 '14 at 14:19
  • We already used ETL for other stuffs, however I think the issue is still present with ETL (i.e. no way to change session properties like autocommit). Is that right? – manash Jan 21 '14 at 08:48
  • @mickaelmarrache That is not true. Check out my new addition. – CRAFTY DBA Jan 21 '14 at 13:35
0

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

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
  • Thanks for your answer but I don't use ADO, I use OLE DB for ODBC. Is it still applicable? I don't see the RetainSameConnection property. – manash Jan 21 '14 at 14:20
  • Does not matter. Retain Same Connection is at the connection object. That is key with the SQL Task to execute the environment commands. Read the article. It is centered around MY SQL. Also, CozyRoc provides a better driver. – CRAFTY DBA Jan 21 '14 at 21:18