CREATE
PROCEDURE
[
dbo
]
.
[
CreateMailProfile
]
@profileName sysname,
@profileDescription nvarchar ( 256 ),
@accountName sysname,
@accountDescription nvarchar ( 256 ),
@mailAddress nvarchar ( 128 ),
@mailDisplayName nvarchar ( 128 ),
@mailServer nvarchar ( 128 ),
@mailUsername nvarchar ( 128 ),
@mailPassword nvarchar ( 128 )
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRANSACTION ;
DECLARE @resultStatus datetime ;
SELECT @resultStatus = last_mod_datetime FROM msdb.dbo.sysmail_profile
WHERE [ name ] = @profileName ;
IF ( @resultStatus IS NOT NULL )
BEGIN
-- 先删除配置文件的用户关联(公用)
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_id = 0
, @profile_name = @profileName ;
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @profileName ;
END ;
DECLARE @profileId int ;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profileName ,
@description = @profileDescription ,
@profile_id = @profileId OUTPUT;
SELECT @profileId ;
-- 将配置文件变为公用配置
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_id = 0
, @profile_id = @profileId
, @is_default = 1 ;
SELECT @resultStatus = last_mod_datetime FROM msdb.dbo.sysmail_account
WHERE [ name ] = @accountName ;
IF ( @resultStatus IS NOT NULL )
BEGIN
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @accountName ;
END ;
DECLARE @accountId int ;
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @accountName ,
@description = @accountDescription ,
@email_address = @mailAddress ,
@display_name = @mailDisplayName ,
@mailserver_name = @mailServer ,
@account_id = @accountId OUTPUT;
SELECT @accountId ;
DECLARE @maxAccountId int ;
SELECT @maxAccountId = ISNULL ( MAX (account_id), 0 ) + 1
FROM msdb.dbo.sysmail_profileaccount
WHERE profile_id = @profileId ;
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @profileId ,
@account_id = @accountId ,
@sequence_number = @maxAccountId ;
IF ( @@ERROR <> 0 )
BEGIN
ROLLBACK TRANSACTION ;
END
ELSE
BEGIN
COMMIT TRANSACTION ;
END ;
END
@profileName sysname,
@profileDescription nvarchar ( 256 ),
@accountName sysname,
@accountDescription nvarchar ( 256 ),
@mailAddress nvarchar ( 128 ),
@mailDisplayName nvarchar ( 128 ),
@mailServer nvarchar ( 128 ),
@mailUsername nvarchar ( 128 ),
@mailPassword nvarchar ( 128 )
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRANSACTION ;
DECLARE @resultStatus datetime ;
SELECT @resultStatus = last_mod_datetime FROM msdb.dbo.sysmail_profile
WHERE [ name ] = @profileName ;
IF ( @resultStatus IS NOT NULL )
BEGIN
-- 先删除配置文件的用户关联(公用)
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_id = 0
, @profile_name = @profileName ;
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @profileName ;
END ;
DECLARE @profileId int ;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profileName ,
@description = @profileDescription ,
@profile_id = @profileId OUTPUT;
SELECT @profileId ;
-- 将配置文件变为公用配置
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_id = 0
, @profile_id = @profileId
, @is_default = 1 ;
SELECT @resultStatus = last_mod_datetime FROM msdb.dbo.sysmail_account
WHERE [ name ] = @accountName ;
IF ( @resultStatus IS NOT NULL )
BEGIN
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @accountName ;
END ;
DECLARE @accountId int ;
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @accountName ,
@description = @accountDescription ,
@email_address = @mailAddress ,
@display_name = @mailDisplayName ,
@mailserver_name = @mailServer ,
@account_id = @accountId OUTPUT;
SELECT @accountId ;
DECLARE @maxAccountId int ;
SELECT @maxAccountId = ISNULL ( MAX (account_id), 0 ) + 1
FROM msdb.dbo.sysmail_profileaccount
WHERE profile_id = @profileId ;
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @profileId ,
@account_id = @accountId ,
@sequence_number = @maxAccountId ;
IF ( @@ERROR <> 0 )
BEGIN
ROLLBACK TRANSACTION ;
END
ELSE
BEGIN
COMMIT TRANSACTION ;
END ;
END