Ads

10 December 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

02 December 2014

Highest Wait Type On A Database

Query TO Use :



WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType,
     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95 ;-- percentage threshold;
GO

Reference Table For Actions 





26 November 2014

Query which searches all columns in all databases

Here "USE" is  Dynamic.



sp_msforeachdb 'Use [?];

begin
select ''?'' as DBNAME
end

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ''%exe%''
ORDER BY schema_name, table_name;'

============================================


sp_MSforeachdb Examples 

Without using cursor we can execute a command in all the databases or on required databases by using sp_MSforeachdb.

Examples
sp_MSforeachdb 'select ''?'''   -- List all databases that are in the instance. '?' Indicated Database Name

sp_MSforeachdb 'Use [?]; exec sp_spaceused'  -- Example to execute any stored procedure

sp_MSforeachdb 'Use [?]; select * from sysfiles'  -- Example to execute any SQL Statement

sp_MSforeachdb 'Use [?]; Create table ForEachDBTest(Id Int)'  -- Creating a table in all databases

sp_MSforeachdb 'Use [?]; select * from ForEachDBTest'   -- Selecting rows from a table through all databases.

sp_MSforeachdb 'If ''?'' like ''TempDB''    -- Conditional wise execution
begin
select * from sysfiles
end '
Below command is used to display list of databases which has Create Statistics property is set to True

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''ISAutocreateStatistics'')=1  
begin
 Print ''?''
end'
 

19 October 2014

Replicated Transaction Information:Oldest distributed LSN OR The process could not execute 'sp_repldone/sp_replcounters'

Some times this is resolved by using repl_done command along with repl_flush.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1
go
sp_repleflush


This time we faced issue and went unsuccessful. Then we followed below steps.

1. Stopped Log Reader Agent
2. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

3. sp_repleflush
4. Started Log reader agent
5. Replication came in Sync.





05 September 2014

Enable Change Tracking - SQL Server 2008



Setup.

  1. First enable Change tracking. Here’s some sample T-SQL you can execute to start the process

ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);

GO

After it’s enabled and you want to check on which databases are affected, run this T-SQL

SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;

GO
To enable change tracking on a specific table use a query like this:
ALTER TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
GO
Now to check the tables that have tracking enabled, you can get that list here:
SELECT OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;
GO
No changes are required to statements for insert or update statements that are enabled with change_tracking.
To find out what’s changed after enabled, you can run a query similar to the following
SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]
FROM CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct
JOIN dbo.MYTABLENAME c ON c.[ID] = ct.[ID];

GO

19 August 2014

Grant access to Report Builder

  1. Start Report Manager.
  2. Click Site Settings.
  3. Click Security.
  4. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
    Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
  5. Select System User, and then click OK.
  6. Click Home.
  7. Click the Folder Settings tab.
  8. Click the Security tab.
  9. If a role assignment already exists for the user or group for which you want to configure Report Builder access, click Edit.
    Otherwise, click New Role Assignment. In Group or user, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
  10. Select Report Builder, and then click Apply.
  11. Repeat to create or modify role assignments for additional users or groups.