OpenRowSet Error

Question

OpenRowSet Error

KT8177

SSC Eights!

Points: 876
More actions
February 20, 2009 at 4:42 pm

#129239

I am having some trouble with the following query in one of our production databases:
select *
from openrowset('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0; IMEX=1;Database=C:\Documents and Settings\med\Hatfield Customer Codes.xls'
, 'select * from [sheet1$]
where CustomerCode is not null') a
After executing the above statement, I get the following error:
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)".
We are using SQL Server 2005 SP3. The odd thing is that if we reboot the server, the statement executes just fine for a couple of days and then we get the error again. I've scoured the internet to try to find why that is happening but I couldn't find much info.
I have checked all the permissions to the TEMP director and it is all set to FULL CONTROL for both the sql service account and the user executing the query.
Can someone please help?

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

This isn't really a T-SQL issue, because your code USUALLY works.

That error doesn't give much to go on, and you say rebooting the server fixes the problem. My immediate thoughts were (1) the spreadsheet wasn't found in the right directory, (2) someone changed the name of the worksheet, or (3) the spreadsheet is open by another application. A reboot should not solve either of the first two issues, but it might clear up #3.

Can you open the spreadsheet in Excel when the job tries to run, to see if it forces the same error?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Make sure you read this fully

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Make sure you read this fully
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Heh... right... read 19 pages of posts... some with some pretty bad information (not yours). Can you summarize, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

KT8177 (2/20/2009)
I am having some trouble with the following query in one of our production databases:
select *
from openrowset('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0; IMEX=1;Database=C:\Documents and Settings\med\Hatfield Customer Codes.xls'
, 'select * from [sheet1$]
where CustomerCode is not null') a
After executing the above statement, I get the following error:
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)".
We are using SQL Server 2005 SP3. The odd thing is that if we reboot the server, the statement executes just fine for a couple of days and then we get the error again. I've scoured the internet to try to find why that is happening but I couldn't find much info.
I have checked all the permissions to the TEMP director and it is all set to FULL CONTROL for both the sql service account and the user executing the query.
Can someone please help?

If someone has the spreadsheet open, you will get a similar error. That's why rebooting fixes the problem... it causes a disconnect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KT8177 SSC Eights! Points: 876 More actions · Answer 5

I have checked and no one has the file open. It actually gives me a different error if the file is open, file not found, etc. I just don't understand why it would work after the reboot and then stop working after a couple of days. I know that the file path is correct because i used the xp_cmdshell 'type ' command to view the file and that works. I just can't seem to find anything on why this is happening and why I'm getting this unspecified error.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

KT:

You are dealing with what I call a "gremlin". A gremlin is an unexplained intermittent error. Without more information, I can't help you. (That is NOT a criticism.)

In the meanwhile, lets try something to shoo the gremlin off without rebooting.

Next time you get that error, don't reboot. Instead, copy the spreadsheet (in the same dir), then delete the original and then rename the copy back to the original name. Best case, you will get a more meaningful error message when you try to delete the spreadsheet. Second best case, it will clear the error without having to reboot.

I know how frustrating dealing with a gremlin can be. The bandaid above just gives you a workaround until you can get a handle on the cause. Good luck, and let me know how it works.

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

[font="Verdana"]One word of warning:

The error could lie with the driver -- a memory leak, or running out of space while building temporary files, etc. So moving the file around may not fix it. However, it's still a good thing to try, because it will confirm if the error is with the driver.

If it is, you may need to move that spreadsheet to live in a database, and replace the spreadsheet with some sort of data entry.

[/font]

KT8177 SSC Eights! Points: 876 More actions · Answer 8

Thanks for the ideas. I have tried copying the file and then deleting the original file to see if that'll give me any other messages. I was able to just delete the original file just fine, no errors. It is still giving me the unspecified error when I run the query even after copying the file and deleting the original.

How can I tell if it's a driver issue or memory issue? I am out of ideas and our customer isn't happy since they have to reboot the server every other day.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

[font="Verdana"]Have you googled the error message? What did you find?[/font]

KT8177 SSC Eights! Points: 876 More actions · Answer 10

Yes, i have googled it extensively and there were a few posts on the same issues that I have where the problem goes away once i reboot the server but there were no resolutions. People have just used that as a workaround. There was also a post about how it's a bug with sql server 2005 and that microsoft is working on it but I haven't been able to find anything about that.

Most of the other posts has said that the problem could be with the permissions to the temp folder but I have already checked that numerous times and it has full access to everything.

I am just out of ideas as to where to look next.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Hmmmm... it's starting to sound like a "connection leak", although I don't know for sure. We had that problem only once and, if memory serves correctly, we found out that we had one by monitoring the number of open SPIDs using sp_Who2 over time.

I'm sure that someone else will be able to do much better than that, but it sounds like connection leaks to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KT8177 SSC Eights! Points: 876 More actions · Answer 12

I just wanted to updated everyone and say that we have found the issue. It was a memory issue, the server had AWE enabled but it didn't have enough memory set so that's why we were getting the errors and had to reboot every other day in order to get it to work. I turned AWE off and it works just fine now! Thanks to all that helped!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Thanks for letting us know what the resolution was, KT.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

KT8177 (3/11/2009)
I just wanted to updated everyone and say that we have found the issue. It was a memory issue, the server had AWE enabled but it didn't have enough memory set so that's why we were getting the errors and had to reboot every other day in order to get it to work. I turned AWE off and it works just fine now! Thanks to all that helped!

Very cool! That's good to know. Thanks for the comeback on that, KT... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)