Unanswered: Exporting table to Excel via Openrowset with SQLCMD, in 64-bit SQL2008.....possible?
I did a SQLCMD query to export a table from SQL2005x32 to an Excel template file:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\abc\def.xls;',
'SELECT * FROM GHI') SELECT JKL,MNO,PQR
with the help of enabling Adhoc Query option under the surface area configuration and turn on the XPCMDShell thingy.
However, I am having problem to execute such thing dispite turning on the similar thing through FACETS under SQL2008x64.
Then I realized OLEDB driver is not really available in 64-bit. However, WindowsServer2008 & R2 does ship with 64-Bit OLEDB Provider for ODBC (MSDASQL).....however, seems like it can't be used for Excel!
I am wondering if anybody has a workaround (as much automation as possible, or just with a mouseclick kind of thing to achieve the same thing) or alternatives to export a table in SQL2008x64 to an Excel file......