Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    8

    Question Unanswered: Using OpenDataSource to Open Excel File from SQL

    I am trying to do this within a stored procedure. I have linked the server and am trying to open a password protected Excel spreadsheet with OpenDataSource. I am getting the error "Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed. [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]"

    I am running the following:

    EXEC sp_addlinkedserver 'ExcelSource',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'd:\mssql7\binn\filename.xls',
    NULL,
    'Excel 5.0'
    GO

    SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="d:\mssql7\binn\filename.xls";User ID=;Password=pwd;Extended properties=Excel 5.0')...sheetname$

    There are two passwords for this file; one to unlock the file and one to gain write access. I don't think this is causing the problem (I removed the second one manually and tried this to no avail).

  2. #2
    Join Date
    May 2003
    Posts
    12
    How do you login to SQL Server EM or QA? Try use SQL Server authentication.

  3. #3
    Join Date
    May 2003
    Posts
    8
    Originally posted by Allen_Cui
    How do you login to SQL Server EM or QA? Try use SQL Server authentication.
    I log into EM and QA as SA. I don't know what you mean by "try to use SQL Server authentication".

    Thanks! :-)

  4. #4
    Join Date
    May 2003
    Posts
    12
    [i]There are two passwords for this file; one to unlock the file and one to gain write access. I don't think this is causing the problem (I removed the second one manually and tried this to no avail).
    I think the problem comes from the password. Try to remove all passwords from this Excel file and it should work. I know it is not good solution. Please post back if you find another ways to do it.

  5. #5
    Join Date
    May 2003
    Posts
    8
    The only way that I can remove the password is manually, and this will not work for me. I have a client that sends the file and he cannot send it without a password, per his company policy.

    Is the password in the OpenDataSource command for SQL or for the Excel file? Now I'm getting confused... :-)

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •