OpenRowSet error Msg 7302 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

OpenRowSet error Msg 7302

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-04 : 16:09:14

Hi I insert data from excel file using openrowset method.
It used to work fine but Now I start getting the error message
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

INSERT INTO tbl_BW_RawData_Temp
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,'' AS [Date Upload]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=\\servername\k$\BWAsset\BW_GWC.xls;HDR=YES', 
    'SELECT * FROM [Report 1$]')

I gave full permission to SQL Agent login name(Network Service) to the shared folder BWAsset
Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 16:41:05

Is the Sheetname and bookname correct? Also is it kept opened?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 09:26:30

I do not see any excel process running in the task manager to explain an opened file.
and the name of both sheetname and workbook is correctly written.

Thanks!

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 10:19:14

Executing exec sp_helpserver
It shows the status as follow:

rpc,rpc out,data access,use remote collation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:21:56

quote:
Originally posted by infodemers

I do not see any excel process running in the task manager to explain an opened file.
and the name of both sheetname and workbook is correctly written.

Thanks!

do you've any other error messages coming along with this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 10:43:35

quote:
Originally posted by visakh16

quote:
Originally posted by infodemers

I do not see any excel process running in the task manager to explain an opened file.
and the name of both sheetname and workbook is correctly written.

Thanks!

do you've any other error messages coming along with this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

quote:
No, that is the only one.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-05 : 11:06:24

Try it without the k$. That will need to access the admin share rather than the folder share.
Otherwise log on to the server as the account and see if you can access the share.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 11:15:50

I tried without the k$ and I also tried using the drive letter instead of UNC and still the same issue.
I tried the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\blmcik\BWAsset\BW_GWC.xls;HDR=YES',
'SELECT * FROM [Report 1$]')

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=k:\RawData\BWAsset\BW_GWC.xls;HDR=YES',
'SELECT * FROM [Report 1$]')

I also tried with 2 other excel file and no luck!
I tried without space in the name of the sheet also, and it did not work
If the file was locked, would I see in the running process "Excel.exe" ?
Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-05 : 11:48:34

The file could be locked from anywhere. Try opening it manually - or just renaming it.
Try accessing it using xp_cmdshell. If you can do that yoou might be able to copy it locally then open it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 13:13:11

I could rename the file without any problem.

Also, I ran the following and it did copy the file successfully.
USE master
EXEC xp_cmdshell 'copy \\blmcik\BWAsset\BW_GWC.xls \\blmcik\BWAsset\BW_GWC.csv',
NO_OUTPUT

That's a tricky one! :-)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-05 : 14:52:13

1. Is "PROGID of the OLE DB provider" installed
2. What happens when you copy excel file to disk of sql server where you are performing the OPENROWSET?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 15:31:29

The ProgID is installed, I can see in the registry under HKEY_CLASSES_ROOT\CLSID\GUID
a string value with value = Microsoft.Jet.OLEDB.4.0. Should I see something else?

I also ran master.dbo.xp_enum_oledb_providers to display the OLE DB providers
Microsoft.Jet.OLEDB.4.0 {dee35070-506b-11cf-b1aa-00aa00b8de95} Microsoft Jet 4.0 OLE DB Provider

Copying files on the SQL server works just fine.

infodemers
Posting Yak Master

183 Posts

Posted - 2012-06-05 : 21:37:28

Hi all,

Outside business hour, I restarted the server and now everything is working fine as before. It is too bad because I have no idea why it stopped working....

Thanks all for your help! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources