| Server IP : 180.180.241.3 / Your IP : 216.73.216.252 Web Server : Microsoft-IIS/7.5 System : Windows NT NETWORK-NHRC 6.1 build 7601 (Windows Server 2008 R2 Standard Edition Service Pack 1) i586 User : IUSR ( 0) PHP Version : 5.3.28 Disable Function : NONE MySQL : ON | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /Program Files/Microsoft SQL Server/MSSQL11.MSSQLSERVER/MSSQL/Binn/Resources/1033/ |
Upload File : |
MZ @ !L!This program cannot be run in DOS mode.
$ Uu]333c3c3Rich3 PE d ÁP "
<
a P @ : > > .rsrc : < @ @ 0 P 0 H & ` P x 8 P h ( @ X p 0 @ P ` p 0 @ P ` p z P 6( h | 0 d R 4 l j X ` | L $ |( P - 0 1 " 9 Z B D \I Z
S C R I P T F I L E I N S T A L L _ D A T A B A S E M A I L U N I N S T A L L _ D A T A B A S E M A I L /**********************************************************************/
/* 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'<ns xmlns:responses="http://schemas.microsoft.com/databasemail/responses" />'
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'<ns xmlns:requests="http://schemas.microsoft.com/databasemail/requests" />'
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 = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
-- 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:
--
--<?xml version="1.0" encoding="utf-8"?>
--<responses:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/responses ResponseTypes.xsd" xmlns:responses="http://schemas.microsoft.com/databasemail/responses">
--<Information>
-- <Failure Message="THe error message...."/>
--</Information>
--<MailItemId Id="1" />
--<SentStatus Status="3" />
--<SentAccountId Id="0" />
--<SentDate Date="2005-03-30T14:55:13" />
--<CallingProcess Id="3012" />
--</responses:SendMail>
-- Get the handle to the xml document
EXEC @rc = sp_xml_preparedocument
@idoc OUTPUT,
@xml_message_body,
N'<ns xmlns:responses="http://schemas.microsoft.com/databasemail/responses" />'
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
PA u s a g e : S q l c m d [ - U l o g i n i d ] [ - P p a s s w o r d ]
[ - S s e r v e r ] [ - H h o s t n a m e ] [ - E t r u s t e d c o n n e c t i o n ]
[ - N E n c r y p t C o n n e c t i o n ] [ - C T r u s t S e r v e r C e r t i f i c a t e ]
[ - d u s e d a t a b a s e n a m e ] [ - l l o g i n t i m e o u t ] [ - t q u e r y t i m e o u t ]
[ - h h e a d e r s ] [ - s c o l s e p a r a t o r ] [ - w s c r e e n w i d t h ]
[ - a p a c k e t s i z e ] [ - e e c h o i n p u t ] [ - I E n a b l e Q u o t e d I d e n t i f i e r s ]
[ - c c m d e n d ] [ - L [ c ] l i s t s e r v e r s [ c l e a n o u t p u t ] ]
[ - q " c m d l i n e q u e r y " ] [ - Q " c m d l i n e q u e r y " a n d e x i t ]
[ - m e r r o r l e v e l ] [ - V s e v e r i t y l e v e l ] [ - W r e m o v e t r a i l i n g s p a c e s ]
[ - u u n i c o d e o u t p u t ] [ - r [ 0 | 1 ] m s g s t o s t d e r r ]
[ - i i n p u t f i l e ] [ - o o u t p u t f i l e ] [ - z n e w p a s s w o r d ]
[ - f < c o d e p a g e > | i : < c o d e p a g e > [ , o : < c o d e p a g e > ] ] [ - Z n e w p a s s w o r d a n d e x i t ]
[ - k [ 1 | 2 ] r e m o v e [ r e p l a c e ] c o n t r o l c h a r a c t e r s ]
[ - y v a r i a b l e l e n g t h t y p e d i s p l a y w i d t h ]
[ - Y f i x e d l e n g t h t y p e d i s p l a y w i d t h ]
[ - p [ 1 ] p r i n t s t a t i s t i c s [ c o l o n f o r m a t ] ]
[ - R u s e c l i e n t r e g i o n a l s e t t i n g ]
[ - b O n e r r o r b a t c h a b o r t ]
[ - v v a r = " v a l u e " . . . ] [ - A d e d i c a t e d a d m i n c o n n e c t i o n ]
[ - X [ 1 ] d i s a b l e c o m m a n d s , s t a r t u p s c r i p t , e n v i r o m e n t v a r i a b l e s [ a n d e x i t ] ]
[ - x d i s a b l e v a r i a b l e s u b s t i t u t i o n ]
[ - ? s h o w s y n t a x s u m m a r y ]
7 S q l c m d : ' % 1 ! s ! ' : U n k n o w n O p t i o n . E n t e r ' - ? ' f o r h e l p .
: S q l c m d : ' - % 1 ! s ! ' : M i s s i n g a r g u m e n t . E n t e r ' - ? ' f o r h e l p .
< S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . E n t e r ' - ? ' f o r h e l p .
J S q l c m d : ' % 1 ! s ! % 2 ! s ! ' : % 3 ! s ! h a s t o b e a n u m b e r b e t w e e n % 4 ! d ! a n d % 5 ! d ! .
T S q l c m d : T h e - % 1 ! s ! p a r a m e t e r c a n n o t b e u s e d i n c o m b i n a t i o n w i t h o t h e r p a r a m e t e r s .
A S q l c m d : T h e % 1 ! s ! a n d t h e % 2 ! s ! o p t i o n s a r e m u t u a l l y e x c l u s i v e .
3 S q l c m d : C o m m a n d % 1 ! s ! : I n v a l i d P a r a m e t e r s p a s s e d .
* S y n t a x E r r o r i n s c r i p t o r b a t c h e x e c u t i o n . ( S q l c m d : R e q u e s t f o r A p p l i c a t i o n t o e x i t . W S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t h a s t o b e a n u m b e r g r e a t e r t h a n % 2 ! d ! .
G S q l c m d : ' - w % 1 ! s ! ' : v a l u e m u s t b e g r e a t e r t h a n 8 a n d l e s s t h a n 6 5 5 3 6 .
D S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t h a s t o b e a n u m b e r .
M S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t v a l u e h a s t o b e z e r o o r o n e .
$ S q l c m d : ' % 1 ! s ! ' : I n v a l i d f i l e n a m e .
E S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t v a l u e h a s t o b e o n e .
A S q l c m d : W a r n i n g : ' - % 1 ! s ! ' i s a n o b s o l e t e o p t i o n a n d i s i g n o r e d .
I n p u t f i l e ' % 1 ! s ! ' n o t f o u n d .
) ' % 1 ! s ! ' s c r i p t i n g v a r i a b l e n o t d e f i n e d .
d S q l c m d : E r r o r : E D a n d ! ! < c o m m a n d > c o m m a n d s , s t a r t u p s c r i p t , a n d e n v i r o m e n t v a r i a b l e s a r e d i s a b l e d .
^ T h e b a t c h t e r m i n a t o r c a n n o t b e a T r a n s a c t - S Q L r e s e r v e d k e y w o r d o r p r o v i d e r - s p e c i f i c k e y w o r d .
U T h e b a t c h t e r m i n a t o r c a n n o t b e a S Q L r e s e r v e d k e y w o r d o r p r o v i d e r s p e c i f i c k e y w o r d .
7 S q l c m d : ' % 1 ! s ! ' : I n v a l i d a r g u m e n t . E n t e r ' - ? ' f o r h e l p . 8 S q l c m d : E r r o r : I n i t i a l i z a t i o n F i l e ' % 1 ! s ! ' n o t f o u n d .
$ F i l e ' % 1 ! s ! ' r e c u r s i v e l y i n c l u d e d .
9 S q l c m d : E r r o r : I n t e r n a l e r r o r a t % 1 ! s ! ( R e a s o n : % 2 ! s ! ) .
Y S q l c m d : E r r o r : E r r o r o c c u r r e d w h i l e o p e n i n g o r o p e r a t i n g o n f i l e % 1 ! s ! ( R e a s o n : % 2 ! s ! ) .
S q l c m d : I n t e r n a l E r r o r .
W M s g % 1 ! d ! , L e v e l % 2 ! d ! , S t a t e % 3 ! d ! , S e r v e r % 4 ! s ! , P r o c e d u r e % 5 ! s ! , L i n e % 6 ! d !
% 7 ! s !
c a n ' t c o n v e r t v a l u e U n k n o w n
S e r v e r s :
}
N e t w o r k p a c k e t s i z e ( b y t e s ) : % 1 ! s !
% 2 ! d ! x a c t [ s ] :
C l o c k T i m e ( m s . ) : t o t a l % 3 ! 7 l d ! a v g % 4 ! s ! ( % 5 ! s ! x a c t s p e r s e c . )
F M s g % 1 ! d ! , L e v e l % 2 ! d ! , S t a t e % 3 ! d ! , S e r v e r % 4 ! s ! , L i n e % 5 ! d !
% 6 ! s !
S Q L C M D c S q l c m d : E r r o r : T h e s c r i p t i n g v a r i a b l e : ' % 1 ! s ! ' s e t w i t h t h e - v o p t i o n h a s i n v a l i d v a l u e : ' % 2 ! s ! ' .
N S q l c m d : E r r o r : T h e e n v i r o n m e n t v a r i a b l e : ' % 1 ! s ! ' h a s i n v a l i d v a l u e : ' % 2 ! s ! ' .
l S q l c m d : E r r o r : T h e s c r i p t i n g v a r i a b l e ' % 1 ! s ! ' , s e t w i t h t h e s e t v a r c o m m a n d , h a s a n i n v a l i d v a l u e : ' % 2 ! s ! ' .
> S q l c m d : E r r o r : T h e s c r i p t i n g v a r i a b l e : ' % 1 ! s ! ' i s r e a d - o n l y .
4 S q l c m d : E r r o r : N o s c r i p t i n g v a r i a b l e i s s p e c i f i e d .
W
S q l c m d : W a r n i n g : T h e l a s t o p e r a t i o n w a s t e r m i n a t e d b e c a u s e t h e u s e r p r e s s e d C T R L + C .
W S q l c m d : E r r o r : ' - ' o r ' / ' d o e s n o t h a v e a n a s s o c i a t e d a r g u m e n t .
E n t e r ' - ? ' f o r h e l p .
G S q l c m d : E r r o r : C a n n o t o b t a i n e x c l u s i v e a c c e s s f o r t h e f i l e - ' % 1 ! s ! ' .
L S q l c m d : E r r o r : A t l e a s t o n e i n p u t f i l e i s t h e s a m e a s o u t p u t f i l e ' % 1 ! s ! ' .
S q l c m d : E r r o r : % 1 ! s ! : % 2 ! s ! .
b S q l c m d : E r r o r : T h e s c r i p t i n g v a r i a b l e : ' % 1 ! s ! ' i s r e a d o n l y a n d c a n n o t b e s e t w i t h t h e - v o p t i o n .
$ G O c o u n t s h o u l d b e g r e a t e r t h a n 0 .
A S q l c m d : ' % 1 ! s ! ' : A r g u m e n t t o o l o n g ( m a x i m u m i s % 2 ! d ! c h a r a c t e r s ) . c S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t h a s t o b e a n u m b e r g r e a t e r t h a n o r e q u a l t o % 2 ! d ! .
( % 1 ! d ! r o w s a f f e c t e d )
\ S q l c m d : ' % 1 ! s ! % 2 ! s ! ' : h e a d e r v a l u e m u s t b e e i t h e r - 1 o r a v a l u e b e t w e e n - 1 a n d 2 1 4 7 4 8 3 6 4 7
8 C a n n o t o b t a i n e x c l u s i v e a c c e s s f o r t h e f i l e - ' % 1 ! s ! ' .
* S q l c m d : E r r o r a t l i n e % 1 ! d ! o f f i l e % 2 ! s ! : S q l c m d : E r r o r : ' % 1 ! s ! ' : I n v a l i d f i l e n a m e .
S q l c m d : E r r o r a t l i n e % 1 ! d ! : < T h e ' % 1 ! s ! ' v a r i a b l e i s n o t r e a d a b l e f o r s e c u r i t y r e a s o n s .
b S q l c m d : T h e c o d e p a g e < % 1 ! d ! > s p e c i f i e d i n o p t i o n - f i s i n v a l i d o r n o t i n s t a l l e d o n t h i s s y s t e m .
PA
P a s s w o r d : w M i c r o s o f t ( R ) S Q L S e r v e r C o m m a n d L i n e T o o l
V e r s i o n % 1 ! s ! % 2 ! s !
C o p y r i g h t ( c ) 2 0 1 2 M i c r o s o f t . A l l r i g h t s r e s e r v e d .
3 S q l c m d : S u c c e s s f u l l y c o n n e c t e d t o s e r v e r ' % 1 ! s ! ' .
! S q l c m d : E r r o r : S c r i p t i n g e r r o r .
6 S q l c m d : E r r o r : U n e x p e c t e d e n d o f s c r i p t e n c o u n t e r e d .
K S q l c m d : E r r o r : U n e x p e c t e d e n d o f s c r i p t e n c o u n t e r e d n e a r c o m m a n d ' % 1 ! s ! ' .
, S q l c m d : E r r o r : S y n t a x e r r o r a t l i n e % 1 ! d ! .
A S q l c m d : E r r o r : S y n t a x e r r o r a t l i n e % 1 ! d ! n e a r c o m m a n d ' % 2 ! s ! ' .
F S q l c m d : E r r o r : U n e x p e c t e d e n d o f s c r i p t e n c o u n t e r e d i n f i l e ' % 1 ! s ! ' .
[ S q l c m d : E r r o r : U n e x p e c t e d e n d o f s c r i p t e n c o u n t e r e d n e a r c o m m a n d ' % 1 ! s ! ' i n f i l e ' % 2 ! s ! ' .
< S q l c m d : E r r o r : S y n t a x e r r o r a t l i n e % 1 ! d ! i n f i l e ' % 2 ! s ! ' .
Q S q l c m d : E r r o r : S y n t a x e r r o r a t l i n e % 1 ! d ! n e a r c o m m a n d ' % 2 ! s ! ' i n f i l e ' % 3 ! s ! ' .
g S q l c m d : ' - % 1 ! s ! % 2 ! s ! ' : v a l u e m u s t b e g r e a t e r t h a n o r e q u a l t o % 3 ! d ! a n d l e s s t h a n o r e q u a l t o % 4 ! d ! .
H S q l c m d : ' % 1 ! s ! ' : U n e x p e c t e d a r g u m e n t . A r g u m e n t v a l u e h a s t o b e 1 o r 2 .
: ! ! [ < c o m m a n d > ]
- E x e c u t e s a c o m m a n d i n t h e W i n d o w s c o m m a n d s h e l l .
: c o n n e c t s e r v e r [ \ i n s t a n c e ] [ - l t i m e o u t ] [ - U u s e r [ - P p a s s w o r d ] ]
- C o n n e c t s t o a S Q L S e r v e r i n s t a n c e .
: e d
- E d i t s t h e c u r r e n t o r l a s t e x e c u t e d s t a t e m e n t c a c h e .
: e r r o r < d e s t >
- R e d i r e c t s e r r o r o u t p u t t o a f i l e , s t d e r r , o r s t d o u t .
: e x i t
- Q u i t s s q l c m d i m m e d i a t e l y .
: e x i t ( )
- E x e c u t e s t a t e m e n t c a c h e ; q u i t w i t h n o r e t u r n v a l u e .
: e x i t ( < q u e r y > )
- E x e c u t e t h e s p e c i f i e d q u e r y ; r e t u r n s n u m e r i c r e s u l t .
g o [ < n > ]
- E x e c u t e s t h e s t a t e m e n t c a c h e ( n t i m e s ) .
: h e l p
- S h o w s t h i s l i s t o f c o m m a n d s .
: l i s t
- P r i n t s t h e c o n t e n t o f t h e s t a t e m e n t c a c h e .
: l i s t v a r
- L i s t s t h e s e t s q l c m d s c r i p t i n g v a r i a b l e s .
: o n e r r o r [ e x i t | i g n o r e ]
- A c t i o n f o r b a t c h o r s q l c m d c o m m a n d e r r o r s .
: o u t < f i l e n a m e > | s t d e r r | s t d o u t
- R e d i r e c t s q u e r y o u t p u t t o a f i l e , s t d e r r , o r s t d o u t .
: p e r f t r a c e < f i l e n a m e > | s t d e r r | s t d o u t
- R e d i r e c t s t i m i n g o u t p u t t o a f i l e , s t d e r r , o r s t d o u t .
: q u i t
- Q u i t s s q l c m d i m m e d i a t e l y .
: r < f i l e n a m e >
- A p p e n d f i l e c o n t e n t s t o t h e s t a t e m e n t c a c h e .
: r e s e t
- D i s c a r d s t h e s t a t e m e n t c a c h e .
: s e r v e r l i s t
- L i s t s l o c a l a n d S Q L S e r v e r s o n t h e n e t w o r k .
: s e t v a r { v a r i a b l e }
- R e m o v e s a s q l c m d s c r i p t i n g v a r i a b l e .
: s e t v a r < v a r i a b l e > < v a l u e >
- S e t s a s q l c m d s c r i p t i n g v a r i a b l e .
E n t e r n e w p a s s w o r d :
E n t e r n e w p a s s w o r d a g a i n : $ R e - e n t e r e d p a s s w o r d d o e s n o t m a t c h
T i m e o u t e x p i r e d
$ C a n c e l i n g c o n n e c t i o n . P l e a s e w a i t . . . < S q l c m d : O p t i o n ' % 1 ! s ! ' c a n n o t b e s p e c i f i e d m u l t i p l e t i m e s .
y S q l c m d : E r r o r : C o n n e c t i o n f a i l u r e . S Q L N a t i v e C l i e n t i s n o t i n s t a l l e d c o r r e c t l y . T o c o r r e c t t h i s , r u n S Q L S e r v e r S e t u p .
U S q l c m d : E r r o r : C o n n e c t i o n f a i l u r e . O n e o r m o r e c o n n e c t i o n p r o p e r t i e s a r e i n c o r r e c t .
? E r r o r I n t i a i l i z i n g C O M . C o I n i t i a l i z e f a i l e d w i t h H r e s u l t : 0 x % X 2 H R e s u l t 0 x % 1 ! X ! , L e v e l % 2 ! d ! , S t a t e % 3 ! d !
% 4 ! s !
= U s a g e : E X E C U T E x p _ s n m p _ g e t s t a t e [ < r e t u r n - s t a t u s I N T > O U T P U T ] U s a g e : E X E C U T E x p _ s n m p _ r a i s e t r a p
< s e r v e r V A R C H A R [ 3 0 ] > ,
< d a t a b a s e V A R C H A R [ 3 0 ] > ,
< e r r o r - m e s s a g e V A R C H A R [ 2 5 5 ] > ,
< m e s s a g e - i d I N T > ,
< s e v e r i t y I N T > ,
< u s e r V A R C H A R [ 3 0 ] > ,
< c o m m e n t V A R C H A R [ 2 5 5 ] > ,
< d a t e - a n d - t i m e D A T E T I M E > ,
< r e t u r n - s t a t u s I N T > O U T P U T T h e S Q L S N M P A g e n t i s r u n n i n g . $ T h e S N M P t r a p n u m b e r i s u n s u p p o r t e d . A m e m o r y e r r o r o c c u r r e d . PA! T h e n e t w o r k h a s n o t b e e n s t a r t e d . I T h e S Q L S N M P A g e n t t i m e d - o u t w a i t i n g f o r a r e s p o n s e f r o m t h e S N M P c l i e n t . " T h e S Q L S N M P A g e n t i s n o t r u n n i n g . S t a t u s M e a n i n g ! T h e t r a p w a s r a i s e d s u c c e s s f u l l y . U n k n o w n S Q L S N M P A g e n t s t a t e . I O n e o r m o r e o f t h e p a r a m e t e r s s u p p l i e d t o x p _ s n m p _ r a i s e t r a p i s t o o l a r g e . f U n a b l e t o l o a d S Q L S N M P . D L L - A c o p y o f t h i s D L L n u s t b e p r e s e n t i n a t l e a s t o n e d i r e c t o r y o n t h e P A T H . A U n a b l e t o f i n d t h e e n t r y p o i n t f o r S Q L s n m p T r a p s ( ) i n S Q L S N M P . D L L . F i l e i s a D i r e c t o r y % M e s s a g e : % l d , S t a t e : % d , S e v e r i t y : % d S e v e r i t y : % d E r r o r : % d , O S : % d PA U s a g e : E X E C U T E x p _ s q l a g e n t _ m o n i t o r @ c o m m a n d = ' S T A R T ' , @ n e t s e n d = ' < a d d r e s s > ' , @ r e s t a r t = < r e s t a r t a t t e m p t s ( 0 - 8 ) > o r E X E C U T E x p _ s q l a g e n t _ m o n i t o r @ c o m m a n d = ' S T O P ' * S Q L S e r v e r A g e n t M o n i t o r i s a l r e a d y r u n n i n g . C S Q L S e r v e r A g e n t M o n i t o r : S Q L S e r v e r A g e n t h a s t e r m i n a t e d u n e x p e c t e d l y . j S Q L S e r v e r A g e n t M o n i t o r s u c c e s s f u l l y r e s t a r t e d S Q L S e r v e r A g e n t a f t e r S Q L S e r v e r A g e n t t e r m i n a t e d u n e x p e c t e d l y . 9 S Q L S e r v e r A g e n t M o n i t o r : S Q L S e r v e r A g e n t h a s b e e n s h u t d o w n . _ Y o u d o n o t h a v e s u f f i c i e n t o p e r a t i n g s y s t e m p e r m i s s i o n t o o p e n t h e S Q L S e r v e r A g e n t s t a t u s e v e n t . U n k n o w n , S Q L S e r v e r A g e n t M o n i t o r f a i l e d t o i n i t i a l i z e . , S Q L S e r v e r A g e n t M o n i t o r s t a r t e d s u c c e s s f u l l y . S Q L S e r v e r A g e n t M o n i t o r s t o p p e d . r S Q L S e r v e r A g e n t M o n i t o r f a i l e d t o r e s t a r t S Q L S e r v e r A g e n t a f t e r S Q L S e r v e r A g e n t t e r m i n a t e d u n e x p e c t e d l y ( r e a s o n : % s ) . 0 S Q L S e r v e r A g e n t M o n i t o r i s n o t c u r r e n t l y r u n n i n g . p T h e m a x i m u m n u m b e r o f p e n d i n g S Q L S e r v e r A g e n t n o t i f i c a t i o n s h a s b e e n e x c e e d e d . T h e n o t i f i c a t i o n w i l l b e i g n o r e d . X U n a b l e t o a d d S Q L S e r v e r A g e n t n o t i f i c a t i o n d u e t o f a i l u r e t o o b t a i n s e r i a l i z a t i o n o b j e c t . U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y < o p e r a t i o n t y p e > , < j o b i d > , < s c h e d u l e i d > , < a l e r t i d > , < a c t i o n t y p e > [ , < l o g i n n a m e > ] [ , < e r r o r f l a g > ] \ U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' J ' , < j o b i d > , N U L L , N U L L , < a c t i o n t y p e > [ , < l o g i n n a m e > ] j U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' J ' , < j o b i d > , N U L L , N U L L , < a c t i o n t y p e > , < l o g i n n a m e > [ , < e r r o r f l a g > ] T U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' S ' , < j o b i d > , < s c h e d u l e i d > , N U L L , < a c t i o n t y p e > M U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' A ' , N U L L , N U L L , < a l e r t _ i d > , < a c t i o n t y p e > K U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' D ' , [ < j o b i d > | N U L L ] , N U L L , N U L L , N U L L : U n a b l e t o p o s t n o t i f i c a t i o n t o S Q L S e r v e r A g e n t ( r e a s o n : % s ) P S Q L S e r v e r A g e n t i s n o t c u r r e n t l y r u n n i n g s o i t c a n n o t b e n o t i f i e d o f t h i s a c t i o n . % T h e s p e c i f i e d A c t i o n T y p e i s i n v a l i d . ( T h e s p e c i f i e d O p e r a t i o n T y p e i s i n v a l i d . m S Q L S e r v e r A g e n t e x c e e d e d t h e a l l o w e d t i m e o u t f o r c r e a t i n g t h e s h a r e d j o b l i s t . T h e J o b s c a n n o t b e e n u m e r a t e d . PAG U n a b l e t o e n u m e r a t e J o b s d u e t o f a i l u r e t o o b t a i n s e r i a l i z a t i o n o b j e c t . S Q L S e r v e r A g e n t E r r o r : % s ( S Q L S e r v e r A g e n t i s n o t c u r r e n t l y r u n n i n g . 4 U n a b l e t o e n u m e r a t e S Q L S e r v e r A g e n t j o b s ( r e a s o n : % s ) D U s a g e : E X E C U T E x p _ f i l e e x i s t < f i l e n a m e > [ , < f i l e _ e x i s t s I N T > O U T P U T ] 6 I n t e r n a l E r r o r : C o u l d n o t a l l o c a t e s u f f i c i e n t m e m o r y . F i l e E x i s t s 9 U s a g e : E X E C U T E x p _ s q l a g e n t _ i s _ s t a r t i n g < F l a g I N T > O U T P U T 5 U s a g e : E X E C U T E x p _ t e r m i n a t e _ p r o c e s s < P r o c e s s I D | 0 > ( U n a b l e t o t e r m i n a t e p r o c e s s ( r e a s o n : % s ) % s ( ) r e t u r n e d e r r o r % l d , ' % s ' U s a g e : E X E C U T E x p _ m s x _ e n l i s t < 0 ( e n l i s t ) | 1 ( d e f e c t ) | 2 ( r e - e n l i s t ) > , < M S X s e r v e r n a m e V A R C H A R [ 6 4 ] > , < W i n d o w s N T u s e r n a m e V A R C H A R [ 1 0 0 ] > , [ , < l o c a t i o n V A R C H A R [ 2 0 0 ] > ] [ , < t i m e z o n e a d j u s t m e n t I N T > ] [ , < l o c a l t i m e V A R C H A R > ] [ , < p o l l i n t e r v a l I N T > ] * U n a b l e t o s e n d r e q u e s t t o M S X ( r e a s o n : % s ) 3 U n a b l e t o r e a d a c k n o w l e d g m e n t f r o m M S X ( r e a s o n : % s ) 4 U n a b l e t o e s t a b l i s h c o n n e c t i o n w i t h M S X ( r e a s o n : % s ) ( T h e % s o p e r a t i o n c o m p l e t e d s u c c e s s f u l l y . PA$ T h e % s o p e r a t i o n f a i l e d ( r e a s o n : % s ) e n l i s t d e f e c t e n l i s t m e n t u p d a t e 5 U n a b l e t o c r e a t e o r m o d i f y o p e r a t o r ' % s ' ( r e a s o n : % s ) T h e s p e c i f i e d M S X d o e s n o t e x i s t , o r i s a 6 . 5 ( o r e a r l i e r ) v e r s i o n o f S Q L S e r v e r , o r i s v e r s i o n 7 . 0 b u t i t ' s S Q L S e r v e r A g e n t h a s n o t b e e n s t a r t e d c S e r v e r ' % s ' m u s t b e r u n n i n g i n a n a c c o u n t o t h e r t h a n ' L o c a l S y s t e m ' i n o r d e r t o p e r f o r m t h i s a c t i o n . [ U s a g e : E X E C U T E x p _ s q l a g e n t _ e n u m _ j o b s < i s s y s a d m i n ( 0 o r 1 ) > , < j o b o w n e r n a m e > [ , < j o b i d > ] PA S e r v i c e A l r e a d y R u n n i n g . S e r v i c e S t o p p e d . S e r v i c e S t a r t e d . S e r v i c e P a u s e d . S e r v i c e C o n t i n u e d . S e r v i c e C o n f i g u r a t i o n C h a n g e d . < E r r o r e x e c u t i n g e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r A E r r o r e x e c u t i n g e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r T y p e d r i v e M B f r e e ; C o u l d n o t a l l o c a t e s u f f i c i e n t m e m o r y t o e x e c u t e x p _ s u b d i r s . PA H A r g u m e n t f o r x p _ a v a i l a b l e m e d i a i s n o t o f t h e c o r r e c t d a t a t y p e ( t i n y i n t ) . B C o u l d n o t a l l o c a t e s u f f i c i e n t m e m o r y t o e x e c u t e x p _ a v a i l a b l e m e d i a . * x p _ a v a i l a b l e m e d i a : c o u l d n ' t g e t t a p e i n f o . # * * * * * P r o c e s s A t t a c h m e n t * * * * * S e r v e r N e t N a m e p U s a g e : x p _ g e t n e t n a m e [ @ n e t n a m e [ n ] [ v a r ] c h a r ( M A X _ P A T H ) O U T ] , [ 1 ( f u l l y q u a l i f i e d d o m a i n n a m e ) | 2 ( d o m a i n n a m e ) ] % s e r r o r 0 x % x : % s E r r o r : l a r g e i n t . h i g h p a r t > 0 N E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r Q E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l e x t e n d e d s t o r e d p r o c e d u r e : U n r e c o g n i z e d S e r v i c e R E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l : I n v a l i d n u m b e r o f p a r a m e t e r s f o r s e r v i c e c h a n g e M E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l : I n v a l i d c o n f i g c h a n g e s t a r t m o d e p a r a m e t e r @ E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l : I n v a l i d c o n f i g c h a n g e c o m m a n d A E r r o r e x e c u t i n g R e a d e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r H E r r o r e x e c u t i n g x p _ r e g w r i t e e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r L E r r o r e x e c u t i n g x p _ r e g w r i t e e x t e n d e d s t o r e d p r o c e d u r e : R e g i s t r y T y p e I n v a l i d ] E r r o r e x e c u t i n g x p _ r e g w r i t e e x t e n d e d s t o r e d p r o c e d u r e : R e g i s t r y R E G _ ( M U L T I ) _ S Z v a l u e m i s m a t c h X E r r o r e x e c u t i n g x p _ r e g w r i t e e x t e n d e d s t o r e d p r o c e d u r e : R e g i s t r y R E G _ D W O R D v a l u e m i s m a t c h L E r r o r e x e c u t i n g x p _ r e g d e l e t e k e y e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P a r a m e t e r V E r r o r e x e c u t i n g R e a d e x t e n d e d s t o r e d p r o c e d u r e : I n v a l i d P r e d e f i n e d R e g i s t r y s y s t e m k e y C o n t i n u a t i o n R o w A r c h i v e # D a t e A l t e r n a t e N a m e S i z e
C r e a t i o n D a t e
C r e a t i o n T i m e L a s t W r i t t e n D a t e L a s t W r i t t e n T i m e L a s t A c c e s s e d D a t e L a s t A c c e s s e d T i m e
A t t r i b u t e s K e y E x i s t I n v a l i d a c k n o w l e d g m e n t f o r m a t 0 E r r o r i n x p _ r e g r e a d : c o u l d n ' t r e a l l o c % l d b y t e s . . E r r o r i n x p _ r e g r e a d : c o u l d n ' t a l l o c % l d b y t e s . X U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' N ' , N U L L , N U L L , N U L L , < n o t i f i c a t i o n t y p e > , < a d d r e s s > P a r e n t D i r e c t o r y E x i s t s U n a b l e t o l o a d s e m m a p . d l l . M e m o r y a l l o c a t i o n f a i l u r e . v F a i l e d t o e x e c u t e t h e q u e r y b e c a u s e t h e @ q u e r y _ r e s u l t _ n o _ a p p e n d a n d @ q u e r y _ n o _ t r u n c a t e o p t i o n s a r e m u t u a l l y e x c l u s i v e . f S y n t a x E r r o r : S y n t a x o f t h i s c o m m a n d i s : x p _ s q l m a i n t ' < l i s t o f p a r a m e t e r s t o s e n d t o t o s q l m a i n t . e x e > ' F a i l e d e x e c u t i n g A P I : % s . s q l m a i n t . e x e f a i l e d . J T h i s x p _ s e r v i c e c o n t r o l p a r a m e t e r i s s u p p o r t e d o n l y o n W i n d o w s S Q L S e r v e r s . ; n e w a c c o u n t w a s s e t b u t t h e o l d o n e w a s ( p a r t i a l l y ) r e m o v e d * n o t e n o u g h r i g h t s t o p e r f o r m t h e o p e r a t i o n O t h e r e r r o r $ T H E M S S e a r c h s e r v i c e i s n o t r u n n i n g . V D T S F l a t F i l e ; M S R e m o t e ; M S D a t a S h a p e ; M S P e r s i s t ; M i c r o s o f t . J e t . O L E D B . 3 . 5 1 ; S a m p P r o v ; C u s t T r a n d O r a c l e S Q L * N e t i s n o t i n s t a l l e d o r t h e r e i s n o t e n o u g h s t o r a g e a v a i l a b l e t o c o m p l e t e t h i s o p e r a t i o n . g S y n t a x E r r o r : S y n t a x o f t h i s c o m m a n d i s : x p _ a d s i r e q u e s t ' < l i s t o f p a r a m e t e r s t o s e n d t o t o x p a d s i . e x e > ' B y t e s I n v a l i d p a r a m e t e r s . I n v a l i d p a r a m e t e r t y p e . I n v a l i d p a r a m e t e r v a l u e . > I n v a l i d e r r o r n u m b e r . E r r o r n u m b e r m u s t b e g r e a t e r t h a n 5 0 0 0 0 . Z I n v a l i d s e v e r i t y v a l u e . S e v e r i t y m u s t b e ' I n f o r m a t i o n a l ' ( d e f a u l t ) , ' W a r n i n g ' , o r ' E r r o r ' . % s : E r r o r % d f r o m % s o n l i n e % d
x p a d s i . e x e f a i l e d . I E r r o r e x e c u t i n g e x t e n d e d s t o r e d p r o c e d u r e : E x p e c t i n g a n o u t p u t p a r a m e t e r . PA D a t a S o u r c e N a m e V a l u e D B M S N a m e D B M S V e r s i o n
D a t a b a s e N a m e S Q L S u b s c r i b e r F A L S E T R U E D e s c r i p t i o n I n f o r m a t i o n T y p e PA L o g F i l e S i z e ( B y t e ) Q E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l : I n v a l i d n u m b e r o f p a r a m e t e r s f o r s e r v i c e q u e r y 8 E r r o r e x e c u t i n g x p _ s e r v i c e c o n t r o l : I n v a l i d q u e r y c o m m a n d E r r o r l o g l o c a t i o n n o t f o u n d . T h e t i m e - o u t w a s e x c e e d e d w h i l e t h e s e r v e r w a i t e d f o r a r e s p o n s e f r o m S Q L S e r v e r A g e n t . M a k e s u r e t h a t t h e S Q L S e r v e r A g e n t s e r v i c e i s r u n n i n g " P r o v i d e o n l y o n e s t r i n g p a r a m e t e r . ( E n c r y p t i o n e r r o r u s i n g C r y p t P r o t e c t D a t a . S e n d s e c r e t e r r o r . I m p e r s o n a t i o n e r r o r . X E r r o r e x e c u t i n g x p _ g e t _ s c r i p t e x t e n d e d s t o r e d p r o c e d u r e : E x p e c t i n g s c r i p t n a m e p a r a m e t e r c E r r o r e x e c u t i n g x p _ g e t _ s c r i p t e x t e n d e d s t o r e d p r o c e d u r e : S p e c i f i e d r e s o u r c e n a m e c o u l d n o t b e f o u n d i E r r o r e x e c u t i n g x p _ d e l e t e _ f i l e e x t e n d e d s t o r e d p r o c e d u r e : S p e c i f i e d f i l e i s n o t a S Q L S e r v e r b a c k u p f i l e . o E r r o r e x e c u t i n g x p _ d e l e t e _ f i l e e x t e n d e d s t o r e d p r o c e d u r e : S p e c i f i e d f i l e i s n o t a M a i n t e n a n c e P l a n r e p o r t f i l e . N F a i l e d t o o p e n l o o p b a c k c o n n e c t i o n . P l e a s e s e e e v e n t l o g f o r m o r e i n f o r m a t i o n . J F i l e a t t a c h m e n t o r q u e r y r e s u l t s s i z e e x c e e d s a l l o w a b l e v a l u e o f % d b y t e s . A t t a c h m e n t f i l e % s i s i n v a l i d . PAF A t t a c h m e n t t r a n s f e r f a i l e d . P l e a s e s e e e v e n t l o g f o r m o r e i n f o r m a t i o n . B Q u e r y e x e c u t i o n f a i l e d . P l e a s e s e e e v e n t l o g f o r m o r e i n f o r m a t i o n . < U n a b l e t o e n u m e r a t e J o b s : m e m o r y m a p p e d f i l e a l r e a d y e x i s t s . K O n l y m e m b e r s o f ' s y s a d m i n ' f i x e d s e r v e r r o l e c a n e x e c u t e m a i n t e n a n c e p l a n s . S E r r o r e x e c u t i n g ' % s ' : P e r m i s s i o n d e n i e d . U s e r m u s t b e a m e m b e r o f ' % s ' s e r v e r r o l e . t T h e c l i e n t c o n n e c t i o n s e c u r i t y c o n t e x t c o u l d n o t b e i m p e r s o n a t e d . A t t a c h i n g f i l e s r e q u i r e a n i n t e g r a t e d c l i e n t l o g i n Z C o u l d n o t c r e a t e D a t a b a s e M a i l . e x e p r o c e s s . E x e c u t i n g A P I ' % s ' f a i l e d w i t h e r r o r n u m b e r % d . T F a i l e d t o o p e n a t t a c h m e n t f i l e ' % s ' . E x e c u t i n g A P I ' % s ' f a i l e d w i t h e r r o r n u m b e r % d . 3 F a i l e d t o o p e n a t t a c h m e n t f i l e ' % s ' . A c c e s s d e n i e d . 5 F a i l e d t o o p e n a t t a c h m e n t f i l e ' % s ' . W r o n g f i l e t y p e . T F a i l e d t o r e a d a t t a c h m e n t f i l e ' % s ' . E x e c u t i n g A P I ' % s ' f a i l e d w i t h e r r o r n u m b e r % d . % s c o u l d n o t a c c e s s ' % s ' : % s A T h e i n s t a n c e p a r a m e t e r i s n o t a v a l i d r e l a t i o n a l e n g i n e i n s t a n c e . ) T h e l o g f i l e i s n o t u s i n g U n i c o d e f o r m a t . W T h e v a l u e p r o v i d e d f o r t h e f i l t e r b y t e x t p a r a m e t e r s m u s t b e l e s s t h a n 4 0 0 0 c h a r a c t e r s . PA T h e f o r m a t f o r t h e d a t e f i l t e r i s i n c o r r e c t . P l e a s e u s e a v a l i d S Q L S e r v e r d a t e o r d a t e t i m e f o r m a t . F o r e x a m p l e , v a l i d f o r m a t s a r e Y Y Y Y - M M - D D ' a n d M M / D D / Y Y Y Y . , F a i l e d t o q u e r y t h e r e g i s t r y o n c o m p u t e r % s . @ F a i l e d t o q u e r y s y s . m a t r i x _ b r i c k s b e c a u s e o f a c o n n e c t i o n e r r o r . > U s a g e : E X E C U T E x p _ s q l a g e n t _ n o t i f y ' M ' , N U L L , N U L L , N U L L , N U L L PA4 V S _ V E R S I O N _ I N F O n ? S t r i n g F i l e I n f o 0 4 0 9 0 4 B 0 & P l a t f o r m N T x 6 4 I L e g a l T r a d e m a r k s M i c r o s o f t S Q L S e r v e r i s a r e g i s t e r e d t r a d e m a r k o f M i c r o s o f t C o r p o r a t i o n . C o m m e n t s S Q L & G o l d e n B i t s T r u e L C o m p a n y N a m e M i c r o s o f t C o r p o r a t i o n P F i l e D e s c r i p t i o n X P S T A R R e s o u r c e D L L 2 F i l e V e r s i o n 2 0 1 1 . 0 1 1 0 . 3 0 0 0 . 0 0 ( ( S Q L 1 1 _ P C U _ M a i n ) . 1 2 1 0 1 9 - 1 3 2 5 ) 4
I n t e r n a l N a m e X P S T A R E N G n % L e g a l C o p y r i g h t M i c r o s o f t C o r p . A l l r i g h t s r e s e r v e d . D O r i g i n a l F i l e n a m e X P S T A R E N G . D L L J P r o d u c t N a m e M i c r o s o f t S Q L S e r v e r < P r o d u c t V e r s i o n 1 1 . 0 . 3 0 0 0 . 0 D V a r F i l e I n f o $ T r a n s l a t i o n <assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
<security>
<requestedPrivileges>
<requestedExecutionLevel level="asInvoker" uiAccess="false"></requestedExecutionLevel>
</requestedPrivileges>
</security>
</trustInfo>
</assembly>PAPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGXXPADDINGPADDINGX > 0= *H
=0=1 0 + 0L
+7>0<0
+70 0!0 + K`Ow>"奠/003 '8 0
*H
0y1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0
120904214209Z
130304214209Z01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1 0 UMicrosoft Corporation0"0
*H
0
Il"e;abt)qX^W
ibn6N"y a%X,12 >tW6SAiimVn&Msdq a !hX[I50Szk+!gtxa|*gٺc<N>}~H= uc )9AT8 Td@7
"0# kYୟ`L
bZxvlL8G
0 0U % 0
+0 U Q J? óe,0 U #0ҴX7.3L
0VU O0M0KIGEhttp://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
*&zbx[ħxS( B?8d5HzOGڇLږdρ3x|mq aNds?{'4R?GCpSK*w{nF(1Ji]3<GE ʋ O &n_ډZ^_|FGX+F Qb_i\qV ,:Nh~;yB9I^pBułUUa003 +92HH +0
*H
0w1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1!0 UMicrosoft Time-Stamp PCA0
120904211234Z
131204211234Z01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1'0%U nCipher DSE ESN:C0F4-3086-DEF81%0#U Microsoft Time-Stamp Service0"0
*H
0
0ePZkH ^Ip|_b_&Ӌ9".K:^92{x78:bhh@,6b`p9`b8o@
x6k&M)\HpF6&454\}1M W|֕
f 2>,Հ_89]d7\;Q[]_ 4_(UpT| UF[((VL H*03 lióN0 7ԘRԩ#E\AIObh 00 U 3S?`CƯ+X0 U #0#4RFp
@v+50TU M0K0IGEChttp://crl.microsoft.com/pki/crl/products/MicrosoftTimeStampPCA.crl0X+L0J0H+0<http://www.microsoft.com/pki/certs/MicrosoftTimeStampPCA.crt0U % 0
+0
*H
~̰{/Vs۪R9ƴ eHFfj&^Xc8F|+sAǪ7d9Ϋ%iFf2<ח2!%^ B,3: Α%qr bpJP/"dMk, y짣1ڐ`n
?,$
j&QJucDi V:r
""rĜ (9f0FY z
cM#O-;G00
a3& 10
*H
0_10
&,dcom10
&,d microsoft1-0+U$Microsoft Root Certificate Authority0
100831221932Z
200831222932Z0y1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA0"0
*H
0
rY\0d ` BvSl>ok:t/2BN#r 5W
ʮ IhMKyKqG%ߝuϪsDׁ6\Eܯ=(hrsV5rv_ wE8s[gRi/ t)v/T0Yҍb_F1P'@0r)("7N=2=͡8\NV(*2KOc-bE= F[&SzJF7<8w\V< _ ^0Z0U 00 U ҴX7.3L
0 U 0 +7 0# +71N&`;1o<-0 +7
S u b C A0 U #0`@V'%*
SY0PU I0G0ECA?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+08http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0
*H
Y9>&Fo@2j/nI_uL;-C -1#|P!]=/LVpyIr>A i˥ - PV-JҼp
VชKWB 4<VS#* NNҏr*qm Sg"\ ͢'ycVDQXVݛED/_ڮԂ2ni穢,+e& e8z JVx\5_ݓ I-4D ]5[!Θ OmN=|)HrlpWW}HZϣg
t6-/r7o<gR3e doFy,
'@F`Ge`@NBNvy
4 m 1 TGdP.|!zA'iÓɍ؛!
\𢮆h j-vk {$jG1/q= ~bGΈɸr=@ji7P=00
ah4 0
*H
0_10
&,dcom10
&,d microsoft1-0+U$Microsoft Root Certificate Authority0
070403125309Z
210403130309Z0w1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1!0 UMicrosoft Time-Stamp PCA0"0
*H
0
lH*|k.řQ#PN$ѧLt0 TIB\0& k{ 3$g ,{ N#SʍE ^аBlg(FS(F.'OUR ,j0nY0~֗!~SmREH JGf')fJG Ux P 0F#F-T xA1w̛;a
"KT 00U 00 U #4RFp
@v+50 U 0 +7 0U #0`@V'%*
SY䤡ca0_10
&,dcom10
&,d microsoft1-0+U$Microsoft Root Certificate AuthorityyJLsX.e0PU I0G0ECA?http://crl.microsoft.com/pki/crl/products/microsoftrootcert.crl0T+H0F0D+08http://www.microsoft.com/pki/certs/MicrosoftRootCert.crt0U % 0
+0
*H
\D6鴭wyQM.tq[m ξ{.mbWN Pz@WX x;⾏
ȍHSOjՀ/*$"px.
pNk
r9g-Qb\? ?21Jv1=+r p=
aN5+^} B{Sr!0ʻxe\[
<fX-/QVpl9Lyzb))4wb= m ,C}㤨t5Nq#&v <RqZFCMgȟBy!0fB^Ȟ"!C+H,2L&*H$_ZVޙ:o'tv!'Tj=vanGo2lQȌ#f d=-Y!嵰cxTA.Pxuْu"/2n[%! OɚIeo }in꧗1)[\M/?.Uk1(H0(D00y1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1#0!UMicrosoft Code Signing PCA3 '8 0 + 0 *H
1
+70
+7 10
+70# *H
1s5;O;o_li$Cf0R
+7 1D0B S Q L S e r v e r 2 0 1 2 http://www.microsoft.com/sql0
*H
Ui%^U~gRiĖfQx%F_ܐ4sgSw/ZVWsz 0{Q"
j{:9iG7 i `f{U 1 7*쮩#)Z_Et04"4i08|,w0dxmH۔1Gjl v6v:1л~,[:]ꃕ2\ab~_N{zI6e97F< mn
#FUQDU gN[%0$ *H
1000w1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1!0 UMicrosoft Time-Stamp PCA3 +92HH +0 + ]0 *H
1 *H
0 *H
1
121020072706Z0# *H
14S _OHc-j0
*H
>cm ݂S@N|e(n_}K,.hӌttrxu*Ce1Gn\]:S y'bq!!\^lfoI%-9<vABl)!
bA+ uA_4/D'0ŊnUH9NUƂn ĜmC.5uNQQ9i^OX Ja,A2 &
ATw\8]PDQZk4h"?K+v0#
+71#0# *H
#0#10
`He 0\
+7N0L0
+70 010
`He XꬌAȜnnϢNJR8
?003 y
%XE 0
*H
0~1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1(0&U Microsoft Code Signing PCA 20110
120707001432Z
131007001432Z01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1 0 UMicrosoft Corporation0"0
*H
0
Qs/ΰ=X?,˹]::HlT~ܸ*
m;Y%N`t۴UO?;A|GKaI aD<o`N 3QPٲ>Fvͫr9q'ٟCT!cnHz ܝP䶃"G *D>8.0+Asp)&9L/*Z6>)P@2'c :Ӝr5Ex(kdtE@o\- ,0(0 U %0+
+7L0 U a??P.Cqc\BF0 U #0HndPӂ77"mu0TU M0K0IGEChttp://www.microsoft.com/pkiops/crl/MicCodSigPCA2011_2011-07-08.crl0a+U0S0Q+0Ehttp://www.microsoft.com/pkiops/certs/MicCodSigPCA2011_2011-07-08.crt0 U 0 0
*H
C k]dRoP(tqO'CMf`a;W~/=E3]v`yN^|#qzA"L̊WC֮ 3=f;vdbWC;Gu jHN2
Lf÷>LH92
x[+07wo \CR,Gasrc3aI_,5O}k>YڞְHa뀱F5i?r~#Cܶ zե:ւMxK<J
DK݀ /d=ͱJ&4p|
LL+E^s{B.>[
4RNq㑹sy
64,I`0 & d <BQp9JDr^B,,}ߍ^Dεblz0(@X ^W/rWY4Jj `RA'MPW+`0z0b
a 0
*H
01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20110
110708205909Z
260708210909Z0~1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1(0&U Microsoft Code Signing PCA 20110"0
*H
0
r .nM4X!B*kZP8I 7k 891BӉ
yd~`$lIh^ߛS
,ï٩+z זY`fv2R&/PϳD].uhm:\F8dnd5xc-@
\yI)
w=+Z MV(zr:I^C{{>]O/Y;-3Xŷ>2ӳ=8~ҝ,NV!5%9ndS#_R!t%_('(8%9J6| #fa j(I_%]!K>RĵW?$z[/#p]QFw Ἤ_UK9#ID|Erzr$߿F WۃMI0[ݰf{KI)(}gbx_/W\w((m(,@7OD LԥC/tnx X,]`>O3ڰޞNFlֈ 00 +7 0 U HndPӂ77"mu0 +7
S u b C A0 U 0U 00 U #0r-:1CN1#40ZU S0Q0OMKIhttp://crl.microsoft.com/pki/crl/products/MicRooCerAut2011_2011_03_22.crl0^+R0P0N+0Bhttp://www.microsoft.com/pki/certs/MicRooCerAut2011_2011_03_22.crt0U 00 +7.00?+3http://www.microsoft.com/pkiops/docs/primarycps.htm0@+04 2 L e g a l _ p o l i c y _ s t a t e m e n t . 0
*H
gTy.tg" cB}y e_.,>rm?
;Gic"5]e}FPU |K <BRаk>.)=Ե w9dD=xz#}t&F* hh.*?*kXIc inZF+;н5n%'(M= 2-[I9
ƭQEX82q'',: iYhnot@*(>?Mf M o}Ev@*e]Rb c6'ͥ]cA[>wu aaA(e/\ ֡FY KH )ű?Ew 5Ңzz"H G#kKux7yKֳ#hu i;0Lumc=ѝVNOW"x2!zA
?̤]WqVNHEBɛv[
HkymmjRs
zP1000~1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1(0&U Microsoft Code Signing PCA 20113 y
%XE 0
`He 0
*H
10 *H
1
+70
+7 10
+70/ *H
1" mX
7Ԟ* | ~Hx0R
+7 1D0B S Q L S e r v e r 2 0 1 2 http://www.microsoft.com/sql0
*H
l֘%
]f?c(9
,TKދ Yv㣈h_iĈ\֓H_MʛKؒwL1Q*j牦u;uqřB.rߣӢqhkP<o.xgh)<@+D>WQDQuF@5|-nHb@WbWpRV˛X *-$?iqϿW0?!FΒCwy5h$F'$ax)SDȝF20.
+71 0 *H
010
`He 0= *H
,(0$
+Y
010
`He PP Ѽ<3OZԹ (3Pd`%20121020072710.281Z001 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1'0%U nCipher DSE ESN:B8EC-30A4-71441%0#U Microsoft Time-Stamp Service0q0Y
a * 0
*H
01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1200U)Microsoft Root Certificate Authority 20100
100701213655Z
250701214655Z0|1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1&0$U Microsoft Time-Stamp PCA 20100"0
*H
0
w: iktTե
|hK,_av>f+[S'1 A |a0Y0D`TC M8Bݓs0W&E
GͿ$`2X`FXG2 tag_TϓLĦ]an(aF' $gr!KdPb]w =WuBM@Q>gfD~Knʱz*
1N5x </Dd xD]^O"M 00 +7 0 U c:\1C{|F3hZmU0 +7
S u b C A0 U 0U 00 U #0Vˏ\bh=[Κ0VU O0M0KIGEhttp://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@+04 2 L e g a l _ P o l i c y _ S t a t e m e n t . 0
*H
Q
?q=!o1Wm0fjxǨ%kTW+QDS`v@@A\\^5$VKtBȯ7}Z? yJR 8/ye٩kz oK0D$"<Y)p2J'U/3b_W@ΙfjbJ &9Jqc{!mÜ<}jxm 8ؔƥ
B"8 %d~cY%z.9Wvqa˚Gͧ};q ]t"aڰP o 1:eGxHr~akow˧ 9r *T9[
Uzs;-3.)/T'!ȬN(ۖBAM*f0ӻt2Kc{/!YD<PqסUiW0M
]O 8/X.
P5 ' ճ~5 6_tI00
aU 0
*H
0|1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1&0$U Microsoft Time-Stamp PCA 20100
120109213531Z
130409214531Z01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1'0%U nCipher DSE ESN:B8EC-30A4-71441%0#U Microsoft Time-Stamp Service0"0
*H
0
袉i%9'̠XrK@gGV"{ ?&佈Je;XrMiU sL^&Qgm`Kf`?ܒn[~p)BBU ٽt{V e$LʀǷ}M?Sylu^]V8 T;) OsutYj% (ubiA~;æ( 4Soo| 00 U u5SشK^Sc0 U #0c:\1C{|F3hZmU0VU O0M0KIGEhttp://crl.microsoft.com/pki/crl/products/MicTimStaPCA_2010-07-01.crl0Z+N0L0J+0>http://www.microsoft.com/pki/certs/MicTimStaPCA_2010-07-01.crt0 U 0 0U % 0
+0
*H
#A^ЪM?uĴ+h;>M\ʔ6>xEǍ 7
bb})5 îK^K_tf-p8qј6[-إPB5x^[ h|7o?og%ٸX&:}K FmZ`q`36}h"0,+?YOl L09(GPT߹sߐi{ P^a<\QӪ "9 ߎS ܡy0a0㡁01 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1'0%U nCipher DSE ESN:B8EC-30A4-71441%0#U Microsoft Time-Stamp Service%
0 + nȕ3+oq&eS'001 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1
0 U MOPR1'0%U nCipher NTS ESN:B027-C6F8-1D881+0)U"Microsoft Time Source Master Clock0
*H
,m0"20121020002333Z20121021002333Z0w0=
+Y
1/0-0
,m 0
S0 0
- 06
+Y
1(0&0
+Y
0 `
0 0
*H
3UR;&?n*sxžԀ_u^YVFHKxC6ԜkL\BE9`(NKg- * HVtWuoÕ m5Pf6lNIsYfYHVyޠa>3^ R& VET<<BT1*"$!_G2}f~ljȏ LNT +N}_NieV$B,fiAO 55πbmu 1000|1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1&0$U Microsoft Time-Stamp PCA 2010
aU 0
`He )0 *H
1
*H
0/ *H
1" MdwƴX FqA 0 *H
1000nȕ3+oq&eS'00~0|1 0 UUS10U
Washington10URedmond1 0 U
Microsoft Corporation1&0$U Microsoft Time-Stamp PCA 2010
aU 0u?c! fbL<zY0
*H
-``+mw&r!fDH>BȆq
]$ ʐ OJdw*V!aH"=Hʀlx
\x <tQ{Rlm.B_swr7$z|QX
ű4u)Ho"gY~#4>rC
a||V Z h P6h#h'u<tyN|fX.w
m"