Hi everyone,

We are trying to come up with ideas to ultimately create and send a
csv file (comma seperated value)
attached in an email via Stored Procedure or VB 2008 to be used in
Excel.

Currently I’m using XML to generate the body of the email with commas
separating the
columns without trouble but the end-user needs its attached.

We plan to recycly the same file name as shown below in the sqlcmd
example.

Are there any other options to sending an attached file using SQL
SERVER 2008 & VB 2008 other than these 3 below?

(1) Execute SP_Send_CdoSysMail 'me (AT) me (DOT) com', @MailList, 'me (AT) me (DOT) com',
@subject, @EmailBody, @Attachment

(2) http://www.sqlservercurry.com/2010/0...rver-data.html

(3) sqlcmd -S Suprotim-PC -d Northwind -E -Q "SELECT CustomerID,
CompanyName, ContactName from Customers" -o "D:\MyData.csv" -s","

Any ideas, suggestions, references or examples are deeply appreciated

Sincerely

George Lewycky
NY City Transit Authority
New York City
grlewycky (AT) yahoo (DOT) com






-- =============================================

-- Author George Lewycky

-- Create date: May 09 2011

-- Description: This stored procedure creates the csv file via XML

-- of the daily checks processed

-- to be emailed to Treasury (also called the PWS
file)

-- =============================================

--

-- how are they inputting the the VOID & STOPS for the day !
using Admin/CheckVoid.aspx for both

--
================================================== ===========================================



CREATE PROCEDURE [dbo].[SP_UpdateDailyCheckPWS]

(@CheckXML text,

@MailList varchar(8000),

@PWSDate varchar(10))



AS



BEGIN



-- handle

DECLARE @idoc INT



-- acct# 601824634 pulled from dbo.bankaccount

DECLARE @AccountNum char(09)

-- check # currently 6 digits

DECLARE @CheckNumber char(09)

DECLARE @CheckDate char(08)

DECLARE @CheckAmount varchar(06)

DECLARE @CustName varchar(80)

DECLARE @CheckStatus char(1)



-- sizes suffucient ???

DECLARE @EmailBody varchar(1000)

DECLARE @Attachment varchar(1000)

DECLARE @subject varchar(100)



SET @subject = 'Check Register For ' + DATENAME(weekday,GETDATE()) +
',' +

CONVERT(varchar, cast(GETDATE() as date) ,101)



SET @Emailbody = char(13) + char(13) + 'The following checks were
printed for customers ' + char(13)





--sp_xml_preparedocument is called to obtain a document handle. This
document handle is passed to OPENXML

EXEC sp_xml_preparedocument @idoc OUTPUT, @CheckXML



declare check_cursor cursor for



SELECT *

FROM OPENXML (@idoc, '/xmldata/Checks',2)

WITH (AccountNum char(09),

CheckNumber char(09),

CheckDate char(08),

CheckAmount varchar(06),

CustName varchar(80),

CheckStatus char(1))



OPEN check_cursor



FETCH NEXT FROM check_cursor INTO @AccountNum, @CheckNumber,
@CheckDate, @CheckAmount, @CustName, @CheckStatus



while @@FETCH_STATUS = 0

begin



update Checks set PWSDate = @PWSDate where CheckNumber =
@CheckNumber



-- create comma delimited csv file with the columns in this
lineup for treasury

-- or do they expect Excel - Ros says Excel

set @Emailbody = @Emailbody + @CheckStatus + ',' +
@AccountNum + ',' + @CheckNumber + ',' + @CheckDate + ','

+ @CheckAmount + ',' + @CustName + char(13)





fetch next from check_cursor into @AccountNum, @CheckNumber,
@CheckDate, @CheckAmount, @CustName, @CheckStatus



end



-- remove the handle

EXEC sp_xml_removedocument @idoc



--- the body becomes an attachment also ?



----- set @Attachment =@Emailbody

----- Execute SP_Send_CdoSysMail 'me (AT) me (DOT) com', @MailList, 'me (AT) me (DOT) com',
@subject, @EmailBody, @Attachment

--

-- http://www.sqlservercurry.com/2010/0...rver-data.html

-- sqlcmd -S Suprotim-PC -d Northwind -E -Q "SELECT CustomerID,
CompanyName, ContactName from Customers" -o "D:\MyData.csv" -s","







Close check_cursor

Deallocate check_cursor

end





GO