vDear Team,
it is regarding store procedure query for sending an email. i am unable to find the smtp codes like we used to do earlier.
could you someone assist me how to smtp confuguration in it
ac
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_send_dbmail] Script Date: 19/08/2020 12:33:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- sp_send_dbmail : Sends a mail from Yukon outbox.
--
ALTER PROCEDURE [dbo].[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,
@query_no_truncate BIT = 0,
@query_result_no_padding BIT = 0,
@mailitem_id INT = NULL OUTPUT,
@from_address VARCHAR(max) = NULL,
@reply_to VARCHAR(max) = NULL
WITH EXECUTE AS 'dbo'
AS
BEGIN
SET NOCOUNT ON
-- 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,
@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,
@is_sysadmin BIT,
@send_request_user sysname,
@database_user_id INT,
@sid varbinary(85)
-- Initialize
SELECT @rc = 0,
@QueryResultsExist = 0,
@AttachmentsExist = 0,
@temp_table_uid = NEWID(),
@procName = OBJECT_NAME(@@PROCID),
@tranStartedBool = 0,
@trancountSave = @@TRANCOUNT,
@sid = NULL
EXECUTE AS CALLER
SELECT @is_sysadmin = IS_SRVROLEMEMBER('sysadmin'),
@send_request_user = SUSER_SNAME(),
@database_user_id = USER_ID()
REVERT
--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
(dbo.get_principal_id(pp.principal_sid) = @database_user_id OR pp.principal_sid = 0x00)
ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
--Was a profile found
IF(@profile_id IS NULL)
BEGIN
-- Try a profile lookup based on Windows Group membership, if any
EXEC @rc = msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
IF (@rc = 0)
BEGIN
SELECT TOP (1) @profile_id = pp.profile_id
FROM msdb.dbo.sysmail_principalprofile as pp
WHERE (pp.is_default = 1) AND
(pp.principal_sid = @sid)
ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
END
IF(@profile_id IS NULL)
BEGIN
RAISERROR(14636, 16, 1)
RETURN 1
END
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 ( @is_sysadmin <> 1)
BEGIN
--Not a sysadmin so check users access to profile
iFNOTEXISTS(SELECT*
FROM msdb.dbo.sysmail_principalprofile
WHERE ((profile_id = @profile_id) AND
(dbo.get_principal_id(principal_sid) = @database_user_id OR principal_sid = 0x00)))
BEGIN
EXEC msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
IF(@sid IS NULL)
BEGIN
RAISERROR(14607, -1, -1, 'profile')
RETURN 1
END
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 NOTIN('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 ISNULLAND @copy_recipients ISNULLAND
@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
EXECUTE AS CALLER
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,
@query_no_truncate = @query_no_truncate,
@query_result_no_padding = @query_result_no_padding
-- 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;
REVERT
-- 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 ISNOTNULL)AND(LEN(@file_attachments)> 0))
BEGIN
EXECUTE AS CALLER
EXEC @rc =sp_GetAttachmentData
@attachments = @file_attachments,
@temp_table_uid = @temp_table_uid,
@exclude_query_output = @exclude_query_output
REVERT
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
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,
send_request_user,
from_address,
reply_to
)
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,
@send_request_user,
@from_address,
@reply_to
)
SELECT @rc = @@ERROR,
@mailitem_id = SCOPE_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, a \r followed by \n, which is 0xd and then 0xa
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
--
-- 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) AND (@exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @RetErrorMsg)
END
RETURN (@rc)
END
USE [BI_Ops]
GO
/****** Object: StoredProcedure [dbo].[p_DBA_TransactionLogDumpCheck] Script Date: 19/08/2020 12:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_DBA_TransactionLogDumpCheck]
AS
/* Name: p_DBA_TransactionLogDumpCheck
Creation Date: 31/05/2013
Written by: Simon Woods
SQL Version: SQL 2005/SQL 2008 R1/SQL 2008 R2
Purpose: Checks databases to see if transaction log dumps
are happening for databases in FULL recovery
Local Variables:
Updates:
Change No. Date Author Purpose
001 31/05/2013 Simon Woods Adapted from script written by Paul Jenkins
*/
BEGIN
SET NOCOUNT ON;
DECLARE @tblResults TABLE
(
DBName VARCHAR(128)
);
DECLARE
@chvDBName VARCHAR(128)
,@intCount INT;
DECLARE crDBName CURSOR LOCAL FAST_FORWARD FOR
--only query suitable databases
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
DATABASEPROPERTYEX([name],'Recovery')='FULL'
AND
DATABASEPROPERTYEX([name],'Status')='ONLINE'
AND
DATABASEPROPERTYEX([name],'Updateability')='READ_WRITE'
AND
[name] NOT IN ('master','model','msdb','tempdb')
ORDER BY
[name] ASC;
OPEN crDBName
FETCH crDBName INTO @chvDBName
WHILE @@FETCH_STATUS != -1
BEGIN
SELECT @intCount = COUNT(backup_set_id)
FROM
msdb.dbo.backupset
WHERE
database_name = @chvDBName
AND
type = 'L' --transaction backups only
AND
backup_finish_date >= DATEADD(DAY , -1, GETDATE() );
IF @intCount = 0
BEGIN
INSERT INTO @tblResults
SELECT @chvDBName;
END;
FETCH crDBName INTO @chvDBName;
END;
CLOSE crDBName;
DEALLOCATE crDBName;
IF ( SELECT COUNT(DBName) FROM @tblResults ) > 0
BEGIN
DECLARE
@chvSubject VARCHAR(128)
,@chvMessage VARCHAR(1024);
SET @chvMessage = 'Databases:' + CHAR(13);
SELECT
@chvSubject = @@SERVERNAME + ': Databases in full recovery without Log Backups'
,@chvMessage = @chvMessage + [DBName] + CHAR(13) FROM @tblResults;
--what version of SQL for email notification
IF ( SELECT cmptlevel FROM master.dbo.sysdatabases
WHERE [name] = 'master' ) >= 90
BEGIN
--2005
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'DBA Alerts'
,@recipients = 'team@team.com'
,@subject = @chvSubject
,@body = @chvMessage;
END
ELSE
BEGIN
--2000
EXEC master.dbo.xp_sendmail
@recipients = 'team@team.com'
,@subject = @chvSubject
,@message = @chvMessage;
END;
END;
END;
USE [BI_Ops]
GO
/****** Object: StoredProcedure [dbo].[p_DBA_BackupAudit] Script Date: 19/08/2020 12:46:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_DBA_BackupAudit]
AS
BEGIN
/* Name: p_DBA_BackupAudit
Creation Date: 31/05/2013
Written by: Simon Woods
SQL Version: SQL 2005/SQL 2008 R1/SQL 2008 R2
Purpose: Checks backups dumps against a backup criteria to determine
a) whether the database has ever been backed up
b) if its been backed up to its schedule
Local Variables: @chvDBName holds name of the database
@chvEmailRecipients holds names of recipients for email alert
@chvSubject holds subject line for email alert
@intDaySchedule holds an number of days that a backup (full or diff) should be completed
e.g. 0 = never, 1 = every day, 7 = every week...
@intRowCheck holds row check value
@dtmBackupFinsihDate holds date the last backup finished
@dtmDateCheck used to calculate if a backup is overdue
Updates:
Change No. Date Author Purpose
001 31/05/2013 Simon Woods Adapted from script written by Paul Jenkins
*/
SET NOCOUNT ON
DECLARE
@chvDBName VARCHAR(128)
,@chvEmailRecipients VARCHAR(255)
,@chvSubject VARCHAR(128)
,@intDaySchedule INT
,@intRowCount INT
,@dtmBackupFinishDate DATETIME
,@dtmDateCheck DATETIME
--create temporary tables
IF EXISTS ( SELECT [name]
FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[##backups]' ))
DROP TABLE ##backups
--holds metadata for email report
CREATE TABLE ##backups ( BckDesc VARCHAR(7) NOT NULL,
DBName VARCHAR(128) NOT NULL,
Backup_Finish_Date DATETIME NULL,
DaySchedule SMALLINT NULL )
--holds metadata about backup history
IF EXISTS ( SELECT [name]
FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#backup_history]') )
DROP TABLE #backup_history
--populate temp backup history table
SELECT database_name,
backup_finish_date,
type
INTO #backup_history
FROM msdb.dbo.backupset AS ms
WHERE ms.type = 'D' or ms.type = 'I' --D is full dump, I is differential. We are not interested in log dumps.
/* insert data into the backup history for databases that may:
exist
have a record in backup criteria
dont have a record in the backupset table because they have not been backed up yet!
*/
INSERT INTO #backup_history
SELECT
bkc.DBName, NULL, bkc.DaySchedule
FROM
dbo.BackupCriteria AS bkc
LEFT JOIN
msdb.dbo.backupset AS ms
ON
bkc.DBName = ms.database_name
WHERE
ms.database_name is NULL
--finds any databases that have no backup history and a record does not exist in our backup criteria
INSERT INTO ##backups
SELECT 'Never', mst.name, NULL, NULL
FROM master.dbo.sysdatabases AS mst
LEFT JOIN #backup_history AS bkh
ON mst.name = bkh.database_name
LEFT JOIN BI_Ops.dbo.BackupCriteria AS bkc
ON mst.name = bkc.DBName
WHERE bkh.database_name IS NULL
AND bkc.DBName IS NULL
--finds any databases that have no backup history and a record does exist in our backup criteria
INSERT INTO ##backups
SELECT 'Never', database_name, NULL, bkc.DaySchedule
FROM #backup_history AS bkh
LEFT JOIN
dbo.BackupCriteria AS bkc
ON
bkh.database_name = bkc.DBName
WHERE
bkh.backup_finish_date IS NULL
AND
bkc.DaySchedule > 0
--find overdue backups
DECLARE crCheckBackupCriteria CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT DBName,
DaySchedule
FROM BackupCriteria
WHERE DaySchedule > 0
OPEN crCheckBackupCriteria
FETCH NEXT FROM crCheckBackupCriteria INTO @chvDBName, @intDaySchedule
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @intDaySchedule > 0
BEGIN
SELECT TOP 1 @dtmBackupFinishDate = backup_finish_date
FROM #backup_history
WHERE database_name = @chvDBName
ORDER BY database_name, backup_finish_date DESC
--calculate date to test against backup datetime stamp
SET @dtmDateCheck = getDate() - @intDaySchedule
IF @dtmBackupFinishDate is null OR @dtmBackupFinishDate < @dtmDateCheck
INSERT INTO ##backups ( BckDesc, DBName, Backup_Finish_Date, DaySchedule )
VALUES ( 'Overdue', @chvDBName, @dtmBackupFinishDate, @intDaySchedule )
END
FETCH NEXT FROM crCheckBackupCriteria INTO @chvDBName, @intDaySchedule
END
CLOSE crCheckBackupCriteria
DEALLOCATE crCheckBackupCriteria
--decide if we have anything to report and send email
SELECT @intRowCount = COUNT(*)
FROM ##backups
IF @intRowCount > 0
BEGIN
SET @chvSubject = @@SERVERNAME + ' - Summary of Backup Dumps'
SET @chvEmailRecipients = 'team-bi-dba@computershare.co.uk'
DECLARE @nvrTableHTML NVARCHAR(MAX)
SET @nvrTableHTML =
N'<table border="1">' +
N'<tr><th>Item</th><th>dbName</th>' +
N'<th>LastBackup</th><th>BackupScheduleInDays</th></tr>' +
CAST ( ( SELECT td = BckDesc, '',
td = DBName, '',
td = ISNULL(CAST(Backup_Finish_Date AS VARCHAR(12)), 'No Backup'), '',
td = DaySchedule, ''
FROM ##backups
FORXMLPATH('tr'),TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'DBA Alerts'
,@recipients = @chvEmailRecipients
,@subject = @chvSubject
,@body_format = 'HTML'
,@body = @nvrTableHTML
END
DROP TABLE ##backups
DROP TABLE #backup_history
END
Roemesh
August 19, 2020 at 4:50 pm
...smtp codes like we used to do earlier.
Earlier than when? What sort of SMTP codes?
Can you clarify a little?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 19, 2020 at 10:42 pm
Normal means - smtp codes server name
like the below mention method but on the above code but in the above code i am unable to understand it.
https://www.sqlshack.com/configure-database-mail-sql-server/
could you please help me to understand from the code i posted how it going to send an email
August 20, 2020 at 1:13 am
I'll start off by saying that, once you've eliminated the double spacing, the first 463 lines of code are all a part of the dbo.sp_send_dbmail stored procedure, which is a system stored procedure that you must not change.
As for the rest of it, it's creating reports as to if and when backups were created and whether or not they were successful or not and then they each us dbo.sp_send_dbmail to send someone the report.
In order for this to work, you need to setup mail for the server. In order to do that, you're going to have to get with the folks in your infrastructure group and have them tell you what the smtp server name is and a couple of other things.
To do that, see the follow Google search page for help... you should go through it before you hit up the infrastructure folks for the smtp information I told you about above...
https://www.google.com/search?&q=set+up+mail+in+sql+server+2017
The link that you provided also has some good information in it but the smtp information you're looking for is esoteric to your company and the infrastructure folks that setup you email will have the information you need. If your email is in the cloud, then call your cloud provider for the information (the folks in infrastructure might also have that info so check with them first).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2020 at 11:19 am
Hi Expert,
My Question was where smtp it suppose to be configure or it is calling via some store procedure and how it is called from SP
Shree
You need to configure database mail via T-SQL or the GUI in the Management->Database Mail area.
The first hit in Jeff's link he kindly posted will point you to these three links on configuring database mail
Then you call sp_send_dbmail with the right accounts/profiles needed to send mail
August 20, 2020 at 11:50 pm
thank you so much for perfect answer!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply