Wednesday, September 25, 2013

Sending query based result set via T-SQL in SQL Server

We used to created databases on the fly by different applications as DBA my team need to know what all databases has been created today. I have developed below script to fetch the list of the databases and send it via sp_send_dbmail.

In order to send email via "sp_send_dbmail" all you need is to create a database mail profile. You can see here how to configure database mail.

Once your DB mail profile is configured, you can use this as a example to send email, you can further modify t-SQL and XML as per your need.

I hope this will help.



CREATE TABLE #tblDBList (RecordID INT IDENTITY(1,1), DBName nVARCHAr(250), CrDate DATETIME)

INSERT INTO #tblDBList (DBName, CrDate)
SELECT name, create_date from sys.databases WHERE CAST(create_date AS DATE)  =  DATEADD(Day, -1, CAST(GETDATE() AS DATE))  ORDER BY create_date

IF EXISTS (SELECT 1 FROM #tblDBList)
BEGIN

      DECLARE
            @xml NVARCHAR(MAX),
            @body NVARCHAR(MAX),
            @ServerName VARCHAR(25),
            @Subject VARCHAR(50)
      SELECT @ServerName = @@SERVERNAME
     
      SET @Subject = 'New Database(s) : ' + @ServerName
      SET @xml = CAST(( SELECT
                              [RecordID] AS 'td',''
                              ,[DBName] AS 'td',''
                              ,CONVERT(VARCHAR, [CrDate], 100) AS 'td',''
      FROM  #tblDBList ORDER BY [RecordID]
      FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

     
      SET @body ='
                             
                                   

Following database(s) has been created on '

+ @ServerName + '

                                   
                                                                            
                                         
S. No.
                                         
Database Name
                                         
CreatedOn (CST)
                                   
'   

     
      SET @body = @body + @xml +'

'

      SET @body = @body + '

This email has been generated by SQL Server as a routine notification.

'
      SET @body = @body + '
'
     
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = '***Server_DBMail_Profile', -- replace with your SQL Database Mail Profile
      @body = @body,
      @body_format ='HTML',
      @recipients = 'Ashish.Upadhyay@******.***', -- replace with your email address
      @subject = @Subject
     
      END
     
            DROP TABLE #tblDBList

      GO

No comments:

Post a Comment