我在asp.net里面有一个insert语句,执行这个insert语句的时候有一个trigger要运行一个msdb下面的一个stored procedure,然后自动发email.但是每次都出错: Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid。
我运行下面语句发现profile是存在的:
EXECUTE msdb.dbo.sysmail_help_principalprofile_sp
@principal_name = 'danw',
@profile_name = 'AdventureWorks Administrator' ;
------解决方案--------------------
CREATE PROCEDURE dbo.sysmail_help_principalprofile_sp
@principal_id int = NULL, -- must provide id or name
@principal_name sysname = NULL,
@profile_id int = NULL, -- must provide id or name
@profile_name sysname = NULL
AS
SET NOCOUNT ON
DECLARE @rc int
DECLARE @principal_sid varbinary(85)
DECLARE @profileid int
exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
IF @rc <> 0
RETURN(1)
IF (@principal_id IS NOT NULL AND @principal_id = 0) OR (@principal_name IS NOT NULL AND @principal_name = N'public')
BEGIN
IF (@principal_id IS NOT NULL AND @principal_id <> 0) OR (@principal_name IS NOT NULL AND @principal_name <> N'public')
BEGIN
RAISERROR(14605, -1, -1, 'principal') -- id and name do not match
END
SET @principal_sid = 0x00 -- public
IF (@profileid IS NOT NULL)
BEGIN
SELECT principal_id=0,
principal_name = N'public',
prof.profile_id,
profile_name=prof.name,
prin.is_default
FROM msdb.dbo.sysmail_principalprofile prin, msdb.dbo.sysmail_profile prof
WHERE prin.profile_id=prof.profile_id AND
prin.principal_sid = @principal_sid AND
prof.profile_id=@profileid
END
ELSE
BEGIN
SELECT principal_id=0,
principal_name = N'public',
prof.profile_id,
profile_name=prof.name,
prin.is_default
FROM msdb.dbo.sysmail_principalprofile prin, msdb.dbo.sysmail_profile prof
WHERE prin.profile_id=prof.profile_id AND
prin.principal_sid = @principal_sid
END
END
ELSE -- non-public profiles