Configure Database Mail

by Amol 30. April 2010 12:55
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.
 
Here is the script to configure database mail by creating Database Mail Account and Database Mail Profile.
Replace Placeholders according to your need. For example Replace #EmailId# with valid Email address.
 

Use [msdb]

GO

 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

 

-- Delete Principle profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

                  @principal_id = 0,

                  @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Pronciple Profile'

 

END CATCH

 

GO

 

-- Delete profile account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp

                  @profile_name = '#ProfileName#',

                  @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile Account'

 

END CATCH

 

GO

 

-- Delete account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_account_sp

              @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Account'

 

END CATCH

 

GO

 

-- Delete profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profile_sp

              @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile'

 

END CATCH

 

GO

 

-- Create a Database Mail account

DECLARE  @emailaddress varchar (100)

        ,@mailservername varchar(128)

 

SET @emailaddress = '#EmailId#'

SET @mailservername = '#MailServerName)'

 

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = '#AccountName#',

    @description = 'Mail account for administrative e-mail.',

    @email_address =  @emailaddress ,

    @display_name = 'Automated Mailer',

    @mailserver_name = @mailservername ,

    @port = 25,

    @use_default_credentials = 1

 

GO

 

-- Create a Database Mail Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = '#ProfileName#',

       @description = 'Profile used for mail'

 

 

GO

 

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = '#ProfileName#',

    @account_name = '#AccountName#',

    @sequence_number = 1

 

GO

 

---- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @principal_id = 0,

    @profile_name = '#ProfileName#',

    @is_default = 1

 

GO

Tags: ,

SQL Server Scripts

SQL Server Partition Details

by Amol 29. April 2010 12:26

You will find the following query useful if you deal with SQL Server Partitioned objects. I have written this query to know whether paritioned data gets instered into desired partition. Apart from this you will get to know partition boundaries, the filegroup is used to store the data, the number of rows in each partition. Provided query is much faster as it deals with catalong views.

You can use this query to find out the number of rows in table if you have billions of rows instead firing query on actual table which may take long time to execute.


You can just replace '<ObjectName>' with the object you interested in filer criteria.

 

 

SELECT

      i.object_id,

      i.name AS IndexName,

      p.partition_number,

      fg.name AS FileGroupName,

      value,

      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags: ,

SQL Server Scripts

Script to generate calender for a month

by Amol 29. April 2010 12:14
Now days we can see variety of calender controls to display on web page. We might not or rarely used t-SQL to generate calender.
This code is just to test our T-SQL logic.
 
Here is T_SQL code.
 

SET NOCOUNT ON

DECLARE @TempT TABLE (MM INT, YYYY INT)

INSERT @TempT(MM, YYYY) SELECT 02, 2009

INSERT @TempT(MM, YYYY) SELECT 03, 2010

;

WITH Numbers(LineNumber) AS

(

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

)

, FirstAndLastDayOfMonth AS

(

    SELECT CAST(CONVERT(VARCHAR,DATEADD(dd,-(DAY(CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))-1),CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)),101) AS DATETIME) AS FirstDay

          ,CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)))),DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))),101) AS DATETIME) AS LastDay

    FROM @TempT

)

, YearMonthAndDays AS

(

    SELECT

         UPPER(DATENAME(MONTH, FirstDay)) + N' ' + DATENAME(YEAR, FirstDay) AS Header

        ,REPLICATE(N' ', 4 * ((CASE WHEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST > 7 THEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST - 7

                               ELSE DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST

                               END) - 1)) +

         SUBSTRING(N'   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31',

            1, 4 * DAY(LastDay) + 1) + N'                             ' AS AllDays

        ,ROW_NUMBER() OVER(ORDER BY FirstDay) AS RowNum

    FROM FirstAndLastDayOfMonth

)

, WeekHeader AS

(

   SELECT 'Sun Mon Tue Wed Thu Fri Sat' AS DayNames

)

, Calendar AS

(

    SELECT

         CASE LineNumber

            WHEN 1 THEN N'*=============================*'

            WHEN 2 THEN N'|' + LEFT(REPLICATE(N' ', (29 - LEN(Header)) / 2) + Header + REPLICATE(N' ', 29), 29) + N'|'

            WHEN 3 THEN N'|=============================|'

            WHEN 4 THEN N'|' + N' ' + DayNames + N' ' + N'|'

            WHEN 5 THEN N'|-----------------------------|'

            WHEN 12 THEN N'*-----------------------------*'

            ELSE N'|' + SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28) + N' ' + N'|'

         END AS Outputlines

        ,RowNum

        ,LineNumber

    FROM YearMonthAndDays

    CROSS JOIN WeekHeader

    CROSS JOIN Numbers

    WHERE LineNumber < 6

       OR LEN(SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28)) > 0

       OR LineNumber = 12

)

SELECT  Outputlines

FROM    Calendar

ORDER BY RowNum, LineNumber

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud