• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

sql 2005 query on a 2007 excel

I am running the query on a 2007 excel file.

SELECT * FROM openquery ([CT AVAIL],
 'Select [Vendor],[Sku],[Size],[Species],[Variety],[Function],[Native],[Shp Mult],[Ship Qty],[Comments]
 from [CONNECTICUT_AVAILABILITY$]
' )
And getting the error

LE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CT AVAIL" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CT AVAIL".

If I restart the server the query will run but the next day I will get the same error message.


Gad
0
GadFriedman
Asked:
GadFriedman
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
GadFriedman,

It sounds like the issue is possibly the Excel file being locked if restarting (force closing process) fixes the issue as Microsoft's documentation on the error would suggest it would happen all the time if your were missing the file types necessary (and they speak specifically on linking to Excel from Access).  Try seeing if OPENROWSET works any better for you.

SELECT [Vendor],[Sku],[Size],[Species],[Variety],[Function],[Native],[Shp Mult],[Ship Qty],[Comments]
FROM OPENROWSET (          
   'Microsoft.ACE.OLEDB.12.0',
   'Data Source={path to your Excel file};Extended Properties="Excel 12.0;HDR=YES"',
   'Select [Vendor],[Sku],[Size],[Species],[Variety],[Function],[Native],[Shp Mult],[Ship Qty],[Comments] from [CONNECTICUT_AVAILABILITY$]'
);
0
 
GadFriedmanAuthor Commented:
I restarted the sql 2005 server services to resolve the problem. The excel file reside on a different server.
I also try to run the openrowset and got the same error.

I also opened the excel file and then ran the query and got a different error, so I do not think that the issue is with the excel file being open.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CT AVAIL" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CT AVAIL".
0
 
Kevin CrossChief Technology OfficerCommented:
The file is on a different server, is it possible to temporarily place a copy of the file locally so you can test if it is a network connectivity/latency issue.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GadFriedmanAuthor Commented:
I can copy the file locally, and try it next time I get the error, right now after restating the sql services the query is running fine. If it is a network issue how restarting of the sql services resolved the issue  

0
 
Kevin CrossChief Technology OfficerCommented:
Restarting services could be forcing reconnect.  I have seen this before from the other direction where database goes offline and application needs to restart to query network again for the server.  It was just a thought -- probably a stretch, but is a possibility.
0
 
GadFriedmanAuthor Commented:
The next time the query fails I will move the file to the local server and test, until then do you have any other suggestions what should I look for
0
 
Kevin CrossChief Technology OfficerCommented:
That is it for now, but will keep thinking.  You may want to have Moderator's add Excel zone if issue persists and local version has same issue.  Might be good to note how frequently it happens, i.e., 1 day after restart, 1 hour, x number of requests/queries, etc.  It may help you correlate what is going on like scheduled reboot on Sunday's of file server where XLS resides so on Monday the database has to reestablish connectivity to linked server.
0
 
Kevin CrossChief Technology OfficerCommented:
Glad that helped.  Just to clarify for others, was it the file server (or system with the XLS file) rebooting per my database losing connectivity to the linked server theory?
0
 
GadFriedmanAuthor Commented:
I am not 100% sure, that that is the main problem, I can query the excel file right now and I will keep on monitoring to see if I can post any more information
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now