Opendatasource problems with excel

  • I have been working on getting opendatasource to work with excel. I have just been using select just to get the ole db thing working before I move on to updates.

    Here is what I have come up with:

    select * from opendatasource('Microsoft.Jet.OLEDB.4.0',

    'DataSource=C:\PIDExcelTest\6322done.xls;

    user id=Admin;password=;Extended properties=Excel 5.0')...[6322missing$]

    Here is the error I am getting:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    I am not sure what is causing that error. Any help would be greatly appreciated.

    I am using excel 2002 and sqlserver express 2005.

    Thanks you.

    Quinn

  • I found this thread using your error message:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=815084&SiteID=1

    See if that has what you need. If not, copy the error into a search string and go from there. That's what I always do, and it almost always works.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for the link.

    There was nothing there that seemed to stand out as being a problem with my connection. The version of msexcl40.dll is the same on both machines.

    I am assuming that they are installed correctly.

    Thanks

    Quinn

  • Heh... I've seen lot's of folks do it... they sit there with the spreadsheet open and try to run the code to populate. That will give such an error.

    Also, you have to enable the server to do "ad hoc" queries. There's a registry entry that must be changed for that... I don't remember what the entry is, but I do remember it was a fairly easy Google.

    Also, the server must have privs to C:\Temp because it will use some of that directory to do some of it's work. By default, the server doesn't have such access, if I recall correctly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the help.

    I enabled ad-hoc queries from surface area configuration. Nobody is in the excel ss.

    As far as server permissions to c:\temp do you mean the SQL Server of the server that I am getting the data from.

    Thanks

    Quinn

  • I mean C:\temp on the SQL Server that's doing the reading.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff

    I am assuming that sqlserver have access to c:\temp sence the account that SQL is running on is administrator and adminitrator have full access.

    Thanks.

    Quinn

  • Shouldn't it be Excel 8.0 for Excel 2002 in extended properties.

    Also, MS recommends using OPENDATASOURCE for only infrequent access. Did you try linked server instead?

    http://support.microsoft.com/kb/321686

  • Glen

    I have already changed it to 8.0 instead of 5.0.

    I have tried using a linked server and got the same error. I have 50 excel spreadsheets that need to be updated from SQLServer. I was looking at the book SQLServer 2005 bible where they discuss accessing excel using opendatasource. I was thinking this could be the solution to my problem. If there is a better way to get that done I am open to other suggestions. I also tried openrowsource and got the same error.

    Thanks

    Quinn

  • One of your previous postings saying that you have two servers?

    Does it mean that your Excel file located on a server different from the server where SQL server is located?

  • Glen,

    I have tried it on another server and on my hard drive on my desktop but not on the server that SQLServer is on.

    Does it nead to be there?

    Thanks

    Quinn

  • That is a statement you are using:

    select * from opendatasource('Microsoft.Jet.OLEDB.4.0',

    'DataSource=C:\PIDExcelTest\6322done.xls;

    user id=Admin;password=;Extended properties=Excel 5.0')...[6322missing$]

    Data Source is pointing to a C:\ drive. I am assuming that you are running this statement on the computer where you SQL server is installed?

    Then you are saying to SQL server that you Excel file is located on the C:\ drive of a server running SQL server. Is it correct?

    If not, then you have to try either copying your Excel file in the specified folder on your SQL server (just for testing) and then changing the DataSource location to an appropriate value pointing to the real Excel file location.

  • Glen

    I had the file on a second server and then moved it to my PC which is the C:\. After your post I was thinking maybe the SQL server didn't know where the file actually was because one of them was a mapped drive to my machine and the other my local hard drive. So I moved the file onto the SQLServer and used SSMS express on the server to run the same command from the server with the excel spreadsheet in a directory on that server. I got the same errror.

    Thanks

    Quinn

  • Can you please try replacing this:

    select * from opendatasource('Microsoft.Jet.OLEDB.4.0',

    'DataSource=C:\PIDExcelTest\6322done.xls;

    user id=Admin;password=;Extended properties=Excel 5.0')...[6322missing$]

    with this:

    sp_addlinkedserver EXCEL,

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'C:\PIDExcelTest\6322done.xls',

    NULL,

    'Excel 5.0;'

    After above statement is executed try:

    Select * from EXCEL...[6322missing$]

    If you will receive error messages, paste them in here.

  • There wasn't any error for the first one and here is the error for the second commands.

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" does not contain the table "6322missing$". The table either does not exist or the current user does not have permissions on that table.

    Thanks

    Quinn

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply