opendatasource excel

Question

opendatasource excel

bilko73

Old Hand

Points: 377
More actions
January 2, 2003 at 7:19 am

#79426

I am trying to query an excel spreadsheet with the following code:
select *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test\11810.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[compoundlists$]
where compoundlists is the name of a sheet in the excel file
I get the following error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
does anyone know why?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

bilko73 Old Hand Points: 377 More actions · Answer 1

in case anyone is wondering the answer is: path needs to be relative to the server and not the local machine. can be on another machine but it needs to be a mapped drive.

John King SSChasing Mays Points: 625 More actions · Answer 2

Hi:

Thanks for the information.

Question: Is OpenDataSource a SQL Server 2000 function, or is that also in SQL Server 7.0? I'm running 7.0, I looked in Books Online for that function and all I saw was that it is a reserved word.

And this is run from TSQL, not from ADO or something else?

This could be very useful information.

Thanks,

JK

Antares686 SSC Guru Points: 125444 More actions · Answer 3

OPENDATASOURCE is available both 7/2k. It is a function within TSQL and can be utilized with ADO by connecting to the SQL Server. However with ADO to an Excel file try to goto the file instead of thru SQL to avoid so connection and memory usage that can be better spent otherwise.

John King SSChasing Mays Points: 625 More actions · Answer 4

Hi:

OK, thanks. I've looked for documentation on the OpenDataSource function in Books Online a few more times using different approaches and just haven't found anything. I currently use ADO to access an Exel spreadsheet, but it is painfully slow over my connection. If I was at the main site, I probably wouldn't notice a delay. Thought this might be an option to explore.

Thanks again for the information.

By the way, what is the proper way to respond? Should an individual's login name be used, or real name, if known (and we have ways of knowing)?

JK

Antares686 SSC Guru Points: 125444 More actions · Answer 5

If you are talking about what to put when responding here I don't think it matters much. I most times use the user name and not the real name unless they tag in their real name, simply because people can tell exactly who you are talking about immediately from info on the current screen.