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
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