Error msg 7399 on Openrowset

Question

Error msg 7399 on Openrowset

Harold Buckner

SSCarpal Tunnel

Points: 4175
More actions
January 30, 2007 at 7:30 am

#68186

I'm trying to run this ad hoc distributed query below
select * into #tbl_mw_temp_cards FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\server\share\DataTransfers\Performance\
PCardProcessing\CardNumberDownload.xls,HDR=YES',
'SELECT * FROM [Sheet 1$]')

It will work fine with any SQL account but it does not work with a trusted connection and I recieve the following error

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
I have disabled DisallowAdhocAccess. I have registered the SPN but still have not gotten anywhere.
Does anyone have any ideas?

Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

what is the account that your sql server runs. i hope it should be some permission issue for that user using winodws account instead of sql account as sql account delegates OS level access to the account running the sql server service.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 2

The account that my SQL Server runs under and my account both have the same primissions. I am a sysadmin and have full NTFS rights to the file. But when I run it authicated as my windows account it fails with the above error. If I create a SQL Account with no server role, it works fine.

How strange is this?

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 3

I verified and corrected some registry settings on the providers and now I am getting the error

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

on both trusted and sql accounts with sysadmin privledges and a sql account returns

Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

If I do a SP_config with the advane options on, should'nt I see an option about ad-hoc querries?

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 4

I got it working now with using a UserID=Admin. Don't know why becauce the excel file has not user id.

select

*

into

#tbl_mw_temp_cards FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=\\psfc\it\DataTransfers\Performance\PCardProcessing\CardNumberDownload.xls;

User ID=Admin;Password=;

HDR=YES'

,

'SELECT * FROM [Sheet 1$]'

)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

Just a question. Looking at your syntax where it says [Sheet 1$], is this the way to refer to an entiry sheet? If so, is it the name of the sheet? If not, what is it?

My experiments with reading Excel files (and BOL confirms this) seem to indicate that the last parameter must be an Excel named range. Yet you seem to have a select statement refering to a sheet. To what extend can you 'push' select statement like this, i.e. can you name columns instead of using asterisk and, if so, where are these names, or can you do an order by? It seems very interesting.

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 6

[sheet 1$] is the default name of the first excel work sheet. I am select everthing in the sheet and trowing it in a temp table. the hdr=yes tell SQL the the first row contain header records they are the name of the columns. You can select ranges as well as having column names. This names would be your header row. I am very sure you can order by and filter.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

BOL (SQL Server 2000) states that the last parameter of OPENROWSET is 'query'.

But the only example for Excel demonstrates that the last parameter must be a named range (without quotes). And I can get this to work just fine (after having created the named range in the Excel file).

I cannot get your 'SELECT * FROM [Sheet 1$]' to work at all.

The other interesting bit is your HDR=YES bit which works like a charm (with a named range) and has solved a big problem for me. But it is not mentioned at all in the BOL.

Thus can you point me to where you got this from?

Thanks loads.

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 8

If the sheet has a true name, you would use it for the name. I have seen the default sheet name as sheet1$ too.

Here is an article that might help you.

http://www.databasejournal.com/features/mssql/article.php/3331881

Michael Meierruth SSChampion Points: 10051 More actions · Answer 9

This works: 'select * from [sheet1$]'

This doesn't work: 'select * from sheet1'

This doesn't work: 'select * from [sheet 1$]'

Neither does this: 'select * from [sheet$1]'

Using the true name of the sheet with or without square brackets doesn't work.

The article certainly got me to a syntax variant that worked, but not much else. I want to get to the 'horse's mouth' regarding openrowset documentation. And it's not BOL.

corey lawson Hall of Fame Points: 3773 More actions · Answer 10

The '$' after the sheet name is required, whether it be a default sheet name like Sheet1, or one that's been renamed.