February 20, 2009 at 4:42 pm
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?
February 20, 2009 at 7:35 pm
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
February 21, 2009 at 2:30 am
Make sure you read this fully
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Failing to plan is Planning to fail
February 21, 2009 at 3:41 pm
Make sure you read this fully
Heh... right... read 19 pages of posts... some with some pretty bad information (not yours). Can you summarize, please?
--Jeff Moden
February 21, 2009 at 3:42 pm
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
February 21, 2009 at 11:41 pm
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.
February 22, 2009 at 2:34 pm
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
February 22, 2009 at 2:45 pm
[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]
February 22, 2009 at 10:39 pm
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.
February 23, 2009 at 12:04 pm
[font="Verdana"]Have you googled the error message? What did you find?[/font]
February 23, 2009 at 4:11 pm
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.
February 23, 2009 at 4:29 pm
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
March 11, 2009 at 3:37 pm
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!
March 11, 2009 at 3:46 pm
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
March 11, 2009 at 7:39 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply