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