OPENROWSET works amazingly well as long as you are not operating on 64bit platform.

Example:

INSERT INTO {some table} ({field list})

SELECT {field list} FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

...

If your procedure uses table variables in complex joins, that can do it.

I use this feature frequently but my syntax looks like: 

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=D:\Book1.xls;',...

Wow! I have been looking for a download page like that for a long time. Very helpful, indeed. Thanks.

Thanks for you input. I did run regedit to modify MSDTC settings but the behavior did not change. I must note that I could not configure exactly like the KB article. The Oracle server...

Thanks for the reply. The second article sounded particularly useful so I set all the parameters as specified. However, it didn't help. Neither article specifically mentions INSERTing from a stored...

If you rely on linked servers for distributed queries, get ready for a potentially bumpy ride. Especially if you want to use Windows-only (Kerberos) security. Also, the Microsoft Oracle provider does not...

Also, you can create a linked server using the Jet OLE DB provider.

And new to SQL 2005, the OPENROWSET function with BULK option is very cool.

Agreed. I have a very similar problem and am working with MS Product Support but no solution yet.

I wonder if your have same behavior. I can create the linked...

Avoid table variables for complex joins or large data sets. SQL Server does not compute statistics for them, hence, can not determine an adequate execution plan. SQL 2005 is even...

Thanks for the effort, but, so far, nothing helps. My understanding is that MS has not ported the Jet and ODBC providers to the 64-bit environment and that's that. I...

Using the IMEX=1 option overcomes the problem (if it is a problem) of SQL Server engine trying to determine data type and size. It can lead to real problems if...

I started off installing Oracle's 10G 64bit OLEDB driver but could not figure out how to make it work for linking purposes and I could not find any info in...

Thanks John. That is pretty much as I suspected. It looks like I'll have to run my queries through the 32-bit server for the time being.

Because we rely so heavily on...

Ditto that. I have the same problem with a couple of linked Oracle servers we use for distributed queries. Care to elaborate on the workaround?