Leeland I get this error:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST2".
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...
EXEC sp_addlinkedserver
@server = 'TEST2', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = 'DSNTEST1', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=DSNTEST1;UID= TestUser;PWD=TEST;' -- ODBC Connection String.