4. Send Email From SQL Server With Attachments скачать в хорошем качестве

4. Send Email From SQL Server With Attachments

Hey everyone, I know I said there were only 3 videos in this series but I was so excited about increasing the flexibility of sending attachments using SQL Server, I have to make a few more. In this video, I bring in Senior SQL Developer and my mentor, Martin Lazarus, to help explain the complicated code. Disclaimer: Consult with your DBA before executing any of these commands. The code: ------------------------------------------------------------------------- --Needed for xp_cmdshell to work ------------------------------------------------------------------------- sp_configure 'show advanced options', '1' RECONFIGURE -- this enables xp_cmdshell sp_configure 'xp_cmdshell', '1' RECONFIGURE ------------------------------------------------------------------------- CREATE PROCEDURE dbo.GetFilesFromFileSystem ( @Directory VARCHAR(5000), @Extension VARCHAR(50) = NULL, @FileName VARCHAR(100) = NULL, @Files VARCHAR(MAX) OUTPUT ) AS BEGIN ------------------------------------------------------ -- TESTING PARAMETERS ------------------------------------------------------ --DECLARE @Directory VARCHAR(5000) = 'C:\AdventureWorks2017SSISProjects\Employees'; --DECLARE @Extension VARCHAR(50) = NULL; --DECLARE @FileName VARCHAR(100) = 'Employee'; --Create command for file system DECLARE @command VARCHAR(5000); SET @command = 'dir /b ' + @Directory; --Create a temp table that stores file system data IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files CREATE TABLE #Files(Directory VARCHAR(5000), FileName VARCHAR(2000)) INSERT INTO #Files (FileName) EXEC master.dbo.xp_cmdshell @command --Delete the NULL row that's added DELETE FROM #Files WHERE FileName IS NULL --Update table with directory as the prefix UPDATE #Files SET Directory = @Directory --Select Files based on Extension and FileName SET @Files = ( SELECT Directory + '\' + FileName + ';' FROM #Files WHERE FileName LIKE CASE WHEN @FileName is not null THEN '%' + @FileName + '%' else '%' END AND FileName LIKE CASE WHEN @Extension is not null THEN '%.' + @Extension else '%' END FOR XML PATH('') ) SET @Files = LEFT(@Files, LEN(@Files) - 1) END ------------------------------------------------------------------------------- --Getting E-mails from table DECLARE @Recipeants VARCHAR(1000) = (select EmailColumnName + ';' from [Table with Emails] FOR XML PATH('')) SET @Recipeants = LEFT(@Recipeants, LEN(@Recipeants) - 1) SELECT @Recipeants --Get attachments dynamically DECLARE @Attachments VARCHAR(MAX) exec Custom_Objects.dbo.GetFilesFromFileSystem @Directory = 'C:\AdventureWorks2017SSISProjects\Employees', @FileName = 'Employees', @Files = @Attachments OUTPUT select @Attachments --Tie everything together and send E-mail exec [Custom_Objects]..[SendEmail] @Recipeants,'This is a test email','This came from my laptop',@Attachments