Solved

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

Posted on 2013-12-12
5
6,596 Views
Last Modified: 2013-12-21
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
0
Comment
Question by:Box-It
  • 4
5 Comments
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
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
 

Author Comment

by:Box-It
Comment Utility
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
 

Author Comment

by:Box-It
Comment Utility
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
 

Accepted Solution

by:
Box-It earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:Box-It
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now