Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-12-12
5
6,903 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
ID: 39720651
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
ID: 39721192
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
ID: 39721451
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
ID: 39723449
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
ID: 39733429
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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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