March 26, 2009 at 9:27 am
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:
April 6, 2009 at 8:57 am
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).
April 6, 2009 at 9:01 am
Hi,
Thanks I will check that and I will let you know.
April 9, 2009 at 2:54 am
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
April 9, 2009 at 3:37 pm
What version of the OS is this on, if its 64 bit, I believe there is no jet driver?
Andrew
April 11, 2009 at 12:09 pm
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
April 27, 2009 at 8:23 am
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:
May 14, 2009 at 1:08 am
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 ?
May 14, 2009 at 7:26 am
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.
May 14, 2009 at 11:22 pm
Hi,
Could you plz tell me that how to add this as a Sql Server Job..
Regards,
Venki
May 15, 2009 at 1:24 pm
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
November 6, 2009 at 9:33 am
[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]
January 20, 2010 at 9:55 am
Hi just a quick note to say thank you for the ideas unfortunately none of which worked.
THanks you all for your contributions though.
January 20, 2010 at 10:55 pm
Just making sure: 32-bit SQL, correct? I've not gotten it to work on x64.
January 25, 2010 at 12:21 pm
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