June 3, 2010 at 3:13 am
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$]')
June 3, 2010 at 6:18 am
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;-)
June 3, 2010 at 7:40 am
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
June 3, 2010 at 7:49 am
yea its the same version office 2003,
and everything is 32 bit
June 3, 2010 at 11:52 am
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
June 3, 2010 at 12:19 pm
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
June 3, 2010 at 2:11 pm
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
June 3, 2010 at 2:33 pm
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
June 3, 2010 at 2:38 pm
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
June 3, 2010 at 2:42 pm
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?
June 3, 2010 at 2:49 pm
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
June 4, 2010 at 7:48 am
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!
June 4, 2010 at 8:43 am
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
June 4, 2010 at 9:22 am
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 !
June 4, 2010 at 10:18 am
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply