Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36

    Red face Unanswered: Distributed query issue

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=W:\MyExcel.xls', 'select * from [Sheet1$]')
    which works on my local server with Microsoft office XP Professional installed

    But keep on failing on the QA server which has MDAC installed only, no Microsoft Office installed

    The error msg is 7399. I thought it's because of permission issue.

    But problem still resides after I move the Excel file into that QA NT server and execute the statement with a service account which has sysadmin permission on both NT Server and SQL Server


    What might be te reason?Why it's working perfectly in my Local server.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I'm guessing "W:" is a network drive? When you're not logged in to your server (QA) there are no drive mappings. They are profile specific. If you need to get to a network drive, use the UNC path.

    Ex:
    \\Servername\sharename\foldername\filename.xls
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    W: is not a network drive, I moved Excel file into the NT Server where the QA SQL Server reside.I also made sure that the SQL Server service account is sysadmin for NT Server (which means the service account can access every drive and folder in that Box) and SQL Server (which is sysadmin for SQL server)

    The only difference is for my local server I have Microsoft Office installed.I could double click on Excel file, read and modify it.

    In the NT Server where SQL Server reside.There isnt Microsoft Office installed.Only MDAC installed alone with SQL Server.


    When I use xp_cmdshell and use dir comand I could go to every folder in the NT Server.Which means I dont have access issue.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by ClaireHsu
    W: is not a network drive, I moved Excel file into the NT Server where the QA SQL Server reside.I also made sure that the SQL Server service account is sysadmin for NT Server (which means the service account can access every drive and folder in that Box) and SQL Server (which is sysadmin for SQL server)

    The only difference is for my local server I have Microsoft Office installed.I could double click on Excel file, read and modify it.

    In the NT Server where SQL Server reside.There isnt Microsoft Office installed.Only MDAC installed alone with SQL Server.


    When I use xp_cmdshell and use dir comand I could go to every folder in the NT Server.Which means I dont have access issue.
    Error 7399
    OLE DB provider '%ls' reported an error. %ls

    Cannot start your application. The workgroup information file is missing
    or opened exclusively by another user.

    Explanation
    This error message returned by the Microsoft OLE DB Provider for Jet
    indicates one of the following:

    The Microsoft® Access database is not a secured database and the login
    and password specified was not Admin with no password.


    The Access database is secured and the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Syst emDB registry
    key is not pointing to the correct Access workgroup file. Secured Access
    databases have a corresponding workgroup file, including the full path,
    which should be indicated by the above registry key.
    It's not wise to give your DB account sysadmin access. If someone get's ahold of that username and password, you've compromised your entire SQL Server instance.
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36

    Lack DLL

    Hi....peoblem solved.

    The reason is because of the lack of microsoft office dll files.Even if a NT server doesnt have microsoft office installed.At least it needs to have complete set of dll file installed.The stored proc will be executed in a NT Server which has compelete DLL file installed.Though the Server still doesnt have Excel installed.But everything is working fine now.

    Thanks for your inputs!

Posting Permissions

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