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
ELSE
exec msdb.dbo.sp_send_dbmail
@recipients = @to
,@subject = 'DS Error'
,@body = 'No file was found. Please check \\source\ for today''s file.'
End
Any thoughts on what the issue might be? Symptoms? Error code? Anything?
What is the error, problem?
Hello,
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.
THanks
declare @dir table
(
ID int identity(1, 1)
primary key
not null,
dirEntry nvarchar(max)
);
insert into @dir
(dirEntry)
exec xp_cmdshell 'dir ' + @filePath + N'.';
select d.dirEntry
from @dir as d
where d.dirEntry like N'DTH' + @fileDate + '%.xlsx'