Help with sending email with attachment when @@ROWCOUNT <> 0

[TomR100]

I am trying to create a proc that will email me an attachment of employees that do not have a date in their Week# columns for the month.

I am trying to send the email/attachment but I keep getting the error "A specified attachment was not found". I do not know where to go from here.

This is my code
BEGIN
DECLARE @intRowCountResults as Integer,
@strEmailTitle as VarChar(100),
@strFileName as VarChar(23),
@strSQL as VarChar(1500),
@strStatus as VarChar(90),
@StartDate as VarChar(20)

SELECT
@strEmailTitle = 'HWP Missing Employee Information Report' SELECT @strFileName = 'MissingEmployee.txt'
SET @strStatus = '(''D'',''E'',''F'',''H'',''N'',''R'',''S'',''T'',''U'')'
SET @StartDate = Convert(Char(10),(GetDate()-4),112)

My query for the xp_sendmail
SET @strSQL = 'SELECT MCard.Employee_ID as Employee_ID,
MCard.Card_Date as CardDate,
EMaster.Status as Status
FROM tblMonthCard AS MCard
LEFT JOIN tblEmployeeMaster AS EMaster
ON (MCard.Employee_ID=EMaster.Employee_ID)
WHERE (MCard.Week1_Date IS NULL
OR MCard.Week2_Date IS NULL
OR MCard.Week3_Date IS NULL
OR MCard.Week4_Date IS NULL)
AND EMaster.Status IN ' + @strStatus +
' AND MCard.Card_Date = ' + @StartDate

SET NOCOUNT ON
This is where the check to see if there are any employees
SELECT MCard.Employee_ID as Employee_ID,
MCard.Card_Date as CardDate,
EMaster.Status as Status
FROM tblMonthCard AS MCard
LEFT JOIN tblEmployeeMaster AS EMaster
ON (MCard.Employee_ID=EMaster.Employee_ID)
WHERE (MCard.Week1_Date IS NULL
OR MCard.Week2_Date IS NULL
OR MCard.Week3_Date IS NULL
OR MCard.Week4_Date IS NULL)
AND EMaster.Status
IN 'D','E','F','H','N','R','S','T','U')
AND MCard.Card_Date= @StartDate

SET @intRowCountResults = @@ROWCOUNT

IF @intRowCountResults > 0
EXEC master.dbo.xp_sendmail
@recipients = 'tomr100@email.com',
@subject = @strEmailTitle,
@message = 'The attached employee list',
@width = 255,
@query = @strSQL,
@attachments = @strFileName

SET NOCOUNT OFF
END

 

[NathanGriffiths]

You need to include the parameters @attach_results = 'true' - without this xp_sendmail tries to find a file of the name specified on the disk. Specifying @attach_results = 'true' causes xp_sendmail to use the first filename specified in @attachments to be used as the name of the file in which the results of @query are placed and attached to the e-mail.

e.g.

  IF @intRowCountResults > 0
        EXEC master.dbo.xp_sendmail     
        @recipients = 'tomr100@email.com',  
        @subject = @strEmailTitle,
        @message = 'The attached employee list',
        @width = 255,  
        @query = @strSQL, 
        @attachments = @strFileName,
        @attach_results = 'true'

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[TomR100]

Thank you Nathan.

I get the email but at the top of the attachment I get the statement Arithmetic overflow occurred. Is this a problem?

SQL Books online is not very helpful explaining this statement.

Thanks again.