I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?

Madhivanan

If you actually want an Excel workbook as output, use a DTS package instead.
bcp is used only for text (or native) output.

I used bcp to export data to Excel file
master..xp_cmdshell 'bcp "select * from MyDB..Test" queryout
"D:\ex.xls" -c'
It is working. But It is actually stored as Text(Tab Delimitated) type.
I have to open it and do saveas and select "Microsoft Excel Workbook"
to actually save it as Excel file. Is there any way to avoid this?

Madhivanan

Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution

Madhivanan

The books online (BOL) discuss DTS.  You can also post the question in the
..dts newsgroup.  Also, have you tried just using the wizard?  This sounds
like a straightforward export.

Can you tell me how to use DTS? I connected SQL Server as Source and
Excel ODBC as Destination. When I run I get the error 1 task(s) falied
during execution

Madhivanan

Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report
Performance Issues using Aggregate MAX function within a temp table