Create Excel doc from SQL sp

Hi All,
Any suggestions for the following would be appreciated:
I need to create an Excel doc from an SQL stored procedure where I pass
parameters into the sp. I know how to pass and accept parameters into the sp
and already have the GUI available for that. What I'm not sure of is the bes
t
approach to create the Excel doc from the sp. In the past, I've used DTS to
"export" to Excel but don't know how to pass a parameter into DTS.
If this is possible is this the best approach or is something similiar to a
linked server a better way to go?
Thanks, MarkI have some ExcelXP and SqlServer 2000 stuff at my blog:
spaces.msn.com/sholliday/
You could send back xml .. which is excel xml.
You could also send back "normal" xml from sql server, and do an xml to xml
transformation to make it "excel xml".
Or wait for someone to tell you how to do parameters with DTS.
i'm just throwing another option out there for you.
..
Sloan
"Mark Paulson" <MarkPaulson@.discussions.microsoft.com> wrote in message
news:F648AED6-800B-4E62-905B-693D797244CF@.microsoft.com...
> Hi All,
> Any suggestions for the following would be appreciated:
> I need to create an Excel doc from an SQL stored procedure where I pass
> parameters into the sp. I know how to pass and accept parameters into the
sp
> and already have the GUI available for that. What I'm not sure of is the
best
> approach to create the Excel doc from the sp. In the past, I've used DTS
to
> "export" to Excel but don't know how to pass a parameter into DTS.
> If this is possible is this the best approach or is something similiar to
a
> linked server a better way to go?
> Thanks, Mark
>|||Or, as a workaround, you can store a blank Excel spreadsheet as a
template and fill its copy with OPENROWSET when procedure is run.|||
"Mark Paulson" wrote:

> Hi All,
> Any suggestions for the following would be appreciated:
> I need to create an Excel doc from an SQL stored procedure where I pass
> parameters into the sp. I know how to pass and accept parameters into the
sp
> and already have the GUI available for that. What I'm not sure of is the b
est
> approach to create the Excel doc from the sp. In the past, I've used DTS t
o
> "export" to Excel but don't know how to pass a parameter into DTS.
> If this is possible is this the best approach or is something similiar to
a
> linked server a better way to go?
> Thanks, Mark
>|||Mark,
To create a comma delimited result set
1. In Query Analyzer or SSMS click Tools>Options>Query Results and change
the
Default Destination for Results to "Results to File"
2. Execute the procedure passing the parameters and specify the file to
save it to.
By default the file extension is .rpt. You can leave the default or
change
it to .txt to reduce confusion if need be.
3. Open Excel and click File>Open and choose file type of All(*.*)
4. Navigate to the result file and click Open
5. Page 1 of the Wizard leave default settings click Next
6. Page 2 uncheck Tab and check Comma in the Delmited Group Box
7. Page 3 you can specify the column data types and click Finish
This allows you to import the results to excel from a comma delimited file,
but does not provide an end user interface. Excel has the ability to connec
t
to outside data sources, but for SQL is limited to Views and Tables. If the
query allows you can create a view and then have the user use the WHERE
clause to take the place of the parameters.
You could also use osql and a batch file or VB script to provide the ability
for the end user to specify parameters.
1. Have the end user create a text file and enter into it only the
parameters
separated by commas. Save the file with a specific name and location.
2. The batch file/vb script will create an input file using the user's
file to create an
EXECUTE statement and placing the parameters into the statementd from
the
users text document.
3. Use an output file to capture the result set and open the output file
in Excel as
outlined above.
Good luck and let me know if this helps.
"Derekman" wrote:
>
> "Mark Paulson" wrote:
>