Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2010-08-18 : 18:47:47
|
can you guys give me a work-around on this query:select distinct *INTO ##[table]FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\[filename].xls', 'select * FROM [Sheet1$]')it doesnt work on our 64x servers, but used to when we were running 32x i know it has to do with the driver not working on 64x but i dont know how to work around it... |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-18 : 18:59:46
|
| get this error by the way:Msg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-18 : 19:38:22
|
| No such thing as 32 bit Jet provider. This is a common question in SSIS, and easily handled there.You're better off importing the Excel workbook and running a local query than running distributed query against it.That or you'll need to execute in 32 bit mode |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-08-18 : 19:53:55
|
| when you say execute in 32 bit mode, does that require restarting the sql instance? or can i do that via t-sql? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 09:09:21
|
| If you must do it inline like that, create a DSN on the server, then make a linked server with that DSN as the connection string.I put it that way because I DO NOT LIKE DSNs -- especially on my servers.If you need to read from Excel, it's far better to write an SSIS package that will load it into a SQL table. |
|
|
|
|
|
|
OpenRowSet wont work on excel in 64x enviornment