SQL Server-ing you

A blog about SQL Server

Archive for September 2009

T_SQL – Stored Procedure to create a time dimension

leave a comment »

This is a stored procedure that will build a 24 hour time dimension with it’s boundaries.

Note: you should create the final table and plan for a surrogate key with identity(1,1).

Also, to store hours in a 24h style, it’s not possible to use an int data type as this would wipe out the starting 0 (zero).

Anyway. here goes:

ALTER PROCEDURE Sp_Create_Dim_Time
AS

BEGIN

DECLARE @COUNT INT
SET @COUNT = 1

CREATE TABLE Dim_Time_TEMP
(

Dim_Time_Time varchar(2) not null,
Dim_Time_min varchar(8) not null,
Dim_Time_max varchar(8) not null
)

WHILE @COUNT <= 24
BEGIN

INSERT    INTO Dim_Time_TEMP
SELECT    CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))
END [TIME],

CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:00:00’
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:00:00’
END [MIN],

CASE WHEN
LEN(CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE())))) = 1
THEN
‘0’ + CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:59:59’
ELSE
CONVERT(VARCHAR(2),DATEPART(HH,DATEADD(HH,@COUNT,GETDATE()))) + ‘:59:59’
END [MAX]

SET @COUNT = @COUNT + 1

END

INSERT INTO Dim_Time (Dim_Time_Time, Dim_Time_Min, Dim_Time_Max)
SELECT        Dim_Time_Time, Dim_Time_Min, Dim_Time_Max
FROM        Dim_Time_TEMP
ORDER BY    Dim_Time_Time

DROP TABLE Dim_Time_TEMP

END

The result will look like this:

Dim_Time_Sk    Dim_Time_Time    Dim_Time_Min    Dim_Time_Max
1    00    00:00:00    00:59:59
2    01    01:00:00    01:59:59
3    02    02:00:00    02:59:59
4    03    03:00:00    03:59:59
5    04    04:00:00    04:59:59
6    05    05:00:00    05:59:59
7    06    06:00:00    06:59:59
8    07    07:00:00    07:59:59
9    08    08:00:00    08:59:59
10    09    09:00:00    09:59:59
11    10    10:00:00    10:59:59
12    11    11:00:00    11:59:59
13    12    12:00:00    12:59:59
14    13    13:00:00    13:59:59
15    14    14:00:00    14:59:59
16    15    15:00:00    15:59:59
17    16    16:00:00    16:59:59
18    17    17:00:00    17:59:59
19    18    18:00:00    18:59:59
20    19    19:00:00    19:59:59
21    20    20:00:00    20:59:59
22    21    21:00:00    21:59:59
23    22    22:00:00    22:59:59
24    23    23:00:00    23:59:59

Hope you enjoyed

Nico

Advertisements

Written by Nicolas de Fontenay

2009/09/04 at 8:29 am

Posted in Data Warehouse

a send-mail function for powershell

leave a comment »

I’ve been working a lot with powershell.

Our data warehouse processes are going to be controlled by powershell which in return calls SSIS procedures.

Every steps are logged for success or failure. Everything is tracked.

At powershell level however I needed to be informed if there’s a string malformation when dtexec is called for exemple.

So I’ve built this send-mail function which I think will be useful to anyone required to send mails:

function Send-Email
{
param
(
[string]$message,
[string]$subject,
[string]$to,
[string]$from,
[string]$attachment
)
$SmtpClient = new-object system.net.mail.smtpClient
$MailMessage = New-Object system.net.mail.mailmessage
$SmtpClient.Host = “smtp.mail.com”
$mailmessage.from = ($from)
$mailmessage.To.add($to)
$mailmessage.Subject = $subject
$mailmessage.Body = $message
if ($attachment)
{
$att = new-object Net.Mail.Attachment($attachment)
$MailMessage.Attachments.Add($att)
}
$smtpclient.Send($mailmessage)
}

usage:

send-mail “some message” “my subject” “to@anaddress.com” “from@me.com”

or

send-mail “some message” “my subject” “to@anaddress.com” “from@me.com” “D:\Test.txt”

The function checks whether $attachment is null or not and will attach a document if it finds any.

Written by Nicolas de Fontenay

2009/09/01 at 7:59 am

Posted in Uncategorized

Tagged with , , ,