Showing posts with label Dbmail. Show all posts
Showing posts with label Dbmail. Show all posts
Thursday, April 21, 2016
dbmail: sending html table in the email
Declare
@xml nvarchar(max),
@body nvarchar(max)
SET @xml = CAST(( SELECT clientcode AS 'td','',right(FilenameStr,charindex('\',reverse(FilenameStr),5)-1) as 'td','', cast(importstartDate as varchar) as 'td','',cast(ImportEndDate as varchar) as 'td'
,'', totnum as 'td', '', totdups as 'td','', totloaded as 'td','', totfail as 'td', '', totskip as 'td' FROM #fileList order by HospitalCode, importstartDate FOR XML PATH('tr'),ELEMENTS) as nvarchar(MAX))
--select @xml
SET @body ='<html><body><H3> files list for date ' + @sqllasjobrundate + '-' + convert(varchar(8),getdate(),112) + ' </H3>
<table border = 1>
<tr>
<td> Hospital code</td> <td> Fine name </td> <td> Import start date </td></td> <td> Import end date </td>
<td> Total count in file </td><td> Total duplicates </td><td> Total imported </td><td> Total failed validation </td><td> Total skipped </td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail@mycompany.com', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'mail1@mycompany.com', -- replace with your email address
@subject = 'file log Report ' ;
end
Friday, October 2, 2015
Configuring Dbmail
--check for operators
use msdb
Go
select * from msdb.dbo.sysoperators
--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'
--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
--Set up account and profile.
--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors
use msdb
Go
select * from msdb.dbo.sysoperators
--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'
--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
--Set up account and profile.
--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors
- -Highlight the "SQL Server Agent" in the object Explorer
- -Go to Properties
- -Go to Alert system options
- - Check the enable profile on and select the profile
Error: Failed to notify via email
The above error occurs when we forget to perform the step to let the SQL Server Agent Talk to Database Mail
To perform that , follow the following steps -
-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile in the mail Session section.
To perform that , follow the following steps -
-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile in the mail Session section.
Thursday, January 17, 2013
Resending the failed mails in DBmail
We had several failed mail items that were supposed to be sent out overnight by some SQL Jobs.
The following query was run to find the failed items
use msdb
GO
declare @StartDate datetime,
@EndDate datetime
select * from dbo.sysmail_faileditems where last_mod_date >=@StartDate and last_mod_date < @EndDate
--all the returned rows had sent_status as failed.
Here is a very cool script that will resend the failed items that I found in sqlservercentral.com. It worked like a charm!!!
declare @mailitem_id int, @sendmailxml
varchar(max), @rc
int,@StartDate datetime, @EndDate
datetime-- get the dates to use in the queryset @StartDate = convert(datetime, convert(char(8), GetDate(), 112))set @EndDate = @StartDate + 1declare cFailedMail cursor forSELECT
mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDateopen cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0
begin
-- 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 <> 0BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')END
fetch next from cFailedMail into @mailitem_idend
close cFailedMail
deallocate cFailedMail
The following query was run to find the failed items
use msdb
GO
declare @StartDate datetime,
@EndDate datetime
select * from dbo.sysmail_faileditems where last_mod_date >=@StartDate and last_mod_date < @EndDate
--all the returned rows had sent_status as failed.
Here is a very cool script that will resend the failed items that I found in sqlservercentral.com. It worked like a charm!!!
declare @mailitem_id int, @sendmailxml
varchar(max), @rc
int,@StartDate datetime, @EndDate
datetime-- get the dates to use in the queryset @StartDate = convert(datetime, convert(char(8), GetDate(), 112))set @EndDate = @StartDate + 1declare cFailedMail cursor forSELECT
mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDateopen cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0
begin
-- 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 <> 0BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')END
fetch next from cFailedMail into @mailitem_idend
close cFailedMail
deallocate cFailedMail
Tuesday, August 28, 2012
Database Mail Executable Minimum Lifetime
We are facing some issue with Sql Server 2008 R2 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time and we get dead locks alert
I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.
Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds
.
I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.
Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds
.
Monday, April 2, 2012
The size of the message body in dbmail
SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.
declare @msg_body varchar(max),
@v_subject
This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)
For .e.g.
declare @msg_body varchar(max),
@v_subject
This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)
Monday, June 27, 2011
Error:System.Data.SqlClient.SqlException: profile name is not valid
We were getting the following error
@profile_name = 'profile1@abc.com'
@principal_name = 'public',@is_default = 1;
Once we did the above two setps after creating the profile the error was gone and everything started to work as normal.=
System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
The profile name is profile1@abc.com
user account associated to it was accountA@abc.com
We had to do the following to get rid of this error
Make sure that the account accountA@abc.com is a role member of the Role DataBaseMailUserRole in the msdb database.This role is found under Security->Role in the msdb database
Set the profile profile1@abc.com to public
use msdb
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'profile1@abc.com'
@principal_name = 'public',@is_default = 1;
Once we did the above two setps after creating the profile the error was gone and everything started to work as normal.=
Subscribe to:
Posts (Atom)