Export Data from a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Export Data from a Stored Procedure

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2004-03-10 : 16:27:14

Hi Folks,
I have a stored procedure that returns a result set. I would like to export the result set into a excel spreadsheet. Is it possible to do this using a DTS package.
Any ideas(s0 and/or suggestions.

Thank you
Ramdas

Ramdas Narayanan
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-10 : 16:40:10

Yes you can do that with DTS. In the transformation, just use a query (EXEC StoredProcName) instead of a table. That's the first tab in the transformation.

Tara

ramdas
Posting Yak Master

181 Posts

Posted - 2004-03-10 : 17:13:47

Hi,
Thanks for your reply. The stored procedure has select * from #TempTable as the last line in the stored procedure. This is causing some issues. How would one handle this.

Thanks

Ramdas Narayanan
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-10 : 17:59:10

You really need to provide more information. Did you setup the DTS package already? If so, what is the error? Could you show us the code with all relevant tables (CREATE TABLE statements)?

Tara

ramdas
Posting Yak Master

181 Posts

Posted - 2004-03-10 : 20:48:05

Hi,
The dts package has a execute sql task, a sql server connection data source object and a excel spreadsheet data source.
The execute sql task has a call to the stored procedure like:
EXEC up_get_data

. In the stored procedure (up_get_data) the last statement is
Create procedure up_get_data
AS
... Process business rules
...
select * FROM #TempPrt
return
GO

When I execute the execute sql task, i get an error saying:
Invalid Object name #TempPrt.

Ramdas Narayanan
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-10 : 20:50:30

The stored procedure should not be called in the execute sql task. You should have a transform data task between the data source object and the excel data source. Double click on the arrow. On the first tab is where you want to call the stored procedure.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources