Wednesday, December 17, 2014

The process could not execute 'sp_repldone/sp_replcounters



Error messages:
·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI '. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
·          Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure. (Source: MSSQLServer, Error number: 18752)
Get help: http://help/18752
·          The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

SOLUTION:-



To clear the pending transactions, run the following against the published database
1. Stop the logreader  Agent 
2. Stop the distribution agent:

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

Turn on both agents.



Wednesday, December 10, 2014

DB Mail Configuration and Testing Mail Profiles



Below Information collected from peer blogs, and very usefull.
===============================================

To Enable Database Mail execute the following block of code:
use master
go
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure
Deleting the Old Profiles :
 IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND
      a.name = @AccountName)
BEGIN
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
END
 IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)
BEGIN
      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
END
 IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)
BEGIN
      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName
END

Creating Accounts & Profiles for DB Mail:
--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Test Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'abc@xyz.com',
    @replyto_address = 'abc@xyz.com',
    @display_name = 'TEST Display',
    @mailserver_name = 'smtp.xxxx.net',
    @port = 587,
    @username = 'xyz',
    @password = 'xxyyzz',
    @enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp     @profile_name = 'Test Mail Profile',     @description = 'Profile used for administrative mail.'
 -- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp     @profile_name = 'Test Mail Profile',     @account_name = 'Test Mail Account',
    @sequence_number =1
 -- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'Test Mail Profile',
    @principal_name = 'public',     @is_default = 1

Sending Mail and Check its status:
--Send mail
EXEC msdb.dbo.sp_send_dbmail     @recipients=N'abba@gmail.com',
    @body= 'Test Email Body',     @subject = 'Test Email Subject',     @profile_name = 'Test Mail Profile'

--Send mail with attachment
EXEC msdb.dbo.sp_send_dbmail      @profile_name = 'DBMail'
    ,@recipients = 'GroupSQLDBA@MyCo.com'
    ,@from_address = 'DBMail@MyCo.com'
    ,@query = 'SELECT resource_type, resource_database_id,
                    request_mode, request_session_id
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')'
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;

Important Tables used in configuring Database mail and check their status:
--Profiles
SELECT * FROM msdb.dbo.sysmail_profile
--Accounts
SELECT * FROM msdb.dbo.sysmail_account
--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount
--Principal Profile
select * from msdb.dbo.sysmail_principalprofile
--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
 --Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp


=====================================================================================
Examples
A. Sending an e-mail message
This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

B. Sending an e-mail message with the results of a query

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a
query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

C. Sending an HTML e-mail message

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'

Work Order Report

' +
    N'' +
    N' Work Order IDProduct ID' +
    N'NameOrder QtyDue Date' +
    N'Expected Revenue
' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

              Below Code We can use to send test email from all existing DB PROFILES

USE msdb
 GO
DECLARE  @Total int
 ,@cnt int  ,@ProfileName varchar(50)
 ,@Server varchar(50)  ,@Sub varchar(400)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
 SET @cnt = 1
SET @Server = @@SERVERNAME
WHILE(@cnt <= @Total)
 BEGIN
 SET @ProfileName = (select name from sysmail_profile where profile_id = @cnt)
SET @Sub = 'Testing Email From ' + @Server + ' from profile : ' + @ProfileName
 IF (@ProfileName is not null)
 BEGIN
PRINT @ProfileName
 PRINT @Sub
EXEC msdb.dbo.sp_send_dbmail
   @recipients = 'Test@gmail.com'
  , @subject = @Sub
  , @body = @Sub
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
 END
SET @cnt = @cnt + 1
 END