April 8, 2014 at 3:07 pm
Hi,
I want to set up a Linked Server between my mssql 2014 and a MySQL.
I set up the system dsn in odbc, the test result says here "Connection Successful":
If i set up the Linked Server, i always get the following Error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [67].
OLE DB provider "SQLNCLI11" for linked server "MYSQL" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "MYSQL" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 67)
------------------------------
Anyone any idea what could the problem be?
April 9, 2014 at 4:49 am
That error looks like it's trying to use SQL Native Client to connect, which is the wrong provider.
What value are you supplying for the @provider parameter when creating the linked server?
Or are you using the GUI - in which case is the radio button on SQL Server or Other Data Source?
Thanks
April 9, 2014 at 10:17 am
I used Microsoft OLE DB Provider for ODBC Drivers
April 10, 2014 at 3:10 am
Does the test connection work when creating the linked server?
Note you won't be able to connect the query window directly to the MySQL instance, you need to reference it from the SQL instance the linked server is created on using OPENQUERY.
April 13, 2014 at 2:14 am
Hi...
Please let us know your prob has resolved or not
April 13, 2014 at 9:15 am
the connection test is working for ODBC, but not for the linked server.
I was not able to fix it so far.
Instead I use a workaround in PHP, connecting to both databases.
I'm still hoping for a solution.
April 29, 2014 at 3:48 pm
Anyone find a solution for this other than work around?
Have SQL2014 Express installed trying to link server to a SQL 2008 Std. Both on 64-bit machines (SQL2014 Exp is Windows 7 and SQL2012 Std is Window2008R2 server). Lots of errors for the provider no matter what one I use. Downloaded and installed SQLNCLI10 and SQLNCLI11.
Tried an ODBC connection and that works fine.
Error is:
Cannot create an instanced of OLE DB provider "SQLNCLI11" for linked server xxxxx/xxxxx.
the provider designation changes for each one I try but the error continues.
This is perplexing to say the least.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply