Linked Server in SQL 2008 on Windows Server 2008
Hello
I have Local MS SQL Server which I need to link to a remote MySql Server.
I have a Linked server set up on my local PC linking the Local SQL to the remote MySql server.
We have installed a Windows Server running the SQL Server and now I can not get the Linked Server to connect.
The Error I get is "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTWEB".
OLE DB provider "MSDASQL" for linked server "TESTWEB" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"
When I test the System DSN under Data Sources it tests fine.
I have set up these linked servers EXACTLY the same but the one on the local PC will connect and the one on the Windows Server refuses to.
Any Ideas??
I have Local MS SQL Server which I need to link to a remote MySql Server.
I have a Linked server set up on my local PC linking the Local SQL to the remote MySql server.
We have installed a Windows Server running the SQL Server and now I can not get the Linked Server to connect.
The Error I get is "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTWEB".
OLE DB provider "MSDASQL" for linked server "TESTWEB" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"
When I test the System DSN under Data Sources it tests fine.
I have set up these linked servers EXACTLY the same but the one on the local PC will connect and the one on the Windows Server refuses to.
Any Ideas??
Hi TempDBA,
How and where do you enable openrowset and opendatasource support?
Regards
David
How and where do you enable openrowset and opendatasource support?
Regards
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Look at tonight
@David,
Thanks, I forgot about the removal of surface area configuration in 2008. Thanks again for correcting me.
Thanks, I forgot about the removal of surface area configuration in 2008. Thanks again for correcting me.
Expand Providers -> Right Click MSDASQL -> Select Properties
Enable Nested queries
Enable Level zero only
Enable Allow inprocess
Enable Supports 'Like' operator
In SQL Server Surface Area Configuration:
Enable Local and Remote connections via TCP/IP and named pipes.Enable OPENROWSET and OPENDATASOURCE support.