locked
Getting an error when running OPENROWSET on remote SSMS RRS feed

  • Question

  • Hi!

    I'm getting an error when I execute OPENROWSET command from a remote SQL Server Management studio. But has no problem when directly execute the command on our database server.

    Msg 7303, Level 16, State 1, Line 2

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


    Is there anything I need to enable or configure?

    Thank you in advance for your help!

    Tuesday, June 24, 2014 8:20 AM

All replies

  • Is the excel sheet you're trying to access already open? Also is path used a local path or UNC path? Where is actual excel sheet? Inside a folder in remote server or inside a fileshare?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, June 24, 2014 8:22 AM
  • No one is using the excel file. File is located in one of our servers inside a folder.
    Tuesday, June 24, 2014 8:49 AM
  • Please check the log file for more information. This is only the result of an error, and there should be another report probably on the reason. Usually it comes in pair like:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXXXX" returned message "The Microsoft Jet database engine cannot open the file '\\XXXX\XXXX.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.".
    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 "XXXXX".

    and the importent information in this case is the first report.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Tuesday, June 24, 2014 8:57 AM
    Tuesday, June 24, 2014 8:55 AM
  • No one is using the excel file. File is located in one of our servers inside a folder.
    Ok. And are you sure you're using UNC path in format \\MachineName\drive$\...

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, June 24, 2014 9:00 AM
  • Yes...

    Here it is:

    select * from OpenRowset('Microsoft.Jet.OLEDB.4.0',
                    'Excel 8.0; IMEX=1;Database=\\sysdev\data\data.xls', [data1$])

    This is the exact error: 

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file '\\sysdev\data\data.xls'.  It is already opened exclusively by another user, or you need permission to view its data.".

    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)".



    • Edited by whitesql Tuesday, June 24, 2014 9:56 AM
    Tuesday, June 24, 2014 9:13 AM
  • :-)

    OK, this is the information that we need.

    "It is already opened exclusively by another user, or you need permission to view its data."

    Please check those 2 options:

    * "already opened": at time the connection is ot working try to open the file localy with the excal application. If the file is in used then the excel will promp about this. If it is not give you any error localy then try again from remoe (since the excel might take control if the connection is opened but not in used by remote connection). If you get that this might be the problem pls repeat this several time to make sure.

    * "you need permission": This is much more common situation 

    >> By default, when you use OpenRowSet to remote server it's the SQL Server Service user that needs access to the file. This is why lot of time it work ocaly and not from remote. check the service's user's permission.

    You can change the SQL server to run as a AD user (with the security implications it has) and then give that user access to the file on the network

    >> You are trying to use a share network folder. check thi link:
    http://technet.microsoft.com/en-us/library/ms175915.aspx
    "To use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form"

    >> check this link as well:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac615f7e-a4af-44d9-8bba-8e5c20be3c76/using-credentials-with-openrowset?forum=sqlintegrationservices

    >> make sure that you use the right provider. Are you using 64 bit or 32?


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Tuesday, June 24, 2014 2:14 PM
    Tuesday, June 24, 2014 2:13 PM
  • Check if there's any other applicaton accessing excel file. Even that can cause error as above.

    Also if this happens while executing it from job or from remote location it may be that account used doesnt have access to file path. In that case give the account access to path where excel file is stored and try again. For SQL AGent jobs you will create a proxy account and give it required access.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, June 24, 2014 2:26 PM
  • sp_configure 'Show Advanced Options', 1;
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO
    
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=C:\test.xlsx',[Sheet1$]) 
     
     SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=\\hqdbsp18\f$\test.xlsx',[Sheet1$]) 
     

    Can you try the above code?

    --Prashanth

    Tuesday, June 24, 2014 2:39 PM
  • Thank you very much pituach for your reply. I followed your suggestions regarding checking delegation etc. I can now run openrowset by connecting to one of the instances on our database server but does not work when I use the default instance. I already checked and compared Linked Server properties from each instances and they are the same.  Any ideas what else to check?

    Thank you!

    Monday, June 30, 2014 10:27 AM
  • Hi,

    OK, if i understand you then we pass one problem. Now you can connect to one instance remotely, but not to other one, which is on the same machine. right?

    The problem is with the efault instance in the machine.

    When using openrowset or linked server, I recommend not to use default connection string with dot but to use the full instance name.

    Please try to connect the server using the instance and server name.

    Please post the queries that you use (both of them), and make sure you have read all the above response.

    * we need to get the different between the instances! check the user that start the services as well.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Monday, June 30, 2014 7:59 PM
    Monday, June 30, 2014 7:58 PM
  • This is the error I'm getting when I try to use the default instance 

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

    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)".

    And this is the query I am using.

    Select * from OpenRowset('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0; IMEX=1;Database=\\sysdeveisweb\upload_mdb\Clearanceliability_.xls', [Sheet1$])

    Yes, it works now on named instance. Same machine.

    ** I can connect to the default instance but I cannot run openrowset.

    **Service account is the same for default and named instances.

    Tuesday, July 1, 2014 7:22 AM
  • Hi,

    1. Since the instances are on the same machine, I have to assume that you use 32 bit since Microsoft.Jet is working only on 32 bit for connecting to Excel. Therefore we will continue using the same provider for the other database.
    >> For 64 bit you should use Microsoft.ACE for example.

    2. "IMEX=1" option in the connection string, is just a workaround for column that has both numeric and text data in the spreadsheet. Please remove it by default if you dont need it.

    3. Unfortunately you current error message is to genaral and can be a result of several situations. Please check this link for more information regarding a common reason:
    http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

    4. Please monitor the problem on the remote machine (where the files are) with FileMon when the SQL is executed, and look for the error there (You might get "ACCESS DENIED" in the log).

    5. A nice workaround that can be use if the problem is with the network folder is using a local windows symbolic link (on the machine that you execute the query). But this will help only in specific situation as I mentioned regarding the path.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, July 1, 2014 2:37 PM
  • Hi Pituach,

    Yes, database server is 32 bit. My laptop is 64 bit where SSMS is installed accessing our db server.

    I have visited that link several times. I checked  the TEMP folder etc. and tried every fix I found on the Internet. Everyone has access to the folders where the files are located. I tried running process monitor and did not find access denied. I tried relocating the files to a different server and I was able to access it by running openrowset on named instance but getting that error when I use the default.

    Wednesday, July 2, 2014 7:57 AM
  • I just noticed that FILESTREAM is enabled in the default instance. Maybe it has something to do with getting this error? Thanks!
    Wednesday, July 2, 2014 11:05 AM