Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #335

deas to ultimately create and send a csv file (comma seperated value)

Show all headers | View raw

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@me.com', @MailList, 'me@me.com',
@subject, @EmailBody, @Attachment

(2) http://www.sqlservercurry.com/2010/01/using-sqlcmd-to-export-sql-server-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@yahoo.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@me.com', @MailList, 'me@me.com',
@subject, @EmailBody, @Attachment

--

-- http://www.sqlservercurry.com/2010/01/using-sqlcmd-to-export-sql-server-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

Back to comp.databases.ms-sqlserver | Previous | Next | Find similar

Thread

deas to ultimately create and send a csv file (comma seperated value) George Lewycky <gelewyc@nyct.com> - 2011-05-16 18:03 -0700

csiph-web