How to export a SQL View to a csv file

Hi all.

I have a DTS that runs weekly, the DTS consists of an ActiveX Script, it looks at a View, it then creates an email and populates an HTML table with the results from the view. That's been working great but now, the user wants to receive the results in a csv instead of an html table, but I don't know how to attach the view results to my email with the script I've been using.

I think the solution would be to replace my: Function TableString(strQuery) with a function that will export the view to a csv file and attach it to my email, I want to create the csv file on the fly, I don't want to save the csv file anywhere.

Any suggestions? Here is my ActiveX Script:

'****************************************************
'  Visual Basic ActiveX Script
'**********************************************************
Function Main()
Dim strQuery
Dim strTableResult
Dim bMail
Dim oConn
Dim rstChk
Dim cntChk


'check if there are any records
set oConn = CreateObject("ADODB.Connection")
set rstChk = CreateObject("ADODB.Recordset")
   
'open connection
   oConn.Open "Driver={SQL Server};Server=WIN1"

'these steps will get the count of records in your view

rstChk.Open "select count(*) FROM [database].dbo.[Expired]" , oConn  
rstChk.MoveFirst
cntChk = rstChk(rstChk.Fields.Item(0).Name).Value
set oConn = Nothing
set rstChk = Nothing

'use conditional IF logic to determine whether or not remainder of task will execute
if cntChk > 0 Then
        'go through entire process to send email

            'specify query, build HTML Table (string)  from query results
        strQuery = "SELECT * FROM [database].dbo.[Expired]"

        strTableResult = TableString(strQuery)
       
   

            'call the SendMail function and pass the string containing query used to build HTML table
            bMail = SendMail(strTableResult)
   
        'if the message was sent successfully then return success else return failure
            If bMail = True Then
                 Main = DTSTaskExecResult_Success
            Else
                  Main = DTSTaskExecResult_Failure
            End If
Else
    'Exit task, report success
    Main = DTSTaskExecResult_Success
End If
End Function





'**********************************************************
'Purpose: To send an e-mail message
'Inputs: String containing HTML table
'Ouputs: boolean value

Function SendMail(strTableResult)
    Dim objMessage
            On Error Resume Next
                Dim myMail
   
        'intialize cdo message object
        Set myMail=CreateObject("CDO.Message")

       'define
        myMail.Subject="Expired"
    'this can be made up or real email address (FROM field)
        myMail.From="test@abc.com"
        myMail.To= "beta@abc.com"
   
    'this is the body of the email, you can change it to suit your needs
        myMail.HTMLBody= "The Following Have Expired "  &"<br><br>"& chr(13) & chr(13)& chr(13) & chr(13) & _
        strTableResult &"<br><br>"& chr(13) & chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &chr(13) &  _
        "If you have any questions, please contact ext 100." & Chr(13) & Chr(13) & "<auto generated message>"
   
    'establish settings for sending of CDO message
        myMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
        'Name or IP of remote SMTP server
        myMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            ="67.59.151.222"
        'Server port
        myMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            =25
        myMail.Configuration.Fields.Update
       
    'send and clean up
        myMail.Send
        set myMail=nothing
 
'return boolean to calling function
            If ErrorCount <> 0 Then
              SendMail = False
            Else
              SendMail = True
            End If

End Function

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

'Purpose: To build an HTML table string from a query
'Inputs: String with query name (Note - modify this to allow passing of connection string)
'Ouputs: string containing code to build HTML table

Function TableString(strQuery)

    Dim rstResult
    Dim oConn
    Dim strTable

    'Initialize connection and recordset
    set oConn = CreateObject("ADODB.Connection")
    set rstResult = CreateObject("ADODB.Recordset")
   
    'open connection
   oConn.Open "Driver={SQL Server};Server=WIN1"

   
    'fill recordset
    rstResult.Open strQuery, oConn
   
    'initialize HTML table string (adjust width to fit  query)    
    strTable = "<table border = 1 width = 500> <tr>"
   
    'move to first row in recordset
    rstResult.MoveFirst

    'Add columns and headers to the table
    For Index = 0 to rstResult.Fields.Count -1
        strTable = strTable &"<td bgcolor = 'blue'><font color = 'white'>" & _
        rstResult.Fields.Item(Index).Name &  "</font></td>"
    Next
   
    'close row?
    strTable = strTable & "</tr>"

    'loop through recordset, populating each row of table
    While (Not rstResult.EOF)
        strTable = strTable & "<tr>"
            'loop through recordset columns and add one value at a time
            For Index = 0 to rstResult.Fields.Count - 1
                strTable = strTable & "<td>" & rstResult(rstResult.Fields.Item(Index).Name).Value & _
                "<br></td>"
            Next
        strTable = strTable & "</tr>"
        rstResult.MoveNext
    Wend

      'clean up and to dispose of any objects the script creates.


      set oConn = Nothing
      set rstResult = Nothing
      
          
    'close out HTML string
    strTable = strTable & "</table>"
   
    'return HTML string
    TableString = strTable

End FunctionStart Free Trial