HOWTO: Setup SQL Server Linked Server to MySQL

We tries this but are getting an error you have not mentioned.

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider “MSDASQL” for linked server “test3″ reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “test3″.

Any ideas?

Thanks,

this was very helpfull and worked great.

EricD

@Greg

I would check two things:
1. Test the ODBC connection you setup in the MySQL Connector. Just hit the “Test” button and verify that the connection succeeds.
2. Copy and paste the “Data Source Name” from the connector to the Linked Server Data Source to ensure you have no typos.

If you still have a problem, try expanding the Linked Server and drilling down through the catalog and tables to see if you have access.

Let me know how that goes for you. If all else fails, you can still try and specify an absolute connection string.

I had the same error. The problem was solved by installing the 5.1 driver and using connection string, without ODBC. Connect through ODBC I have not succeeded, although Access takes the data source.

[...] Configure your MySQL Server as a Linked Server in SSMS. Plenty of guides on the net about this so I won’t repeat it here. Here’s good one. [...]

Thanks for this post – Very useful!

works great, even on a 64bits 2003 Server with SQL Server 2008
Thanks a lot!

Do you have issues like “Unable to begin a distributed transaction” using ON INSERT triggers? If not, how did you solved this problem?

Thank you in advantage.

Thanks for the post. Had an issue with ODBC recognition…we’re MS SQL Server 2008, 64-bit but I had to configure the 32-bit ODBC (was not recognizing 64-bit).

Issue I have now – Linked server succeeds but I cannot see any of the tables (literally, from Mgmt Studio when I try to branch out the Table listing, none appear). When I try to query, object does not exist. I don’t think this is a priv issue on my mysql db – but not sure how to get tables to appear.

Any advice appreciated

Thanks for this how-to.
Maybe you can help me with an issue I am facing.
I have established a link between MS SQL 2000 and MySQL. I have successfully queried the MySQL database from MS SQL Analyzer.

Now I would like to store passwords on the MySQL table using the MySQL “AES_ENCRYPT()” function. (using MS SQL Analyzer) Can this be done? and if so how?

>2. Pass the plaintext to a stored procedure on MySQL and have that proc encrypt it locally.

Any thoughts on how to pull this off?

@Troy

It seems like it can’t even find the MySQL server. Ensure that traditional connections work before trying something un-interactive as Linked Servers. MySQL Query browser would be a good place to start.

My guess is that outside connections to the hosted MySQL instance are blocked by default.

I keep running into a funny issue with my Linked Server setup. If I have the “Don’t cache results” and “Force forward only cursors” options checked, any time I run an insert I get:
“OLE DB provider “MSDASQL” for linked server “test” returned message “[MySQL][ODBC 3.51 Driver][mysqld-5.0.77-log]Commands out of sync; you can’t run this command now”.”

I can run the inserts without these options checked but after some time the MySQL server returns an “Out of Memory” error (which I imagine is do to it caching results). Anyone have some insights into this?

@Dustin, in my experience that error usually has to do with an invalid date in the MySQL DB. MySQL allows for some non-existent dates such as 0000-00-000, 1999-00-00 or 2010-01-00, all of which SQL Server will choke on. Also, T-SQL datetime limits it to dates after year 1753, though this shouldn’t matter in most cases.

Nevermind – I found the issue. I needed to install the following hotfix from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en#filelist

I’m trying to link a MYSQL database (held on a LINUX server) as a linked server through SQL Server 2005.

I have installed all the relevant software – ODBC MYQL drivers (latest version) and have set up an ODBC DNS which seems to connect with no problems. I have also set up the linked server which enables me to see the MYSQL database and expand to the tables, however i can’t see any tables, the folder is empty.

This seems to me like its a priovelege issue within MYSQL, howevere all the priveleges seem okay and look like they are setup to see SQL server 2005 user.

Can anyone shed any ligtht on this?

Could it be something to do with it being on a LINUX server?

Thanks in advance.

I used this process and it worked great – however the only problem that I had was when I would run a query through MS SQL it would crash. This was due to the 32-bit overflow from MS SQL. To solve it follow this process in “Flag 3″ – Checked the option “Limit Column Size to 32-bit range”

Hope this helps anyone that is having problems. Thanks again for the great article

Thank you very much for this article! I was really struggling until I found your notes. I was able to connect MySQL 5.1.41 to SQL Server Express 2008 R2 succesfully on WindowsXP using your method.

When I do a right click to create a Linked Server in sql 2008 in the :- Provider drop down box I do NOT see a selection for :- Microsoft OLE DB Provider for ODBC Drivers.

Would be grateful if some one could please tell me why this might be

Regards

Ghost

Sir , After configuring linked server, when i execute the query then system shows the error message. Please guide me.

OLE DB provider “SQLNCLI” for linked server “Jas” returned message “Invalid authorization specification”.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI” for linked server “Jas” reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “SQLNCLI” for linked server “Jas”.

If you’re on a 64-bit OS, did you run the 32-bit version of ODBC applet? Run: odbcad32

USE master
GO
– To use named parameters: Add linked server in the source (Local machine – eg: MachineName or LocalSeverLoginName)

sp_addlinkedserver
@server = N’LnkSrv_RemoteServer_TEST’,
@srvproduct=N”, — Leave it blank when its not ‘SQL Server’
@provider=N’SQLNCLI’, — see notes
@datasrc=N’RemoteServerName’,
@provstr=N’UID=sa;PWD=sa;’
–,@catalog = N’MYDATABASE’ eg: pubs
GO

/*
Note:
To check provider name use the folling query in the destination server
Select Provider From sys.servers
*/
———————————————————————————————————-
– Optional
–EXEC sp_addlinkedsrvlogin ‘LnkSrv_RemoteServer_TEST’, ‘true’ — (self is true) — for LocalSeverLoginName
–GO

– Remote login
sp_addlinkedsrvlogin
@rmtsrvname = ‘LnkSrv_RemoteServer_TEST’,
@useself = ‘False’,
@rmtuser = ‘sa’,
@rmtpassword = ‘sa’
GO

– OR
/*
IF the above add linked server login failed then try in the Linked Server (LnkSrv_RemoteServer_TEST) Property
Select -> Security – > ‘For a login not defined in the list above, Connection will:’

Choose – > Be made using this security context
SET Remote login: sa
With password: sa
*/
———————————————————————————————————-

– Test server connection
declare @srvr nvarchar(128), @retval int;
set @srvr = ‘LnkSrv_RemoteServer_TEST’;
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval 0
raiserror(‘Unable to connect to server. This operation will be tried later!’, 16, 2 );

– OR

BEGIN TRY
EXEC sp_testlinkedserver N’LnkSrv_RemoteServer_TEST’;
END TRY
BEGIN CATCH
PRINT ‘Linked Server not available’;
RETURN;
END CATCH
———————————————————————————————————-

– Get access linked server database
SET xact_abort ON
GO

BEGIN TRANSACTION
SELECT * FROM LnkSrv_RemoteServer_TEST.DBName.dbo.tblName
COMMIT TRAN
GO

– OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, ‘SELECT * FROM DBName.dbo.tblName’)
GO

– OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, ‘SELECT * FROM sys.databases Order by name’)
GO
———————————————————————————————————-

Very helpful. Thanks

Great article, I had used a similar article to setup SQL Server 2008 linked server to MySQL with MySQL Connector 5.1.6

Connection works great.

Queries work great.

Setup a Stored Procedure that populates a temporary table for reporting, works great.

Running reports from dev environment in Visual Studio works great.

Deploying and executing the report from Report Manager on the Report Server returns a generic message:

An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset ‘spUMGAudioStreetdateCheckDataSet’.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Running the report from the Web Service gives a little more info:

An error occurred during client rendering.
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘spUMGAudioStreetdateCheckDataSet’. (rsErrorExecutingCommand)
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQLPROD”.

Any ideas? I’ve got the MySQL Connector installed and the System DSN configured on both the SQL Server and Web Server. Testing the connection works fine in both places.

I’ve checked the MSDASQL properties as mentioned in this article. The only problem I have is trying to run a report from Reporting Services in Report Manager. Permissions are set the same as all other reports I am able to execute.

Cheers for any feedback!

[...] This week I had to create a linked server to a MySQL database on SQL Express (development environment)….whoohooo!…NOT But ok, it’s possible. Found this step-by-step blogpost. [...]

I’m desperate. Anyone still there?

I had a linked server to mysql working perfectly. I ran a big job to export data from SQL Server to mysql at 3 am this morning. Went to bed and this morning got up to check on it. some data was missing. Started to troubleshoot problem and my linked server no longer works. Everyone in my office swears they didn’t do anything to the machine.

I’m getting OLE DB provider “MSDASQL” for linked server “REDMINE” returned message “[Microsoft][ODBC Driver Manager] Invalid connection string attribute”

but if I go to my ODBC driver and click test connection it works perfectly.

I tried recreating another ODBC driver and another linked server. ODBC driver works, but linked server does not. Only settings I made to linked server is
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: redmine
Data source: redmine

My system ODBC driver data source name is redmine

I’ve googled the h. e. double toothpicks out of this thing and can’t find anything. I enabled ODBC driver debug logging and the logging doesn’t show anything.

@Tom Did you double-check the Provider configuration to ensure it didn’t get reset? Those settings stand for all links of that type, so creating a new Linked Server wouldn’t fix anything if this were the problem.

As an aside, I did see that some types of tables still get the ‘commands out of sync’ error.

Dustin was right about the date fields – the queries work now on all except those with empty date fields in my remote MySQL db. If you do a select on such a table, you may get the same error as me:

“An unexpected NULL value was returned for column [...]‘

An insert on this table always produces some sort of error — generally the ‘out of sync’ error.

Excellent work…. thumbs Up thank a lot.

Just doing:
Run > cmd > obdcad32

Didn’t get ‘er done…

I had to open 32 bit odbc connector by entering this in cmd:
C:\Windows\SysWOW64\odbcad32.exe

bx,
i hope you realized that you have a typo in your statement.

it is ‘odbcad32′ not ‘obdcad32′, and even then you could simply just go Programs -> Administrative Tools -> Data Sources (ODBC) to access your panel.

————————————–
Response:
bx

July 15th, 2011 at 10:20 am

Just doing:
Run > cmd > obdcad32

Didn’t get ‘er done…

I had to open 32 bit odbc connector by entering this in cmd:
C:\Windows\SysWOW64\odbcad32.exe

————————————–

Taylor, Hoping to get some help from you. I am good all the way up to setting the ODBC driver and with test connection successful. But when i try to Create Linked Server to MySQL, I got this error:
The OLE DB Provider “SQLNCLI10″ for linked Server “MYSQLAPP” reported an error.
Cannot initialize the data source object of OLE DB provider “SQLNCLI10″ for linked server “MYSQLAPP”
OLE DB Provider “SQLNCLI10″ for linked server “MYSQLAPP” returned message “invalid authorization sepcification”. )Microsoft SQL Server Error 7399)

I am using SQLEXPRESS 2008 on my laptop and window authentication for password. There is no other user.
Any suggestion? thanks

Hello,

I find a solution to this, and I put it to my Blog:
http://blog.danielcosta.pt/?p=492

There is a problem using this driver, and with semicolon separated files.

Hope this help!

Daniel Costa

I have been able to set up the ODBC data sourse(s), and they test OK. I have been able to configure the MSDASQL provider as indicated, and I have been able to create the linked server. I can open the catalog, database, and see the table names.

When I try to do a select, however, I just get errors:

Select * from LinkSrv1.DB1..table1

gives Msg 7399 …error. provider did not give info, and
Msg 7312, Invalid use of shema or catalog. Four part name supplied, but the provider does not expose the needed interface to use catalog or schema.

…

Select * from openquery(LinkSrv1, ‘Select * from LinkSrv1.DB1..table1′)

gives

OLE DB provider “MSDASQL” for linked server returned message … You have an error in your SQL syntax; … check for syntax near ..table1

and

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider …

AND if I try Select * from LinkSrv1.DB1.table1

I get Msg 208 Invalid object name…

Any ideas?? All help is greatly appreciated.

@Taylor : Thanks alot, it was so helpful and quick.