Export Query

[JefB]

I would like to create a stored procedure that exports the result of a query to an Excel spreadsheet.

Ultimately, I want to be able to execute the SP from a FrontPage Database Result so that the data displayed on the webpage can be moved to the spreadsheet by the user (on demand) for customized manipulation without affecting the original database.

Any help would be appreciated.

JefB

 

[markVII]

You cannot export directly into Excel, unless you use DTS. You could use xp_cmdshell extended stored procerdure to produce comma delimited files which then could be exported into Excel. Another option is invoking COM objects in your stored procedure, using sp_OA... procedures

 

[JefB]

Can I execute a DTS package from within a stored procedure?

If so, I'd appreciate some sample code.

JefB

 

[markVII]

You should be able to execute a DTS package from within a store procedure using xp_cmdshell and dtsrun utility (supplied with SQL Server)... Why not produce the file from your ASP page, using Excel's OLE Automation objects?

The code for the DTS package would be very similar to VBScript one. You also may take a look at invoking OLE Automation objects directly in the stored procedure code - look for sp_OACreate and such.

 

[tlbroadbent]

Check SQLDTS.com for examples of executing DTS packages from various programs including T-SQL.

http://www.sqldts.com/default.aspx?6,104,104,5,1

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[entrekken]

This is what I did:
1. I created a DTS package using SQL Enterprise Manager's GUI DTS package creator. I called it MyDTS.
2. I created the following stored procedure



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/******
Object: Stored Procedure dbo.spMyDTS
Script Date: 4/8/2003 3:24:43 PM
******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spMyDTS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spMyDTS]
GO


CREATE PROCEDURE spMyDTS as
DECLARE @v_UserName varchar(20)
DECLARE @v_Password varchar(20)
DECLARE @v_DTSString varchar(300)
DECLARE @v_Status int

begin

--I am using a trusted connection hence the /E
--If no trusted connection --> -Uusername -Ppassword
set nocount on
select @v_DTSString = 'dtsrun -S'+@@servername+' /E -NMyDTS '
exec @v_Status = master..xp_cmdshell @v_DTSString

end

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

3. Issue the following SQL command from within your page. I'm not sure of the Frontpage syntax but the following is the SQL portion:
'execute spMyDTS'

I hope this helps!

 

[JefB]

Thanks to all who responded.
I will be trying the suggestions over the next few days and will report back.

JefB