Table content to spreadsheet

Question

Post reply

Table content to spreadsheet

san43

Hall of Fame

Points: 3196
More actions
August 29, 2006 at 8:00 pm

#153160

How to output table contents to spreadsheet?
I just want to schedule a job to send this spreadsheet to my email. Is this possible through through sql agent?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jan-155192 SSCrazy Points: 2613 More actions · Answer 1

Jan-155192

SSCrazy

Points: 2613

August 30, 2006 at 12:34 am

#657430

You could easily write a DTS for this.

Jan

san43 Hall of Fame Points: 3196 More actions · Answer 2

I know how to create dts package, but can anyone give the steps I need to do or procedure ?

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

1.Create ActiveX Cript to delete Destination Excel

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim objFSO, objFile,strFileName

strFileName ="\\ServerName\folderName\ExcelNameReport.xls"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFileName) Then

set objFile = objFSO.GetFile(strFileName)

objFile.Delete(True)

End If

Main = DTSTaskExecResult_Success

End Function

2.Create SQL Connection Object

3.a.Create Excel Destination object(that is accessible from the server,peferably on a folder on the SQL Server)

3.b.Use Transform Data task to export Source to Destination.Copy the table that SQL Enterprise manager generates when Clicking on Destination tab.This would be added as step 4 in DTS

4.Create Execute SQL task (Call it Create Destination) and choose the Destination Excel and past the SQL that you had copied.

for example

CREATE TABLE `RWC_TemeculaDC` (

`TransactionDate` VarChar (10) ,

`ShipmentDate` VarChar (10) ,

`TransactionFunctionCode` VarChar (4) )

6.On Success of the Create destination job set the DTS to run the "Transform Data task " so the table in first generated and the result is exported to Excel.

7.Create SQL Task to email the excel as attachment

Declare @s_Recipients nvarchar(512),

@s_Attachment nvarchar(512),

@s_Subject nvarchar(512)

select @s_Recipients='myname@mycompany.com'

set @s_Attachment = '\\ServerName\folderName\ExcelNameReport.xls'

set @s_Subject = My Subject'

exec master.dbo.xp_sendmail @recipients=@s_Recipients,

@subject =@s_Subject,

@attachments= @s_Attachment

Hope this helps.

Sreejith

san43 Hall of Fame Points: 3196 More actions · Answer 4

Thanks for the reply.

Is it not possible to export output to spreadsheet using SQL query instead of VB script?

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

In query analyzer you can export the result to text.

Query Analyzer->Tools->Options->Results->

Set Default Result Target = "Result to File"

Result output Format ="CSV"

This will get you results in csv (It will promt when you execute the query) and CSV can be opened in Excel and Notepad.

Thanks

Sreejith

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 6

Hi

I create CSV files using BCP from stored procedures. I build the data in a temp table, then update an "outputs" column on each record with the concatenated values from each record. Finally, I run BCP selecting the output column.

Researching the use of BCP should give you the answer you are looking for. As you are wanting to run this on a scheduled basis, a stored procedure based solution would work well.

Luck.

Rowan