SQLTeam.com | Weblogs | Forums

SQL Email Attachment Troubleshoot


ALTER Procedure   [dbo].[DSEmail] as begin

declare  @to varchar(1000)          
        ,@sub varchar(1000)         
        ,@subdate varchar(10)       = CONVERT(Varchar(10),GETDATE(),101)
        ,@bod varchar(1000)         
        ,@filePath varchar(1000)    = '\\source\'
        ,@fileDate varchar (8)      = CONVERT(Varchar(8),GETDATE(),112)
        ,@attachments varchar(1000)
        ,@pathAndFile varchar (1000)

Set @attachments = 'DTH' + @fileDate + '^.*$'
Set @pathAndFile = @filePath + @attachments
Set @sub         = 'dS Report ' + @subdate
Set @bod         = '\\source\' + @attachments

Set @to = 'email@email.com'

IF dbo.fn_FileExists(@pathAndFile) = 1
exec msdb.dbo.sp_send_dbmail
             @recipients = @to
            ,@subject = @sub
            ,@body = @bod
            ,@file_attachments = @pathAndFile


exec msdb.dbo.sp_send_dbmail
             @recipients = @to
            ,@subject = 'DS Error'
            ,@body = 'No file was found. Please check \\source\ for today''s file.'



Any thoughts on what the issue might be? Symptoms? Error code? Anything?


What is the error, problem?



I was told regex will not work with IF dbo.fn_FileExists and to try using this:
I am having trouble figuring out how to incorporate the below to replace the regex. Any help would be most appreciated.

declare @dir table
ID int identity(1, 1)
primary key
not null,
dirEntry nvarchar(max)

insert into @dir
exec xp_cmdshell 'dir ' + @filePath + N'.';

select d.dirEntry
from @dir as d
where d.dirEntry like N'DTH' + @fileDate + '%.xlsx'