MZ@ !L!This program cannot be run in DOS mode. $-TٽL:L:L:FL:BL:RichL:PEdyK" S`_@`.rsrc@@(H 8Ph  ( @ X p        0 H ` x  (8HXhx(8HXhxzԐ1( >F,t@`X|.,0x\RDfJ*n D SCRIPTFILEINSTALL_DATABASEMAILUNINSTALL_DATABASEMAIL/**********************************************************************/ /* INSTALL_DATABASEMAIL.SQL */ /* */ /* Installs the tables, triggers and stored procedures necessary for */ /* dbmail operations */ /* */ /* ** Copyright Microsoft, Inc. 2003 ** All Rights Reserved. */ /**********************************************************************/ PRINT '----------------------------------------------' PRINT 'Starting execution of INSTALL_DATABASEMAIL.SQL' PRINT '----------------------------------------------' go PRINT '----------------------------------' PRINT ' OBD: enable sqlimail ' PRINT '----------------------------------' declare @sqlimail_enable bit declare @show_advanced bit select @show_advanced = cast(value_in_use as bit) from sys.configurations where name = N'show advanced options' select @sqlimail_enable = cast(value_in_use as bit) from sys.configurations where name = N'Database Mail XPs' if 1 <> @sqlimail_enable begin if 1 <> @show_advanced begin exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1 reconfigure end if 1 <> @sqlimail_enable begin exec sys.sp_configure @configname = N'Database Mail XPs', @configvalue = 1 reconfigure end if 1 <> @show_advanced begin exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0 reconfigure end end go IF (NOT EXISTS (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME() AND 80 <= compatibility_level)) BEGIN IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1) RAISERROR(14660, 16, 1) WITH NOWAIT -- not sys-admin ELSE RAISERROR(14660, 20, 1) WITH LOG, NOWAIT -- sys-admin END go --Check if SSB is enabled in this database IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N'IsBrokerEnabled'), 0) <> 1) BEGIN RAISERROR(14650, 16, 1) END go /**************************************************************/ /* Record time of start of creates */ /**************************************************************/ SELECT start = getdate() INTO #InstIMail go -- Explicitly set the options that the server stores with the object in sysobjects.status SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON -- We don't want (NULL = NULL) == TRUE SET ANSI_PADDING ON -- Set so that trailing zeros aren't trimmed off sysjobs.owner_login_sid go -- Allow updates to system catalogs so that all our SP's inherit full DML capability on -- system objects and so that we can exercise full DDL control on our system objects EXECUTE master.dbo.sp_configure N'allow updates', 1 go RECONFIGURE WITH OVERRIDE PRINT '' go /**************************************************************/ -- Creating all Mail TABLES /**************************************************************/ ----------------------------- -- Table drops, only if needed ----------------------------- SET NOCOUNT ON DECLARE @build_number INT DECLARE @rebuild_needed TINYINT SET @build_number = @@microsoftversion & 0xffff IF (@build_number < 1050) -- The last build that we changed the schema in SELECT @rebuild_needed = 1 ELSE SELECT @rebuild_needed = 0 -- table sysmail_log IF (@rebuild_needed = 1 AND OBJECT_ID('dbo.sysmail_log', 'U') IS NOT NULL) BEGIN PRINT 'Dropping TABLE sysmail_log' DROP TABLE sysmail_log END -- table sysmail_attachments IF (@rebuild_needed = 1 AND OBJECT_ID('dbo.sysmail_attachments', 'U') IS NOT NULL) BEGIN PRINT 'Dropping TABLE sysmail_attachments' DROP TABLE sysmail_attachments END -- table sysmail_quota_information IF (@rebuild_needed = 1 AND OBJECT_ID('dbo.sysmail_quota_information', 'U') IS NOT NULL) BEGIN PRINT 'Dropping TABLE sysmail_quota_information' DROP TABLE sysmail_quota_information END -- table sysmail_mailitems IF (@rebuild_needed = 1 AND OBJECT_ID('dbo.sysmail_mailitems', 'U') IS NOT NULL) BEGIN PRINT 'Dropping TABLE sysmail_mailitems' DROP TABLE sysmail_mailitems END GO ----------------------------------------------------------- -- TABLE sysmail_quota_information ----------------------------------------------------------- -- sysmail_quota_information : Contains one row for each mail sent -- it is used to enforce quota on number of mails sent -- login_name : the login that sent the mail -- sent_mail_time : time when the mail was sent IF(OBJECT_ID('dbo.sysmail_quota_information', 'U') IS NULL) BEGIN PRINT 'Creating TABLE sysmail_quota_information' create table sysmail_quota_information ( login_name sysname, sent_mail_time datetime ) CREATE CLUSTERED INDEX MAIL_QUOTA_IDX ON sysmail_quota_information(sent_mail_time, login_name) END ----------------------------------------------------------- -- TABLE sysmail_mailitems ----------------------------------------------------------- -- sysmail_mailitems : Contains one row for each mail sent using sp_send_dbmail. -- Contains mails that are waiting to be sent and the sent mail. -- sent_status determines its status -- -- mailitem_id : Id for the row. Auto-generated. -- profile_id : ID of profile to use to send the mail. -- recipients : People on the To list. -- copy_recipients : People on the Cc list. -- blind_copy_recipients : People on the Bcc list. -- subject : Subject of the email. -- body : Body of the email. -- body_format : Body format. 0 (Text), 1(Html) -- importance : Importance of the email: -- 0(Low), 1(Normal), 2(High). -- sensitivity : Sensitivity of the email: -- 0(Normal), 1(Personal), 2(Private), 3(Confidential). -- attachment_encoding : Encoding to use for mail and attachments: -- 0(MIME), 1(UUEncode), 2(BINHEX), 3(S/MIME). -- query : SQL query that was executed in this mail -- execute_query_database : The database to execute the query in -- attach_query_result_as_file : Option for attaching the query result -- as a file instead of in the mail body -- query_result_header : Option for including query result column headers -- query_result_width : The query result overall width in characters -- query_result_separator : The query result column separaror character -- exclude_query_output : Option for supressing query output being returned to -- the client that is sending the mail -- append_query_error : Option for appending query error messages to the mail item -- send_request_date : Date this mail item was created -- send_request_user : The user that created this mail item -- sent_account_id : The account_id that was used to send this mail item -- sent_status : The current status of the mail item. -- : 0(PendingSend), 1(SendSuccessful), 2(SendFailed), 3(AttemptingSendRetry) -- sent_date : Date the mail item was sent or failed to be sent IF(OBJECT_ID('dbo.sysmail_mailitems', 'U') IS NULL) BEGIN PRINT 'Creating TABLE sysmail_mailitems' CREATE TABLE sysmail_mailitems ( mailitem_id INT IDENTITY(1,1) NOT NULL, profile_id INT NOT NULL, recipients VARCHAR(MAX) NULL, copy_recipients VARCHAR(MAX) NULL, blind_copy_recipients VARCHAR(MAX) NULL, subject NVARCHAR(255) NULL, body NVARCHAR(MAX) NULL, body_format VARCHAR(20) NULL, importance VARCHAR(6) NULL, sensitivity VARCHAR(12) NULL, file_attachments NVARCHAR(MAX) NULL, attachment_encoding VARCHAR(20) NULL, query NVARCHAR(MAX) NULL, execute_query_database sysname NULL, attach_query_result_as_file BIT NULL, query_result_header BIT NULL, query_result_width INT NULL, query_result_separator CHAR(1) NULL, exclude_query_output BIT NULL, append_query_error BIT NULL, send_request_date DATETIME NOT NULL DEFAULT GETDATE(), send_request_user sysname NOT NULL DEFAULT SUSER_SNAME(), sent_account_id INT NULL, sent_status TINYINT NULL DEFAULT 0, sent_date DATETIME NULL, last_mod_date DATETIME NOT NULL DEFAULT GETDATE(), last_mod_user sysname NOT NULL DEFAULT SUSER_SNAME(), CONSTRAINT [sysmail_mailitems_id_MustBeUnique] PRIMARY KEY(mailitem_id), CONSTRAINT [sysmail_OutMailMustHaveAtleastOneRecipient] CHECK (NOT (recipients IS NULL AND copy_recipients IS NULL AND blind_copy_recipients IS NULL)), CONSTRAINT [sysmail_OutMailRecipientCannotBeEmpty] CHECK (DATALENGTH(ISNULL(recipients, '')) + DATALENGTH(ISNULL(copy_recipients, '')) + DATALENGTH(ISNULL(blind_copy_recipients, '')) <> 0), CONSTRAINT [sysmail_OutMailAttachmentEncodingMustBeValid] CHECK (attachment_encoding IN ('MIME', 'S/MIME', 'BINHEX', 'UUENCODE')), CONSTRAINT [sysmail_OutMailImportanceMustBeValid] CHECK (importance IN ('LOW', 'NORMAL', 'HIGH')), CONSTRAINT [sysmail_OutMailSensitivityMustBeValid] CHECK (sensitivity IN('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL')) ) END GO ----------------------------------------------------------- -- TABLE sysmail_attachments ----------------------------------------------------------- -- sysmail_attachments : Contains mail item attachments -- -- attachment_id : Id for the row. Auto-generated -- mailitem_id : Optional key to the mail items that this entry is a about -- filename : The filename of the attachment -- filesize : Size of the file -- encoded_attachment : The file data encoded in base64 IF (OBJECT_ID('dbo.sysmail_attachments', 'U') IS NULL) BEGIN PRINT 'Creating TABLE sysmail_attachments' CREATE TABLE sysmail_attachments ( attachment_id INT IDENTITY(1, 1) NOT NULL, mailitem_id INT NOT NULL REFERENCES sysmail_mailitems(mailitem_id), filename NVARCHAR(260) NOT NULL, filesize INT NOT NULL, attachment VARBINARY(MAX) NULL, last_mod_date DATETIME NOT NULL DEFAULT GETDATE(), last_mod_user sysname NOT NULL DEFAULT SUSER_SNAME() ) END GO ----------------------------------------------------------- -- TABLE sysmail_send_retries ----------------------------------------------------------- -- sysmail_send_retries : Contains send mail retry history -- -- conversation_handle : The conversation handle that initiated the retry -- mailitem_id : Optional key to the mail items that this entry is a about -- send_attempts : The current number of send attempts -- last_send_attempt_date : date of the last send attempt IF (OBJECT_ID('dbo.sysmail_send_retries', 'U') IS NULL) BEGIN PRINT 'Creating TABLE sysmail_send_retries' CREATE TABLE sysmail_send_retries ( conversation_handle uniqueidentifier PRIMARY KEY NOT NULL, mailitem_id INT NOT NULL REFERENCES sysmail_mailitems(mailitem_id), send_attempts INT NOT NULL DEFAULT 1, last_send_attempt_date DATETIME NOT NULL DEFAULT GETDATE() ) END GO ----------------------------------------------------------- -- TABLE sysmail_log ----------------------------------------------------------- -- sysmail_log : Contains error and event logging -- -- log_id : Id for the row. Auto-generated. -- event_type : The event type for this record -- 0(Success), 1(information), 2(Warning), 3(error) -- log_date : Create date of this log entry -- description : The text description of this entry -- process_id : The DatabaseMail (exe) process id that added this entry -- mailitem_id : Optional key to the mail items that this entry is a about -- account_id : Optional account_id hat this entry is a about IF (OBJECT_ID('dbo.sysmail_log', 'U') IS NULL) BEGIN PRINT 'Creating TABLE sysmail_log' CREATE TABLE sysmail_log ( log_id INT IDENTITY(1, 1) NOT NULL, event_type INT NOT NULL, log_date DATETIME NOT NULL DEFAULT GETDATE(), description NVARCHAR(max) NULL, process_id INT NULL, mailitem_id INT NULL, account_id INT NULL, last_mod_date DATETIME NOT NULL DEFAULT GETDATE(), last_mod_user sysname NOT NULL DEFAULT SUSER_SNAME(), CONSTRAINT [sysmail_log_id_MustBeUnique] PRIMARY KEY(log_id), CONSTRAINT [sysmail_MailItemIdMustBeValid] FOREIGN KEY(mailitem_id) REFERENCES sysmail_mailitems(mailitem_id) ) END GO ----------------------------------------------------------- PRINT 'Creating TABLE sysmail_query_transfer' ----------------------------------------------------------- -- Always drop sysmail_query_transfer. It has no user data IF (OBJECT_ID('dbo.sysmail_query_transfer', 'U') IS NOT NULL) DROP TABLE sysmail_query_transfer -- sysmail_query_transfer : Table used to transfer data between a helper xp's and the calling sp's. -- Rows are created and deleted in the context of each call -- -- uid : guid for the row. Generated by the user -- text_data : Attachment data in binary form CREATE TABLE sysmail_query_transfer ( uid uniqueidentifier NOT NULL PRIMARY KEY, text_data NVARCHAR(max) NULL, create_date DATETIME NOT NULL DEFAULT GETDATE() ) GO ----------------------------------------------------------- PRINT 'Creating TABLE sysmail_attachments_transfer' ----------------------------------------------------------- -- Always drop sysmail_attachments_transfer. It has no user data IF (OBJECT_ID('dbo.sysmail_attachments_transfer', 'U') IS NOT NULL) DROP TABLE sysmail_attachments_transfer -- sysmail_attachments_transfer : Table used to transfer data between a helper xp's -- and the calling sp's. Rows are created and deleted -- in the context of each call -- -- uid : guid for the row. Generated by the user -- filename : Attachment file name -- filesize : Attachment file size in bytes -- attachment : Attachment data in binary form CREATE TABLE sysmail_attachments_transfer ( transfer_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, uid uniqueidentifier NOT NULL, filename NVARCHAR(260) NOT NULL, filesize INT NOT NULL, attachment VARBINARY(MAX) NULL, create_date DATETIME NOT NULL DEFAULT GETDATE() ) GO /**************************************************************/ -- Create all triggers /**************************************************************/ ----- PRINT 'Creating TRIGGER trig_sysmail_mailitems' ----- IF (OBJECT_ID('dbo.trig_sysmail_mailitems', 'TR') IS NOT NULL) DROP TRIGGER dbo.trig_sysmail_mailitems GO CREATE TRIGGER trig_sysmail_mailitems ON sysmail_mailitems FOR UPDATE AS BEGIN UPDATE sysmail_mailitems SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() FROM sysmail_mailitems m, inserted i WHERE m.mailitem_id = i.mailitem_id END GO ----- PRINT 'Creating TRIGGER trig_sysmail_attachments' ----- IF (OBJECT_ID('dbo.trig_sysmail_attachments', 'TR') IS NOT NULL) DROP TRIGGER dbo.trig_sysmail_attachments GO CREATE TRIGGER trig_sysmail_attachments ON sysmail_attachments FOR UPDATE AS BEGIN UPDATE sysmail_attachments SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() FROM sysmail_attachments a, inserted i WHERE a.attachment_id = i.attachment_id END GO ----- PRINT 'Creating TRIGGER trig_sysmail_log' ----- IF (OBJECT_ID('dbo.trig_sysmail_log', 'TR') IS NOT NULL) DROP TRIGGER dbo.trig_sysmail_log GO CREATE TRIGGER trig_sysmail_log ON sysmail_log FOR UPDATE AS BEGIN UPDATE sysmail_log SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() FROM sysmail_log l, inserted i WHERE l.log_id = i.log_id END GO /**************************************************************/ -- Drop Create all DatabaseMail Util Functions /**************************************************************/ ----------------------------------------------------------- -- function ConvertToInt ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.ConvertToInt', 'FN') IS NULL DROP FUNCTION ConvertToInt GO ----- PRINT 'Creating function ConvertToInt' ----- GO -- ConvertToInt : Converts a string to integer. Returns null -- if the input string is not a valid int. -- CREATE FUNCTION ConvertToInt(@string nvarchar(255), @maxValue int, @defValue int) RETURNS int AS BEGIN DECLARE @value bigint SET @value = @defValue SET @string = LTRIM(RTRIM(@string)) -- Check if there is any character other than 0-9 in the string. IF ((@string IS NOT NULL AND @string <> N'') AND (@string NOT LIKE '%[^0-9]%')) BEGIN --INT's have a max of 10 digits IF(LEN(@string) <= 10) BEGIN -- Try converting to bigint. Return default if the value is bigger than @maxValue SET @value = CONVERT(bigint, @string) IF(@value > CONVERT(bigint, @maxValue)) SET @value = @defValue END END RETURN CONVERT(int, @value) END GO /**************************************************************/ -- Drop and Create all DatabaseMail Stored Procedures /**************************************************************/ ----------------------------------------------------------- -- procedure sysmail_start_sp ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sysmail_start_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_start_sp GO ----- PRINT 'Creating sysmail_start_sp' ----- GO -- sysmail_start_sp : allows databasemail to process mail from the queue CREATE PROCEDURE sysmail_start_sp AS SET NOCOUNT ON DECLARE @rc INT DECLARE @localmessage nvarchar(255) ALTER QUEUE ExternalMailQueue WITH STATUS = ON; SELECT @rc = @@ERROR IF(@rc = 0) BEGIN ALTER QUEUE SysMailNotificationQueue WITH ACTIVATION (STATUS = ON); SELECT @rc = @@ERROR IF(@rc = 0) BEGIN SET @localmessage = FORMATMESSAGE(14639, SUSER_SNAME()) INSERT sysmail_log (event_type, description) VALUES (1, @localmessage) END END RETURN @rc GO ----------------------------------------------------------- -- procedure sysmail_stop_sp ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sysmail_stop_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_stop_sp GO ----- PRINT 'Creating sysmail_stop_sp' ----- GO -- sysmail_stop_sp : stops the DatabaseMail process. Mail items remain in the queue until sqlmail started CREATE PROCEDURE sysmail_stop_sp AS SET NOCOUNT ON DECLARE @rc INT DECLARE @localmessage nvarchar(255) ALTER QUEUE SysMailNotificationQueue WITH ACTIVATION (STATUS = OFF); SELECT @rc = @@ERROR IF(@rc = 0) BEGIN ALTER QUEUE ExternalMailQueue WITH STATUS = OFF; IF(@rc = 0) BEGIN SET @localmessage = FORMATMESSAGE(14640, SUSER_SNAME()) INSERT sysmail_log (event_type, description) VALUES (1, @localmessage) END END RETURN @rc GO ----------------------------------------------------------- -- procedure sysmail_logmailevent_sp ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sysmail_logmailevent_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_logmailevent_sp GO ----- PRINT 'Creating sysmail_logmailevent_sp' ----- GO -- sysmail_logmailevent_sp : inserts an entry in the sysmail_log table CREATE PROCEDURE sysmail_logmailevent_sp @event_type INT, @description NVARCHAR(max) = NULL, @process_id INT = NULL, @mailitem_id INT = NULL, @account_id INT = NULL AS SET NOCOUNT ON INSERT sysmail_log(event_type, description, process_id, mailitem_id, account_id) VALUES(@event_type, @description, @process_id , @mailitem_id, @account_id) RETURN 0 GO ----------------------------------------------------------- -- procedure sp_SendMailMessage ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_SendMailMessage', 'P') IS NULL DROP PROCEDURE dbo.sp_SendMailMessage GO ----- PRINT 'Creating sp_SendMailMessage' ----- GO -- sp_SendMailMessage : Sends a request on the mail items SSB queue CREATE PROCEDURE sp_SendMailMessage @contract_name sysname, -- Name of contract @message_type sysname, -- Type of message @request varchar(max) -- XML message to send WITH EXECUTE AS 'dbo' AS SET NOCOUNT ON DECLARE @conversationHandle uniqueidentifier; DECLARE @error int -- Start a conversation with the remote service BEGIN DIALOG @conversationHandle FROM SERVICE [InternalMailService] TO SERVICE 'ExternalMailService' ON CONTRACT @contract_name -- Check error SET @error = @@ERROR IF @error <> 0 BEGIN RETURN @error END -- Send message ;SEND ON CONVERSATION @conversationHandle MESSAGE TYPE @message_type (@request) -- Check error SET @error = @@ERROR IF @error <> 0 BEGIN RETURN @error END RETURN 0 GO ----------------------------------------------------------- -- procedure sp_isprohibited ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_isprohibited', 'P') IS NULL DROP PROCEDURE dbo.sp_isprohibited GO ----- PRINT 'Creating sp_isprohibited' ----- GO -- sp_isprohibited : To test if the attachment is prohibited or not. -- CREATE PROCEDURE sp_isprohibited @attachment nvarchar(max), @prohibitedextensions nvarchar(1000) AS DECLARE @extensionIndex int DECLARE @extensionName nvarchar(255) IF (@attachment IS NOT NULL AND LEN(@attachment) > 0) BEGIN SET @prohibitedextensions = UPPER(@prohibitedextensions) -- find @extensionName: the substring between the last '.' and the end of the string SET @extensionIndex = 0 WHILE (1=1) BEGIN DECLARE @lastExtensionIndex int SET @lastExtensionIndex = CHARINDEX('.', @attachment, @extensionIndex+1) IF (@lastExtensionIndex = 0) BREAK SET @extensionIndex = @lastExtensionIndex END IF (@extensionIndex > 0) BEGIN SET @extensionName = SUBSTRING(@attachment, @extensionIndex + 1, (LEN(@attachment) - @extensionIndex)) SET @extensionName = UPPER(@extensionName) -- compare @extensionName with each extension in the comma-separated @prohibitedextensions list DECLARE @currentExtensionStart int DECLARE @currentExtensionEnd int SET @currentExtensionStart = 0 SET @currentExtensionEnd = 0 WHILE (@currentExtensionEnd < LEN(@prohibitedextensions)) BEGIN SET @currentExtensionEnd = CHARINDEX(',', @prohibitedextensions, @currentExtensionStart) IF (@currentExtensionEnd = 0) -- we have reached the last extension of the list, or the list was empty SET @currentExtensionEnd = LEN(@prohibitedextensions)+1 DECLARE @prohibitedExtension nvarchar(1000) SET @prohibitedExtension = SUBSTRING(@prohibitedextensions, @currentExtensionStart, @currentExtensionEnd - @currentExtensionStart) IF( @extensionName = @prohibitedExtension ) RETURN 1 SET @currentExtensionStart = @currentExtensionEnd + 1 END END RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_SendMailQueues ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_SendMailQueues', 'P') IS NULL DROP PROCEDURE dbo.sp_SendMailQueues GO ----- PRINT 'Creating sp_SendMailQueues' ----- GO -- sp_SendMailQueues : Writes a send mail request to the queue. -- CREATE PROCEDURE sp_SendMailQueues @message_data varchar(max) -- The request in XML AS BEGIN SET NOCOUNT ON DECLARE @contract_name nvarchar(128) DECLARE @message_type nvarchar(128) DECLARE @retValue int SET @message_type = '{//www.microsoft.com/databasemail/messages}SendMail' SET @contract_name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0' --Writes the message to the queue EXEC @retValue = sp_SendMailMessage @contract_name, @message_type, @message_data RETURN @retValue END GO ----------------------------------------------------------- -- procedure sp_ProcessResponse ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_ProcessResponse', 'P') IS NULL DROP PROCEDURE dbo.sp_ProcessResponse GO ----- PRINT 'Creating sp_ProcessResponse' ----- GO -- Processes responses from dbmail -- CREATE PROCEDURE sp_ProcessResponse @conv_handle uniqueidentifier, @message_type_name NVARCHAR(256), @xml_message_body VARCHAR(max) AS BEGIN DECLARE @idoc INT, @mailitem_id INT, @sent_status INT, @rc INT, @index INT, @processId INT, @sent_date DATETIME, @localmessage NVARCHAR(max), @LogMessage NVARCHAR(max), @retry_hconv uniqueidentifier, @paramStr NVARCHAR(256), @accRetryDelay INT -------------------------- --Always send the response ;SEND ON CONVERSATION @conv_handle MESSAGE TYPE @message_type_name (@xml_message_body) -- -- Need to handle the case where a sent retry is requested. -- This is done by setting a conversation timer, The timer with go off in the external queue -- Get the handle to the xml document EXEC @rc = sp_xml_preparedocument @idoc OUTPUT, @xml_message_body, N'' IF(@rc <> 0) BEGIN --Log the error. The response has already sent to the Internal queue. -- This will update the mail with the latest staus SET @localmessage = FORMATMESSAGE(14655, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) GOTO ErrorHandler; END -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId and sent status. SELECT @mailitem_id = MailItemId, @sent_status = SentStatus FROM OPENXML (@idoc, '/responses:SendMail', 1) WITH (MailItemId INT './MailItemId/@Id', SentStatus INT './SentStatus/@Status') --Close the handle to the xml document EXEC sp_xml_removedocument @idoc IF(@mailitem_id IS NULL OR @sent_status IS NULL) BEGIN --Log error and continue. SET @localmessage = FORMATMESSAGE(14652, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) GOTO ErrorHandler; END -- -- A send retry has been requested. Set a conversation timer IF(@sent_status = 3) BEGIN -- Get the associated mail item data for the given @conversation_handle (if it exists) SELECT @retry_hconv = conversation_handle FROM sysmail_send_retries as sr RIGHT JOIN sysmail_mailitems as mi ON sr.mailitem_id = mi.mailitem_id WHERE mi.mailitem_id = @mailitem_id --Must be the first retry attempt. Create a sysmail_send_retries record to track retries IF(@retry_hconv IS NULL) BEGIN INSERT sysmail_send_retries(conversation_handle, mailitem_id) --last_send_attempt_date VALUES(@conv_handle, @mailitem_id) END ELSE BEGIN --Update existing retry record UPDATE sysmail_send_retries SET last_send_attempt_date = GETDATE(), send_attempts = send_attempts + 1 WHERE mailitem_id = @mailitem_id END --Get the global retry delay time EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'AccountRetryDelay', @parameter_value = @paramStr OUTPUT --ConvertToInt will return the default if @paramStr is null SET @accRetryDelay = dbo.ConvertToInt(@paramStr, 0x7fffffff, 300) -- 5 min default --Now set the dialog timer. This triggers the send retry ;BEGIN CONVERSATION TIMER (@conv_handle) TIMEOUT = @accRetryDelay END ELSE BEGIN --Only end theconversation if a retry isn't being attempted END CONVERSATION @conv_handle END -- All done OK goto ExitProc; ----------------- -- Error Handler ----------------- ErrorHandler: ------------------ -- Exit Procedure ------------------ ExitProc: RETURN (@rc); END GO ----------------------------------------------------------- -- procedure sp_MailItemResultSets ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_MailItemResultSets', 'P') IS NULL DROP PROCEDURE dbo.sp_MailItemResultSets GO ----- PRINT 'Creating sp_MailItemResultSets' ----- GO -- sp_MailItemResultSets : -- Sends back multiple rowsets with the mail items data CREATE PROCEDURE sp_MailItemResultSets @mailitem_id INT, @profile_id INT, @conversation_handle uniqueidentifier, @service_contract_name NVARCHAR(256), @message_type_name NVARCHAR(256) AS BEGIN SET NOCOUNT ON -- -- Send back multiple rowsets with the mail items data ---- -- 1) MessageTypeName SELECT @message_type_name as 'message_type_name', @service_contract_name as 'service_contract_name', @conversation_handle as 'conversation_handle', @mailitem_id as 'mailitem_id' ----- -- 2) The mail item record from sysmail_mailitems. SELECT mi.mailitem_id, mi.profile_id, (SELECT name FROM msdb.dbo.sysmail_profile p WHERE p.profile_id = mi.profile_id) as 'profile_name', mi.recipients, mi.copy_recipients, mi.blind_copy_recipients, mi.subject, mi.body, mi.body_format, mi.importance, mi.sensitivity, ISNULL(sr.send_attempts, 0) as retry_attempt FROM sysmail_mailitems as mi LEFT JOIN sysmail_send_retries as sr ON sr.mailitem_id = mi.mailitem_id WHERE mi.mailitem_id = @mailitem_id ----- -- 3) Account information SELECT a.account_id, a.name FROM msdb.dbo.sysmail_profileaccount as pa JOIN msdb.dbo.sysmail_account as a ON pa.account_id = a.account_id WHERE pa.profile_id = @profile_id ORDER BY pa.sequence_number ----- -- 4) Attachments if any SELECT attachment_id, mailitem_id, filename, filesize, attachment FROM sysmail_attachments WHERE mailitem_id = @mailitem_id RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_process_DialogTimer ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_process_DialogTimer', 'P') IS NULL DROP PROCEDURE dbo.sp_process_DialogTimer GO ----- PRINT 'Creating sp_process_DialogTimer' ----- GO -- Processes a DialogTimer message from the the queue. This is used for send mail retries. -- Returns the mail to be send if a retry is required or logs a failure if max retry count has been reached CREATE PROCEDURE sp_process_DialogTimer @conversation_handle uniqueidentifier, @service_contract_name NVARCHAR(256), @message_type_name NVARCHAR(256) AS BEGIN SET NOCOUNT ON -- Declare all variables DECLARE @mailitem_id INT, @profile_id INT, @send_attempts INT, @mail_request_date DATETIME, @localmessage NVARCHAR(255), @paramStr NVARCHAR(256), @accRetryAttempts INT -- Get the associated mail item data for the given @conversation_handle SELECT @mailitem_id = mi.mailitem_id, @profile_id = mi.profile_id, @mail_request_date = mi.send_request_date, @send_attempts = sr.send_attempts FROM sysmail_send_retries as sr JOIN sysmail_mailitems as mi ON sr.mailitem_id = mi.mailitem_id WHERE sr.conversation_handle = @conversation_handle -- If not able to find a mailitem_id return and move to the next message. -- This could happen if the mail items table was cleared before the retry was fired IF(@mailitem_id IS NULL) BEGIN --Log warning and continue -- "mailitem_id on conversation %s was not found in the sysmail_send_retries table. This mail item will not be sent." SET @localmessage = FORMATMESSAGE(14662, convert(NVARCHAR(50), @conversation_handle)) INSERT sysmail_log (event_type, description) VALUES (2, @localmessage) RETURN 1; END --Get the retry attempt count from sysmailconfig. EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'AccountRetryAttempts', @parameter_value = @paramStr OUTPUT --ConvertToInt will return the default if @paramStr is null SET @accRetryAttempts = dbo.ConvertToInt(@paramStr, 0x7fffffff, 1) --Check the send attempts and log and error if send_attempts >= retry count. --This shouldn't happen unless the retry configuration was changed IF(@send_attempts > @accRetryAttempts) BEGIN --Log warning and continue -- "Mail Id %d has exceeded the retry count. This mail item will not be sent." SET @localmessage = FORMATMESSAGE(14663, @mailitem_id) INSERT sysmail_log (event_type, mailitem_id, description) VALUES (2, @mailitem_id, @localmessage) RETURN 1; END -- This returns the mail item to the client as multiple result sets EXEC sp_MailItemResultSets @mailitem_id = @mailitem_id, @profile_id = @profile_id, @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = @message_type_name RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_readrequest ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_readrequest', 'P') IS NULL DROP PROCEDURE dbo.sp_readrequest GO ----- PRINT 'Creating sp_readrequest' ----- GO -- sp_readrequest : Reads a request from the the queue and returns its -- contents. CREATE PROCEDURE sp_readrequest @receive_timeout INT -- the max time this read will wait for new message AS BEGIN SET NOCOUNT ON -- Table to store message information. DECLARE @msgs TABLE ( [conversation_handle] uniqueidentifier, [service_contract_name] nvarchar(256), [message_type_name] nvarchar(256), [message_body] varbinary(max) ) -- Declare variables to store row values fetched from the cursor DECLARE @exit INT, @idoc INT, @mailitem_id INT, @profile_id INT, @conversation_handle uniqueidentifier, @service_contract_name NVARCHAR(256), @message_type_name NVARCHAR(256), @xml_message_body VARCHAR(max), @timediff INT, @rec_timeout INT, @start_time DATETIME, @rc INT --Init variables SELECT @start_time = GETDATE(), @timediff = 0, @exit = 0 WHILE (@timediff < @receive_timeout) BEGIN -- Delete all messages from @msgs table DELETE FROM @msgs -- Pick all message from queue SET @rec_timeout = @receive_timeout - @timediff WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout -- Check if there was some error in reading from queue SET @rc = @@ERROR IF (@rc <> 0) BEGIN --Note: we will get error no. 9617 if the service queue 'ExternalMailQueue' is currently disabled. RETURN @rc END --If there is no message in the queue return 1 to indicate a timeout IF NOT EXISTS(SELECT * FROM @msgs) RETURN 1 -- Create a cursor to iterate through the messages. DECLARE msgs_cursor CURSOR FOR SELECT conversation_handle, service_contract_name, message_type_name, CONVERT(VARCHAR(MAX), message_body) FROM @msgs; -- Open the cursor OPEN msgs_cursor; -- Perform the first fetch and store the values in the variables. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE (@@FETCH_STATUS = 0) BEGIN -- Check if the message is a send mail message IF(@message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail') BEGIN -- Get the handle to the xml document EXEC @rc = sp_xml_preparedocument @idoc OUTPUT, @xml_message_body, N'' IF(@rc <> 0) RETURN @rc -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId. SELECT @mailitem_id = MailItemId FROM OPENXML (@idoc, '/requests:SendMail', 1) WITH (MailItemId INT './MailItemId') --Close the handle to the xml document EXEC sp_xml_removedocument @idoc -- -- get account information SELECT @profile_id = profile_id FROM sysmail_mailitems WHERE mailitem_id = @mailitem_id -- This returns the mail item to the client as multiple result sets EXEC sp_MailItemResultSets @mailitem_id = @mailitem_id, @profile_id = @profile_id, @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = @message_type_name -- OK, return the mail item to the client SET @exit = 1 BREAK END -- Check if the message is a dialog timer. This is used for account retries ELSE IF(@message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer') BEGIN -- Handle the retry case. - DialogTimer is used for send mail reties EXEC @rc = sp_process_DialogTimer @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail' -- return the mail item to the client if the return code is success if(@rc = 0) BEGIN SET @exit = 1 BREAK END END -- Error case ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error') -- Error in the conversation, hence ignore all the messages of this conversation. BREAK -- This is executed as long as fetch succeeds. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body END CLOSE msgs_cursor; DEALLOCATE msgs_cursor; -- Check if we read any request or only SSB generated messages -- If a request as read break out of loop. IF (@exit = 1) BREAK --Keep track of how long this sp has been running select @timediff = DATEDIFF(ms, @start_time, getdate()) END RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_GetAttachmentData ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_GetAttachmentData', 'P') IS NULL DROP PROCEDURE dbo.sp_GetAttachmentData GO ----- PRINT 'Creating sp_GetAttachmentData' ----- GO CREATE PROCEDURE sp_GetAttachmentData @attachments nvarchar(max), @temp_table_uid uniqueidentifier AS BEGIN SET NOCOUNT ON SET QUOTED_IDENTIFIER ON DECLARE @rc INT, @prohibitedExts NVARCHAR(1000), @attachFilePath NVARCHAR(260), @scIndex INT, @startLocation INT, @fileSizeStr NVARCHAR(256), @fileSize INT, @mailDbName sysname, @uidStr VARCHAR(36) --Get the maximum file size allowed for attachments from sysmailconfig. EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize', @parameter_value = @fileSizeStr OUTPUT --ConvertToInt will return the default if @fileSizeStr is null SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000) --May need this if attaching files EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ProhibitedExtensions', @parameter_value = @prohibitedExts OUTPUT SET @mailDbName = DB_NAME() SET @uidStr = CONVERT(VARCHAR(36), @temp_table_uid) SET @attachments = @attachments + ';' SET @startLocation = 0 SET @scIndex = CHARINDEX(';', @attachments, @startLocation) WHILE (@scIndex <> 0) BEGIN SET @attachFilePath = SUBSTRING(@attachments, @startLocation, (@scIndex - @startLocation)) -- Make sure we have an attachment file name to work with, and that it hasn't been truncated IF (@scIndex - @startLocation > 260 ) BEGIN RAISERROR(14628, 16, 1) RETURN 1 END IF ((@attachFilePath IS NULL) OR (LEN(@attachFilePath) = 0)) BEGIN RAISERROR(14628, 16, 1) RETURN 1 END --Check if attachment ext is allowed EXEC @rc = sp_isprohibited @attachFilePath, @prohibitedExts IF (@rc <> 0) BEGIN RAISERROR(14630, 16, 1, @attachFilePath, @prohibitedExts) RETURN 2 END -- return code checked after select and delete calls EXEC @rc = master..xp_sysmail_attachment_load @message = @mailDbName, @attachments = @attachFilePath, @subject = @uidStr, @max_attachment_size = @fileSize IF (@rc <> 0) RETURN (@rc) --Get next substring index SET @startLocation = @scIndex + 1 SET @scIndex = CHARINDEX(';', @attachments, @startLocation) IF (@scIndex = 0) BREAK END RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_RunMailQuery ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_RunMailQuery', 'P') IS NULL DROP PROCEDURE dbo.sp_RunMailQuery GO ----- PRINT 'Creating sp_RunMailQuery' ----- GO CREATE PROCEDURE sp_RunMailQuery @query NVARCHAR(max), @attach_results BIT, @query_attachment_filename NVARCHAR(260) = NULL, @no_output BIT, @query_result_header BIT, @separator VARCHAR(1), @echo_error BIT, @dbuse sysname, @width INT, @temp_table_uid uniqueidentifier AS BEGIN SET NOCOUNT ON SET QUOTED_IDENTIFIER ON DECLARE @rc INT, @prohibitedExts NVARCHAR(1000), @fileSizeStr NVARCHAR(256), @fileSize INT, @attach_res_str VARCHAR(5), @no_output_str VARCHAR(5), @no_header_str VARCHAR(5), @echo_error_str VARCHAR(5), @mailDbName sysname, @uid uniqueidentifier, @uidStr VARCHAR(36) -- --Get config settings and verify parameters -- SET @query_attachment_filename = LTRIM(RTRIM(@query_attachment_filename)) --Get the maximum file size allowed for attachments from sysmailconfig. EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize', @parameter_value = @fileSizeStr OUTPUT --ConvertToInt will return the default if @fileSizeStr is null SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000) IF (@attach_results = 1) BEGIN --Need this if attaching the query EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ProhibitedExtensions', @parameter_value = @prohibitedExts OUTPUT -- If attaching query results to a file and a filename isn't given create one IF ((@query_attachment_filename IS NOT NULL) AND (LEN(@query_attachment_filename) > 0)) BEGIN EXEC @rc = sp_isprohibited @query_attachment_filename, @prohibitedExts IF (@rc <> 0) BEGIN RAISERROR(14630, 16, 1, @query_attachment_filename, @prohibitedExts) RETURN 2 END END ELSE BEGIN --If queryfilename is not specified, generate a random name (doesn't have to be unique) SET @query_attachment_filename = 'QueryResults' + CONVERT(varchar, ROUND(RAND() * 1000000, 0)) + '.txt' END END --Init variables used in the query execution SET @mailDbName = db_name() SET @uidStr = convert(varchar(36), @temp_table_uid) IF(@attach_results = 1) SET @attach_res_str = 'TRUE' ELSE SET @attach_res_str = 'FALSE' IF(@no_output = 1) SET @no_output_str = 'TRUE' ELSE SET @no_output_str = 'FALSE' IF(@query_result_header = 0)SET @no_header_str = 'TRUE' ELSE SET @no_header_str = 'FALSE' IF(@echo_error = 1) SET @echo_error_str = 'TRUE' ELSE SET @echo_error_str = 'FALSE' EXEC @rc = master..xp_sysmail_format_query @query = @query, @message = @mailDbName, @subject = @uidStr, @dbuse = @dbuse, @attachments = @query_attachment_filename, @attach_results = @attach_res_str, -- format params @separator = @separator, @no_header = @no_header_str, @no_output = @no_output_str, @echo_error = @echo_error_str, @max_attachment_size = @fileSize, @width = @width RETURN @rc END GO ----------------------------------------------------------- -- procedure sp_current_principal_mails ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_current_principal_mails', 'P') IS NULL DROP PROCEDURE dbo.sp_current_principal_mails GO ----- PRINT 'Creating sp_current_principal_mails' ----- GO -- sp_current_principal_mails : returns the mails sent by the current login -- see sysmail_mailitems for fields description -- CREATE PROCEDURE sp_current_principal_mails AS BEGIN SELECT profile.name, mail.recipients, mail.copy_recipients, mail.blind_copy_recipients, mail.subject, mail.body, mail.body_format, mail.importance, mail.sensitivity, mail.file_attachments, mail.attachment_encoding, mail.query, mail.execute_query_database, mail.attach_query_result_as_file, mail.query_result_header, mail.query_result_width, mail.query_result_separator, mail.exclude_query_output, mail.append_query_error, mail.send_request_date, mail.sent_status, mail.sent_date FROM dbo.sysmail_mailitems mail JOIN msdb.dbo.sysmail_profile AS profile ON profile.profile_id = mail.profile_id WHERE mail.send_request_user = suser_sname() END GO ----------------------------------------------------------- -- procedure sp_delete_quota_information ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_delete_quota_information', 'P') IS NULL DROP PROCEDURE dbo.sp_delete_quota_information GO ----- PRINT 'Creating sp_delete_quota_information' ----- GO -- sp_delete_quota_information : delete the quota information. For now it -- will delete entries more than 1 day old, in the future based -- on the new quotas added, the algorithm might be more complex. -- CREATE PROCEDURE sp_delete_quota_information AS BEGIN -- delete all records alder than a day DELETE FROM sysmail_quota_information WHERE 0 < DATEDIFF(day, sent_mail_time, GETDATE()) END GO ----------------------------------------------------------- -- procedure sp_verify_quota_mail_count ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_verify_quota_mail_count', 'P') IS NULL DROP PROCEDURE dbo.sp_verify_quota_mail_count GO ----- PRINT 'Creating sp_verify_quota_mail_count' ----- GO -- sp_verify_quota_mail_count : verifies that the qouta for the count -- of mails that can be sent is not met, raises error -- and returns 1 if the quota was met, 0 otherwise -- CREATE PROCEDURE sp_verify_quota_mail_count AS BEGIN -- cleanup unnecessary quota info, If the performance penalty is too big -- then it should be called more rarely EXEC dbo.sp_delete_quota_information -- sysadmins are extempt from this this limit if( 1 = is_srvrolemember(N'sysadmin') ) BEGIN RETURN 0 END -- -- get the MaxNumberOfMailsPerDay setting as string -- DECLARE @MaxNumberOfMailsPerDayStr nvarchar(256) --Get the maximum number of mails that can be sent in a day EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxNumberOfMailsPerDay', @parameter_value = @MaxNumberOfMailsPerDayStr OUTPUT -- -- convert the MaxNumberOfMailsPerDay setting to int -- DECLARE @MaxNumberOfMailsPerDay int --ConvertToInt will return the default if @MaxNumberOfMailsPerDayStr is null SET @MaxNumberOfMailsPerDay = dbo.ConvertToInt(@MaxNumberOfMailsPerDayStr, 0x00ffffff, -1) -- if negative, we are done, there is no limit if @MaxNumberOfMailsPerDay < 0 BEGIN RETURN 0 END -- -- get the current login name -- declare @current_login_name sysname set @current_login_name = suser_sname() -- -- get the number of mails sent by the curent login in the last day -- DECLARE @NumberOfMailsSentToday int DECLARE @CurrentDate datetime set @CurrentDate = GETDATE() select @NumberOfMailsSentToday = COUNT(*) FROM sysmail_quota_information WHERE 0 = DATEDIFF(day, sent_mail_time, @CurrentDate) AND login_name = @current_login_name -- -- check if the quota is met -- IF (@NumberOfMailsSentToday >= @MaxNumberOfMailsPerDay) BEGIN RAISERROR(14657, 16, 1, @MaxNumberOfMailsPerDay, @current_login_name) RETURN 1 END RETURN 0 END GO ----------------------------------------------------------- -- procedure sp_add_quota_information ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_add_quota_information', 'P') IS NULL DROP PROCEDURE dbo.sp_add_quota_information GO ----- PRINT 'Creating sp_add_quota_information' ----- GO -- sp_add_quota_information : add the quota information. For now it -- will only remember date and login, in the future based -- on the new quotas added, there might be more data -- CREATE PROCEDURE sp_add_quota_information AS BEGIN -- sysadmins are extempt from this limit if( 1 = is_srvrolemember(N'sysadmin') ) BEGIN RETURN 0 END -- -- get the MaxNumberOfMailsPerDay setting as string -- DECLARE @MaxNumberOfMailsPerDayStr nvarchar(256) --Get the maximum number of mails that can be sent in a day EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxNumberOfMailsPerDay', @parameter_value = @MaxNumberOfMailsPerDayStr OUTPUT -- -- convert the MaxNumberOfMailsPerDay setting to int -- DECLARE @MaxNumberOfMailsPerDay int --ConvertToInt will return the default if @MaxNumberOfMailsPerDayStr is null SET @MaxNumberOfMailsPerDay = dbo.ConvertToInt(@MaxNumberOfMailsPerDayStr, 0x00ffffff, -1) -- if negative, we are done, there is no limit if( @MaxNumberOfMailsPerDay < 0 ) BEGIN RETURN 0 END -- else add the quota info INSERT INTO sysmail_quota_information (login_name, sent_mail_time) VALUES ( suser_sname(), GETDATE()) END GO ----------------------------------------------------------- -- procedure sp_send_dbmail ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_send_dbmail', 'P') IS NULL DROP PROCEDURE dbo.sp_send_dbmail GO ----- PRINT 'Creating sp_send_dbmail' ----- GO -- sp_sendemail : Sends a mail from Yukon outbox. -- CREATE PROCEDURE sp_send_dbmail @profile_name sysname = NULL, @recipients VARCHAR(MAX) = NULL, @copy_recipients VARCHAR(MAX) = NULL, @blind_copy_recipients VARCHAR(MAX) = NULL, @subject NVARCHAR(255) = NULL, @body NVARCHAR(MAX) = NULL, @body_format VARCHAR(20) = NULL, @importance VARCHAR(6) = 'NORMAL', @sensitivity VARCHAR(12) = 'NORMAL', @file_attachments NVARCHAR(MAX) = NULL, @query NVARCHAR(MAX) = NULL, @execute_query_database sysname = NULL, @attach_query_result_as_file BIT = 0, @query_attachment_filename NVARCHAR(260) = NULL, @query_result_header BIT = 1, @query_result_width INT = 256, @query_result_separator CHAR(1) = ' ', @exclude_query_output BIT = 0, @append_query_error BIT = 0 AS BEGIN SET NOCOUNT ON --check quota DECLARE @quota_result bit EXEC @quota_result = dbo.sp_verify_quota_mail_count IF( 0 <> @quota_result ) BEGIN --quota was met RETURN 1 END -- And make sure ARITHABORT is on. This is the default for yukon DB's SET ARITHABORT ON --Declare variables used by the procedure internally DECLARE @profile_id INT, @mailitem_id INT, @temp_table_uid uniqueidentifier, @sendmailxml VARCHAR(max), @CR_str NVARCHAR(2), @localmessage NVARCHAR(255), @QueryResultsExist INT, @AttachmentsExist INT, @RetErrorMsg NVARCHAR(4000), --Impose a limit on the error message length to avoid memory abuse @rc INT, @procName sysname, @trancountSave INT, @tranStartedBool INT -- Initialize SELECT @rc = 0, @QueryResultsExist = 0, @AttachmentsExist = 0, @temp_table_uid = NEWID(), @procName = OBJECT_NAME(@@PROCID), @tranStartedBool = 0, @trancountSave = @@TRANCOUNT --Check if SSB is enabled in this database IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N'IsBrokerEnabled'), 0) <> 1) BEGIN RAISERROR(14650, 16, 1) RETURN 1 END --Report error if the mail queue has been stopped. --sysmail_stop_sp/sysmail_start_sp changes the receive status of the SSB queue IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1) BEGIN RAISERROR(14641, 16, 1) RETURN 1 END -- Get the relevant profile_id -- IF (@profile_name IS NULL) BEGIN -- Use the global or users default if profile name is not supplied SELECT TOP (1) @profile_id = pp.profile_id FROM msdb.dbo.sysmail_principalprofile as pp WHERE (pp.is_default = 1) AND (pp.database_id = db_id() OR pp.database_id = 0) AND (pp.principal_id = USER_ID() OR pp.principal_id = 0) ORDER BY pp.database_id DESC, pp.principal_id DESC --Was a profile found IF(@profile_id IS NULL) BEGIN RAISERROR(14636, 16, 1) RETURN 1 END END ELSE BEGIN --Get primary account if profile name is supplied EXEC @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id = NULL, @profile_name = @profile_name, @allow_both_nulls = 0, @allow_id_name_mismatch = 0, @profileid = @profile_id OUTPUT IF (@rc <> 0) RETURN @rc --Make sure this user has access to the specified profile. --sysadmins can send on any profiles IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1) BEGIN --Not a sysadmin so check users access to profile iF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_principalprofile WHERE ((profile_id = @profile_id) AND (database_id = db_id() OR database_id = 0) AND (principal_id = USER_ID() OR principal_id = 0))) BEGIN RAISERROR(14607, -1, -1, 'profile') RETURN 1 END END END --Attach results must be specified IF @attach_query_result_as_file IS NULL BEGIN RAISERROR(14618, 16, 1, 'attach_query_result_as_file') RETURN 2 END --No output must be specified IF @exclude_query_output IS NULL BEGIN RAISERROR(14618, 16, 1, 'exclude_query_output') RETURN 3 END --No header must be specified IF @query_result_header IS NULL BEGIN RAISERROR(14618, 16, 1, 'query_result_header') RETURN 4 END -- Check if query_result_separator is specifed IF @query_result_separator IS NULL OR DATALENGTH(@query_result_separator) = 0 BEGIN RAISERROR(14618, 16, 1, 'query_result_separator') RETURN 5 END --Echo error must be specified IF @append_query_error IS NULL BEGIN RAISERROR(14618, 16, 1, 'append_query_error') RETURN 6 END --@body_format can be TEXT (default) or HTML IF (@body_format IS NULL) BEGIN SET @body_format = 'TEXT' END ELSE BEGIN SET @body_format = UPPER(@body_format) IF @body_format NOT IN ('TEXT', 'HTML') BEGIN RAISERROR(14626, 16, 1, @body_format) RETURN 13 END END --Importance must be specified IF @importance IS NULL BEGIN RAISERROR(14618, 16, 1, 'importance') RETURN 15 END SET @importance = UPPER(@importance) --Importance must be one of the predefined values IF @importance NOT IN ('LOW', 'NORMAL', 'HIGH') BEGIN RAISERROR(14622, 16, 1, @importance) RETURN 16 END --Sensitivity must be specified IF @sensitivity IS NULL BEGIN RAISERROR(14618, 16, 1, 'sensitivity') RETURN 17 END SET @sensitivity = UPPER(@sensitivity) --Sensitivity must be one of predefined values IF @sensitivity NOT IN ('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL') BEGIN RAISERROR(14623, 16, 1, @sensitivity) RETURN 18 END --Message body cannot be null. Atleast one of message, subject, query, --attachments must be specified. IF( (@body IS NULL AND @query IS NULL AND @file_attachments IS NULL AND @subject IS NULL) OR ( (LEN(@body) IS NULL OR LEN(@body) <= 0) AND (LEN(@query) IS NULL OR LEN(@query) <= 0) AND (LEN(@file_attachments) IS NULL OR LEN(@file_attachments) <= 0) AND (LEN(@subject) IS NULL OR LEN(@subject) <= 0) ) ) BEGIN RAISERROR(14624, 16, 1, '@body, @query, @file_attachments, @subject') RETURN 19 END ELSE IF @subject IS NULL OR LEN(@subject) <= 0 SET @subject='SQL Server Message' --Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified IF ( (@recipients IS NULL AND @copy_recipients IS NULL AND @blind_copy_recipients IS NULL ) OR ( (LEN(@recipients) IS NULL OR LEN(@recipients) <= 0) AND (LEN(@copy_recipients) IS NULL OR LEN(@copy_recipients) <= 0) AND (LEN(@blind_copy_recipients) IS NULL OR LEN(@blind_copy_recipients) <= 0) ) ) BEGIN RAISERROR(14624, 16, 1, '@recipients, @copy_recipients, @blind_copy_recipients') RETURN 20 END --If query is not specified, attach results and no header cannot be true. IF ( (@query IS NULL OR LEN(@query) <= 0) AND @attach_query_result_as_file = 1) BEGIN RAISERROR(14625, 16, 1) RETURN 21 END -- -- Execute Query if query is specified IF ((@query IS NOT NULL) AND (LEN(@query) > 0)) BEGIN EXEC @rc = sp_RunMailQuery @query = @query, @attach_results = @attach_query_result_as_file, @query_attachment_filename = @query_attachment_filename, @no_output = @exclude_query_output, @query_result_header = @query_result_header, @separator = @query_result_separator, @echo_error = @append_query_error, @dbuse = @execute_query_database, @width = @query_result_width, @temp_table_uid = @temp_table_uid -- This error indicates that query results size was over the configured MaxFileSize. -- Note, an error has already beed raised in this case IF(@rc = 101) GOTO ErrorHandler; -- Always check the transfer tables for data. They may also contain error messages -- Only one of the tables receives data in the call to sp_RunMailQuery IF(@attach_query_result_as_file = 1) BEGIN IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid) SET @AttachmentsExist = 1 END ELSE BEGIN IF EXISTS(SELECT * FROM sysmail_query_transfer WHERE uid = @temp_table_uid AND uid IS NOT NULL) SET @QueryResultsExist = 1 END -- Exit if there was an error and caller doesn't want the error appended to the mail IF (@rc <> 0 AND @append_query_error = 0) BEGIN --Error msg with be in either the attachment table or the query table --depending on the setting of @attach_query_result_as_file IF(@attach_query_result_as_file = 1) BEGIN --Copy query results from the attachments table to mail body SELECT @RetErrorMsg = CONVERT(NVARCHAR(4000), attachment) FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid END ELSE BEGIN --Copy query results from the query table to mail body SELECT @RetErrorMsg = text_data FROM sysmail_query_transfer WHERE uid = @temp_table_uid END GOTO ErrorHandler; END SET @AttachmentsExist = @attach_query_result_as_file END ELSE BEGIN --If query is not specified, attach results cannot be true. IF (@attach_query_result_as_file = 1) BEGIN RAISERROR(14625, 16, 1) RETURN 21 END END --Get the prohibited extensions for attachments from sysmailconfig. IF ((@file_attachments IS NOT NULL) AND (LEN(@file_attachments) > 0)) BEGIN EXEC @rc = sp_GetAttachmentData @attachments = @file_attachments, @temp_table_uid = @temp_table_uid IF (@rc <> 0) GOTO ErrorHandler; IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid) SET @AttachmentsExist = 1 END -- Start a transaction if not already in one. -- Note: For rest of proc use GOTO ErrorHandler for falures if (@trancountSave = 0) BEGIN TRAN @procName else SAVE TRAN @procName SET @tranStartedBool = 1 -- Store complete mail message for history/status purposes INSERT sysmail_mailitems ( profile_id, recipients, copy_recipients, blind_copy_recipients, subject, body, body_format, importance, sensitivity, file_attachments, attachment_encoding, query, execute_query_database, attach_query_result_as_file, query_result_header, query_result_width, query_result_separator, exclude_query_output, append_query_error ) VALUES ( @profile_id, @recipients, @copy_recipients, @blind_copy_recipients, @subject, @body, @body_format, @importance, @sensitivity, @file_attachments, 'MIME', @query, @execute_query_database, @attach_query_result_as_file, @query_result_header, @query_result_width, @query_result_separator, @exclude_query_output, @append_query_error ) SELECT @rc = @@ERROR, @mailitem_id = @@IDENTITY IF(@rc <> 0) GOTO ErrorHandler; --Copy query into the message body IF(@QueryResultsExist = 1) BEGIN -- if the body is null initialize it UPDATE sysmail_mailitems SET body = N'' WHERE mailitem_id = @mailitem_id AND body is null --Add CR SET @CR_str = CHAR(13) + CHAR(10) UPDATE sysmail_mailitems SET body.WRITE(@CR_str, NULL, NULL) WHERE mailitem_id = @mailitem_id --Copy query results to mail body UPDATE sysmail_mailitems SET body.WRITE( (SELECT text_data from sysmail_query_transfer WHERE uid = @temp_table_uid), NULL, NULL ) WHERE mailitem_id = @mailitem_id END --Copy into the attachments table IF(@AttachmentsExist = 1) BEGIN --Copy temp attachments to sysmail_attachments INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment) SELECT @mailitem_id, filename, filesize, attachment FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid END -- Create the primary SSB xml maessage SET @sendmailxml = '' + CONVERT(NVARCHAR(20), @mailitem_id) + N'' -- Send the send request on queue. EXEC @rc = sp_SendMailQueues @sendmailxml IF @rc <> 0 BEGIN RAISERROR(14627, 16, 1, @rc, 'send mail') GOTO ErrorHandler; END -- Print success message if required IF (@exclude_query_output = 0) BEGIN SET @localmessage = FORMATMESSAGE(14635) PRINT @localmessage END --add quota info EXEC dbo.sp_add_quota_information -- -- See if the transaction needs to be commited -- IF (@trancountSave = 0 and @tranStartedBool = 1) COMMIT TRAN @procName -- All done OK goto ExitProc; ----------------- -- Error Handler ----------------- ErrorHandler: IF (@tranStartedBool = 1) ROLLBACK TRAN @procName ------------------ -- Exit Procedure ------------------ ExitProc: --Always delete query and attactment transfer records. --Note: Query results can also be returned in the sysmail_attachments_transfer table DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid DELETE sysmail_query_transfer WHERE uid = @temp_table_uid --Raise an error it the query execution fails -- This will only be the case when @append_query_error is set to 0 (false) IF(@RetErrorMsg IS NOT NULL) BEGIN RAISERROR(14661, -1, -1, @RetErrorMsg) END RETURN (@rc) END GO ----------------------------------------------------------- -- procedure sp_ExternalMailQueueListener ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_ExternalMailQueueListener', 'P') IS NULL DROP PROCEDURE dbo.sp_ExternalMailQueueListener GO ----- PRINT 'Creating sp_ExternalMailQueueListener' ----- GO -- Processes messages from the external mail queue -- CREATE PROCEDURE sp_ExternalMailQueueListener AS BEGIN DECLARE @idoc INT, @mailitem_id INT, @sent_status INT, @sent_account_id INT, @rc INT, @processId INT, @sent_date DATETIME, @localmessage NVARCHAR(max), @conv_handle uniqueidentifier, @message_type_name NVARCHAR(256), @xml_message_body VARCHAR(max), @LogMessage NVARCHAR(max) -- Table to store message information. DECLARE @msgs TABLE ( [conversation_handle] uniqueidentifier, [message_type_name] nvarchar(256), [message_body] varbinary(max) ) --RECEIVE messages from the exernal queue. --MailItem status messages are sent from the external sql mail process along with other SSB notifications and errors ;RECEIVE conversation_handle, message_type_name, message_body FROM InternalMailQueue INTO @msgs -- Check if there was some error in reading from queue SET @rc = @@ERROR IF (@rc <> 0) BEGIN --Log error and continue. Don't want to block the following messages on the queue SET @localmessage = FORMATMESSAGE(@@ERROR) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) GOTO ErrorHandler; END ----------------------------------- --Process sendmail status messages SELECT @conv_handle = conversation_handle, @message_type_name = message_type_name, @xml_message_body = CAST(message_body AS VARCHAR(MAX)) FROM @msgs WHERE [message_type_name] = N'{//www.microsoft.com/databasemail/messages}SendMailStatus' IF(@message_type_name IS NOT NULL) BEGIN -- --Expecting the xml body to be n the following form: -- -- -- -- -- -- -- -- -- -- -- -- -- Get the handle to the xml document EXEC @rc = sp_xml_preparedocument @idoc OUTPUT, @xml_message_body, N'' IF(@rc <> 0) BEGIN --Log error and continue. Don't want to block the following messages on the queue SET @localmessage = FORMATMESSAGE(14655, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) GOTO ErrorHandler; END -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId and sent status. SELECT @mailitem_id = MailItemId, @sent_status = SentStatus, @sent_account_id = SentAccountId, @sent_date = SentDate, @processId = CallingProcess, @LogMessage = LogMessage FROM OPENXML (@idoc, '/responses:SendMail', 1) WITH (MailItemId INT './MailItemId/@Id', SentStatus INT './SentStatus/@Status', SentAccountId INT './SentAccountId/@Id', SentDate DATETIME './SentDate/@Date', --The date was formated using ISO8601 CallingProcess INT './CallingProcess/@Id', LogMessage NVARCHAR(max) './Information/Failure/@Message') --Close the handle to the xml document EXEC sp_xml_removedocument @idoc IF(@mailitem_id IS NULL) BEGIN --Log error and continue. Don't want to block the following messages on the queue by rolling back the tran SET @localmessage = FORMATMESSAGE(14652, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) END ELSE BEGIN -- check sent_status is valid : 0(PendingSend), 1(SendSuccessful), 2(SendFailed), 3(AttemptingSendRetry) IF(@sent_status NOT IN (1, 2, 3)) BEGIN SET @localmessage = FORMATMESSAGE(14653, N'SentStatus', CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (2, @localmessage) --Set value to SendFailed SET @sent_status = 2 END --Make the @sent_account_id NULL if it is 0. IF(@sent_account_id IS NOT NULL AND @sent_account_id = 0) SET @sent_account_id = NULL -- -- Update the mail status if not a retry. Nothing else needs to be done in this case UPDATE sysmail_mailitems SET sent_status = CAST (@sent_status as TINYINT), sent_account_id = @sent_account_id, sent_date = @sent_date WHERE mailitem_id = @mailitem_id -- Report a failure if no record is found in the sysmail_mailitems table IF (@@ROWCOUNT = 0) BEGIN SET @localmessage = FORMATMESSAGE(14653, N'MailItemId', CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body) --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) END IF (@LogMessage IS NOT NULL) BEGIN --Log any failure message INSERT sysmail_log (event_type, process_id, mailitem_id, account_id, description) VALUES (3, @processId, @mailitem_id, @sent_account_id, @LogMessage) END END END ------------------------------------------------------- --Process all other messages by logging to sysmail_log SET @conv_handle = NULL; --Always end the conversion if this message is received SELECT @conv_handle = conversation_handle FROM @msgs WHERE [message_type_name] = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' IF(@conv_handle IS NOT NULL) BEGIN END CONVERSATION @conv_handle; END INSERT INTO sysmail_log(event_type, description) SELECT 2, FORMATMESSAGE(14654, CONVERT(NVARCHAR(50), conversation_handle), message_type_name, message_body) FROM @msgs WHERE [message_type_name] NOT IN (N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', N'{//www.microsoft.com/databasemail/messages}SendMailStatus') -- All done OK goto ExitProc; ----------------- -- Error Handler ----------------- ErrorHandler: ------------------ -- Exit Procedure ------------------ ExitProc: RETURN (@rc) END GO ----------------------------------------------------------- -- procedure sp_sysmail_activate ----------------------------------------------------------- IF NOT OBJECT_ID('dbo.sp_sysmail_activate', 'P') IS NULL DROP PROCEDURE dbo.sp_sysmail_activate GO ----- PRINT 'Creating sp_sysmail_activate' ----- GO -- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running -- CREATE PROCEDURE sp_sysmail_activate AS BEGIN DECLARE @mailDbName sysname DECLARE @mailDbId INT DECLARE @mailEngineLifeMin INT DECLARE @loggingLevel nvarchar(256) DECLARE @parameter_value nvarchar(256) DECLARE @localmessage nvarchar(max) DECLARE @rc INT -- Table to store message information. DECLARE @msgs TABLE ( [message_type_name] nvarchar(256) ) --RECEIVE and purge the notification queue. ;RECEIVE message_type_name FROM SysMailNotificationQueue INTO @msgs --Remove EndDialog messages. Don't fire activation for these DELETE @msgs WHERE [message_type_name] = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' -- IF there are other messages fire activation IF(NOT EXISTS(SELECT * FROM @msgs)) RETURN (0) EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime', @parameter_value = @parameter_value OUTPUT IF(@rc <> 0) RETURN (1) --ConvertToInt will return the default if @parameter_value is null or config value can't be converted --Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600) --Try and get the optional logging level for the DatabaseMail process EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel', @parameter_value = @loggingLevel OUTPUT SET @mailDbName = DB_NAME() SET @mailDbId = DB_ID() EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @mailEngineLifeMin, @loggingLevel IF(@rc <> 0) BEGIN DECLARE @message_type_name NVARCHAR(256); SELECT TOP 1 @message_type_name = message_type_name FROM @msgs SET @localmessage = FORMATMESSAGE(14637) + @message_type_name --Log failure INSERT sysmail_log (event_type, description) VALUES (3, @localmessage) END ELSE BEGIN SET @localmessage = FORMATMESSAGE(14638) -- Log success INSERT sysmail_log (event_type, description) VALUES (0, @localmessage) END RETURN @rc END GO /**************************************************************/ -- GRANTS /**************************************************************/ GRANT EXECUTE ON sp_send_dbmail TO PUBLIC GRANT EXECUTE ON sp_current_principal_mails TO PUBLIC /**************************************************************/ -- Drop MESSAGES, CONTRACTS, QUEUES AND SERVICES /**************************************************************/ PRINT '' PRINT 'Dropping MESSAGES, CONTRACTS, QUEUES AND SERVICES...' PRINT '' -- Drop service InternalMailService if existing. IF EXISTS (SELECT * FROM sys.services WHERE name ='InternalMailService') BEGIN PRINT 'Dropping SERVICE InternalMailService' DROP SERVICE InternalMailService; END -- Drop service ExternalMailService if existing. IF EXISTS (SELECT * FROM sys.services WHERE name ='ExternalMailService') BEGIN PRINT 'Dropping SERVICE ExternalMailService' DROP SERVICE ExternalMailService; END -- Drop queue InternalMailQueue if existing. IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InternalMailQueue' AND type = 'SQ') BEGIN PRINT 'Dropping QUEUE InternalMailQueue' DROP QUEUE InternalMailQueue; END -- Drop queue ExternalMailQueue if existing. IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ExternalMailQueue' AND type = 'SQ') BEGIN PRINT 'Dropping QUEUE ExternalMailQueue' DROP QUEUE ExternalMailQueue; END --Drop Notification service for activation of DatabaseMail.exe IF EXISTS (SELECT * FROM sys.services WHERE name ='SQL/Notifications/SysMailNotification/v1.0') BEGIN PRINT 'Dropping SERVICE [SQL/Notifications/SysMailNotification/v1.0]' DROP SERVICE [SQL/Notifications/SysMailNotification/v1.0]; END --Drop SysMailNotificationQueue if existing IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SysMailNotificationQueue' AND type = 'SQ') BEGIN PRINT 'Dropping QUEUE SysMailNotificationQueue' DROP QUEUE SysMailNotificationQueue; END -- Drop SendMail v1.0 contract if existing. IF EXISTS(SELECT * FROM sys.service_contracts WHERE name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0') BEGIN PRINT 'Dropping CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]' DROP CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]; END -- Drop SendMail message type if existing. IF EXISTS(SELECT * FROM sys.service_message_types WHERE name = '{//www.microsoft.com/databasemail/messages}SendMail') BEGIN PRINT 'Dropping MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]' DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]; END -- Drop SendMailStatus message type if existing. IF EXISTS(SELECT * FROM sys.service_message_types WHERE name = '{//www.microsoft.com/databasemail/messages}SendMailStatus') BEGIN PRINT 'Dropping MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus]' DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus]; END GO /**************************************************************/ -- Create MESSAGES, CONTRACTS, QUEUES AND SERVICES /**************************************************************/ PRINT '' PRINT 'Creating MESSAGES, CONTRACTS, QUEUES AND SERVICES...' PRINT '' -- Create SendMail message type. PRINT 'Creating MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]' CREATE MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail] VALIDATION = NONE CREATE MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus] VALIDATION = NONE -- Create SendMail contract. PRINT 'Creating CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]' CREATE CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0] ( [{//www.microsoft.com/databasemail/messages}SendMail] SENT BY INITIATOR, [{//www.microsoft.com/databasemail/messages}SendMailStatus] SENT BY TARGET ) -- Create InternalMailQueue queue. PRINT 'Creating QUEUE InternalMailQueue' CREATE QUEUE InternalMailQueue WITH ACTIVATION (PROCEDURE_NAME = sp_ExternalMailQueueListener, MAX_QUEUE_READERS = 1, EXECUTE AS SELF); -- Create ExternalMailQueue queue. PRINT 'Creating QUEUE ExternalMailQueue' CREATE QUEUE ExternalMailQueue; -- Create InternalMailService service. PRINT 'Creating SERVICE InternalMailService ON QUEUE InternalMailQueue' CREATE SERVICE InternalMailService ON QUEUE InternalMailQueue ( [//www.microsoft.com/databasemail/contracts/SendMail/v1.0] --,[//www.microsoft.com/databasemail/contracts/TestProfile/v1.0] ); -- Create ExternalMailService service. PRINT 'Creating SERVICE ExternalMailService ON QUEUE ExternalMailQueue' CREATE SERVICE ExternalMailService ON QUEUE ExternalMailQueue ( [//www.microsoft.com/databasemail/contracts/SendMail/v1.0] -- ,[//www.microsoft.com/databasemail/contracts/TestProfile/v1.0] ); --Create NotificationQueue PRINT 'Creating QUEUE SysMailNotificationQueue' CREATE QUEUE SysMailNotificationQueue WITH ACTIVATION (PROCEDURE_NAME = sp_sysmail_activate, MAX_QUEUE_READERS = 1, EXECUTE AS SELF); --Create notification service PRINT 'Creating SERVICE [SQL/Notifications/SysMailNotification/v1.0] ON QUEUE SysMailNotificationQueue' CREATE SERVICE [SQL/Notifications/SysMailNotification/v1.0] ON QUEUE SysMailNotificationQueue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] ); -- Create event notification PRINT 'Creating queue_activation EVENT NOTIFICATION SysMailNotification' CREATE EVENT NOTIFICATION SysMailNotification ON QUEUE ExternalMailQueue FOR queue_activation TO SERVICE 'SQL/Notifications/SysMailNotification/v1.0', 'current database' ; GO --------------------------------------------------------------------- -- Sign the public SP's to allow access to objects in msdb and master DECLARE @sqlcmd nvarchar(max), @mailDb sysname, @CertName sysname, @CertNameQuoted sysname, @CertPwd sysname, @CertFile nvarchar(261), @MailLogin sysname, @MailLoginQuoted sysname, @MailLoginStrQuoted sysname, @path nvarchar(261), @idx int SET @mailDb = DB_NAME() SET @CertName = N'DatabaseMail-Certificate-' + @mailDb SET @CertNameQuoted = QUOTENAME( @CertName ) SET @CertPwd = CAST(NEWID() AS sysname) SET @CertFile = N'DatabaseMail-' + CAST(NEWID() AS sysname) + '.cer' SET @MailLogin = N'DatabaseMail-' + @mailDb + '-Certificate-Login' SET @MailLoginQuoted= QUOTENAME( @MailLogin ) SET @MailLoginStrQuoted = QUOTENAME(@MailLogin, '''') -- Drop user in msdb SET @sqlcmd = N'USE msdb IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + @MailLoginStrQuoted + ')) DROP USER ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd -- Drop user in master SET @sqlcmd = N'USE master IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + @MailLoginStrQuoted + ')) DROP USER ' + @MailLoginQuoted + 'IF(EXISTS(select * from sys.server_principals where name = N' + @MailLoginStrQuoted + ')) DROP LOGIN ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd -- Drop user in this database IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = @MailLogin)) BEGIN SET @sqlcmd = N'DROP USER ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd END --Drop the certificate if it already exists in this db and recreate it IF(EXISTS(SELECT * FROM sys.certificates WHERE name = @CertName)) BEGIN SET @sqlcmd = N'DROP CERTIFICATE ' + @CertNameQuoted EXEC sp_executesql @sqlcmd END --Create a certificate for signing the public DatabaseMail SP's SET @sqlcmd = N'CREATE CERTIFICATE ' + @CertNameQuoted + ' ENCRYPTION BY PASSWORD = ''' + @CertPwd + ''' WITH subject = ''DatabaseMail Signing Cerfificate''' EXEC sp_executesql @sqlcmd --Add certificate signature to public SP's SET @sqlcmd = N'ADD SIGNATURE TO dbo.sp_send_dbmail BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_SendMailMessage BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_GetAttachmentData BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_verify_quota_mail_count BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_add_quota_information BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_current_principal_mails BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_RunMailQuery BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_SendMailQueues BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_ExternalMailQueueListener BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_process_DialogTimer BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_ProcessResponse BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + ''' ADD SIGNATURE TO dbo.sp_sysmail_activate BY CERTIFICATE ' + @CertNameQuoted + ' WITH password = ''' + @CertPwd + '''' EXEC sp_executesql @sqlcmd -- Remove private key so certificate can't be used to sign other SP's SET @sqlcmd = N'ALTER CERTIFICATE ' + @CertNameQuoted + ' remove private key' EXEC sp_executesql @sqlcmd -- Remove certificate from master if it already exists SET @sqlcmd = N'USE master IF(EXISTS(SELECT * FROM sys.certificates WHERE name = N' + QUOTENAME(@CertName, '''') + ')) DROP CERTIFICATE ' + @CertNameQuoted EXEC sp_executesql @sqlcmd -- IF( (DB_NAME() <> 'msdb') and (DB_NAME() <> 'master')) IF(DB_NAME() <> 'master') BEGIN --Get the path to the sqlserver logs CREATE TABLE #xp_results(regkey nvarchar(260), regvalue nvarchar(260)) INSERT INTO #xp_results EXEC master.dbo.xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters' --Locate the Log path in the Parameters hive SELECT @path = regvalue from #xp_results where regvalue like N'-e%' DROP TABLE #xp_results SET @path = substring(@path, 3, 260) SET @path = REVERSE(@path) IF(@path IS NOT NULL) BEGIN SET @idx = CHARINDEX('\', @path, 0) IF(@idx <> 0) BEGIN SET @path = substring(@path, @idx, 260) SET @path = REVERSE(@path) SET @CertFile = @path + @CertFile END END -- Make sure @path and @CertFile were not truncated. If they were, their length would be 261 -- In case of truncation, the SP is not executed (but no error is raised either) IF ( (LEN(@path) <= 260) AND (LEN(@CertFile) <= 260) ) BEGIN --Export the certificate to master SET @sqlcmd = N'DUMP CERTIFICATE ' + @CertNameQuoted + ' TO file = ''' + @CertFile + '''' EXEC sp_executesql @sqlcmd SET @sqlcmd = N'USE master CREATE CERTIFICATE ' + @CertNameQuoted + ' FROM file = ''' + @CertFile + '''' EXEC sp_executesql @sqlcmd END END -- Import the certificate into master and create a login from it SET @sqlcmd = N'USE master CREATE LOGIN ' + @MailLoginQuoted + ' From CERTIFICATE ' + @CertNameQuoted + --Create user in master for this login and grant access to DatabaseMail XP's N' CREATE USER ' + @MailLoginQuoted + ' FOR login ' + @MailLoginQuoted + -- Now do the grants to the new user N' GRANT EXECUTE ON master.dbo.xp_sysmail_format_query TO ' + @MailLoginQuoted + N' GRANT EXECUTE ON master.dbo.xp_sysmail_attachment_load TO ' + @MailLoginQuoted + N' GRANT EXECUTE ON master.dbo.xp_sysmail_activate TO ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd -- grant access in msdb to DatabaseMail configuration SP's --Create user in msdb for this login and grant access to DatabaseMail config SP's IF(DB_NAME() <> 'msdb') BEGIN SET @sqlcmd = N'USE msdb CREATE USER ' + @MailLoginQuoted + ' FOR LOGIN ' + @MailLoginQuoted + -- Now do the grants to the new user N' GRANT EXECUTE ON dbo.sysmail_verify_profile_sp TO ' + @MailLoginQuoted + N' GRANT EXECUTE ON dbo.sysmail_help_configure_value_sp TO '+ @MailLoginQuoted + N' GRANT SELECT ON dbo.sysmail_profileaccount TO ' + @MailLoginQuoted + N' GRANT SELECT ON dbo.sysmail_principalprofile TO ' + @MailLoginQuoted + N' GRANT SELECT ON dbo.sysmail_profile TO ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd END --Create the user in this database SET @sqlcmd = N'CREATE USER ' + @MailLoginQuoted + ' FOR LOGIN ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd --Grant right to use the SSB service in this database SET @sqlcmd = N' GRANT SEND ON SERVICE :: InternalMailService TO ' + @MailLoginQuoted + N' GRANT SEND ON SERVICE :: ExternalMailService TO ' + @MailLoginQuoted + N' GRANT RECEIVE ON InternalMailQueue TO ' + @MailLoginQuoted + N' GRANT RECEIVE ON ExternalMailQueue TO ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd go /**************************************************************/ /* Mark system objects */ /**************************************************************/ declare @start datetime ,@name sysname select @start = start from #InstIMail declare newsysobjs cursor for select name from sys.objects where schema_id = 1 and create_date >= @start open newsysobjs fetch next from newsysobjs into @name while @@fetch_status = 0 begin Exec sp_MS_marksystemobject @name fetch next from newsysobjs into @name end deallocate newsysobjs drop table #InstIMail go EXECUTE master.dbo.sp_configure N'allow updates', 0 go RECONFIGURE WITH OVERRIDE go PRINT '' PRINT '----------------------------------------------' PRINT 'Execution of INSTALL_DATABASEMAIL.SQL complete' PRINT '----------------------------------------------' go CHECKPOINT go PA/**********************************************************************/ /* Uninstall_DatabaseMail.sql */ /* */ /* Uninstalls the tables, triggers and stored procedures necessary for*/ /* databasemail operations */ /* */ /* ** Copyright Microsoft, Inc. 2004 ** All Rights Reserved. */ /**********************************************************************/ PRINT '---------------------------------------------' PRINT 'Starting execution of Uninstall_DatabaseMail.sql' PRINT '---------------------------------------------' go /**************************************************************/ -- Drop Create all DatabaseMail Util Functions/Procedures /**************************************************************/ PRINT '' PRINT 'Dropping FUNCTIONS and PROCEDURES ...' PRINT '' ----- PRINT 'Dropping function ConvertToInt' ----- IF NOT OBJECT_ID('dbo.ConvertToInt', 'FN') IS NULL DROP FUNCTION ConvertToInt ----- PRINT 'Dropping procedure sysmail_start_sp' ----- IF NOT OBJECT_ID('dbo.sysmail_start_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_start_sp ----- PRINT 'Dropping procedure sysmail_stop_sp' ----- IF NOT OBJECT_ID('dbo.sysmail_stop_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_stop_sp ----- PRINT 'Dropping procedure sysmail_logmailevent_sp' ----- IF NOT OBJECT_ID('dbo.sysmail_logmailevent_sp', 'P') IS NULL DROP PROCEDURE dbo.sysmail_logmailevent_sp ----- PRINT 'Dropping procedure sp_SendMailQueues' ----- IF NOT OBJECT_ID('dbo.sp_SendMailQueues', 'P') IS NULL DROP PROCEDURE dbo.sp_SendMailQueues ----- PRINT 'Dropping procedure sp_isprohibited' ----- IF NOT OBJECT_ID('dbo.sp_isprohibited', 'P') IS NULL DROP PROCEDURE dbo.sp_isprohibited ----- PRINT 'Dropping procedure sp_SendMailMessage' ----- IF NOT OBJECT_ID('dbo.sp_SendMailMessage', 'P') IS NULL DROP PROCEDURE dbo.sp_SendMailMessage ----- PRINT 'Dropping procedure sp_ProcessResponse' ----- IF NOT OBJECT_ID('dbo.sp_ProcessResponse', 'P') IS NULL DROP PROCEDURE dbo.sp_ProcessResponse ----- PRINT 'Dropping procedure sp_readrequest' ----- IF NOT OBJECT_ID('dbo.sp_readrequest', 'P') IS NULL DROP PROCEDURE dbo.sp_readrequest ----- PRINT 'Dropping procedure sp_process_DialogTimer' ----- IF NOT OBJECT_ID('dbo.sp_process_DialogTimer', 'P') IS NULL DROP PROCEDURE dbo.sp_process_DialogTimer ----- PRINT 'Dropping procedure sp_MailItemResultSets' ----- IF NOT OBJECT_ID('dbo.sp_MailItemResultSets', 'P') IS NULL DROP PROCEDURE dbo.sp_MailItemResultSets ----- PRINT 'Dropping procedure sp_RunMailQuery' ----- IF NOT OBJECT_ID('dbo.sp_RunMailQuery', 'P') IS NULL DROP PROCEDURE dbo.sp_RunMailQuery ----- PRINT 'Dropping procedure sp_GetAttachmentData' ----- IF NOT OBJECT_ID('dbo.sp_GetAttachmentData', 'P') IS NULL DROP PROCEDURE dbo.sp_GetAttachmentData ----- PRINT 'Dropping procedure sp_send_dbmail' ----- IF NOT OBJECT_ID('dbo.sp_send_dbmail', 'P') IS NULL DROP PROCEDURE dbo.sp_send_dbmail ----- PRINT 'Dropping procedure sp_ExternalMailQueueListener' ----- IF NOT OBJECT_ID('dbo.sp_ExternalMailQueueListener', 'P') IS NULL DROP PROCEDURE dbo.sp_ExternalMailQueueListener ----- PRINT 'Dropping procedure sp_sysmail_activate' ----- IF NOT OBJECT_ID('dbo.sp_sysmail_activate', 'P') IS NULL DROP PROCEDURE dbo.sp_sysmail_activate ----- PRINT 'Dropping procedure sp_add_quota_information' ----- IF NOT OBJECT_ID('dbo.sp_add_quota_information', 'P') IS NULL DROP PROCEDURE dbo.sp_add_quota_information ----- PRINT 'Dropping procedure sp_current_principal_mails' ----- IF NOT OBJECT_ID('dbo.sp_current_principal_mails', 'P') IS NULL DROP PROCEDURE dbo.sp_current_principal_mails ----- PRINT 'Dropping procedure sp_delete_quota_information' ----- IF NOT OBJECT_ID('dbo.sp_delete_quota_information', 'P') IS NULL DROP PROCEDURE dbo.sp_delete_quota_information ----- PRINT 'Dropping procedure sp_verify_quota_mail_count' ----- IF NOT OBJECT_ID('dbo.sp_verify_quota_mail_count', 'P') IS NULL DROP PROCEDURE dbo.sp_verify_quota_mail_count GO /**************************************************************/ -- Drop all DatabaseMail TABLES /**************************************************************/ PRINT '' PRINT 'Dropping TABLES...' PRINT '' ----- PRINT 'Dropping table sysmail_log' ----- IF NOT OBJECT_ID('dbo.sysmail_log', 'U') IS NULL DROP TABLE sysmail_log ----- PRINT 'Dropping table sysmail_query_transfer' ----- IF NOT OBJECT_ID('dbo.sysmail_query_transfer', 'U') IS NULL DROP TABLE sysmail_query_transfer ----- PRINT 'Dropping table sysmail_attachments_transfer' ----- IF NOT OBJECT_ID('dbo.sysmail_attachments_transfer', 'U') IS NULL DROP TABLE sysmail_attachments_transfer ----- PRINT 'Dropping table sysmail_send_retries' ----- IF NOT OBJECT_ID('dbo.sysmail_send_retries', 'U') IS NULL DROP TABLE sysmail_send_retries ----- PRINT 'Dropping table sysmail_attachments' ----- IF NOT OBJECT_ID('dbo.sysmail_attachments', 'U') IS NULL DROP TABLE sysmail_attachments ----- PRINT 'Dropping table sysmail_mailitems' ----- IF NOT OBJECT_ID('dbo.sysmail_mailitems', 'U') IS NULL DROP TABLE sysmail_mailitems ----- PRINT 'Dropping table sqlimail_data_transfer' ----- IF NOT OBJECT_ID('dbo.sqlimail_data_transfer', 'U') IS NULL DROP TABLE sqlimail_data_transfer ----- PRINT 'Dropping table sysmail_quota_information' ----- IF NOT OBJECT_ID('dbo.sysmail_quota_information', 'U') IS NULL DROP TABLE sysmail_quota_information GO /**************************************************************/ -- Drop MESSAGES, CONTRACTS, QUEUES AND SERVICES /**************************************************************/ PRINT '' PRINT 'Dropping MESSAGES, CONTRACTS, QUEUES AND SERVICES...' PRINT '' ----- PRINT 'Dropping service InternalMailService' ----- IF EXISTS (SELECT * FROM sys.services WHERE name ='InternalMailService') DROP SERVICE InternalMailService; ----- PRINT 'Dropping service ExternalMailService' ----- IF EXISTS (SELECT * FROM sys.services WHERE name ='ExternalMailService') DROP SERVICE ExternalMailService; ----- PRINT 'Dropping queue InternalMailQueue' ----- IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InternalMailQueue' AND type = 'SQ') DROP QUEUE InternalMailQueue; ----- PRINT 'Dropping queue ExternalMailQueue' ----- IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ExternalMailQueue' AND type = 'SQ') DROP QUEUE ExternalMailQueue; ----- PRINT 'Dropping service [SQL/Notifications/SysMailNotification/v1.0]' ----- IF EXISTS (SELECT * FROM sys.services WHERE name ='SQL/Notifications/SysMailNotification/v1.0') DROP SERVICE [SQL/Notifications/SysMailNotification/v1.0]; ----- PRINT 'Dropping queue SysMailNotificationQueue' ----- IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SysMailNotificationQueue' AND type = 'SQ') DROP QUEUE SysMailNotificationQueue; ----- PRINT 'Dropping contract [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]' ----- IF EXISTS(SELECT * FROM sys.service_contracts WHERE name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0') DROP CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]; ----- PRINT 'Dropping message type [{//www.microsoft.com/databasemail/messages}SendMail]' ----- IF EXISTS(SELECT * FROM sys.service_message_types WHERE name = '{//www.microsoft.com/databasemail/messages}SendMail') DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]; ----- PRINT 'Dropping message type [{//www.microsoft.com/databasemail/messages}SendMailStatus]' ----- IF EXISTS(SELECT * FROM sys.service_message_types WHERE name = '{//www.microsoft.com/databasemail/messages}SendMailStatus') DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus]; GO ----- PRINT 'Dropping certificates and related users' ----- DECLARE @sqlcmd nvarchar(max), @mailDb sysname, @CertName sysname, @CertNameQuoted sysname, @MailLogin sysname, @MailLoginQuoted sysname, @MailLoginStrQuoted sysname SET @mailDb = DB_NAME() SET @CertName = N'DatabaseMail-Certificate-' + @mailDb SET @CertNameQuoted = QUOTENAME( @CertName ) SET @MailLogin = N'DatabaseMail-' + @mailDb + '-Certificate-Login' SET @MailLoginQuoted= QUOTENAME( @MailLogin ) SET @MailLoginStrQuoted = QUOTENAME(@MailLogin, '''') ----- PRINT 'Dropping user in msdb' ----- SET @sqlcmd = N'USE msdb IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + @MailLoginStrQuoted + ')) DROP USER ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd ----- PRINT 'Dropping user and certificate login in master' ----- SET @sqlcmd = N'USE master IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + @MailLoginStrQuoted + ')) DROP USER ' + @MailLoginQuoted + 'IF(EXISTS(select * from sys.server_principals where name = N' + @MailLoginStrQuoted + ')) DROP LOGIN ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd ----- PRINT 'Dropping user in this database' ----- IF(EXISTS (SELECT * FROM sys.database_principals WHERE name = @MailLogin)) BEGIN SET @sqlcmd = N'DROP USER ' + @MailLoginQuoted EXEC sp_executesql @sqlcmd END ----- PRINT 'Dropping the certificate in this db' ----- IF(EXISTS(SELECT * FROM sys.certificates WHERE name = @CertName)) BEGIN SET @sqlcmd = N'DROP CERTIFICATE ' + @CertNameQuoted EXEC sp_executesql @sqlcmd END ----- PRINT 'Dropping certificate from master' ----- SET @sqlcmd = N'USE master IF(EXISTS(SELECT * FROM sys.certificates WHERE name = N' + QUOTENAME(@CertName, '''') + ')) DROP CERTIFICATE ' + @CertNameQuoted EXEC sp_executesql @sqlcmd PAD(ul: Sqlcmd [-U {vU_ ID] [-P [x] [-S gRhV] [-H ;N:g T] [-E SOޏc] [-N R[ޏc][-C ON gRhVfN] [-d O(upenc^ Ty] [-l {vU_efeQ] [-I AQ&^_SvhƋ&{] [-c }TN~_g] [-L[c] RQ gRhV[ndQ]] [-q "}TNLg"] [-Q "}TNLg" v^Q] [-m ~+R] [-V %N͑~+R] [-W Rd>\zz | i:<Nxu>[,o:<Nxu>]] [-Z e^[xv^Q] [-k[1|2] Rd[fbc]c6RW[&{] [-y SS^{|W>f:y[^] [-Y V[^{|W>f:y[^] [-p[1] SbpS~Oo`[QSf:ylXd] %Sqlcmd: %1!s! : *gw y0eQ "-?" Sg w.^R0 &Sqlcmd: -%1!s! : :\Spe0eQ "-?" Sg w.^R0 &Sqlcmd: %1!s! : Spe Ncknx0eQ "-?" Sg w.^R0 8Sqlcmd: %1!s! %2!s! : %3!s! _{/fNN %4!d! T %5!d! KNvpeW[0 Sqlcmd: -%1!s! Spe NTvQNSpe~TO(u0 Sqlcmd: %1!s! T %2!s! yNe0 Sqlcmd: }TN %1!s!: OvSpeeHe0 ,gbybYtgbL-NQsl0Sqlcmd: BlQ^(u z^0*Sqlcmd: %1!s! : Spe Ncknx0Spe_{/f'YN %2!d! vpeW[0 'Sqlcmd: -w %1!s! : }TN0/TR,gTsXSϑy(u0 *ybYt~bk&{ N/f Transact-SQL OYusQ.W[bcO z^N(uvsQ.W[0 !ybYt~bk&{ N/f SQL OYusQ.W[bcO z^N(uvsQ.W[0 #Sqlcmd: %1!s! : SpeeHe0eQ "-?" Sg w.^R0Sqlcmd: : ~b N0RRYSeN %1!s! 0 eN %1!s! S YSb(WQ0 &Sqlcmd: : %1!s! X[(WQ(SV: %2!s!)0 /Sqlcmd: : Sb_eN %1!s! b[vQgbLd\OeQ(SV: %2!s!)0 Sqlcmd: Q0 PA@mo` %1!d! ~+R %2!d! r` %3!d! gRhV %4!s! Ǐ z %5!s! ,{ %6!d! L %7!s! ellbc eHe b|~*g[ň[0 PA[x: ZMicrosoft (R) SQL Server }TNL]wQ Hr,g %1!s! %2!s! HrCg@b g (c) Microsoft Corporation0OYu@b gCg)R0 Sqlcmd: ]bRޏc0R gRhV %1!s! 0 Sqlcmd: : ,gQ0 Sqlcmd: : G0RaYv,g~>\0 $Sqlcmd: : (W}TN %1!s! DяG0RaYv,g~>\0 Sqlcmd: : ,{ %1!d! LX[(Wl0 *Sqlcmd: : }TN %2!s! Dяv,{ %1!d! LX[(Wl0 #Sqlcmd: : (WeN %1!s! -NG0RaYv,g~>\0 /Sqlcmd: : (WeN %2!s! -Nv}TN %1!s! DяG0RaYv,g~>\0 (Sqlcmd: : eN %2!s! v,{ %1!d! LX[(Wl0 5Sqlcmd: : (WeN %3!s! Q}TN %2!s! Dяv,{ %1!d! LX[(Wl0 3Sqlcmd: -%1!s! %2!s! : ] - (W Windows }TN shell -NgbL}TN0 :connect server[\instance] [-l e - \Q͑[T0ReN0stderr b stdout0 :exit - zsSQ sqlcmd0 :exit() - gbLSX[ NԏV

) - gbLc[vgԏVpeW[~g0 go [<n>] - gbLSX[(n !k)0 :help - >f:ydk}TNRh0 :list - QSX[vQ[0 :listvar - RQnv sqlcmd ,gSϑ0 :on error [exit|ignore] - (WgbLybYtb sqlcmd }TNe^[vce0 :out <eN T>|stderr|stdout - \g⋓Q͑[T0ReN0stderr b stdout0 :perftrace <eN T>|stderr|stdout - \eQ͑[T0ReN0stderr b stdout0 :quit - zsSQ sqlcmd0 :r <eN T> - \eNQ[R0RSX[KNT0 :reset - >e_SX[0 :serverlist - RQ,g0W SQL Server TQ~-Nv SQL Server0 :setvar {variable} - Rd sqlcmd ,gSϑ0 :setvar <Sϑ> <

- n sqlcmd ,gSϑ0 eQe[x: Q!keQe[x: Q!keQv[x N9SM ee]0R ck(WSmޏc zP...Sqlcmd: NY!kc[ y %1!s! 0 GSqlcmd: : ޏc1Y%0SQL Native Client *gcknx[ň0傁fckdk ЏL SQL Server [ň z^0 !Sqlcmd: : ޏc1Y%0 gN*NbY*Nޏc^\'` Ncknx0 )RYS COM eQ0CoInitialize 1Y% Hresult: 0x%XPA*HResult 0x%1!X! ~+R %2!d! r` %3!d! %4!s! 0(ul: EXECUTE xp_snmp_getstate [<ԏVr` INT> OUTPUT](ul: EXECUTE xp_snmp_raisetrap < gRhV VARCHAR[30]>, <penc^ VARCHAR[30]>, <mo` VARCHAR[255]>, <mo` ID INT>, <%N͑'` INT>, <(u7b VARCHAR[30]>, <lʑ VARCHAR[255]>, <egTe DATETIME>, <ԏVr` INT> OUTPUTSQL SNMP Ntck(WЏL0 N/ec SNMP w1S0QsQX[0Q~*g/TR0SQL SNMP Nt(WI{_ SNMP [7bzT^ee0SQL SNMP Nt*gЏL0r`+TIN ]bRzNw10SQL SNMP Ntr`*gw0!cO~ xp_snmp_raisetrap vN*NbY*NSpeǏ'Y00elR} SQLSNMP.DLL - (W PATH -N\_{ gN*NvU_S+T DLL voR,g0'(W SQLSNMP.DLL -N~b N0R SQLsnmpTraps() veQSp0eN/fvU_mo`: %ld r`: %d %N͑'`: %d%N͑'`: %d : %d d\O|~: %d (ul: EXECUTE xp_sqlagent_monitor @command = 'START', @netsend = '<0W@W>', @restart = <͑e/TR\Ջ!kpe(0 - 8)> b EXECUTE xp_sqlagent_monitor @command = 'STOP'SQLServerAgent vƉhV](WЏL0)SQLServerAgent vƉhV: SQLServerAgent ]_8^~bk0A(W SQLServerAgent _8^~bkT SQLServerAgent vƉhVbR0W͑e/TRN SQLServerAgent0'SQLServerAgent vƉhV: SQLServerAgent ]sQ0$`vd\O|~CgP NY elSb_ SQLServerAgent r`NN0*gwSQLServerAgent vƉhVRYS1Y%0SQLServerAgent vƉhV]bR/TR0SQLServerAgent vƉhV]\Pbk0H(W SQLServerAgent _8^~bkT SQLServerAgent vƉhVel͑e/TR SQLServerAgent (SV: %s)0SQLServerAgent vƉhVS_MR*gЏL0PA%]Ǐcwv SQLServerAgent wvg'Ypev \_euw0#1uNS^RS[a1Y% elmR SQLServerAgent w0](ul: EXECUTE xp_sqlagent_notify <d\O{|W>, <\ON ID>, <R ID>, <fb ID>, <R\O{|W> [, <{vU_ T>] [, <h_>]I(ul: EXECUTE xp_sqlagent_notify 'J', <\ON ID>, NULL, NULL, <R\O{|W> [, <{vU_ T>]Q(ul: EXECUTE xp_sqlagent_notify 'J', <\ON ID>, NULL, NULL, <R\O{|W>, <{vU_ T> [, <h_>]B(ul: EXECUTE xp_sqlagent_notify 'S', <\ON ID>, <R ID>, NULL, <R\O{|W>B(ul: EXECUTE xp_sqlagent_notify 'A', NULL, NULL, <alert_id>, <R\O{|W>F(ul: EXECUTE xp_sqlagent_notify 'D', [<\ON ID> | NULL], NULL, NULL, NULL el\wb~ SQLServerAgent (SV: %s)!SQLServerAgent S_MR*gЏL Vdkel\dkd\Ow[0 c[vR\O{|WeHe0 c[vd\O{|WeHe0(SQLServerAgent ǏNR^qQN\ONRhAQve

N\ON01uNS^RS[a1Y% elg>N\ON0SQLServerAgent : %sSQLServerAgent S_MR*gЏL0elg>N SQLServerAgent \ON(SV: %s);(ul: EXECUTE xp_fileexist <eN T> [, <file_exists INT> OUTPUT]Q: elRMYvQX[0eN]X[(W3(ul: EXECUTE xp_sqlagent_is_starting <h_ INT> OUTPUT,(ul: EXECUTE xp_terminate_process <ۏ z ID | 0>el~bkۏ z(SV: %s)%s() ԏVN %ld  %s (ul: EXECUTE xp_msx_enlist <0 ({v) | 1 (1y) | 2 (͑e{v)>, <MSX gRhV Ty VARCHAR[64]>, <Windows NT (u7b T VARCHAR[100]>, [, <MOn VARCHAR[200]>] [, <e:Ste INT>] [, <,g0We VARCHAR>] [, <n INT>]el\BlS0R MSX (SV: %s)elN MSX SnxOo`(SV: %s)elN MSX ^zޏc(SV: %s) %s d\O]bR[b0%s d\O1Y%(SV: %s){v1y{vfeelR^bO9ed\OXT %s (SV: %s)Hc[v MSX NX[(Wb SQLServer vHr,g:N 6.5 (bfNO)b}:N 7.0 Hr FOvQ SQLServerAgent *g/TR) gRhV %s _{N "LocalSystem" KNYv^7bЏL MbgbLdkd\O0N(ul: EXECUTE xp_sqlagent_enum_jobs <is sysadmin (0 b 1)>, <\ON@b g Ty> [, <\ON ID>] gR](WЏL0 gR]\Pbk0 gR]/TR0 gR]f\P0 gR]~~0 gRMn]f9e0gbLibU\X[PǏ zeQ: SpeeHegbLibU\X[PǏ zeQ: Spe{|WeHeqRhVMB S(uzzelRMYQX[eggbL xp_subdirs0*xp_availablemedia vSpevpenc{|W Ncknx ^/f tinyint0elRMYQX[eggbL xp_availablemedia0xp_availablemedia: elSx&^Oo`0***** YtDN ***** gRhVQ~ TC(ul: xp_getnetname [@netname [var]char(MAX_COMPUTERNAME_LENGTH) OUT]%s  0x%x: %sPA: largeint.highpart > 0PA$gbL xp_servicecontrol ibU\X[PǏ zeQ: SpeeHe&gbL xp_servicecontrol ibU\X[PǏ zeQ: gRelƋ+R'gbL xp_servicecontrol eQ: (uN gRf9evSpepeveHe&gbL xp_servicecontrol eQ: Mnf9e/TR!j_SpeeHe"gbL xp_servicecontrol eQ: Mnf9e}TNeHePAgbL Read ibU\X[PǏ zeQ: SpeeHegbL xp_regwrite ibU\X[PǏ zeQ: SpeeHe!gbL xp_regwrite ibU\X[PǏ zeQ: lQh{|WeHe1gbL xp_regwrite ibU\X[PǏ zeQ: lQh REG_(MULTI)_SZ

, <0W@W>6rvU_]X[(WelR} semmap.dll0Qs MAPI (S: %u)0QX[RM1Y%0MneN T/f؞MneN>V:N @query_result_no_append T @query_no_truncate yNe @bNelgbLg0PA6l: dk}TNvl:N: xp_sqlmaint '<S~ sqlmaint.exe vSpeRh>'gbL API 1Y%: %s0sqlmaint.exe 1Y%00N(W Windows SQL Server N/ecdk xp_servicecontrol Spe0]ne^7b FOe^7b](R) Rd CgP NY elgbLdkd\OvQNMSSearch gR*gЏL0VDTSFlatFile;MS Remote;MSDataShape;MSPersist;Microsoft.Jet.OLEDB.3.51;SampProv;CustTran#*g[ň Oracle SQL*Net bX[Pzz N el[bdkd\O08l: dk}TNvl/f: xp_adsirequest '<S~ xpadsi.exe vSpevRh>' W[PASpeeHe0Spe{|WeHe0SpeN\ON: QX[ f\eN]X[(W0!S g "sysadmin" V[ gRhV҉rvbXTMbgbL~bR0"gbL %s eQ: CgP N0(u7b_{/f %s gRhV҉rvbXT0el!jb[7bzޏc[hQ N Ne0DReNƖb[7bz{vU_-elR^ DatabaseMail.exe ۏ z0gbL API %s 1Y% S %d0elSb_DN %s 0gbL API %s 1Y% S %d0elSb_DN %s 0b~0elSb_DN %s 0eN{|W Ncknx0elSDN %s 0gbL API %s 1Y% S %d0%s el %s : %sPAD4VS_VERSION_INFOd@2 @?StringFileInfo~080404B0&PlatformNT x644LegalTrademarksMicrosoft SQL Server /f Microsoft Corporation vlQFUh0 CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationPFileDescriptionXPSTAR Resource DLLt*FileVersion2009.0100.1600.01 ((KJ_RTM).100402-1539 )4 InternalNameXPSTARENGTLegalCopyrightMicrosoft Corp. OYu@b gCg)R0DOriginalFilenameXPSTARENG.DLLJProductNameMicrosoft SQL Server> ProductVersion10.50.1600.1DVarFileInfo$TranslationPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGX`0S *H D0@1 0 +0h +7Z0X03 +70% <<<Obsolete>>>0!0 +cbWV\6nLf610`0L .P\0 +0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority0 070822223102Z 120825070000Z0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Code Signing PCA0"0  *H 0 y}]E941%5IwEqFԌkLRbMIl/$>e# HuEP%+ #A$bEJͳ/"J-|o;99ݽ)f;-2'Hlc\򸔣8P'N0==l9.4. }bxfs Oc,2EJ;PSfQyV>Pn5{$Rf=N+~3nGўJnS00U% 0 +0U0[pir#Q~Mˡr0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority<<>c@0U00Uvp[NQD.Dc0 U0 +{~J&μNtX't*uLxMi|CʇSŸVocDDȚ  )}s9=j8m҈#i 4|.)Bk(q8 ]hͽAkf4|zB{ p֒O8|-=4 b7j#\cZ9`U3; _,˫ 0  *RA^0(ip΀Brv0z0b a>0  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Code Signing PCA0 091207224029Z 110307224029Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR10UMicrosoft Corporation0"0  *H 0 0ErSkO#=Y@8s&S<B8auM\F^i[s)DdY/]nǫ9eħuꇗ&&v89&+ZT!򗍇)I,?}͖ q B5׸?ݎE }K[vrw#}5]PKGmUTN؁B1U SI0奈|^zZ7i2$00U% 0 +0U8xs2_Uƙt0U0U#0vp[NQD.Dc0DU=0;09753http://crl.microsoft.com/pki/crl/products/CSPCA.crl0H+<0:08+0,http://www.microsoft.com/pki/certs/CSPCA.crt0  *H ( oBvCB$e4uq0r7:pQ q(ps{V4ZrN {DO aun+Z 9`jT;1_,nM*v{Ƈֱ*rTn*ȞobKk=/zw9gj{cI&Bޕ" _s2r6)$ROfH&9iPNl.r!m$H@|t8JO00j O%EXzg0  *H 0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority0 060916010447Z 190915070000Z0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA0"0  *H 0 7nBJqH>S,2ORȃ>3I1(dPKuǨծipfx'f趷 Y")/@VvmdmJT޿ǀL7VhGv\/}%V[jc|<%M9wt]\؆7,u9 |vlnz>q_*Ob`2҃N+"\hE/Pl%ׅvs6ƕz`3[AXn,HoCj&k(0$0U% 0 +0U0[pir#Q~Mˡr0p1+0)U "Copyright (c) 1997 Microsoft Corp.10U Microsoft Corporation1!0UMicrosoft Root Authority<<>c@0 +70UoN?4K;AC0 +7  SubCA0 U0U00  *H M1|PapEsT? -QS9V ތ;ɷQ!oi~k"Flm|"Fӄ6~p]Eݎ*|ɮ2Շc6!v;s!شTeJ(&`;exHϭ:ObX099!dcC/{FeJtn ̝(a|H!8Ŗ2@S=f7"̰wTQ:rD#00 a00  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Timestamping PCA0 080725190115Z 130725191115Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:85D3-305C-5BCF1%0#UMicrosoft Time-Stamp Service0"0  *H 0 -g%&bK08e46$h{b,FKQ4lZ;G=.5kE<fiJ',gbT'9هN`G#{'@ 15 ӋxaŵA>6X':ڽ]L0 +0 *H  1  +70 +7 10  +70# *H  1Q.A+|@ 0X +7 1J0H&$SQL Server 2008 R2http://www.microsoft.com/sql0  *H 2݇x*^2!sJ*|a2'p(ވxם'I GCM1EjZ췹;U-C( K ½ \Zc/5IUz03ݐQ-p4%[+ K5}IH0DŽ4VzK%MlN듀ĿR#W3!t4銐k6;|M3o|U.܄kژoۈCZD~Ⱦ3ߊUL4VDD38ݓ4 &Nuʖ}]5T{v-3aOUuj(/a) BB$ׂk#} A/Aż=ϳ%v