Useful Links for SQL Server

Stuck while manually inserting values in an Identity column? Here is what you need to do How to Insert Values into an Identity Column in SQL Server

Here is a great topic for beginners to learn about the Execution Plans in SQL Server Beginners topic for understanding Execution Plans in SQL Server

Is your SQL Server eating your CPU too much? Don't know which Query or Stored Procedure is behind this? A must have tool for SQL Server Making the Most Out of the SQL Server 2005 Performance Dashboard

Are you an advanced SQL Server 2005 Programmer? Do you know about CROSS APPLY? Using CROSS APPLY in SQL Server 2005

Importing IIS Web Log into SQL Server 2005

Today I was trying to import the IIS log of my Web Server into SQL Server 2005 Database (for further analyses). So first I started googling about it and found this link,

How To Use SQL Server to Analyze Web Logs

Very useful article BUT this one is old maybe because the table have different schema. By the way I have IIS 6.0 on my web server. So I did some workarounds and successfully imported the Log. Now I am posting here in case someone might be trying to do the same thing.

Step #1: Create a table in Database

CREATE TABLE [dbo].[IISLog](
 [date] [datetime] NULL,
 [time] [datetime] NULL,
 [site-name] [varchar](255) NULL,
 [s-computername] [varchar](255) NULL,
 [s-ip] [varchar](50) NULL,
 [cs-uri-stem] [varchar](255) NULL,
 [cs-uri-query] [varchar](2048) NULL,
 [c-ip] [varchar](50) NULL,
 [cs(User-Agent)] [varchar](2048) NULL,
 [cs(Cookie)] [varchar](2048) NULL,
 [cs(Referer)] [varchar](2048) NULL,
 [sc-status] [int] NULL,
 [sc-bytes] [int] NULL,
 [cs-bytes] [int] NULL,
 [time-taken] [int] NULL
)

Step #2: Prepare the Log file (since it contains some description lines on top)

So this is a tricky step. As the Log file contains some description lines on top starting with "#" sign. Therefore SQL Server will NOT be able to import it. One more thing is that these log files can be large (or very large). The Log file I had was of size aprox. 216 MB. So obviously we cannot open it in NotePad etc.

The same article provides a small utility which removes the line, but I think there is bug in this utility, cause it is limiting file upto 43 MB. So I decided to write my own version.

PrepIISLogFileForImport.zip

As I started to create this application for IIS Log import but then it ends up with a generic utility. Which actually displays the text file content and have an option to skip number of lines from start. Therefore we can use it as,

C:\>PrepIISLogFileForImport C:\LogFile.Log skip=4 >newlogfile.txt

Step #3: Bulk import the Log file into SQL Server Table

Hence the final step is to import the log file in the same table. Which can be done by,

BULK INSERT [dbo].[IISLog] FROM 'C:\newlogfile.txt'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

SQL Server Date Time Useful Functions

Here are some SQL Server Date Time Useful functions.

• DateCreate - Creates Date through Year, Month and Day
• DateToStrShort - Accepts Date and returns as '1-Jan-2009'
• DateGetDaysInMonth - Accepts Month and Year and returns number of days in month
• DateGetTimeOnly - Accepts date and returns time as '10:33 PM'
• DateGetDateOnly - Accepts Date and convert it into Date only i.e. removes the Time Part
• DateGetMonthFirstDate - Gets the first date of Month
• DateGetMonthLastDate - Gets the last date of Month
• DateGetMonthName - Gets the Month Name

Review the functions below or download the SQL file.

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function accepts Year, Month and Day
--                and returns Date
-- =============================================
CREATE FUNCTION [DateCreate]
(
    @Year int,
    @Month int,
    @Day int
)
RETURNS datetime
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result datetime;

    -- Add the T-SQL statements to compute the return value here
    SET @Result = CAST    (
                            CAST(@Year AS varchar(10)) + '-' +
                            CAST(@Month AS varchar(2)) + '-' + 
                            CAST(@Day AS varchar(2))
                            AS datetime
                          );

    -- Return the result of the function
    RETURN @Result;
END

Back to Top

-- =============================================
-- Author:        Arsalan Tamiz
-- Description:   This function converts the given
--                date in to string. Useful function
--                for displaying date as '10-Jan-2009' for example
-- =============================================
CREATE FUNCTION [DateToStrShort] 
(
    -- Add the parameters for the function here
    @DateToConvert datetime
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(50)

    SET @Result = CAST(Day(@DateToConvert) AS varchar(50)) + '-' + 
                    Left(DateName(m, @DateToConvert), 3) + '-' + 
                    CAST(Year(@DateToConvert) AS varchar(50))

    -- Return the result of the function
    RETURN @Result;
END

Back to Top

-- =============================================
-- Author:        unknown (I copied it from internet)
-- Description:   Returns the Days in a Month
--                parameters = Year and Month
-- =============================================
CREATE FUNCTION [DateGetDaysInMonth] (@Year int, @Month int)
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN @Month IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (@Year % 4    = 0 AND
                                @Year % 100 != 0) OR
                               (@Year % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END

Back to Top

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the Time from Date
-- =============================================
CREATE FUNCTION [DateGetTimeOnly] 
(
 -- Add the parameters for the function here
 @DateToFormat datetime
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100);
 
 DECLARE @H varchar(3);
 DECLARE @Hour int;
 DECLARE @M varchar(3); 

 SET @Hour = DatePart(hh, @DateToFormat);
 SET @M = DateName(mi, @DateToFormat);
 If @M = '0' SET @M = '00';

 -- Add the T-SQL statements to compute the return value here
 SET @H = CAST(CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE CASE WHEN @Hour = 0 THEN 12 ELSE @Hour END END AS varchar);
 
 SET @Result = @H + ':' + @M + ' ' + CASE WHEN @Hour >= 12 THEN 'PM' ELSE 'AM' END;
 
 -- Return the result of the function
 RETURN @Result;
END

Back to Top

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns Date only from given Date
--    That is removes the Time Part
--    so that we can compare two dates
-- =============================================
CREATE FUNCTION [DateGetDateOnly] 
(
 -- Add the parameters for the function here
 @DateToConvert datetime
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;

 -- Add the T-SQL statements to compute the return value here
 SET @Result = CAST(CONVERT(varchar(100), @DateToConvert, 112) AS datetime);

 -- Return the result of the function
 RETURN @Result;
END

Back to Top

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Gets the first date of month
-- =============================================
CREATE FUNCTION [DateGetMonthFirstDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;

 SET @Year = Year(GetDate());

 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-1' AS datetime
    );

 -- Return the result of the function
 RETURN @Result;
END

Back to Top

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: Returns last date of month
-- ***********************************************
-- IMPORTANT: This function depends on [dbo].[DateGetDaysInMonth]() function
--    which can be found above
-- ***********************************************
-- =============================================
CREATE FUNCTION [DateGetMonthLastDate] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS datetime
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result datetime;
 DECLARE @Year int;
 
 SET @Year = Year(GetDate());
 
 SET @Result = CAST (
    CAST(@Year AS varchar(10)) + '-' + 
    CAST(@Month AS varchar(2)) + '-' +
    CAST([dbo].[DateGetDaysInMonth](@Year, @Month) AS varchar(2)) +
    AS datetime
    );

 -- Return the result of the function
 RETURN @Result;
END

Back to Top

-- =============================================
-- Author:  Arsalan Tamiz
-- Description: This function Retuns the name of month
-- =============================================
CREATE FUNCTION [DateGetMonthName] 
(
 -- Add the parameters for the function here
 @Month int
)
RETURNS varchar(100)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(100)
 DECLARE @d datetime;

 SET @d = CAST('2009-' + CAST(@Month AS varchar(2)) + '-1' AS datetime);

 -- Add the T-SQL statements to compute the return value here
 SET @Result = DateName(mm, @d);

 -- Return the result of the function
 RETURN @Result;
END

Back to Top

Notification Bar Control For Windows Forms (Win Forms)

Here is simple Notification Bar control (like we see in Internet Explorer). Gives application a good look. Note that I haven't done much thing, you can customize is further if you like. It supports blinking too. Usage: ShowNotification() method to display the Notification. BlinkTimes property to set the Number of Times to blink. Download Source Code: Notificationbar.zip