Twitter Feed Popout byInfofru

Installing SQL Server 2008 on Windows Server 2008 R2

In my attempt of installing and configuring SharePoint 2010 Farm, I have given a new Windows Server 2008 R2 box that should have SQL Server 2008.

So when I attempt to install SQL Server 2008 on it, I came across the following error message.


Not so weird because every server product have a set of requirement to be installed. First off, before you do any thing Just run the Windows Update. That’s recommended because there can be some thing else that is not mention in this post or any other but can hanged you for some time.

Ok now come to the second part of Enabling .Net Framework Core Role. For that you have to
1) Open Server Manager and Go to the Features on the tree available on left hand.
2) From the Features Summary, Click Add roles.
3) Following window will appear.


4) You have to check the top most option which is (in my case) .net Framework 3.5.1 Feature. That’s it

if you are installing SQL Server 2008 for SharePoint, make sure to check WCF Activation also, Like I did.


Show images on Grid View from File Stream SQL Server 2008

Background :

In my last post about SQL Server 2008 new feature File Stream (Saving and Retrieving File Using FileStream SQL Server 2008), we did an example of saving an image to the file stream and then retrieve it back and make it available for download.The result of that example looks like as below.fs_snapshot1But, one has to press the button to download the image file. One of my blog reader raise a point that he wants to display the same image instead of Get File button which is going to download.

Introduction :

So, in this post I will explain you, how can we rendered the image before actually downloading it and show that in the grid (Maybe as thumbnail, but this post will not discuss any thing about generating thumbnails).Note : If you want to know. How to add files to the file stream please see my post Saving and Retrieving File Using FileStream SQL Server 2008

Getting Started:

We will complete this goal by using HttpHandler. lets first alter our gridview.

   1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
   2:         onrowcommand="GridView1_RowCommand">
   3:         <Columns>
   4:             <asp:BoundField DataField="ID" HeaderText="ID" />
   5:             <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
   6:             <asp:TemplateField>
   7:                 <ItemTemplate>
   8:                     <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' ><img src='<%#Eval("ID") %>.jpg' /></asp:LinkButton>
   9:                 </ItemTemplate>
  10:             </asp:TemplateField>
  11:         </Columns>
  13:     </asp:GridView>

Notice the link button on line no 8. I have now specify an image tag inside Link button and pass the primarykey of tbl_files as the file name along with random “.jpg”. So that, it can finally looks like as follows8e7af927-cc7e-4515-8409-d94566246de8.jpga3de6abb-382f-484c-822c-7f93e0ede0c7.jpg4ad64bf1-ea6e-4228-bdc0-300a0cd90f5a.jpgThe idea is, I will attach the handler with jpg file type to accommodate the incoming requests.Now, lets create HttpHandler and name it “imageHandler”

public class imageHandler : IHttpHandler
    #region IHttpHandler Members
    public bool IsReusable
        get { return false; }
    public void ProcessRequest(HttpContext context)
        //Getting file name from incoming request.
        string url = Path.GetFileName(context.Request.Path);
        Guid FileId;
            //Since we have all our primary keys stored in GUID
            //Try parsing the file name to Guid
            FileId = new Guid(Path.GetFileNameWithoutExtension(url));
        catch (FormatException)
            //If some other JPG file is requested
            FileId = Guid.Empty;
        if (FileId != Guid.Empty) // If the call is for valid Image File Stream
            SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
            SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
            objSqlCmd.CommandType = CommandType.StoredProcedure;
            SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
            objSqlParam1.Value = FileId.ToString();
            string path = string.Empty;
            string fileType = string.Empty;
            using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
                while (sdr.Read())
                    path = sdr[0].ToString();
                    fileType = sdr[1].ToString();
            objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
            byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
            SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
            byte[] buffer = new byte[(int)objSqlFileStream.Length];
            objSqlFileStream.Read(buffer, 0, buffer.Length);
            context.Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
            // Here you need to manage the download file stuff according to your need
            context.Response.ContentType = "application/octet-stream";
        { // If the call is for some other JPG file, nothing to do with file stream.

Well, read the comments I wrote in the code. That will of course help you to understand what actually I have done.And then register the HttpHandler.

    <add verb="*" path="*.jpg" type="LearningApp.imageHandler, LearningApp"/>


There we go, In this way we can show the images stored on file stream in grid view. You can download both VS 2008 and VS 2010 project files.

Storing/Retrieving Hierarchies In SQL Server Database

Hierarchies are one of the most important part of many applications. It can be in shape of categories, family tree, organization charts etc. Maintaining hierarchies in data source like XML is not a big deal but when it comes to the database like SQL Server which is consist of flat table in terms of storing data, then it become a challenge.To overcome this (which I am sure most of us did ), we use recursion. Implementing recursion is very easy and can be applied on both back-end (by using cursors) and front-end. But yet recursive operation can be a great mess if hierarchy grow larger. Lots of repetitive quires and n number of database connection can make your application way slow.So, the idea of the moment is to use the hierarchical data without using recursion. while searching for the solution I came across “Storing Hierarchical Data in a Database” by Gijs Van Tulder. In this article, the Dutch guy mention two method for the processing of Hierarchal data. One is the same recursion which we have discussed above and the other one is ‘modified preorder tree traversal' algorithm.
Modified preorder tree traversal :
To make you understand better let me quote what he mention in his article.We'll start by laying out our tree in a horizontal way. Start at the root node (‘Food'), and write a 1 to its left. Follow the tree to ‘Fruit' and write a 2 next to it. In this way, you walk (traverse) along the edges of the tree while writing a number on the left and right side of each node. The last number is written at the right side of the ‘Food' node. In this image, you can see the whole numbered tree, and a few arrows to indicate the numbering order.sitepoint_numberingWe'll call these numbers left and right (e.g. the left value of ‘Food' is 1, the right value is 18). As you can see, these numbers indicate the relationship between each node. Because ‘Red' has the numbers 3 and 6, it is a descendant of the 1-18 ‘Food' node. In the same way, we can say that all nodes with left values greater than 2 and right values less than 11, are descendants of 2-11 ‘Fruit'. The tree structure is now stored in the left and right values. This method of walking around the tree and counting nodes is called the ‘modified preorder tree traversal' algorithm.Sources : : Let me keep myself honest, the above two paragraphs and image is by Gijs Van Tulder so He deserve the credit of this content. I am just sharing the T/SQL implementation of this algorithm.I again strongly recommend you to read the article completely  before going onward.

Schema :

Ok to start, lets create a following tabletblCommentsID is of course the primary key, where as comments is the simple text. Left and Right are the indexes of the child records where as level will show us at what level this node belongs to. so that we can set the tab index and indentation where as Entity Id is to group the nodes because in this table hundred different nodes set. So in short, Entity Id is to separate the different node set in a single table.Following is the script of the table
   1: CREATE TABLE [dbo].[tblComments](
   2:     [Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
   3:     [Comments] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   4:     [Left] [int] NULL,
   5:     [right] [int] NULL,
   6:     [Level] [int] NULL,
   7:     [EntityId] [int] NULL,
   9: (
  10:     [Id] ASC
  12: ) ON [PRIMARY]

Add Record:

Now to add the record in this table, lets have a look at the following procedure
   1: CREATE PROCEDURE [dbo].[InsertComments]
   2:     @Comments nvarchar(2000),
   3:     @EntityId int,
   4:     @ParentId int = null
   5: AS
   6: BEGIN
   7:     Declare @Left int
   8:     Declare @Right int
   9:     Declare @Level int
  10:     declare @RightParent int
  11:     declare @rightParentOrig int
  12:     declare @leftParentOrig int
  14:     set @RightParent = null
  16:     if @ParentId is null
  17:     begin
  18:         set @Left = 1
  19:         set @Right = 2
  20:     end
  21:     else
  22:     begin
  23:         select @rightParentOrig = [right],@leftParentOrig =[left] , @Right = [right] + 1,@Left=[right],@Level=[Level]+1 from tblComments where id = @ParentId
  24:         set @RightParent =  @Right  + 1
  25:     end
  27:     UPDATE tblcomments
  28:     set
  29:     [right] = [right] + 2,
  30:     [left] = [left] + 2
  31:     where
  32:     [left] >=  @Left and entityId = @EntityId
  34:     UPDATE tblcomments
  35:     SET
  36:     [right] = [right] + 2
  37:     WHERE [left] < @leftParentOrig and [right] > @rightParentOrig
  39:     INSERT INTO [tblComments]([Comments],[Left],[right],[Level],[EntityId])
  40:      VALUES
  41:            (@Comments,@Left,@Right,@Level,@EntityId)
  43:     if @RightParent is not null
  44:         update tblComments set [right] = @RightParent where id = @ParentId
  46: END
Ok, now let me define the code line by lineFirst 15 : Well, these lines are quit easy to understand, we have declared some variables which we will use in next lines.Line no 16-24 : We are checking if the ParentId is null, then of course the node is the parent it self, that is why setting left to 1 and right to 2. But when the parent is specified, we need to set the right and left value according to the parent (+1 of the parent) and also need to +1 the level.Line no 27-37 : Consider a situation, where you have a tree completed and you want to add a node in the middle of the tree. These are the lines for that. Here, we are Creating space of the node by increasing the left and right values of the sibling nodes.Line no 39: This is the simple insert statement, where we are adding our record to the table with calculated right and left.Line no 43: In this statement, we are updating the right side of the parent node.Now Add some records using the above procedures, and do the select as following

Select Records:

   1: select * from tblComments where EntityId = 1
   2: order by [left] ,[right]
   3: -- 2nd option
   4: select * from tblComments where [left] >= 1 and [right] <= 10 and EntityId = 1
   5: order by [left] ,[right]
Both the above query will return the following result set.tblcommentsresultNow you can see the first record i.e id=30 is the parent record. So, the left value is 1 and the right is 10 and the level is 1. where as when you see the second record, it is the child of the first record that is why level is 2 and left is +1 greater then the parent and because all the records up to id=35 are it’s child so it has right index +1 greater then the last child.Now if you want to see the child of id=32 you can do as follows
   1: declare @left int,
   2: @right int,
   3: @EntityId int
   5: select @left= [left],  @right= [right],@EntityId =  EntityId from tblComments where Id = 32
   7: select * from tblComments where [left] >= @left and [right] <= @right and EntityId = @EntityId
   8: order by [left] ,[right]
In this way, you can exactly see the child nodes of each node.

Delete Records:

Now, lets see how can we remove the record from this table as hierarchy is depend upon the left and right index.
   1: CREATE PROCEDURE [dbo].[DeleteComments]
   2:     @CommentId int
   3: AS
   4: BEGIN
   5: Declare @Left int
   6: Declare @Right int
   7: declare @EntityId int
   9: select @Left = [left], @Right = [right], @EntityId = [EntityId] from tblComments
  10: where id = @CommentId
  13: delete from tblComments
  14: where id = @CommentId
  16: update tblComments
  17: set [left] = [left] - 2,
  18: [right] = [right] - 2
  19: where [left] >= @Left and entityId = @EntityId
  22: UPDATE tblcomments
  23:     SET
  24:     [right] = [right] - 2
  25:     WHERE [left] < @Left and [right] > @Right
  27: END
In Line no 9-10 : we are storing the left, right and entity of the record that will be deleted.In Line no 13-14 : delete the record from table.In Line no 16- end: updating the right and left values of the parent and sibling nodes by –2.


This way, we can easily achieve the task of Hierarchal data without recursion. One more time, with full respect I like to give all the credit to Gijs Van Tulder. I have just made small modification in this to make it work according to me to.

Saving and Retrieving File Using FileStream SQL Server 2008

FileStream data type is a very important feature of SQL Server 2008 and gradually getting popular amongst developer for it’s feasibility. And in the past few days specially after “Configure SQL Server 2008 for File Stream” post. I received several feedbacks regarding the usage of FileStream with and Frankly there is not much stuff available on Google for this topic.

In this post, I will guide you to use FileStream Data type in But before we start make sure you have configure your SQL Server 2008 instance to use File Stream Data type and for this you can read this post.

Once you finish with the Configuration execute the following script

   1: CREATE TABLE [dbo].[tbl_Files](
   2:     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
   3:     [SystemNumber] [int] NOT NULL,
   4:     [SystemFile] [varbinary](max) FILESTREAM  NULL,
   5:     [FileType] [varchar](5) NULL,
   7: (
   8:     [Id] ASC
  10: ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
  12: GO
  14: ALTER TABLE [dbo].[tbl_Files] ADD  CONSTRAINT [DF_tbl_Files_Id]  DEFAULT (newid()) FOR [Id]

This will create a table with FileStream Data type. Notice the FileType field I have used here to determine the type of file which we will use when we were downloading the file.

Now that we have created a table, lets now move on to the Stored Procedures by which we will access this newly created table.

Security Setting:

Don’t get confused with the heading, there is no additional setting required. You need to do one of the two available options. Either you need to specify “Integrated Security = true” in Connection String or you need to implement Impersonation. It is there because SQL Server 2008 will not allow un authenticated user or instance to read/modify the file.

Most of the developers usually aware of Integrated Security stuff but let me give a little detail about impersonation. Actually, it is a way to Authorize the Instance of your application on SQL Server by using Credential Information.

Following links will help you to understand or implement impersonation.

Add Procedure:

Lets create a procedure call it “”FileAdd” and past the following script.

   1: Create PROCEDURE [dbo].[FileAdd]
   2: @SystemNumber int,
   3: @FileType varchar(5),
   4: @filepath varchar(max) output
   5: AS
   6: BEGIN
   7:     -- SET NOCOUNT ON added to prevent extra result sets from
   8:     -- interfering with SELECT statements.
   9:     SET NOCOUNT ON;
  12:     SET @ID = NEWID()
  14:     INSERT INTO [dbo].[tbl_Files]
  15:     ([Id],[SystemNumber],SystemFile,FileType)
  16:     VALUES (@ID ,@SystemNumber,CAST('' AS VARBINARY(MAX)),@FileType)
  18:     select @filepath = SystemFile.PathName() from tbl_Files where Id = @ID
  22: END

In the above procedure, we add new records in our table and just pass empty (null) to the FileStream field because we first want our SQL Server to create an empty file on NTFS location which we can access from our code behind by using the path which we have taken as Output Parameter here.

notice the SystemFile.PathName(), it is a new function introduced in SQL Server 2008 which will return the NTFS location of the file.


Get Procedure:

Create a procedure and call it “FileGet”

   1: CREATE PROCEDURE [dbo].[FileGet]
   2: @Id varchar(50)
   3: AS
   4: BEGIN
   5:      select  SystemFile.PathName(),FileType from tbl_Files where Id = @ID
   6: END

This is a simple stuff, we are returning PathName and FileType by specifying ID. Just to read the record.

Upload and Store:

To save the file in the file stream, we will use FileUpload control to upload the file and then save it to FileStream field. For that we have created a page and drag FileUpload control with an Upload button.

Now on the click event of the button write the following code.

   1: byte[] buffer = new byte[(int)FileUpload1.FileContent.Length];
   2: FileUpload1.FileContent.Read(buffer, 0, buffer.Length);
   5: if (FileUpload1.FileContent.Length > 0)
   6: {
   7:     SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
   8:     objSqlCon.Open();
   9:     SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
  11:     SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran);
  12:     objSqlCmd.CommandType = CommandType.StoredProcedure;
  14:     SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);
  15:     objSqlParam1.Value = "1";
  17:     SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4);
  18:     objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName);
  20:     SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
  21:     objSqlParamOutput.Direction = ParameterDirection.Output;
  23:     objSqlCmd.Parameters.Add(objSqlParam2);
  24:     objSqlCmd.Parameters.Add(objSqlParam1);
  25:     objSqlCmd.Parameters.Add(objSqlParamOutput);
  28:     objSqlCmd.ExecuteNonQuery();
  30:     string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();
  32:     objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
  34:     byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
  37:     SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);
  39:     objSqlFileStream.Write(buffer, 0, buffer.Length);
  40:     objSqlFileStream.Close();
  42:     objSqlTran.Commit();

Well, in the first two lines we have saved the uploaded file in byte and call this variable “buffer”.
As we are simply using, that is why in line 7 and 8 we have created and open a connection. Where as it is worth to mention here, we need to use transaction when we want to do any operation on FileStream field that is why we have begin a new transaction in line no 9.

On line number 11 to 30, we have a simply setup command object and parameter stuff and then execute the procedure and save the output parameter in a variable called “Path”.

This new variable will contain the NTFS location of the file which is stored on SQL Server FileStream. It should be clear that, this file is empty yet as we have not stored any thing in it.

Now on line number 32 we have reused command object and this time we are executing a simple statement “GET_FILESTREAM_TRANSACTION_CONTEXT”. It is also a newly added feature in SQL Server 2008 which will return current transaction context to be used in the next few lines. Now, on line number 34 we have stored the output of the above statement in byte.

In line number 37, here is some thing new which is called “SqlFileStream”. It is a new class which you can find under “System.Data.SqlTypes”. It seems more like FileStream of “System.IO” but it should be cleared here that the file stored in FileStream field cannot be access using regular file stream object of “System.IO” we need to use SqlFileStream to access those files which are stored in FileStream field.

In line no 39 and on, we are writing the file with the content of uploaded file (Remember we have stored our uploaded file in bytes and call it “buffer”). and that’s it.

Read The Stored File:

We have finished with storing the file, now lets see how can we read this file back. To do this, Drag a Grid View and make it similar to the following

   1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
   2:        onrowcommand="GridView1_RowCommand">
   3:        <Columns>
   4:            <asp:BoundField DataField="ID" HeaderText="ID" />
   5:            <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
   6:            <asp:TemplateField>
   7:                <ItemTemplate>
   8:                    <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' Text="Get File"></asp:LinkButton>
   9:                </ItemTemplate>
  10:            </asp:TemplateField>
  11:        </Columns>
  13:    </asp:GridView>

And bind the GridView using the following code.

   1: protected void bindData()
   2: {
   3:     SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
   4:     objSqlCon.Open();
   6:     SqlCommand objSqlCmd = new SqlCommand("Select * from tbl_Files", objSqlCon);
   7:     SqlDataAdapter objSqlDat = new SqlDataAdapter(objSqlCmd);
   8:     DataTable objdt = new DataTable();
   9:     objSqlDat.Fill(objdt);
  11:     GridView1.DataSource = objdt;
  12:     GridView1.DataBind();
  13: }

Well, the above markup and the code is enough self explaining but the little important stuff to mention here is the link button. We will use the same link button to download the stored file. Lets quickly move on to the RowCommand implementation of the GridView.

   1: protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
   2: {
   3:     if (e.CommandName == "GetFile")
   4:     {
   6:         SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
   7:         objSqlCon.Open();
   8:         SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
  10:         SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
  11:         objSqlCmd.CommandType = CommandType.StoredProcedure;
  13:         SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
  14:         objSqlParam1.Value = e.CommandArgument;
  16:         objSqlCmd.Parameters.Add(objSqlParam1);
  17:         string path = string.Empty;
  18:         string fileType = string.Empty;
  20:         using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
  21:         {
  22:             while (sdr.Read())
  23:             {
  24:                 path = sdr[0].ToString();
  25:                 fileType = sdr[1].ToString();
  26:             }
  28:         }
  30:         objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
  32:         byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
  35:         SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
  37:         byte[] buffer = new byte[(int)objSqlFileStream.Length];
  38:         objSqlFileStream.Read(buffer, 0, buffer.Length);
  39:         objSqlFileStream.Close();
  41:         objSqlTran.Commit();
  42:         Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
  43:         // Here you need to manage the download file stuff according to your need
  44:         Response.ContentType = "application/octet-stream";
  46:         Response.BinaryWrite(buffer);
  50:     }
  51: }

Well, in the first 8 lines, we have created and opened a connection and then begin the transaction. from line no 10 to 28, we are setting the parameter stuff, executing the procedure and save the output in the two variable called “path” and “fileType”.

In line no 30 to 32, we are executing the the transaction context statement and then save the output the bytes. (Same we have done when we were writing the file)

In line no 35 to 40, we have used the same SqlFileStream and instead of writing, we are reading the file this time(notice line no 38) and save the content of the file to the bytes. Now we have file content in bytes, So we have now commit the transaction in line no 41.

In line no 42 and 44, we are setting the content type and specifying the file name with the extension. That is why, we have also saved file type in the database so that at the time of downloading we can make it available in its original state.

And in line no 46, we are simply writing the binary of the file to the browser so that it can be downloaded.


I have tried my best to explain the integration of FileStream field with, and I found this is an easy way to accomplish the task. You can download the VS 2008 solution which contain the complete source code along with procedures and table SQL.

Since, it is a new featured in SQL Server 2008 which is still in CTP by the time I am posting this stuff that is why we can expect some modifications in the method of reading and saving files using FileStream. If somebody face any challenge in the above code. Please feel to contact me.

Get Column name From Stored Procedure

The requirement of the day is to extract the name of the columns returned by procedures. Stored Procedures are dynamic that is why we need to create a function that takes Stored Procedure name as parameter and return the column names in string. So here is the quick snippet for that

   1: Public Shared Function getMetaData(ByVal spName As String) As String()
   2:        Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("lmString").ConnectionString)
   3:        sqlCon.Open()
   5:        Dim sqlCmd As New SqlCommand("sp_helptext " + spName, sqlCon)
   6:        Dim sqlDataAdapter As New SqlDataAdapter(sqlCmd)
   7:        Dim dt As New DataTable
   8:        Dim strTempQuery As String = String.Empty
   9:        Dim strColumns As String()
  10:        Dim strCol As String = String.Empty
  12:        sqlDataAdapter.Fill(dt)
  13:        If dt.Rows.Count > 0 Then
  14:            For Each dr As DataRow In dt.Rows
  15:                strTempQuery += dr.Item(0)
  16:            Next
  17:        End If
  19:        If Not strTempQuery = "" Then
  21:            'Dim objRegex As New Regex("select([^<]*)from")
  24:            Dim objMatches As MatchCollection = Regex.Matches(strTempQuery, "select([^<]*)from", RegexOptions.IgnoreCase)
  26:            For Each mymatch As Match In objMatches
  27:                strCol += mymatch.Groups(1).Value
  28:            Next
  30:            If Not strCol = "" Then
  31:                strColumns = strCol.Split(",")
  32:                For a As Integer = 0 To strColumns.Length - 1
  33:                    strColumns(a) = strColumns(a).Trim()
  34:                Next
  35:            End If
  36:        End If
  37:        Return strColumns
  38:    End Function


Restriction : Though, we have achieved the target, but since we have used sp_helptext to extract the Stored Procedure data that is why it is not possible to process encrypted stored procedure.

Will make it more better in the future to accommodate all type of Stored Procedures.

Configure SQL Server 2008 for File Stream

Well, from past two days I am working on SQL Server 2008 new feature called File Stream. In the period of SQL Server 2005 when we want to store some files to the database we can have that using varbinary(max) but that approach is not either smart nor popular amongst the developers. So, many developers like me wants to store images on any physical location and keep the file location in the table. But, that have issues too, what if somebody delete the files from physical location ? will  file entries in the database also deleted and what if somebody deleted the records using t/sql will the files on the physical location also deleted.

In nutshell, both the previous approaches have issues. So, this File Stream data type can replace the problem we had before. It will save the file to the physical location and store the stream of of that file to the table. In my opinion, that is the smart approach.

So, let us dig down and see how can we configure file stream to the new SQL Server 2008 instance , Database and then Table.

Getting Your SQL Server 2008 Instance Ready for File Stream:
  • Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
  • Now that Configuration Manager is open, right click on the default instance and go to properties.
  • On this form, Go to File Stream Tab and Enable the file stream. See the image below


Ok, let me brief you the option we have here.

  • Enable FileStream for transact-sql access : This way you can access the file using t/sql
  • Enable FileStream for File I/O streaming access : By checking this you can access the files using IO Stream
  • All remote clients to have streaming access to file stream data : Here you are allowing remote connections to play around with File Stream Files.

Once you finish with this you need to set the access level by run the following query. Please bear in mind that the following query will not work until you set FileStream stuff from Configuration manager.

   1: EXEC sp_configure filestream_access_level, 2 -- 0 : Disable , 1 : Transact Sql Access , 2 : Win IO Access
   2: GO
   4: GO

Or alternatively, you can

  • Go to SQL Server Management Studio
  • Right Click the database server then properties
  • From the properties window select advance and you will see the following screen


  •  Now simply select the access level you want for your server.
Enable database to have FileStream Data type:

Now, you have done with the sql server instance configuration. Let’s move to Database Configuration. How can we create a database which is FileStream Supported.

For that there are two options.

  1. Using T-sql
  2. Using Management Studio

Using T-Sql

For that you need to run the following query

   1: CREATE DATABASE Learning_Db 
   2: ON
   3: PRIMARY ( NAME = LearnDb1,
   4:     FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_Db .mdf'),
   6:     FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_DbStream')
   7: LOG ON  ( NAME = LearnDbLog1,
   8:     FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_Db.ldf')
   9: GO

Ok now, notice that along with the Primary and Log file we have one new file group which we use for FileStream. Remember we have discuss above that FileStream will save a file on a physical location and store the stream in the the database which will later use for accessing that file. 

Now, when you go to the location where we create our new database you will see there is a folder (in our case it should be “Learning_DbStream”).  This folder contain all the files of your FileStream.

Using Management Studio

While creating a new database window go to file group, you will find the File Stream section at the bottom. See image below
Note : If your database is already created, you can set FileStream stuff by right click your database and then properties and then file group


For using the FileStream, you need to add a File Stream Group here and make it default. Once you add that, Go to the Files and add a new file

  • Give Logical name In our case we have used “LearningDb_FileStream” and then Select  “FileStream Data” from file type.
  • After that, you only need to set the path and that’s it

  • Click Ok, and now your database is ready to play with FileStream. 
Using FileStream Data type in table:

Now, for using the FileStream data type in table you need to create a Unique Column which is off uniqueidentified datatype and a column that uses varbinary(max) to store the Stream. Here is the SQL for that

   1: CREATE TABLE [dbo].[tbl_Files](
   2:     [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
   3:     [SystemNumber] [int] NOT NULL,
   4:     [SystemFile] [varbinary](max) FILESTREAM NULL
   5: ) ON [PRIMARY]
   7: GO
Just give this a Go and you are all done with FileStream configuration.

How to Group by Just Date Portion Of DateTime Field

There are times when we need to group by the table with the date. But Datetime field also contain time part which is very deep. So, there is no way you can group by datetime field and see correct records because it will group by including the time portion of DateTime field.

So, to get rid off the time portion in group by clause here is a quick query.

   1: select  dateadd(dd,0, datediff(dd,0,dateCreated)) as Date,count(*) TotalCount  from tblRecords   
   2: group by dateadd(dd,0, datediff(dd,0,dateCreated)) 

CLR Integration and Lightweight Pooling Conflict

I thought I am CLR Integration Guru in Sql Server but this morning I came to know that there are lots of things left to grab. While I was moving the local database on the new live server I run some CLR Procedure which Says these procedures required CLR Integration enabled. So to get that done.
I went on SQL Server Surface Area Configuration -> Surface Area configuration for features -> Enable CLR Integration. till here, every thing seems fine I encounter no error.
After that when I re-execute the same query I get a new error:
"Invalid connection (Common languageruntime (CLR) execution is not supported under lightweight pooling.Disable one of two options: "clr enabled or "lightweight pooling".
I really have no idea what the light weight pooling is but the need of the time is to disable it. After googling for a bit I get this link. but still no use as the query written in this page is not working infact it is giving error.
Query:sp_configure 'show advanced options', 1;GOsp_configure 'lightweightpooling', 0;GORECONFIGURE;GO
Error:The configuration option 'lightweightpooling' does not exist, or it may be an advanced option.
To find what is going wrong, I quried on sys.configurations because this table contain database configuration.
select * from sys.configurations where name like '%light%'
Finally, after running the above query I came to know that there is a space in 'lightweightpooling'. It means it is 'lightweight pooling. Now when I diagnose the problem I execute the following query which make my sql server CLR Integrated.
USE masterGOEXEC sp_configure 'show advanced options', 1GORECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'lightweight pooling', 0GOEXEC sp_configure 'clr enabled', 1goRECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'show advanced options', 0GO
After executing the query, we might need to restart the SQL Services to make it work.

Beta Components Detected on SQL Server 2005 Installation

This weekend, I was in Morgan Technologies as they were facing some installation problem in SQL Server 2005 enterprise edition. So they called me up with relation to the community work. So the error was
Errors occurred during the installation:Beta components detected.
It looks that there is some beta software running on the server but after looking at the Program Files and Add Remove Program It seems that there is nothing installed before. No Visual Studio Installation (as that is server) , no Beta stuff. There was .net framework 2.0 installed but to get rid of this error I did uninstall that too.But the error doesnot seems to go. still the beta components deteced comes on screen each time I try to install SQL Server.So, Finally I found Microsoft MSI Cleanup Utility which did my job perfectly. on a clean machine where there is nothing Installed I found two microsoft beta installations, No idea where it came from but this tool remove that and afterward SQL Server installed like a butter with no issue.To download the tool :

Backup Failed : System.Data.SqlClient.SqlError: Failed to pause catalog for backup. Backup was aborted.

So, not too long ago I was regular blogger but due to builds and examinations I am getting far from this stuff. Anyway, this morning when I was taking backup of the database located on one of our dedicated server. I hunt the following error.
System.Data.SqlClient.SqlError: Failed to pause catalog for backup. Backup was aborted.
In short, you are no more able to take backups. So I googled about this error but trust me, there is no much information regarding this stuff. So, to get rid of it, you quickly need to do the following steps.
1. Jump to the services console. (Control Panel-> Administrative Tools -> Services)2. Locate the SQL Server FullText Search and verify is it running.3. If it is not running then start it .... but hold on read the next point4. If it is running then verify what account which it is using under Log On tab.  (it should be Local System Account)5. Save the settings and make sure to restart the service and you are done.