none
Linked Server connection issue

    Question

  • We have a SQL Server 2005 Enterprise Edition set up on Server 2003 Standard Edtion OS and one on Server 2003 OS Enterprise Edition.  All of them have their Service Pack ones installed.  On the 2005 on Standard 2003 OS I can get to linked ODBC connection servers with no problem.  On the 2005 SQL Server on the Enterprise Edition I get

    Msg 7303, Level 16, State 1, Line 1

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

    We are not sure whether or not this is caused by something on the OS or on the SQL SERVER software.  Any help would be appreciated. 

    Allow remote connections is on and so is the SQL Browser Service.

    Wednesday, June 14, 2006 10:59 AM

Answers

All replies

  • I presume it is 32 bit server. There is no 64-bit MSDASQL.

    Can you try using the same parameters which you have for the linked server and try connecting to the server from outside of SQL Server. You can use a new UDL file, or a simple VB script for that. Would it work?

    Wednesday, June 14, 2006 11:16 PM
    Moderator
  • if your linked server EAWARMBKUP is a sql2000 (sp3/sp3a or sp4) you should run instcat.sql on that sql2000 instance.

    check out http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

    also check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381384&SiteID=1 where is stated that in a 64-bit env, "

    You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

    "

    Thursday, June 15, 2006 5:56 AM
  • It is a 32 bit system trying to get to a Sybase database.  I can get to it from another SQL Server 2005 database with no problem but not from the one we just setup.  The one I can get to it from is on a Server 2003 standard edition OS and the new one is on a Server 2003 Enterprise Edition.  We have to use Enterprise Edition because we are going to cluster it in Production.
    Thursday, June 15, 2006 10:30 AM
  • did you enable oledbconnections in your sql2005 surface area config ?
    Thursday, June 15, 2006 10:58 AM
  • I'm not sure how to do that so probably not.
    Thursday, June 15, 2006 11:16 AM
  • Start / programs / Microsoft SQL Server 2005 / configuration tools / SQL Server Surface Area Configuration

    Change computer (to your sql2005 where you have the problems)

    Surface Area Configuration for Features

    Select oledb automation and enable it

    Thursday, June 15, 2006 11:23 AM
  • It is enabled but I get the same message do I need to restart anything?
    Thursday, June 15, 2006 11:44 AM
  • Can you successfully ping the SQL Server machine from the machine where you are executing the linked server query?

    Also you may want to review some of the ideas suggested at http://www.aspfaq.com/show.asp?id=2544 to trouble shoot linked server errors.

    Thanks

    Suroor

    Monday, June 19, 2006 9:33 PM
  • Solution to this problem is to force 'SQL Server Agent' to use an existing windows user account while connecting to a linked server.

    Steps: Services --> SQL Server Agent --> Properties --> Log On --> select 'This account'  and provide it with a valid windows domain account username and password.

    This should help you.

    • Edited by Mahesh Ko Tuesday, June 16, 2009 5:21 PM Perfect Answer
    Thursday, April 16, 2009 3:03 PM
  • hello,

    Basically what I want to do , is get data from the SQLBase server of
    ADP(database - PAY4WIN) in to SQL server database.
    How can I achieve this ? Is this possible at the first place ?

    I was able to test the odbc connection successfully. Was able to
    connect to Pay4Win database thru odbc.
    I created a linked server : I get error "cannot initialize the data
    source object of oledb provider "MSDASQL" for linked server "ADPTest".
    OLE DB provider "MSDASQL" for linked server "ADPTest" returned message
    "[Microsft][ODBC Driver manager] Data source name not found and no
    default driver specified" SQL server error 7303.

    am I missing here anything ? what does this error indicates ?
    thanks

    Monday, November 30, 2009 7:54 PM