Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    Newcastle, Australia
    Posts
    3

    Question Unanswered: Extending CDOSYS Mail to include Query Attachments??

    Hi,

    I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

    I have set up a SP for CDOSYS Mail as outlined in the following link:
    http://support.microsoft.com/default...312839&sd=tech

    Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

    Thanks,
    Kim

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Pearsok
    Hi,

    I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

    I have set up a SP for CDOSYS Mail as outlined in the following link:
    http://support.microsoft.com/default...312839&sd=tech

    Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

    Thanks,
    Kim
    Jasper has written an sp for it check it out here:
    http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649

  3. #3
    Join Date
    May 2004
    Location
    Newcastle, Australia
    Posts
    3

    Question Extending CDOSYS Mail to include Query Attachments??

    Sorry, I don't think I explained myself very well!

    I currently use xp_sendmail and pass a query as a parameter and attach the results of this query to an email. I would like to know if anyone has extended CDOSYS Mail to have the same functionality? Example of how I use this is in xp_sendmail @query parameter would be a query which returns the total number of records on an import table and xp_sendmail sends the results in an email attachement OR sends the results as part of the body of the email.

    Extract of the xp_sendmail Syntax:
    xp_sendmail {[@recipients =] 'recipients [;...n]'}
    [,[@query =] 'query']

    [,[@attach_results =] 'attach_value']


  4. #4
    Join Date
    Jun 2004
    Posts
    1

    Including the query attachment

    I had the same problem and found a workaround using osql to write the results of the query to a file. I then include the file as an attachment and delete it from the server. Seems to work well even within a loop.

    I used this to work from.
    http://www.sqlteam.com/item.asp?ItemID=4722

    And came up with this command line that seems to create a file with the same format as the attach query results did with sendMail:

    SET @bcpCommand = "osql -h-1 -w800 /U usrId /P pw /d " + @dbName + " /Q ""myprocname parm1, parm2"" -o "
    SET @bcpCommand = @bcpCommand + @FileName

    EXEC master..xp_cmdshell @bcpCommand


    Then just pass @fileName as the attachment to the cdosys mail procedure. I then use xp_cmdshell to delete the file from the server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •