The database mail configuration saved into a temp table
This is actually super easy, and you don't even need to go through the trouble of doing INSERT... EXEC
with the stored proc.
USE msdb
EXEC sp_helptext 'sysmail_help_account_sp'
This will return the script info:
CREATE PROCEDURE dbo.sysmail_help_account_sp
@account_id int = NULL,
@account_name sysname = NULL
AS
SET NOCOUNT ON
DECLARE @rc int
DECLARE @accountid int
exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT
IF @rc <> 0
RETURN(1)
IF (@accountid IS NOT NULL)
SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
WHERE a.account_id = s.account_id AND a.account_id = @accountid
ELSE
SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
WHERE a.account_id = s.account_id
RETURN(0)
You can see that it just queries these system views in msdb:
SELECT *
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
WHERE a.account_id = s.account_id
Which should be easy to integrate into your process.
Hope this helps!
@Erik Darling has a great method of extracting the account name from the msdb table directly. Use that method.
However, to help you understand how you could use the INSERT INTO ... EXEC
construct, I've written this little snippet of code:
DECLARE @Command nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @ProfileName sysname;
DECLARE @ColumnList nvarchar(max);
DECLARE @tsql nvarchar(max);
SET @params = '@ProfileName sysname OUTPUT';
SET @tsql = N'EXEC msdb.dbo.sysmail_help_account_sp;'
SET @Command = N'CREATE TABLE #sysmailaccount
(
<COLUMNLIST>
);';
SET @ColumnList = '';
SET @ColumnList = STUFF((SELECT ', ' + QUOTENAME(rs.name) + ' ' + rs.system_type_name
FROM sys.dm_exec_describe_first_result_set(@tsql, NULL, NULL) rs
ORDER BY rs.column_ordinal
FOR XML PATH('')), 1, 2, '');
SET @Command = REPLACE(@Command, N'<COLUMNLIST>', @ColumnList);
SET @Command = @Command + N'
INSERT INTO #sysmailaccount
' + @tsql + N';
SELECT @ProfileName = sma.name
FROM #sysmailaccount sma;
';
EXEC sys.sp_executesql @Command, @params, @ProfileName = @ProfileName OUT;
SELECT ProfileName = @ProfileName;
At the end of above code, you have @ProfileName
containing the name of the first database mail profile name.
The code uses the sys.dm_exec_describe_first_result_set
system function to construct a temporary table with the same definition as the output of msdb.dbo.sysmail_help_account_sp
. It then uses sys.sp_executesql
to execute an INSERT INTO ... EXEC
command to actually run the sysmail_help_account_sp
stored proc, placing the output into the #sysmailaccount
temp table.