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

DataGridView Custom Cell ToolTip

Level: Intermediate

Knowledge Required:

• DataGridView Control
• Windows Forms

Description:
We have a built-in ToolTip for each Cell in DataGridView control. Which is displayed when the text is long and cannot be displayed completely in Cell. But my requirement was to create a custom ToolTip (as displayed in above image).

There were 2 main requirements,

1) ToolTip should be displayed permanently that is, it should NOT be disappeared automatically after sometime.
2) I want to have a fancy look of my ToolTip (again see the image).

For this purpose we can also use the .net's built-in ToolTip control. Which can have a little fancy look by setting some of its properties like IsBubble, ToolTipIcon and ToolTipTitle. Also the ToolTip control can be more customized by using OwnerDraw mode. But still I would like to use my own ToolTip window. Because to me, this is more easier to do.

Simply I implemented it by creating a new window for ToolTip and I show this window when mouse enters in a cell. But to make it more user friendly, I have used a Timer control. So that whenever mouse enters in a Cell I start timer and in Timer's Tick Event Handler, I display the ToolTip window.

Few things to be considered here,

1) DataGridView's ShowCellToolTips property should be set to False
2) Decide where to display the ToolTip window

So in my case I decided to display the ToolTip window just on the Cell. OK this is NOT just straight. We first need to get the Cell's actual Coordinate in DataGridView by executing DataGridView's GetCellDisplayRectangle() function. Then we need to convert these coordinates into Screen's Coordinates by calling the DataGridView's PointToScreen() function.

Please note that, to display our own custom ToolTip window on our given position we need to set its property StartPosition = Manual, which I have discussed in my earlier post Setting Window / Form Position Programmatically

Another thing I have added in the ToolTip window is the Close Button. Which is actually a NON Focusable button as I have discussed my previous post NOT Focusable / NOT Selectable Button

Download Source:
DataGridViewCustomCellToolTip.zip

NOT Focusable / NOT Selectable Button

Level: Intermediate

Knowledge Required:
User Controls

Description:
Few days back I was creating a user control like ComboBox. In ComboBox we have a Drop Down button at the right side, which cannot have focus. This button can only be clicked by mouse to open the Drop Down portion (Alt+Down is the shortcut).

Similarly my control also have a button on right. I decided to make that button just like the Drop Down button of ComboBox. For this purpose I added a plain Class in my project and then put the following Code,

Public Class NotSelectableButton
    Inherits Button

    Public Sub New()
        MyBase.New()
        ' following line will make this button Not Focusable
        SetStyle(ControlStyles.Selectable, False)
    End Sub
End Class

FTP File Upload Error

It seems that our Network Administrator has changed some settings because my previous file uploading code is NOT working. And giving the following exception:

The server returned an address in response to the PASV command that is different than the address to which the FTP connection was made.

I was using the VB.net My namespace,

My.Computer.Network.UploadFile("C:\SomeFile.txt", "ftp://ftpsite/somefile.txt", "userid", "pwd")

So I Googled about that error and found some dicussion on MSDN forums.

Then I modified and created my own upload function as,

Private Sub UploadFile(ByVal sDestination As String, ByVal sSource As String, ByVal sUserID As String, ByVal sPassword As String)
    Dim i As System.Net.FtpWebRequest
    Dim us As System.IO.Stream
    Dim filebytes As Byte()

    ' create the FTP Web Request
    i = System.Net.WebRequest.Create(sDestination)
    ' set credentials
    i.Credentials = New System.Net.NetworkCredential(sUserID, sPassword)
    ' method = Upload File
    i.Method = System.Net.WebRequestMethods.Ftp.UploadFile
    i.UsePassive = False
    i.Proxy = Nothing
    ' get the Request Stream
    us = i.GetRequestStream()
    ' get the file bytes from source
    filebytes = My.Computer.FileSystem.ReadAllBytes(sSource)
    ' write the bytes in stream
    us.Write(filebytes, 0, filebytes.Length)
    ' close the stream
    us.Close()
End Sub

Warning! Above function will read all the bytes (in one go) from file. Which is NOT a good practice for larger files.