Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

My favorite way to access Excel files Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 5:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 8:19 AM
Points: 1,295, Visits: 1,570
(5/20/2009)
Please read this MS article: http://support.microsoft.com/kb/814398

I experienced this same error and the article helped solve the problem.



I also found this useful when querying a workbook with several worksheets:

EXECUTE SP_TABLES_EX 'your_linked_servername_goes_here'

which returns the names of the worksheets


_____________________________________________________________________________________
gsc_dba
Post #720450
Posted Tuesday, October 6, 2009 4:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 10:52 PM
Points: 1,940, Visits: 1,170
Hi,
I tested this but it is not worked for me.

when i use openrowset it showed executed.[color=#00ffff][/color]
but when i used opendatasource it shows an exception linked server is null.
and also even though i created linked server through Managementstudio.it created linked server
but does not show the table in it.
In my system IIS was not installed .is there is link between iis and linked server.Please send the details reply.


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #798409
Posted Thursday, December 31, 2009 1:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 5, 2014 2:01 PM
Points: 88, Visits: 36
Gaby, Jacob

We have used the ACE provider with Excel 2007 and Excel 2010 Beta files and itworks great with a couple of issues.

1 - no 64 bit as discussed previously in this post and

2 - if you convert the file back to an excel 2003 version and use the jet provider - it is about twice as fast.... not sure why this is but the ace driver is slow.

Post #840711
Posted Thursday, December 31, 2009 1:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 5, 2014 2:01 PM
Points: 88, Visits: 36
one other thing that I forgot to mention - you need to install either Excel 2007 on the server that you are executing on or download the AccessDatabaseEngine driver from

http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

also remember that when you run these queries via Management studio, the source files must be on server themselves.

you cannot connect to a local instance of SQL and execute against files not on your local machine.
Post #840712
Posted Tuesday, March 22, 2011 6:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:43 AM
Points: 716, Visits: 3,031
I use OPENDATASOURCE, too. In addition to the gotchas mentioned above (64-bit, file must reside on server), you might also want to know:

- The filename must be hard-coded; you cannot pass a variable to it. You can use dynamic SQL as a work-around. Here's a write-up: http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

- This might be obvious, but you can't have the file open in Excel. This is annoying if you're massaging data in Excel and using SQL to import/QC the data.

Thanks,
Rich
Post #1081953
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse