Link to home
Start Free TrialLog in
Avatar of Box-It
Box-ItFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "".

Hi,
 
We have just moved our primary SQL server to a new server (SQL 2012  Standard). I did this via log shipping and then failing over to the new server so it became the primary as due to the size of the database and time restiction of when the move could be made I chose log shipping to limit any down time.
 
Everything went to plan with one exception. One of the linked servers we use from the server to a SQL 2000 server using a ODBC connection is not working when being called upon in stored procedures. You can see the catalogs and run queries on it but we are getting the below error's. I have googled for 4 days and tried various things but to no avail. Part of me thinks this is due to double hop?
 
The DBA before me used synonyms and did not document any of the work he did when he first set up the original server so this has been a nightmare. I have ensured that all logins etc... have the same permissions as they did on the old server but I still cannot get this to work.
 
When we use a try catch in the stored procedures being called it brings back "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "". Error Number 7303.
 
But when they are using the linked server from their end (they use a linked server on the SQL 2000 server, it is then executing part of the stored procedures on the SQL 2012 server but when it goes back out to reference the SQL 2000 linked server it brings the below error).
 
"Mail (Id: 1078) queued. OLE DB provider "MSDASQL" for linked server "Server" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server] cannot open user default database. Login failed.". OLE DB provider "MSDASQL" for linked server "Server" returned message ""[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open user default databse. Login failed.". String data, right truncation, string data, right truncation"
 
Any help would be greatly appreciated as everything I have found so far has not helped.
 
Regards
 
Jordan
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

You need the native client for SQL2008 (it can speak SQL2000'ish).

look at: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2e02c603-e28d-49eb-b073-548c59732b5d/linked-server-from-sql2012-to-sql2000?forum=sqlsetupandupgrade

Download the native client and use the sp_addlinked server script. You'll be fine.


Regards Marten
PS SQL 2000 hasn't been supported for nearly 9 years, time to do something about that application maybe.
(http://support.microsoft.com/lifecycle/default.aspx?LN=en-us&p1=2852&x=15&y=16)
Avatar of Box-It

ASKER

Hi Marten,
Thank you for your response.

I have the native client for 2008 installed. The linked server works without issue. It is only when it is used in a stored procedure that goes from SQL 2000 to 2012 via a linked server and then back to 2000 via another linked server that the error occurs.

The linked server works without issue otherwise.

I have the linked server pointing to a DSN. I have however experimented by setting the linked server to use SQLNCLI10 and point it to the server. This however means that I need to use distributed transactions. As the linked server was working without this previously on the old server I am reluctant to change it.

We are looking to move away from 2000 but we cannot quickly so I need to resolve the current issue until we are ready to move to a new server and away from SQL 2000 finally.

Thanks
Avatar of Box-It

ASKER

Have you heard of any issues like this in regard to the version of the driver? I have now seen that my colleague before me used the 32 bit SQLNCLI10 client and not the 64 bit version.

I have the 64 bit SQLNCLI10 client installed.
ASKER CERTIFIED SOLUTION
Avatar of Box-It
Box-It
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Box-It

ASKER

I have had next to no responses and have put a work around in place which has resolved the issue although not ideally as I would have liked to have kept the new server exactly like the old one but for now all is working. I have not seen anyone mention what I have done to bypass the error so there is no one else I can give the points to.