question

Sharma avatar image
Sharma asked

Insert data from Excel file through OPENROWSET function.

INSERT INTO XXX_temp( A,B) SELECT [A],[B] FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\CLASUploadXLS\Client_Refresh_052611nh.xls', 'SELECT * FROM [Client List$]') But it is giving the below issue. I don't know how to fix it? Could you please help me in this regards. error: > OLE DB provider > "Microsoft.Jet.OLEDB.4.0" for linked > server "(null)" returned message > "Unspecified error". Cannot initialize > the data source object of OLE DB > provider "Microsoft.Jet.OLEDB.4.0" for > linked ser I Tried the below solutions 1. restart of system 2. reconfugaring the openrow and opendatasource. 3. Execution of ole automation procedure Still the issue is there. Could you please help me in this regard.
excelopenquery
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have the excel document closed before running the query? 32-bit or 64-bit? Does the SQL Server service account have permission to the file (full control)? Do you have an up-to-date Jet driver? - http://support.microsoft.com/kb/239114 Can you create a linked server to this file and reference it that way? Can you use SSIS/DTS instead of T-SQL (which version of SQL Server are you using)?
1 Like 1 ·
Kev Riley avatar image
Kev Riley answered
> ...linked server "(null)"...... this means it can't even 'find' what you are trying to use as a source, either because it is locked/denied/non-existant. I would expect this error if the Excel sheet was already open. If it is not open, then check the filename and path, and finally any permissions to that file and path.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
Check my answers to following questions: [import excel to sql][1] [SSIS Excel dumping][2] You can receive this on X64 system, where you cannot use the 32 bit Microsoft.Jet.OLEDB.4.0. To read the Excel file on x64 system you can use x64 version of [Microsoft Access Database Engine 2010 Redistributable.][3]. However it cannot coexist with 32 bit version of MS office if installed on the same machine. On Server where you do not have office installed, it should not be a problem. [1]: http://ask.sqlservercentral.com/questions/74179/import-excel-to-sql?page=1#74212 [2]: http://ask.sqlservercentral.com/questions/73948/ssis-excel-dumping?page=1#74008 [3]: http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
You didn't specify which version of SQL Server you are running. Can you confirm you are using the CTE release of SQL Server 2005? This error was very popular prior to SP1 for SQL 2005.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.