OpenRowset import from excel to sql fails

Question

OpenRowset import from excel to sql fails

dopydb

SSCertifiable

Points: 6687
More actions
June 3, 2010 at 3:13 am

#235890

hi all
i used to import excel data in my last job using the below OpenRowset statement, but when i try on a now different server ( still sql 2005) it fails with the below message; i have tried suggestions such as enable ole automation to no avail, Can anyone help ?
;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)".
sql statment-
INSERT INTO #t1(code)
Select * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Mraf\top10.xls','SELECT * FROM [Sheet1$]')

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

malachyrafferty (6/3/2010)
when i try on a now different server ( still sql 2005) it fails with the below message; i have tried suggestions such as enable ole automation to no avail, Can anyone help ?

Does the new server contain same MS office version as other server has ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

WayneS SSC Guru Points: 95409 More actions · Answer 2

This looks like the error I get on my 64-bit laptop... there is no 64-bit version of the Jet engine. Is the sql running on a 64-bit OS?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

dopydb SSCertifiable Points: 6687 More actions · Answer 3

yea its the same version office 2003,

and everything is 32 bit

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 4

May be what I am trying to suggest is dumb, please forgive me if it is. Does the aforementioned xls file exists in C:\Mraf folder where C: is the server hard drive? If it does then just to eliminate the permissions problems, try the following:

Open C:\Mraf on the server and create a small text file named say sample.txt. Type something in the file (Hello there) and save it.

Open SSMS and execute the following:

declare @contents varchar(max);

select @contents = f.BulkColumn

from openrowset

(

bulk 'C:\Mraf\sample.txt',

single_clob

) f;

select @contents contents;

Do you get the results window showing whatever you typed in the file? Something like

contents

-----------

Hello there

Oleg

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 5

I was able to reproduce the error you are getting. Here are the steps:

Create the xls file on the server (C:\Useless\sample.xls)

Run the script to get the data:

select *

from openrowset

(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Useless\sample.xls',

'SELECT * FROM [Sheet1$]'

);

Get the reults:

Col1 Col2

-------- --------

Phoney1 Boloney1

Phoney2 Boloney2

At this point everything is fine. Now to reproduce the error:

Open the file in Excel so it now locked.

Try to execute the same query as above. This will result in the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an 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)".

Please check the xls file to make sure that no process has it locked.

Oleg

dopydb SSCertifiable Points: 6687 More actions · Answer 6

hi oleg

i will try your suggestions tomorrow, i think you may be more correct on the security /permissions though, as excel definitely was closed any time i tried to run the query - as i encountered having it open caused it to fail before.

thanks for taking the time to help !

ill update in the morning

WayneS SSC Guru Points: 95409 More actions · Answer 7

Are you saying that you actually have Excel loaded on your server? Excel (all Office products) are not supported on a server.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

For sanity check you can still try checking for locks though. I don't know which process you have in place to write Excel files, but in theory what is possible is the following: some application creates Excel file and writes some data into it. If the app leaks and does not close the handle to the file properly then the latter is going to be locked. This is certainly possible (memory stream is not disposed properly after it is flushed etc). One way to quickly check it is to open the file in Excel and try to change something. If the file is locked then Excel will notify you.

Oleg

dopydb SSCertifiable Points: 6687 More actions · Answer 9

yea on a server - server 2003

not supported? maybe not officially - iv no idea

but i never have had issues before and iv used on server 2000, 2003,2008?

how else would citrix and terminal service users access such apps?

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

WayneS (6/3/2010)
Are you saying that you actually have Excel loaded on your server? Excel (all Office products) are not supported on a server.

I was trying to say that Excel file location with openrowset is relative to the server that is all. I just have seen in the past that sometimes people may reference Database=whatever_the_path in openrowset queries and try to run it without first checking validity of the path relative to the server and permissions.

Oleg

dopydb SSCertifiable Points: 6687 More actions · Answer 11

ok, i have checked and excel is not open, i can edit and save etc ok and no excel process's are running

i have also shared the folder with full privileges for my user etc but i get the same error!

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 12

Did you try to run the text file, just to make sure that there is nothing wrong with openrowset queries?

declare @contents varchar(max);

select @contents = f.BulkColumn

from openrowset

(

bulk 'C:\Mraf\sample.txt',

single_clob

) f;

select @contents contents;

Oleg

There is another thing I would check as well. If Office is installed on the server, did you try to actually use Excel while logged in to the server itself. Sometimes what happens (this is rare but I have seen it happening) is the following: The service pack is applied to the Office product. After that the first invocation of any of the existing Office application results in a dialog box prompting to confirm user initials. You click OK and the Office is ready to use from then on. But if it is called programmatically before the initials are confirmed (like in case of openrowset), there is no dialog box, and Excel still cannot provide desired functionality. The bottom line: it does make sense to check whether Excel application can be launched locally.

Oleg

dopydb SSCertifiable Points: 6687 More actions · Answer 13

hi oleg

i just ran that and the txt file opens fine

i have opened excel locally on the server as well

i just tried the same thing on a sql 2000 server and it worked fine!

i have a feeling there is some domain security restrictions playing with me !

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

And the ad hoc distributed queries option is set to 1 right?

sp_configure 'Ad Hoc Distributed Queries';

shows 1 in both config_value and run_value columns?

Oleg