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

I 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



Show quoteHide quote

Or, as a workaround, you can store a blank Excel spreadsheet as a
template and fill its copy with OPENROWSET when procedure is run.

Show quote Hide quote

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 connect
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.

Show quoteHide quote

stop using dynamic sql
how to write extended stored procedure to generate sequences.
dll & Web Service in VB.Net 2005 Stored Procedure
Sorting a parent-child list
UDT, what's the point?