Openrowset and Microsoft.Jet.OLEDB.4.0

  • Hi, I have seen quite a few requests for help on the subject of importing data from Access and Excel and I have followed all the advice given but I am still pulling out what little hair I have left as sod’s law says it will work on all servers apart from the one I want it to.

    As a test I am running the following, which as I say works on every other server apart from this one which is also running SQL 2005 Version 9.00.3042. SP2 Standard Edition.

    select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;database=d:\DataExtracts\test.xls', [Sheet1$]);

    which produces the following error message.

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified 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)".

    I have :-

    1)Set the Distributed Transaction Coordinator.

    2)Tested that SQL can write to the SQL services logon TEMP folder.

    3)Tested that SQL can write to the D:\DataExtracts folder

    4)Ensured that the test.xls is the same one used on all other servers for testing.

    The excel being used is 2003 and I have also tried Excel 5.0 in my openrowset statement.

    In my investigations I also read quite a bit about creating a linked server so I thought I would try that and the command

    EXEC sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'd:\DataExtracts\test.xls', NULL, 'Excel 8.0'

    Return Command(s) completed successfully.

    However the command

    SELECT * FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

    Returned the same error as before.

    Then I wondered if it was the RPC properties of the linked server and I attempted to edit them from False to True but that resulted in the error message

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

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ImportData" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

    So now I am completely at a loss. I am going to check the MDAC but the installation came from the same set as the other servers so I suspect that will not be the issue.

    Any help or fresh ideas would be most appreciated. Thanks.

    :crazy:

  • Check and make sure WMI is running properly. We were having a similar issue, and after restarting the server (not just the service) it worked. WMI appeared to have stopped working (no "play" icon for the server in SSMS' Object Explorer is an obvious way to know).

  • Hi,

    Thanks I will check that and I will let you know.

  • Hi,

    We have checked the WMI and that seems to be working correctly, but thanks for the tip.

    In fact we have been through everything with a fine tooth comb checking and comparing our findings against one of the sister serves that works.

    We are completely stumped and like I say it is sod's law that the one server we want it to work on it won't.

    Still its the long weekend end so enjoy your holidays.

    Thanks

    Ron

  • What version of the OS is this on, if its 64 bit, I believe there is no jet driver?

    Andrew

  • I used this yesterday and worked perfectly, slightly different from your syntax

    Select *

    from OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\temp\Spreadsheet.xls;',

    'SELECT * FROM [Sheet1$]')

    You had used:

    select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;database=d:\DataExtracts\test.xls', [Sheet1$]);

    Per the earlier post, this driver is not available for the 64 bit OS

  • Hi Grasshopper,

    I have tried that but still the same error.

    My syntax works fine on at least five other pc's and servers so we have come to the conclusion that we have a possbile installation issue.

    Thanks:ermm:

  • I also have the same issue in my win 2003 R2 (64bit) server.

    But whenever i got this problem i need to re-register the service of Excel

    by Regsvr32 msexcel4.0.dll. After register this in command prompt i didn't receive such error until i restart my machine. Can anyone plz tell me is there any permanent solution for this ?

  • I don't have anything, but remember that you can probably set up a SQL Server job to run on startup that run your regsvr command. That's not a fix, just a workaround.

  • Hi,

    Could you plz tell me that how to add this as a Sql Server Job..

    Regards,

    Venki

  • I'd try something like this...

    USE [msdb]

    GO

    DECLARE @jobId BINARY(16)

    EXEC msdb.dbo.sp_add_job @job_name=N'start regsvr',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa', @job_id = @jobId OUTPUT

    select @jobId

    GO

    EXEC msdb.dbo.sp_add_jobserver @job_name=N'start regsvr', @server_name = N'yourservername'

    GO

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_jobstep @job_name=N'start regsvr', @step_name=N'regsvr32',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_fail_action=2,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'CmdExec',

    @command=N'Regsvr32 /s msexcel4.0.dll',

    @database_name=N'master',

    @flags=0

    GO

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_update_job @job_name=N'start regsvr',

    @enabled=1,

    @start_step_id=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @description=N'',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'',

    @notify_netsend_operator_name=N'',

    @notify_page_operator_name=N''

    GO

    USE [msdb]

    GO

    DECLARE @schedule_id int

    EXEC msdb.dbo.sp_add_jobschedule @job_name=N'start regsvr', @name=N'on agent start',

    @enabled=1,

    @freq_type=64,

    @freq_interval=1,

    @freq_subday_type=0,

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=1,

    @active_start_date=20090515,

    @active_end_date=99991231,

    @active_start_time=0,

    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

    select @schedule_id

    GO

  • [p]Hi

    I have been having the same issue and found a solution for reading Data in from Excel Files.

    To achive this,

    The Sheet name in Excell must not have spaces,

    The Directory the file is in must be accessible to the SQL Server Service user Account

    The 'Ad Hoc Distributed Queries' Advanced SQL Config option must be enables

    USE: SP_CONFIGURE 'show advanced options',1

    RECONFIGURE WITH OVERRIDE

    SP_CONFIGURE 'Ad Hoc Distributed Queries',1

    RECONFIGURE WITH OVERRIDE

    Then Create the following Stored Procedure in your DB:

    CREATE PROC stp_ReadXLS@file varchar(1000)

    ,@template varchar(300)

    ,@hashtable varchar(300) = 'loader_table'

    ,@excell_version varchar(2) = '8'

    AS

    DECLARE @SQL_T varchar(4000)

    EXECUTE AS login ='Sup_sp_exec'

    Begin Try

    SET @SQL_T = 'SELECT * INTO ##'+@hashtable+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '

    EXEC (@SQL_T)

    END TRY

    BEGIN Catch

    SET @SQL_T = 'INSERT INTO ##'+@hashtable+' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '

    EXEC (@SQL_T)

    END Catch

    PRINT ('Populated ##'+@hashtable+' table.....')

    The Stored Proc Will read in the file and create a ## TABLE with the Contents.

    IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one.

    Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number.

    DON'T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED.

    Hope this helps.[/p]

  • Hi just a quick note to say thank you for the ideas unfortunately none of which worked.

    THanks you all for your contributions though.

  • Just making sure: 32-bit SQL, correct? I've not gotten it to work on x64.

  • I had same problem and everything was fine,temp folder the user I used. everything.

    I traced different servers and different applications to check what happened behind OLE DB jet engine stuff. When I looked at traces I noticed that work flow was different, but when you stop SQL server service and start and run the excel stuff(below script) it was following same root.

    Then it showed me SQL server keeps some configurations when some stuff runs first time.

    Such as below script. When it runs on SQL server first time, it reads user permissions and other settings and after that it uses them and it does not recheck them again.

    SELECT esn from

    openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;

    Database=p:\Promo\Promo_Temp.xls',

    'Select esn from [Sheet1$]')

    In our situation, probably something changed the settings maybe an application or after first restart similar script ran with lower permissions and SQL server started using them.

    So for solution:

    I just restarted SQL server service and I ran above script with higher permission user.

    I hope it helps...

    Rabia

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

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