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
Box-ItAsked:
Who is Participating?
 
Box-ItConnect With a Mentor Author Commented:
Hi,

This is not fully resolved but I have put in a work around. I have been unable to negate this error from occurring.

I have bypassed any possibility of double hop or permissions (although the permissions all look identical) by using SQL authentication.

I have set up a SQL login on both servers and used this in the linked server. I was reluctant to do this as I wanted to mirror the old server but I have done this as an interim to get it working. I will investigate further and resolve the issue and add a post once fully resolved rather than using a workaround which is far from ideal.
Many thanks for your comments

Regards

Jordan
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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)
0
 
Box-ItAuthor Commented:
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
0
 
Box-ItAuthor Commented:
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.
0
 
Box-ItAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.