Set up Database Mail - SQL Server 2005

I found the following set of scripts at techrepublic.com


Running the scripts after customising the variables to match your/your company's email address and smtp server will setup Database Mail on your database server.

For more information on how Database mail works see techrepublic.com

List tables files and filegroups

Here's a simple query to get a list of database tables and the files and filegroups they are assigned to:
I found this helpful tip on blog.sqlauthority.com and added a few little bits for better readability.

Calculating British Summer Time (BST)

I ran into a problem where time sensitive data was being sent to Sales staff. Our servers are permanently set to Greenwich Mean Time (GMT) to avoid problems with scheduled tasks on the days when the clocks change. Sales staff, however were continually baffled by the GMT times when we were in BST time. So I wrote a User Defined Scalar Function to return an bit value indicating whether a date falls within BST or not.

The function returns 1 if the date falls within BST
The function returns 0 if the date falls outside BST

BST is the same as European Summer Time. Clocks move forward and hour on the last Sunday of March and move back again on the last Sunday of October.

I've modified this function to calculate Daylight Saving Time in the United States (USA) - See below. Clocks move forward and hour on the second Sunday of March and move back again on the first Sunday of November.

UPDATE: Based on the comment from Howard (see below): Please use this script:
/*****************************
My old and clumsy script:
*****************************/
Britain and Europe:
United States:
Example of usage:

Padding a String With a Zero (0)

I constantly had the problem that I needed to use DATEPART to create filenames and other customized strings from dates. The problem is that DATEPART(mm,GETDATE()) will return the following results:

• 1 for January
• 2 for February
• 3 for March

instead of 01 for January, etc.

So, for the purposes of sorting files by dates it is no good. In order to pad the datepart with a leading zero you need to use the RIGHT command. It's defined in BOL as:
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression
Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. integer_expression can be of type bigint.

An example for my DATEPART predicament is as follows:
It's a good idea to convert the DATEPART from an integer to a string (though the RIGHT command does implicitly convert to a string). Limit it to two characters in length and by specifying a that you use 2 of the expression's characters makes sure that you don't add the leading zero to the months/days above 9. Make sense?

For a more generic example that I found on:
http://classicasp.aspfaq.com/general/how-do-i-pad-digits-with-leading-zeros.html
Obviously, if you need trailing zeroes you can use the LEFT command. It works in the same way.

Stored Procedure to move user database files

The following stored procedure allows you to move all files, including any fulltext catalogs, of a specific database.

To run:

I still have yet to add the ability to move files to different locations based on filegroup. Will come later.

Function for Determining the First and Last Days of Calendar Months

This Scalar-Valued Function below returns the last day of the month for the date passed into it. For example running:

SELECT [dbo.][fn_GetLastDayOfMonth] ('20090320')

 will return '2009-03-31 00:00:00.000'.

So here's the function:

CREATE FUNCTION [dbo].[fn_GetLastDayOfMonth] (@pInputDate datetime)RETURNS datetime    BEGIN        DECLARE @vOutputDate datetime        SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS decimal(12, 5))) - (DAY(@pInputDate) - 1) AS datetime)        SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))        RETURN @vOutputDate    END

 As a further example, I have a report that needs to be run for last month's numbers.
That is, in May I need to run the report for April. Therefore I create the variables @stardate and @enddate as follows:

DECLARE @startdate datetimeDECLARE @enddate datetimeSET @startdate = (SELECT    DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                 )SET @enddate = (SELECT  [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))               )

Or for SQL Server 2008+

DECLARE @startdate datetime = (SELECT   DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                              )DECLARE @enddate datetime = (SELECT [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))

So, if it's May, 2009 the variables returned are: @startdate = '2009-04-01'@enddate = '2009-04-30'

This function was found at www.sql-server-helper.com.