Hi,
I have been at this since 5 am. I still can't even find proper info. I have
a sp that takes a while to run and I want to have the sp create an excel file
of the result and send it to several users. I created a DTS but how do I
execute that DTS from withing my sp and pass a parameter to the DTS at the
same time. Also won't it be easy to just create the excel file form within
the sp then mail it as an attachment? I tried the foll but didn't work


insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;',
'SELECT * FROM [Sheet1]') select * from table wher col = value

I get the error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 
[OLE/DB provider returned message: The Microsoft Jet database engine could
not find the object 'Sheet1'.  Make sure the object exists and that you spell
its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

I tried this as well



Exec Master..xp_cmdshell 'bcp "select * from table wher col = value"
queryout "C:\test.xls" -c'

I got


Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'database2.dbo.table'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
not be prepared.
NULL


Please help

How to execute a DTS package from TSQL:
http://www.sqldts.com/default.aspx?210

OPENROWSET won't create an Excel file for you.

Apart from DTS another option might be to create an Excel sheet using the
Excel.Application object and an Excel macro. It is possible to invoke an
Excel macro from TSQL (using sp_OAmethod) but DTS is really much simpler to
setup unless you need to implement features that are only accessible via the
Excel API.

Alternatively, if your goal is to create a file that users can view in Excel
then why not create a TAB delimited file? You could do that easily with BCP
and the users can open it with File / Open in Excel or just paste it into a
sheet.

Recursive rows in table and deletion problem
Why Output
Centralizing Two Stored Procedure Queries (one using Cursor Output
Send Mail with SQL Server issue
"Insufficient key column information for updating and refreshing"
Display pictures in Northwind on the VB form
SQL Server EM/Query Analyzer Access
Improving performance of SPs
file operations
Possible to drop IDENTITY from a column?