MZ@ !L!This program cannot be run in DOS mode. $Uu]333c3c3Rich3PEd5O"  < aPL>@:>X<.rsrc:<@@0P0H&`Px  8 P h        ( @ X p      0 @ P ` p             0 @ P ` p         zP6(h|0dR4l jX`|L$|(P-01"9Z BDXIZ SCRIPTFILEINSTALL_DATABASEMAILUNINSTALL_DATABASEMAIL/**********************************************************************/ /* INSTALL_DATABASEMAIL.SQL */ /* */ /* Installs the tables, triggers and stored procedures necessary for */ /* dbmail operations */ /* */ /* ** Copyright (c) Microsoft Corporation ** 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 P/**********************************************************************/ /* Uninstall_DatabaseMail.sql */ /* */ /* Uninstalls the tables, triggers and stored procedures necessary for*/ /* databasemail operations */ /* */ /* ** Copyright (c) Microsoft Corporation ** 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 PAusage: Sqlcmd [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-N Encrypt Connection][-C Trust Server Certificate] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-z new password] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p[1] print statistics[colon format]] [-R use client regional setting] [-b On error batch abort] [-v var = "value"...] [-A dedicated admin connection] [-X[1] disable commands, startup script, enviroment variables [and exit]] [-x disable variable substitution] [-? show syntax summary] 7Sqlcmd: '%1!s!': Unknown Option. Enter '-?' for help. :Sqlcmd: '-%1!s!': Missing argument. Enter '-?' for help. <Sqlcmd: '%1!s!': Unexpected argument. Enter '-?' for help. JSqlcmd: '%1!s! %2!s!': %3!s! has to be a number between %4!d! and %5!d!. TSqlcmd: The -%1!s! parameter can not be used in combination with other parameters. ASqlcmd: The %1!s! and the %2!s! options are mutually exclusive. 3Sqlcmd: Command %1!s!: Invalid Parameters passed. *Syntax Error in script or batch execution.(Sqlcmd: Request for Application to exit.WSqlcmd: '%1!s!': Unexpected argument. Argument has to be a number greater than %2!d!. GSqlcmd: '-w %1!s!': value must be greater than 8 and less than 65536. DSqlcmd: '%1!s!': Unexpected argument. Argument has to be a number. MSqlcmd: '%1!s!': Unexpected argument. Argument value has to be zero or one. $Sqlcmd: '%1!s!': Invalid filename. ESqlcmd: '%1!s!': Unexpected argument. Argument value has to be one. ASqlcmd: Warning: '-%1!s!' is an obsolete option and is ignored. Input file '%1!s!' not found. )'%1!s!' scripting variable not defined. dSqlcmd: Error: ED and !!<command> commands, startup script, and enviroment variables are disabled. ^The batch terminator cannot be a Transact-SQL reserved keyword or provider-specific keyword. UThe batch terminator cannot be a SQL reserved keyword or provider specific keyword. 7Sqlcmd: '%1!s!': Invalid argument. Enter '-?' for help.8Sqlcmd: Error: Initialization File '%1!s!' not found. $File '%1!s!' recursively included. 9Sqlcmd: Error: Internal error at %1!s! (Reason: %2!s!). YSqlcmd: Error: Error occurred while opening or operating on file %1!s! (Reason: %2!s!). Sqlcmd: Internal Error. WMsg %1!d!, Level %2!d!, State %3!d!, Server %4!s!, Procedure %5!s!, Line %6!d! %7!s! can't convert valueUnknown Servers: } Network packet size (bytes): %1!s! %2!d! xact[s]: Clock Time (ms.): total %3!7ld! avg %4!s! (%5!s! xacts per sec.) FMsg %1!d!, Level %2!d!, State %3!d!, Server %4!s!, Line %5!d! %6!s! SQLCMDcSqlcmd: Error: The scripting variable: '%1!s!' set with the -v option has invalid value: '%2!s!'. NSqlcmd: Error: The environment variable: '%1!s!' has invalid value: '%2!s!'. lSqlcmd: Error: The scripting variable '%1!s!', set with the setvar command, has an invalid value: '%2!s!'. >Sqlcmd: Error: The scripting variable: '%1!s!' is read-only. 4Sqlcmd: Error: No scripting variable is specified. W Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C. WSqlcmd: Error: '-' or '/' does not have an associated argument. Enter '-?' for help. GSqlcmd: Error: Cannot obtain exclusive access for the file - '%1!s!'. LSqlcmd: Error: At least one input file is the same as output file '%1!s!'. Sqlcmd: Error: %1!s! : %2!s!. bSqlcmd: Error: The scripting variable: '%1!s!' is readonly and cannot be set with the -v option. $GO count should be greater than 0. ASqlcmd: '%1!s!': Argument too long (maximum is %2!d! characters).cSqlcmd: '%1!s!': Unexpected argument. Argument has to be a number greater than or equal to %2!d!.  (%1!d! rows affected) \Sqlcmd: '%1!s! %2!s!': header value must be either -1 or a value between -1 and 2147483647 8Cannot obtain exclusive access for the file - '%1!s!'. *Sqlcmd: Error at line %1!d! of file %2!s!:Sqlcmd: Error: '%1!s!': Invalid filename. Sqlcmd: Error at line %1!d!:<The '%1!s!' variable is not readable for security reasons. bSqlcmd: The code page <%1!d!> specified in option -f is invalid or not installed on this system. PA Password: wMicrosoft (R) SQL Server Command Line Tool Version %1!s! %2!s! Copyright (c) 2012 Microsoft. All rights reserved. 3Sqlcmd: Successfully connected to server '%1!s!'. !Sqlcmd: Error: Scripting error. 6Sqlcmd: Error: Unexpected end of script encountered. KSqlcmd: Error: Unexpected end of script encountered near command '%1!s!'. ,Sqlcmd: Error: Syntax error at line %1!d!. ASqlcmd: Error: Syntax error at line %1!d! near command '%2!s!'. FSqlcmd: Error: Unexpected end of script encountered in file '%1!s!'. [Sqlcmd: Error: Unexpected end of script encountered near command '%1!s!' in file '%2!s!'. <Sqlcmd: Error: Syntax error at line %1!d! in file '%2!s!'. QSqlcmd: Error: Syntax error at line %1!d! near command '%2!s!' in file '%3!s!'. gSqlcmd: '-%1!s! %2!s!': value must be greater than or equal to %3!d! and less than or equal to %4!d!. HSqlcmd: '%1!s!': Unexpected argument. Argument value has to be 1 or 2. :!! [<command>] - Executes a command in the Windows command shell. :connect server[\instance] [-l timeout] [-U user [-P password]] - Connects to a SQL Server instance. :ed - Edits the current or last executed statement cache. :error <dest> - Redirects error output to a file, stderr, or stdout. :exit - Quits sqlcmd immediately. :exit() - Execute statement cache; quit with no return value. :exit(<query>) - Execute the specified query; returns numeric result. go [<n>] - Executes the statement cache (n times). :help - Shows this list of commands. :list - Prints the content of the statement cache. :listvar - Lists the set sqlcmd scripting variables. :on error [exit|ignore] - Action for batch or sqlcmd command errors. :out <filename>|stderr|stdout - Redirects query output to a file, stderr, or stdout. :perftrace <filename>|stderr|stdout - Redirects timing output to a file, stderr, or stdout. :quit - Quits sqlcmd immediately. :r <filename> - Append file contents to the statement cache. :reset - Discards the statement cache. :serverlist - Lists local and SQL Servers on the network. :setvar {variable} - Removes a sqlcmd scripting variable. :setvar <variable> <value> - Sets a sqlcmd scripting variable. Enter new password:  Enter new password again: $Re-entered password does not match Timeout expired $Canceling connection. Please wait...<Sqlcmd: Option '%1!s!' cannot be specified multiple times. ySqlcmd: Error: Connection failure. SQL Native Client is not installed correctly. To correct this, run SQL Server Setup. USqlcmd: Error: Connection failure. One or more connection properties are incorrect. ?Error Intiailizing COM . CoInitialize failed with Hresult: 0x%X2HResult 0x%1!X!, Level %2!d!, State %3!d! %4!s! =Usage: EXECUTE xp_snmp_getstate [<return-status INT> OUTPUT]Usage: EXECUTE xp_snmp_raisetrap <server VARCHAR[30]>, <database VARCHAR[30]>, <error-message VARCHAR[255]>, <message-id INT>, <severity INT>, <user VARCHAR[30]>, <comment VARCHAR[255]>, <date-and-time DATETIME>, <return-status INT> OUTPUTThe SQL SNMP Agent is running.$The SNMP trap number is unsupported.A memory error occurred.PA!The network has not been started.IThe SQL SNMP Agent timed-out waiting for a response from the SNMP client."The SQL SNMP Agent is not running.StatusMeaning!The trap was raised successfully.Unknown SQL SNMP Agent state.IOne or more of the parameters supplied to xp_snmp_raisetrap is too large.fUnable to load SQLSNMP.DLL - A copy of this DLL nust be present in at least one directory on the PATH.AUnable to find the entry point for SQLsnmpTraps() in SQLSNMP.DLL.File is a Directory%Message: %ld, State: %d, Severity: %dSeverity: %d Error:%d, OS: %d PAUsage: EXECUTE xp_sqlagent_monitor @command = 'START', @netsend = '<address>', @restart = <restart attempts (0 - 8)> or EXECUTE xp_sqlagent_monitor @command = 'STOP'*SQLServerAgent Monitor is already running.CSQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.jSQLServerAgent Monitor successfully restarted SQLServerAgent after SQLServerAgent terminated unexpectedly.9SQLServerAgent Monitor: SQLServerAgent has been shutdown._You do not have sufficient operating system permission to open the SQLServerAgent status event.Unknown,SQLServerAgent Monitor failed to initialize.,SQLServerAgent Monitor started successfully.SQLServerAgent Monitor stopped.rSQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: %s).0SQLServerAgent Monitor is not currently running.pThe maximum number of pending SQLServerAgent notifications has been exceeded. The notification will be ignored.XUnable to add SQLServerAgent notification due to failure to obtain serialization object.Usage: EXECUTE xp_sqlagent_notify <operation type>, <job id>, <schedule id>, <alert id>, <action type> [, <login name>] [, <error flag>]\Usage: EXECUTE xp_sqlagent_notify 'J', <job id>, NULL, NULL, <action type> [, <login name>]jUsage: EXECUTE xp_sqlagent_notify 'J', <job id>, NULL, NULL, <action type>, <login name> [, <error flag>]TUsage: EXECUTE xp_sqlagent_notify 'S', <job id>, <schedule id>, NULL, <action type>MUsage: EXECUTE xp_sqlagent_notify 'A', NULL, NULL, <alert_id>, <action type>KUsage: EXECUTE xp_sqlagent_notify 'D', [<job id> | NULL], NULL, NULL, NULL:Unable to post notification to SQLServerAgent (reason: %s)PSQLServerAgent is not currently running so it cannot be notified of this action.%The specified Action Type is invalid.(The specified Operation Type is invalid.mSQLServerAgent exceeded the allowed timeout for creating the shared job list. The Jobs cannot be enumerated.PAGUnable to enumerate Jobs due to failure to obtain serialization object.SQLServerAgent Error: %s(SQLServerAgent is not currently running.4Unable to enumerate SQLServerAgent jobs (reason: %s)DUsage: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]6Internal Error: Could not allocate sufficient memory. File Exists9Usage: EXECUTE xp_sqlagent_is_starting <Flag INT> OUTPUT5Usage: EXECUTE xp_terminate_process <Process ID | 0>(Unable to terminate process (reason: %s)%s() returned error %ld, '%s'Usage: EXECUTE xp_msx_enlist <0 (enlist) | 1 (defect) | 2 (re-enlist)>, <MSX server name VARCHAR[64]>, <Windows NT user name VARCHAR[100]>, [, <location VARCHAR[200]>] [, <time zone adjustment INT>] [, <local time VARCHAR>] [, <poll interval INT>]*Unable to send request to MSX (reason: %s)3Unable to read acknowledgment from MSX (reason: %s)4Unable to establish connection with MSX (reason: %s)(The %s operation completed successfully.PA$The %s operation failed (reason: %s)enlistdefectenlistment update5Unable to create or modify operator '%s' (reason: %s)The specified MSX does not exist, or is a 6.5 (or earlier) version of SQLServer, or is version 7.0 but it's SQLServerAgent has not been startedcServer '%s' must be running in an account other than 'LocalSystem' in order to perform this action.[Usage: EXECUTE xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]PAService Already Running.Service Stopped.Service Started.Service Paused.Service Continued.Service Configuration Changed.<Error executing extended stored procedure: Invalid ParameterAError executing extended stored procedure: Invalid Parameter TypedriveMB free;Could not allocate sufficient memory to execute xp_subdirs.PAHArgument for xp_availablemedia is not of the correct datatype (tinyint).BCould not allocate sufficient memory to execute xp_availablemedia.*xp_availablemedia: couldn't get tape info.#***** Process Attachment *****Server Net NamepUsage: xp_getnetname [@netname [n][var]char(MAX_PATH) OUT], [1 (fully qualified domain name) | 2 (domain name)]%s error 0x%x: %sError: largeint.highpart > 0NError executing xp_servicecontrol extended stored procedure: Invalid ParameterQError executing xp_servicecontrol extended stored procedure: Unrecognized ServiceRError executing xp_servicecontrol: Invalid number of parameters for service changeMError executing xp_servicecontrol: Invalid config change start mode parameter@Error executing xp_servicecontrol: Invalid config change commandAError executing Read extended stored procedure: Invalid ParameterHError executing xp_regwrite extended stored procedure: Invalid ParameterLError executing xp_regwrite extended stored procedure: Registry Type Invalid]Error executing xp_regwrite extended stored procedure: Registry REG_(MULTI)_SZ value mismatchXError executing xp_regwrite extended stored procedure: Registry REG_DWORD value mismatchLError executing xp_regdeletekey extended stored procedure: Invalid ParameterVError executing Read extended stored procedure: Invalid Predefined Registry system keyContinuationRow Archive #DateAlternate NameSize Creation Date Creation TimeLast Written DateLast Written TimeLast Accessed DateLast Accessed Time AttributesKeyExistInvalid acknowledgment format0Error in xp_regread: couldn't realloc %ld bytes..Error in xp_regread: couldn't alloc %ld bytes.XUsage: EXECUTE xp_sqlagent_notify 'N', NULL, NULL, NULL, <notification type>, <address>Parent Directory ExistsUnable to load semmap.dll.Memory allocation failure.vFailed to execute the query because the @query_result_no_append and @query_no_truncate options are mutually exclusive.fSyntax Error: Syntax of this command is: xp_sqlmaint '<list of parameters to send to to sqlmaint.exe>'Failed executing API: %s.sqlmaint.exe failed.JThis xp_servicecontrol parameter is supported only on Windows SQL Servers.;new account was set but the old one was (partially) removed*not enough rights to perform the operation Other error$THE MSSearch service is not running.VDTSFlatFile;MS Remote;MSDataShape;MSPersist;Microsoft.Jet.OLEDB.3.51;SampProv;CustTrandOracle SQL*Net is not installed or there is not enough storage available to complete this operation.gSyntax Error: Syntax of this command is: xp_adsirequest '<list of parameters to send to to xpadsi.exe>' BytesInvalid parameters.Invalid parameter type.Invalid parameter value.>Invalid error number. Error number must be greater than 50000.ZInvalid severity value. Severity must be 'Informational' (default), 'Warning', or 'Error'. %s: Error %d from %s on line %d xpadsi.exe failed.IError executing extended stored procedure: Expecting an output parameter.PAData Source NameValue DBMS Name DBMS Version Database NameSQL SubscriberFALSETRUE DescriptionInformation TypePALog File Size (Byte)QError executing xp_servicecontrol: Invalid number of parameters for service query8Error executing xp_servicecontrol: Invalid query commandError log location not found.The time-out was exceeded while the server waited for a response from SQL Server Agent. Make sure that the SQL Server Agent service is running"Provide only one string parameter.(Encryption error using CryptProtectData.Send secret error.Impersonation error.XError executing xp_get_script extended stored procedure: Expecting script name parametercError executing xp_get_script extended stored procedure: Specified resource name could not be foundiError executing xp_delete_file extended stored procedure: Specified file is not a SQL Server backup file.oError executing xp_delete_file extended stored procedure: Specified file is not a Maintenance Plan report file.NFailed to open loopback connection. Please see event log for more information.JFile attachment or query results size exceeds allowable value of %d bytes.Attachment file %s is invalid.PAFAttachment transfer failed. Please see event log for more information.BQuery execution failed. Please see event log for more information.<Unable to enumerate Jobs: memory mapped file already exists.KOnly members of 'sysadmin' fixed server role can execute maintenance plans.SError executing '%s': Permission denied. User must be a member of '%s' server role.tThe client connection security context could not be impersonated. Attaching files require an integrated client loginZCould not create DatabaseMail.exe process. Executing API '%s' failed with error number %d.TFailed to open attachment file '%s'. Executing API '%s' failed with error number %d.3Failed to open attachment file '%s'. Access denied.5Failed to open attachment file '%s'. Wrong file type.TFailed to read attachment file '%s'. Executing API '%s' failed with error number %d.%s could not access '%s': %sAThe instance parameter is not a valid relational engine instance.)The log file is not using Unicode format.WThe value provided for the filter by text parameters must be less than 4000 characters.PAThe format for the date filter is incorrect. Please use a valid SQL Server date or datetime format. For example, valid formats are  YYYY-MM-DD' and  MM/DD/YYYY .,Failed to query the registry on computer %s.@Failed to query sys.matrix_bricks because of a connection error.>Usage: EXECUTE xp_sqlagent_notify 'M', NULL, NULL, NULL, NULLPA4VS_VERSION_INFOn<4 <4?StringFileInfo040904B0&PlatformNT x64ILegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation. CommentsSQL&GoldenBitsTrueLCompanyNameMicrosoft CorporationPFileDescriptionXPSTAR Resource DLL|.FileVersion2011.0110.2100.060 ((SQL11_RTM).120210-1917 )4 InternalNameXPSTARENGn%LegalCopyrightMicrosoft Corp. All rights reserved.DOriginalFilenameXPSTARENG.DLLJProductNameMicrosoft SQL Server> ProductVersion11.0.2100.60DVarFileInfo$Translation  PAPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADX<00<0 +70 0!0 +%WPmױR00 aj0  *H 0y1 0 UUS10U Washington10URedmond10U Microsoft Corporation1#0!UMicrosoft Code Signing PCA0 111101223917Z 130201224917Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR10UMicrosoft Corporation0"0  *H 0 é|#1%uyϓKa%wKZmI17t`N%X N-:e.[]u4P!}9D#fe 6`*dȇE(WY&Jl)2?% oj-:mf16p@v.G+e+_hE xkP5[qa! EeI~7Z߸v Q*P:c܁G00U% 0 +0UqÏv, J(0U0U#0WEt]CT-2H0VUO0M0KIGEhttp://crl.microsoft.com/pki/crl/products/MicCodSigPCA_08-31-2010.crl0Z+N0L0J+0>http://www.microsoft.com/pki/certs/MicCodSigPCA_08-31-2010.crt0  *H yiȏk`|>8Rϙ-=au8NY?C|Sb֨vuYl?/~9T'X5aymQWcrf9*35 ^15E ]Ǘ*:IHE26m nvUU4y]n̗h}i{ U}M0b{젒*W)HͶ! b 5_kԱwRw?lV\6N^z 1ynr_0W|UplxC 500 a0  *H 0w1 0 UUS10U Washington10URedmond10U Microsoft Corporation1!0UMicrosoft Time-Stamp PCA0 110725204219Z 121025204219Z01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:9E78-864B-039D1%0#UMicrosoft Time-Stamp Service0"0  *H 0 ;SDzMVqSw7~>Sѱ"unav; CS b83+={EW c4bFٞ9Uw5\HOCX8lKzn`[?:? }m/X}ʊ89h-X#*Z їE7 |ӶSx#!bZjipS4%1UL5[ɫS)_,%d'u 00Uy# U,h)bg68Z0U#0#4RFp @v+50TUM0K0IGEChttp://crl.microsoft.com/pki/crl/products/MicrosoftTimeStampPCA.crl0X+L0J0H+03kir|_%D5( Րap-o,ݹ0qxȩg z ¼W?i00&Ԟ{ IERu{".VϩN< :ŭj"#l'~2y%B❅WY/-&)ųׅ`Z{{^4Km'Тѐ }|4lJDyh(" ,Ha#00 +70UWEt]CT-2H0 U0U00U#0`@V'%* SY䤡ca0_10 &,dcom10 &,d microsoft1-0+U$Microsoft Root Certificate AuthorityyJLsX.e0PUI0G0ECA?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+08http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0vU o0m0k +7/0^0\+0PNCopyright 2006 Microsoft Corporation.0U% 0 +0  *H 0 b9&a=ܬƙІ.2e^">ֆ趟ʋ;u >LdtIglfF:FBȑ7$AwO_R/>V[uSTie$%MPO "Y"t E{YL`xѺk~D@ҽKnU8HUz }۔/{kdO,4)] 8/ V1i΅ ֮8 nOL)) \\V#W &6Do阵sxIԩǒ)1}㷧IMM=}G` ٌ!x ף'<GdS,Wthzl]6''ŽAnQ-,6O|BׄEs?='CoV&< %D(ǡ#u0 *H  1 000w1 0 UUS10U Washington10URedmond10U Microsoft Corporation1!0UMicrosoft Time-Stamp PCA a0+]0 *H  1  *H 0 *H  1 120211160119Z0# *H  1ou+/A&KPh0  *H EGEn%wp:: (Rpz[i?-Խ!O= ʩ1zvh=mN» ߦzhsq,q}+of٧2*R oo ~ bmʘ5RF4BƗgF=phttp://www.microsoft.com/pki/certs/MicCodSigPCA_2010-07-06.crt0 U00  *H  x[Zm#}u j'EӒNħe݅ JSxA 0 T'Yh8.?iL Lx P:M?+R=Mk&Z\j@K1(|Tp?vY0nm. $X9MRlj[AS{T1 DA~Ri?c2KW8X—@c-mk|+;B+9@"QpA `&ǩc؅0p0X a RL0  *H  01 0 UUS10U Washington10URedmond10U Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100 100706204017Z 250706205017Z0~1 0 UUS10U Washington10URedmond10U Microsoft Corporation1(0&UMicrosoft Code Signing PCA 20100"0  *H 0 dPyg LVhDXOv|mE9eҏDe,U}.+A+KnILk‰q͵K̈k:&?4W]I*.ՅY?+t+;FFIfTUbWrg% 4]^(ղcӲȊ& Y5LR[ HwօGj-\`ƴ*[#_Eo73jMjfcx0ϕ00 +70U_{"XrN!t#20 +7  SubCA0 U0U00U#0Vˏ\bh=[Κ0VUO0M0KIGEhttp://crl.microsoft.com/pki/crl/products/MicRooCerAut_2010-06-23.crl0Z+N0L0J+0>http://www.microsoft.com/pki/certs/MicRooCerAut_2010-06-23.crt0U 00 +7.00=+1http://www.microsoft.com/PKI/docs/CPS/default.htm0@+042 Legal_Policy_Statement. 0  *H  tWO){xP" 4*,Ͽ4ہ5oywNaZ#bQEg?<09@!)奡i"tGCS0i% moar,iv=Qۦ9H7amS˧a¿⃫k}(QJQlȷJi~IprGc֢DciFz?!{#-A˿Lﱜ"KInv[Sy=s5SP8f3'9x6N_=GS a=*ג,7Z>@B1V$]Qjy{%qDj#u1000~1 0 UUS10U Washington10URedmond10U Microsoft Corporation1(0&UMicrosoft Code Signing PCA 2010 aIU 0  `He0 *H  10 *H  1  +70 +7 10  +70/ *H  1" B{7T8vYmeHr 0R +7 1D0B SQL Server 2012http://www.microsoft.com/sql0  *H %az{U/ YaHFA6FWħؤ^C=k&7>75 H)8Fej`j h:J%m\SLӅr(^gGB .JGi[;Jawqzi^ŻA`A4z8^ّ0|?d >*zڎF/Kj&lpȯG^=^=}*g7E5ZRI UT>K:G0 +710 *H 01 0 +0* *H  0 +Y 0!0 +-Pg3Nz35$O1&"r20120211160121.176Z001 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:936C-7AEE-74AE1%0#UMicrosoft Time-Stamp Service0q0Y a *0  *H  01 0 UUS10U Washington10URedmond10U Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100 100701213655Z 250701214655Z0|1 0 UUS10U Washington10URedmond10U Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 20100"0  *H 0  w: iktTե |hK,_av>f+[S'1A |a0Y0D`TC M8Bݓs0W&E GͿ$`2X`FXG2tag_TϓLĦ]an(aF'$gr!KdPb]w=WuBM@Q>gfD~Knʱz* 1N5xhttp://www.microsoft.com/pki/certs/MicRooCerAut_2010-06-23.crt0U 00 +7.00=+1http://www.microsoft.com/PKI/docs/CPS/default.htm0@+042 Legal_Policy_Statement. 0  *H  Q ?q=!o1Wm0fjxǨ%kTW+QDS`v@@A\\^5$VKtBȯ7}Z?yJR8/ye٩kzoK0D$"<Y)p2J'U/3b_W@ΙfjbJ &9Jqc{!mÜ<}jxm8ؔƥ B"8 %d~cY%z.9Wvqa˚Gͧ};q ]t"aڰPo1:eGxHr~akow˧ 9؂r*T9[ Uzs;-3.)/T'!ȬN(ۖBAM*f0ӻt2Kc{/!YDǘP5Gj+ἃdPeMsؾHu84YR䇉 ;G+ TH9Hkd~"AH6/S4J&)IaʱX&_" wguaS+ƶwp/%B:2AOTlE {@PFWƀb00U?9v5#[LW70U#0c:\1C{|F3hZmU0VUO0M0KIGEhttp://crl.microsoft.com/pki/crl/products/MicTimStaPCA_2010-07-01.crl0Z+N0L0J+0>http://www.microsoft.com/pki/certs/MicTimStaPCA_2010-07-01.crt0 U00U% 0 +0  *H  O)KSQ\\s*8hO9KC54Ud" `΃ ;@|D[ifa?ann!ҲgR*ru1>QI̐Ԉ!kbب"pQØ9X=pD.mLsT&cYNOg~u:TePbɂ%=e?cbӉ_ɩVK@TA%X,O㣏)}Wrxnj] PLv0^0㡁01 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher DSE ESN:936C-7AEE-74AE1%0#UMicrosoft Time-Stamp Service% 0 +^$3+b xc ցs적001 0 UUS10U Washington10URedmond10U Microsoft Corporation1 0 U MOPR1'0%U nCipher NTS ESN:B027-C6F8-1D881+0)U"Microsoft Time Source Master Clock0  *H ϣ0"20120211112939Z20120212112939Z0t0: +Y 1,0*0 ϣ000 !#06 +Y 1(0&0  +Y  0 0 0  *H *Aܚ}kpEK)pin_f\P0a:>uѣ{77y X&2pR"V>zi]s^FxB>c3n*dQ9ΤMpEh Dh-ak8I65΢kvg};du;OJ-(N!vm cPU\d6F!nkBD$>r;,!D]_H7ı!^"z#|RS5]1000|1 0 UUS10U Washington10URedmond10U Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 2010 aN 0 +0 *H  1  *H  0# *H  1=?=fQ -0 *H   1000^$3+b xc ցs00~0|1 0 UUS10U Washington10URedmond10U Microsoft Corporation1&0$UMicrosoft Time-Stamp PCA 2010 aN 0ZǕ. Ԥ@0  *H  15 s}brc;6!R&ߔ?SV?w3`S紆cU$*ldg"Ad7o>W4GFoLX+*4r!_ENh#Eг$~_m=hrLa+)##4}`/q*j8B.T˵emO?pQ6lqqex~*hG]i>/-5cܗnC!KF'U8uՐ|