Laura_SqlNovice (7/26/2012)
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.
You created the DSN both 32 bit and x64? (not sure if it was totally needed...I did it anyway)...
One other thing...you made them as System DSN's vs User DSN's correct?
When inside the Create New data source menu make sure you choose the 6.01.xxx version named "SQL Server"
You tested that account login and password on the 2000 server? case sensitive?
I seem to recall getting a similar error and I basically wiped everything off in regards to DSN's and Linked servers and started from scratch with the steps listed above...
I would say to start from square 1, won't take that long to go in and recreate the dsn's (are you using that account listed above when you are configuring the DSN?).
Then configure the script to add the linked server and then the linked server login...
let me know if it helps