Post reply

Database Mail - EXEC msdb.dbo.sp_send_dbmail

  • July 21, 2009 at 10:15 am

    #136377

    I have a Row Count that saves to a variable RowCountResult. How do I include that in the body of my email? I'd ilke the body to say something like: There are ___ records.

    I'd also like it to send an attachment, but the attachment has a dynamic filename. Something like: C:\Ryan\Output_072109.xls or C:\Ryan\Output_June_2009.xls

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'ryan@test.com',

    @subject ='Test Message Subject',

    @profile_name ='Ryan_Test'

    @body = ???

    @file_attachments = ???

    Any input/solution/comment is greatly appreciated!!

    Merci!

    Ryan

  • July 21, 2009 at 10:57 am

    #1027804

    Hi Ryan,

    Something like this where @YouVar is the variable with your count

    DECLARE @MessageBody AS VARCHAR(255)

    SET @MessageBody = 'Contains ' + @YourVar + ' records'

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'ryan@test.com',

    @subject ='Test Message Subject',

    @profile_name ='Ryan_Test'

    @body = @MessageBody,

    @file_attachments = ???

    But watch out for @YourVar being NULL

    You can probably do something similar with your attachment

    Allen

  • July 21, 2009 at 11:30 am

    #1027819

    I have:

    Set @body = 'This step generated ' + @RowCountResult + ' records.'

    But I'm getting an error: "Must declare the scalar variable "@RowCountResult"

    I searched it and the solutions don't make sense to me. Replace "@RowCountResult" with "?" ?? Kind of weird.

    @RowCountResult is Int32 DataType so I tried: CAST(@RowCountResult AS varchar(10)) but it didn't work.

  • July 21, 2009 at 12:15 pm

    #1027831

    Can you post the SQL?

  • July 21, 2009 at 12:23 pm

    #1027838

    Declare @body VARCHAR(255)

    Set @body = 'This step generated ' + CAST(@RowCountResult AS varchar(10)) + ' records.'

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'ryan@test.com',

    @subject = 'Message Subject',

    @profile_name ='Ryan_Test',

    @body = @body

    I'm guessing it's because I didn't declare/set @RowCountResult in this Execute SQL Task?

  • July 21, 2009 at 12:42 pm

    #1027853

    Great - I think you answered the question!

  • July 21, 2009 at 1:10 pm

    #1027870

    hehe So how do I do that exactly?

    Declare @RowCountResult Int (or Varchar??)

    Set @RowCountResult = 'SELECT Count(*) AS CountResult From Table Where name = 'Ryan''

    I'm getting a syntax error.

  • July 21, 2009 at 1:15 pm

    #1027872

    SELECT @RowCountResult = Count(*) AS CountResult From Table Where name = 'Ryan'

  • July 21, 2009 at 1:17 pm

    #1027873

    actually u may get errors because of no values or null values.

    Check out my blog for this section:

    http://sqlscape.blogspot.com/2009_03_01_archive.html

    It has the section to set the value correctly.

    Hope it helps.

  • July 21, 2009 at 1:18 pm

    #1027874

    More like this (on sql 2005)

    DECLARE @i AS INT

    SELECT @i = COUNT(*) FROM sys.columns WHERE object_id = 8

    SELECT @i -- lets see the result

  • July 21, 2009 at 1:32 pm

    #1027880

    Probably no need for NULL check because

    DECLARE @i AS INT

    SELECT @i = COUNT(*) FROM sys.columns WHERE 1 = 0

    SELECT @i

    Returns 0

    but no bad thing to mention our friend/enemy NULL 🙂

  • July 21, 2009 at 2:01 pm

    #1027901

    It worked!! Thanks so much for the help!

    I'm not worried about the NULL values. I have a Row Count that executes first and I can just edit the Constraint going to this Execute SQL Task. 🙂

    Thanks again!

  • July 21, 2009 at 2:25 pm

    #1027917

    Very welcome

Viewing 13 posts - 1 through 12 (of 12 total)

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