Post reply

Opendatasource problems with excel

  • September 2, 2008 at 9:49 am

    #192219

    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

  • September 2, 2008 at 12:22 pm

    #866131

    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

  • September 3, 2008 at 9:16 am

    #866552

    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

  • September 3, 2008 at 6:57 pm

    #866902

    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)

  • September 4, 2008 at 7:45 am

    #867142

    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

  • September 5, 2008 at 6:30 pm

    #868086

    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)

  • September 8, 2008 at 11:47 am

    #868635

    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

  • September 8, 2008 at 1:00 pm

    #868684

    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

  • September 8, 2008 at 1:28 pm

    #868694

    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

  • September 8, 2008 at 1:33 pm

    #868697

    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?

  • September 8, 2008 at 1:47 pm

    #868705

    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

  • September 8, 2008 at 1:53 pm

    #868707

    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.

  • September 9, 2008 at 7:23 am

    #869038

    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

  • September 9, 2008 at 8:55 am

    #869102

    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.

  • September 9, 2008 at 9:19 am

    #869124

    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