locked
Openrowset using ODBC DSN is not working! RRS feed

  • Question

  • Hello,

     

    I am using the following code to retrieve data from an AS400 datasource. I have already setup the DSN connection(TestDSN) which is working just fine and I am able to connect and look at the data by creating linked-table in MS Access.

    SELECT a.* from OPENROWSET('MSDASQL', 'DSN=TestDSN', 'select * from mylibrary.mytable') as a

     

    Now the problem is I need to browse the data from SQL server, but when I use the above TSQL I get the following error message.

     

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

    Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.

     

    Anybody knows how to get this to work?

     

    BTW, I tried MSDASQL provider to access a SQL server and it worked so it does not seem to be a problem with the driver registeration.

     

    Thanks,

    LOA

    Friday, March 14, 2008 6:11 PM

Answers

  • For the past week, I have been seeing the same error

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error


    when trying to use OpenRowset againt an MDW-secured Access97 database from a production server, using a DSN  identical to a DSN on my development PC, where the same OpenRowset query succeeds. I've discovered that the production server lacked a Providers node in the registry:

    HKEY_LOCAL_MACHINE
     Software
      Microsoft
       MSSqlServer
         Providers

    So this morning I created a third DSN (identical to the others) on another test server also running SQL Server 2000. First I checked that the Providers node existed in the registry. It did.  I ran this query:

    select * from
    OPENROWSET('MSDASQL','DSN=MYDSN','select top 10 * from myfile') as F

    and it failed with the error message above.  So I removed the "DSN=" from the second parameter and ran the query again:

    select * from
    OPENROWSET('MSDASQL','MYDSN','select top 10 * from myfile') as F

    and this time it failed with these errors:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error. 
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Invalid connection string attribute]
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].


    Seeing the spate of errors, I restored the "DSN=" and ran the query again:

    select * from
    OPENROWSET('MSDASQL','DSN=MYDSN','select top 10 * from myfile') as F

    and this time  ...  it worked!  I had done nothing to the server in the interim.

    {queue the eery  theremin}

    Regards
    TR



    Friday, March 21, 2008 1:26 PM

All replies

  • For the past week, I have been seeing the same error

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error


    when trying to use OpenRowset againt an MDW-secured Access97 database from a production server, using a DSN  identical to a DSN on my development PC, where the same OpenRowset query succeeds. I've discovered that the production server lacked a Providers node in the registry:

    HKEY_LOCAL_MACHINE
     Software
      Microsoft
       MSSqlServer
         Providers

    So this morning I created a third DSN (identical to the others) on another test server also running SQL Server 2000. First I checked that the Providers node existed in the registry. It did.  I ran this query:

    select * from
    OPENROWSET('MSDASQL','DSN=MYDSN','select top 10 * from myfile') as F

    and it failed with the error message above.  So I removed the "DSN=" from the second parameter and ran the query again:

    select * from
    OPENROWSET('MSDASQL','MYDSN','select top 10 * from myfile') as F

    and this time it failed with these errors:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error. 
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Invalid connection string attribute]
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].


    Seeing the spate of errors, I restored the "DSN=" and ran the query again:

    select * from
    OPENROWSET('MSDASQL','DSN=MYDSN','select top 10 * from myfile') as F

    and this time  ...  it worked!  I had done nothing to the server in the interim.

    {queue the eery  theremin}

    Regards
    TR



    Friday, March 21, 2008 1:26 PM
  • Seems like you fixed the issue with your provider adding the key to the registry, but the DSN credentials was cached until you changed the linked query (to the erroneous DSN and back). Good that it is working now.
    Wednesday, March 26, 2008 12:19 AM
  • Bruno,
    Thanks for the explanation.  But we are still not out of the woods. How are the Provider nodes:

    HKEY_LOCAL_MACHINE
     Software
      Microsoft
       MSSqlServer
         Providers


    created in the registry in the first place?  The production SQL Server 2000 machine has no Provider node (and also none of the individual provider entries beneath it). TheSQL Server 2000 Client Software has been installed on the production server.  What else needs to be installed on the production server?

    The other two machines (my development PC and a test server) do have these provider registry entries.  So far, these are the only two machines where OpenRowset('MSDASQL','MyDSN'....) queries are working.

    Thanks
    TR


    Wednesday, March 26, 2008 12:05 PM