I'm Trying to create a Linked Server to an OBDC System DSN as Data Source in a SQL server 2008 64bit.
I run this script:
Linked Server is created, but when I test the connection, it returns an error:
I search the books online and found this:
*To create a linked server to access an ODBC database when you are using an ODBC data source: Create a System data source on the computer on which SQL Server is installed. Execute sp_addlinkedserver to create the linked server, specifying MSDASQL or NULL as provider_name, and the name of an ODBC system data source as data_source.*
So, It's any way to register this provider to SQL2008..?
I think you may have run into a 64bit issue. Some linked server drivers have not been updated and you can only create some (sorts of) linked servers on 32 bit SQL Server. I thought it was either Access or Excel (memory fails me and sadly I am about to leave home right now so hopefully someone else can complete the details for you/me.
answered Jun 15 '10 at 01:50 AM
@Fatherjack has the meat of the matter.
64-bit SQL Server requires a 64-bit OLE DB Provider for ODBC (e.g., MSDASQL) and a 64-bit ODBC driver.
32-bit SQL Server requires a 32-bit OLE DB Provider for ODBC, and a 32-bit ODBC driver.
Microsoft has been hot-and-cold on support for their OLE DB Provider for ODBC, so you may want to look into a third-party solution, such as this one from my employer. More details on using this solution are found here.
That said, there are many limitations to SQL Server Linked Server functionality, so you may want to clearly define your goals, and see what other solutions are available....
answered Jun 15 '10 at 12:10 PM