Truncate all table in a database

/*Disable Constraints & Triggers*/

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'


/*Perform delete operation on all table for cleanup*/

exec sp_MSforeachtable 'DELETE ?'


/*Enable Constraints & Triggers again*/

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/

exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''),

''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Call Validators form javascript

We can able to call the Validator controls form the javascript function
if needed

ValidatorEnable(document.getElementById ('reqItem'), true); 

reqItem is the ID of the validator control.

www.codeproject.com

lord-krishna

http://www.authorstream.com/presentation/ginga-116274-lord-krishna-190-pictures-education-ppt-powerpoint/

CTE (Common Table Expressions)

A common table expression (CTE) is a temporary named result set that can be referred to by a defining statement.

; WITH ()
AS
(

)

If the WITH clause for a CTE is not the first statement in the batch, you should delimit it from the preceding statement by placing a semicolon (;) in front of it.

Otherwise some time it will produce an error.

Msg 319, Level 15, State 1, Line 228
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

;WITH cte AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

--If we are having only CTE and no other statements
--it will produce an error.

--Msg 102, Level 15, State 1, Line 29
--Incorrect syntax near ')'.

;WITH cte AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

--SELECT * FROM City1
--SELECT * FROM cte

--A Common Table Expression must be called immediately after stating it.
--If not it will fail

;WITH cte (StationID, Station) AS
(

SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

SELECT * FROM cte

--All columns in the CTE must be in the Column List.
--Otherwise it will produce the error

--Msg 8158, Level 16, State 1, Line 1
--'cte' has more columns than were specified in the column list.

;WITH cte (A, B, C, D, E) AS
(
SELECT StationID,
Station,
Line1,
Line2,
Line3,
Line4,
Line5
FROM City1
)

SELECT * FROM cte

--But we can give new name for each column in CTE in Column List

Recurisve CTE
When a CTE refers to itself, it is considered to be recursive.
This is mainly used when hierarchical data is stored in the table.

; WITH cteName (column list(optional)) AS
(
-- Anchor Query or Anchor Member(AM)
UNION ALL --(This is the only thing allowed)
-- Recursive Query or Recursive Member (RM).
)

Defined number of loops

Default MAXRECURSION is 1000.

SELECT * FROM cteName OPTION (MAXRECURSION 4)

MAXRECURSION OPTION allowed maximum is 32767.

Reference Link

http://jonathanparker.com.au/

http://www.databasejournal.com/

http://www.mssqltips.com/

http://omnibuzz-sql.blogspot.com

Search for the below text in the below link and it is explained well.

Recursive Queries and Common Table Expressions

DataReader to DataTable

public class DataUtils: System.Data.Common.DataAdapter
{
 public DataUtils()
 {
     //
     // TODO: Add constructor logic here
     //
 }

 public int FillFromReader( DataTable dataTable, IDataReader dataReader )
 {
     return this.Fill( dataTable, dataReader );
 }
}

How to call

DataUtils _objDataUtils = new DataUtils();

objDataUtils.FillFromReader( dataTable, dr ); //converts a datareader into a datatable

SqlParameter

Case 1
SqlParameter[] osqlParam = new SqlParameter[2];

osqlParam[0] = new SqlParameter("@FromCity", SqlDbType.VarChar);
osqlParam[0].Value = _objTubeMap.fromStation;

osqlParam[1] = new SqlParameter("@ToCity", SqlDbType.VarChar);
osqlParam[1].Value = _objTubeMap.toStation;

return SQLHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "GetStations", osqlParam);


Case2



string[] paramsNames = new string[2] { "@PostTitle", "@PostBody" };
string[] values = new string[2] { TextBox1.Text, Body };
SqlParameter[] parameters = new SqlParameter[2];

for (int i = 0; i < paramsNames.Length; i++)
{
parameters[i] = new SqlParameter();
parameters[i].ParameterName = paramsNames[i];
parameters[i].Value = values[i];
}

AJAX Exceptions

Sys.WebForms.pageRequestManager TimeoutException : the server request timed out
Solve this error by setting the executionTimeout inside ScriptManager


Sys.WebForms.PageRequestManagerParserErrorException: The message received from the
server could not be parsed. Common causes for this error are when the response is
modified by calls to Response.Write(), response filters, HttpModules, or server
trace is enabled.

"Sys.WebForms.PageRequestManagerParserErrorException: The message received from the
server could not be parsed. Common causes for this error are when the response is
modified by calls to Response.Write(), response filters, HttpModules, or server
trace is enabled.".

Solution  http://siderite.blogspot.com/


Sys.WebForms.PageRequestManagerParserErrorException:
The message received from the server could not be parsed.
Common causes for the error are when the response
is modified by calls to Response.Write(), response, filters,
HttpModules, or server trace is enabled.
Details: Error parsing near '/div> |  '.












Asynchronous (independent) postbacks through the "life cycle" is the same as the
regular pages. The differences occur at the time of rendering. With AJAX,
rendering happens in the partial rendering occurred in the course is in the
UpdatePanel. Render the process of using the "special form" that is only understood
by the JavaScript on the client.

When there are interfensi of the data that is sent to the client
(in the form of a special format that), the special format that will be
damaged and in the end so the client does not understand the longer format of
the data it receives. Parsing process failed and then going over munculah error.


Why it occurs  http://weblogs.asp.net/leftslipper/archive/2007/02/26/sys-webforms-pagerequestmanagerparsererrorexception-what-it-is-and-how-to-avoid-it.aspx#b!

Solution  http://siderite.blogspot.com/2007/02/aspnet-ajax-and-responsewrite-or.html



protected override void Render(HtmlTextWriter writer)
 {

     //base.Render(writer);

     // render to my own text writer

     HtmlTextWriter tw = new HtmlTextWriter(new StringWriter());
     base.Render(tw);

     /* Code 1 */
     //if ( Page != null )
     //{
     //    Page.VerifyRenderingInServerForm ( this );
     //}

     //base.Render ( tw );
     /* Code 1 */

     /* Code 2 */

     //        base.Render(tw); //  Code 2 is replaced by code 1

     /* Code 2 */

     // get the Rendered content of the page

     string content = tw.InnerWriter.ToString();

     content = RecursiveTranslateAjax(content);
     writer.Write(content);
 }

 private static string RecursiveTranslateAjax(string content)
 {

     // look for the basic Ajax response syntax

     Regex reg = new Regex ( @"^(\d+)\|[^\|]*\|[^\|]*\|", RegexOptions.Singleline );
     Match m = reg.Match ( content );

     // if found, search deeper, by taking

     // into account the length of the html text

     if ( m.Success )
     {

         // custom method to get an integer value

         int length = Int ( m.Groups [1] );

         reg = new Regex ( @"^(\d+)(\|[^\|]*\|[^\|]*\|)(.{" + length + @"})\|",

                           RegexOptions.Singleline );

         m = reg.Match ( content );

         if ( m.Success )
         {

             string trans = Translate ( m.Groups [3].Value );
             return trans.Length + m.Groups [2].Value + trans + "|" +
                    RecursiveTranslateAjax ( content.Substring ( m.Length ) );
         }
     }

     // if not Ajax, just translate everything,

     // it must be a normal PostBack or a string of some sort.

     return Translate ( content );
 }

 private static bool IsNumericVariable(object o)
 {
     return (o is byte || o is int || o is long || o is float || o is double || o is decimal);
 }

 public static int Int(object o)
 {

     if ( o == null ) return 0;

 /*    const string pattern = "(^[-+]?\\d+(,?\\d*)*\\.?\\d*([Ee][-+]\\d*)?$)|(^[-+]?\\d?(,?\\d*)*\\.\\d+([Ee][-+]\\d*)?$)";
     Match numberic = Regex.Match ( o.ToString (), pattern );

     //        Match numberic = Regex.Match(o.ToString(), "(-\\d+|\\d+)");

     //        if (IsNumericVariable(o)) return (int)CastDouble(o);

     //        if (numberic.Success) return (int)CastDouble(o);


     if (numberic.Success) return Convert.ToInt32 ( ( (Capture) ( o ) ).Value );
*/
     if (IsNumericVariable(o)) return Convert.ToInt32(((Capture)(o)).Value);


     string s = o.ToString ();

     if ( s == "" ) return 0;

     Match m = Regex.Match ( s, "(-\\d+|\\d+)" );

     if ( m.Success )

         try
         {

             return Int32.Parse ( m.Groups [0].Value );

         }
         catch
         {

         }

     return 0;

 }

 // this method only fixes the weird characters

 // but you can put here any string change you would like

 // like search and replace some words.

 private static string Translate(string content)
 {

     // Html code all chars that are not ASCII, thus getting rid of strange or Unicode characters

     StringBuilder sb = new StringBuilder ();

     for ( int c = 0; c <> 127 ) sb.Append ( "&#" + ( (int) content [c] ) + ";" );

         else sb.Append ( content [c] );

     }

     return sb.ToString ();

 }

HTTP Handler

Create an ASP.NET HTTP Module Using Visual C# .NET



INFO: ASP.NET HTTP Modules and HTTP Handlers Overview

How To Create an ASP.NET HTTP Handler by Using Visual C# .NET

RPX - Intermediater between OpenID providers and the WebSite Hosters

We can get OpenId provies by adding like this in our web site

<iframe src="https://asp-tech-blogspot.rpxnow.com/openid/embed?token_url=http://asp-tech.blogspot.com/" style="width: 400px; height: 240px;" scrolling="no" frameborder="no">&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;br&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;</iframe>


Look at this for more detais https://rpxnow.com/

Journey Planner

From

Location typeStation or stopPostcodeAddressPlace of interest

From

Location typeStation or stopPost codeAddressPlace of interest

Advanced options

Background image on button not showing in IE

This render the image on the background of the Input tag in FF but not in IE

<input class="button" value="Login" style="" type="submit"/>



I just had to add a border to the style containing my background image.
The image is ignored by IE without the border.
I don't want to see a border, 
but fortunately, including a 0px border still works.
I simply changed

 border-style: none;

to

border: 0px solid #FFFFFF;

<input class="button" value="Login"
style="border: 0px solid rgb(255, 255, 255);
background-image:
url(Images/button_bg.png);
background-repeat: repeat-x; display: block;" type="submit"/>

Web Method

If our return type is DataSet then we will get this error message

A circular reference was detected while serializing an object of
type "System.goblaization.CultureInfo"

setting response format to XML

[System.Web.Script.Services.ScriptMethod(ResponseFormat=
System.Web.Script.Services.ResponseFormat.Xml)]
public DataSet Get()
{
   DataSet ds = new DataSet();

   return ds;
}

Difference between NULL and (Empty or ' ')

DECLARE @A VARCHAR(10)
DECLARE @B VARCHAR(10)
DECLARE @C VARCHAR(10)

BEGIN
 --Step:1
        SELECT ISNULL(@A , ' ')  -- RETURNS ' '
        SELECT ISNULL(@A , NULL) -- RETURNS NULL
        SELECT ISNULL(@A , @B) -- RETURNS NULL
        
        --Step:2
        SET @A = '' --Since we have assigned some value which is not null
        SELECT ISNULL(@A , ' ')  -- RETURNS ''
        SELECT ISNULL(@A , NULL) -- RETURNS ''
        SELECT ISNULL(@A , @B) -- RETURNS ''
        
        --Step:3
 SET @A = NULL--Which is same as the Step:1
        SELECT ISNULL(@A , ' ')  -- RETURNS ''
        SELECT ISNULL(@A , NULL) -- RETURNS NULL
        SELECT ISNULL(@A , @B) -- RETURNS NULL
        
        --Step:4
        SET @B = ' '
        SELECT ISNULL(@B , ' ')  -- RETURNS ' '
        SELECT ISNULL(@B , NULL) -- RETURNS ' '
        SELECT ISNULL(@B , @C)   -- RETURNS ' '
        SELECT ISNULL(@C , @B)   -- RETURNS ' '
        SELECT ISNULL(@B , @A)   -- RETURNS ' '
        
        --Step:5
        SET @A = '1'
        SELECT ISNULL(@B , @A) -- RETURNS ' '
        SELECT ISNULL(@A , @B) -- RETURNS 1
        SELECT ISNULL(@C , @A) -- RETURNS 1
END

SQL Server Error Messages

Get all the error message available in our server

SELECT        
    *
FROM        
    sys.messages
WHERE        
    message_id >= 0 AND
    language_id = 1033

ErrorLogging in EnterpriseLibrary Step by Step

[step 1]

[step 2]

[step 3]

[step 4]

[step 5]

[step 6]

[step 7]

[step 8]

[step 9]

Finding size of Table/Indexes in SQL Server 2005

Original file

http://www.extremeexperts.com/sql/Scripts/FindSizeOfTable.aspx

Updated query

WITH table_space_usage
( schema_name, table_name, index_name, used, reserved, ind_rows, tbl_rows )
AS (
SELECT s.Name
     , o.Name
     , COALESCE(i.Name, 'HEAP')
     ,p.used_page_count -- total_used_pages
     ,p.reserved_page_count * 8 --total_reserved_pages
     ,p.row_count --rows
     , case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
  FROM sys.dm_db_partition_stats p
  INNER JOIN sys.objects as o
    ON o.object_id = p.object_id
  INNER JOIN sys.schemas as s
    ON s.schema_id = o.schema_id
  LEFT OUTER JOIN sys.indexes as i
    on i.object_id = p.object_id and i.index_id = p.index_id
 WHERE o.type_desc = 'USER_TABLE'
   and o.is_ms_shipped = 0
)
 SELECT t.schema_name
     , t.table_name
     , t.index_name
     , sum(t.used) as used_in_kb
     , sum(t.reserved) as reserved_in_kb
     , case grouping(t.index_name)
    when 0 then sum(t.ind_rows)
    else sum(t.tbl_rows) end as rows
 FROM table_space_usage as t
 GROUP BY
       t.schema_name
     , t.table_name
     , t.index_name
 WITH ROLLUP
 ORDER BY
      grouping(t.schema_name)
    , t.schema_name
    , grouping(t.table_name)
    , t.table_name
    , grouping(t.index_name)
    , t.index_name

Numeric Validation Using JavaScript

<asp:textbox id="TextBox1" runat="server"
onkeypress="return detectEvent(event);"
onkeydown="return detectEvent(event);"></asp:textbox>

onkeydown
onkeypress


we are using both the event since some
browsers don't have one of the event.


To accept only numbers both from NUMPAD and
from KeyBoard we use this method.

function detectEvent(e) {
var evt = e || window.event;
// Firefox
if(evt.type == 'keydown') {
 if ( (evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96
                      && evt.keyCode <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if (evt.charCode >= 48 && evt.charCode <= 57) return true;
 return false;
 }
}

This one for IE  since IE don't have charCode

function detectEvent(e) {
var evt = e || window.event;
// IE
if(evt.type == 'keydown') {
 if ( (evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96
                      && evt.keyCode <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if (evt.keyCode >= 48 && evt.keyCode <= 57) return true;
 return false;
 }
}


Now a common method for both the things(it works both in IE and FireFox
for both NUMPAD and for KeyBoard)

function validKey(e) {
var KeyID = window.event ? event.keyCode : e.which;
var evt = e || window.event;
if(evt.type == 'keydown') {
 if ( (KeyID >= 48 && KeyID <= 57) || (KeyID >= 96 && KeyID <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if(KeyID >= 48 && KeyID <= 57) return true;
 return false;
 }
}

Table Reference Levels

CREATE FUNCTION RISequence
(
)
RETURNS @RISEQUENCE TABLE
(
        TABLE_NAME SYSNAME,
        PRIORITY SMALLINT NOT NULL,
        PRIMARY KEY
        (
                TABLE_NAME))
AS
        --Returns The user tables and the "depth" of the table in the RI chain.
        --Tables with no FK's are given a PRIORITY of 0
        --Circular reference tables have a minimum PRIORITY of 1
BEGIN
        --COUNTER
        DECLARE @COUNTER  INT,
                @PRIORITY INT
                --RI Table Matching
        DECLARE @RIMATCH TABLE (PK_TABLE SYSNAME NOT NULL, FK_TABLE SYSNAME NOT NULL)
                --Populate @RIMATCH -- Remove Self referenced entities (WHERE expression)
                INSERT @RIMATCH
                        (
                                PK_TABLE,
                                FK_TABLE
                        )
                SELECT  PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME          AS PK_TABLE ,
                        FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME          AS FK_TABLE
                FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS     AS RC
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK
                        ON      FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK
                        ON      PK.CONSTRAINT_NAME       = RC.UNIQUE_CONSTRAINT_NAME
                WHERE   FK.TABLE_SCHEMA + FK.TABLE_NAME != PK.TABLE_SCHEMA + PK.TABLE_NAME
                        --No FK's (PRIORITY=0)
                        SET @PRIORITY = 0
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  TABLE_SCHEMA + '.' + TABLE_NAME,
                        @PRIORITY
                FROM    INFORMATION_SCHEMA.TABLES AS T
                WHERE   TABLE_TYPE  = 'BASE TABLE'
                    AND TABLE_NAME != 'dtproperties'
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RIMATCH C
                        WHERE   C.FK_TABLE = T.TABLE_SCHEMA + '.' + T.TABLE_NAME
                        )
                        --Circular Reference (CR) PRIORITY=1 ie The only FK is the circular FK
                        SET @PRIORITY = @PRIORITY + 1
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                WHERE   EXISTS
                        (SELECT 1
                        FROM    @RIMATCH X
                        WHERE   X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                        )
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence C
                        WHERE   C.TABLE_NAME = S.FK_TABLE
                        )
                GROUP BY S.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH K
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        --Fudge a DO operation
                        SET @COUNTER = 1 WHILE @Counter > 0 BEGIN
                        SET @COUNTER = 0
                --Standard RI
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  K.FK_TABLE ,
                        @PRIORITY
                FROM    @RIMATCH AS K
                        INNER JOIN @RISequence O
                        ON      O.TABLE_NAME = K.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence F
                        WHERE   K.FK_TABLE = F.TABLE_NAME
                        )
                GROUP BY K.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH S
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        SET @COUNTER =
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
                        --Because of the forced discovery of PRIORITY 1 CR, increment takes place here
                        SET @PRIORITY = @PRIORITY + 1
                --Circular References with related RI at the current PRIORITY Level
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                        INNER JOIN @RIMATCH X
                        ON      X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence W
                        WHERE   S.FK_TABLE = W.TABLE_NAME
                        )
                    AND EXISTS
                        (SELECT 1
                        FROM    @RIMATCH Z
                        WHERE   S.PK_TABLE  = Z.FK_TABLE
                            AND Z.PK_TABLE != S.FK_TABLE
                            AND EXISTS
                                (SELECT 1
                                FROM    @RISequence A
                                WHERE   Z.PK_TABLE = A.TABLE_NAME
                                )
                        )
                        SET @COUNTER = @COUNTER +
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
        END RETURN
END GO




SELECT  *
FROM    dbo.RISequence()
ORDER BY PRIORITY,
        TABLE_NAME
        --DROP FUNCTION RISequence

Asp.Net Code Compressor

The below code compress your code when rendering to browser 
and it helps to increase the speed of loading

Click these link


http://blog.madskristensen.dk/

http://www.mironabramson.com/


http://msmvps.com/blogs/omar

http://www.codeproject.com/

http://www.codeproject.com/

http://www.codeproject.com/

http://www.jigar.net/

http://www.jigar.net/

Send mail from SQL SERVER 2005 after Insert Statement

The below stored procedure send a mail.

The profile name should be already created.

The Procedure that sends the mail is msdb.dbo.sp_send_dbmail

ALTER TRIGGER [MailOnError] ON [dbo].[LogMessages] AFTER
INSERT AS
DECLARE @LoggerId    INT
DECLARE @TableName   VARCHAR(75)
DECLARE @TableNameId INT
DECLARE @MessageId   INT
DECLARE @MessageText VARCHAR(1000)
DECLARE @LoggedTime  DATETIME
DECLARE @Body        VARCHAR(1100)
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -- Insert statements for trigger here
        SELECT @LoggerId    = LoggerId   ,
               @TableName   = TableName  ,
               @TableNameId = TableNameId,
               @MessageId   = MessageId  ,
               @MessageText = MessageText,
               @LoggedTime  = LoggedTime
        FROM   LogError
        
        IF @MessageId = 3
        BEGIN
                SET @Body = 
                'LoggerId = ' + CAST( @LoggerId AS VARCHAR ) + ',   ' + 
                'TableName  = '+ @TableName + ', ' + 
                'TableNameId = ' + CAST( @TableNameId AS VARCHAR ) + ', ' + 
                'MessageId = ' + CAST( @MessageId AS VARCHAR ) + ', ' + 
                'MessageText = ' + @MessageText + ', ' + 
                'LoggedTime = '+ CAST( @LoggedTime AS VARCHAR )
                
                EXEC msdb.dbo.sp_send_dbmail
                        @profile_name = 'DBOException' ,
                        @recipients   = 'babukumarasamy@gmail.com' ,
                        @subject      = 'Exception occured!' ,
                        @body         = @Body,
                        @importance   = 'High',
                        @body_format  = 'HTML',
                        @sensitivity  = 'Confidential'
        END
END

Read the book
Pro SQL Server 2012 Administration By Ken Simmons, Sylvester Carstarphen
Page#: 326

SQL Server 2005 Database Mail Setup and Configuration Scripts

This sight gives us how to configure and send mail from sql server 2005

SQL Server 2005 Database Mail Setup and Configuration Scripts
Setting up Database Mail for SQL 2005
SQL Server Database Mail and SQL Agent Mail setup by using a script

GridView - Create THeader and TBody

This is useful when we are applying sytle.

    protected void Page_Load(object sender, EventArgs e)
    {
        //Add data to the GridView  
        GridView1.DataSource = SQLHelper.ExecuteDataset("Data Source=SERVER;Initial Catalog=; Uid=; PWD=; Connect Timeout=360; pooling=true; Max Pool Size=200; ", CommandType.Text, "Select * from a, b");
      GridView1.DataBind();

      GenerateHeaders(GridView1);
    }

    private void GenerateHeaders(GridView grdView)
    {
        //HtmlTextWriterStyle style = new HtmlTextWriterStyle();
        //TableRow tr = new TableRow();
        ////tr.Style.Add(HtmlTextWriterStyle
        //tr.Style.Add(style , 
        if grdView.Rows.Count > 0)
        {
            //This replaces  with  and adds the scope attribute
            grdView.UseAccessibleHeader = true;

            //This will add the  and  elements
            grdView.HeaderRow.TableSection = TableRowSection.TableHeader;
            //grid.HeaderStyle.AddAttributesToRender(


            //This adds the  element. Remove if you don't have a footer row
            grdView.FooterRow.TableSection = TableRowSection.TableFooter;
        }
    }

GridView - Confirm MessageBox before Deleting

    public static void AddConfirmDelete(GridView gv, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            foreach (DataControlField dcf in gv.Columns)
            {
                CommandField cf = dcf as CommandField;
                if (cf != null)
                {
                    TableCell cell = e.Row.Cells[gv.Columns.IndexOf(dcf)];
                    foreach (Control ctl in cell.Controls)
                    {
                        switch (cf.ButtonType)
                        {
                            case ButtonType.Button:
                                if (ctl is Button && ((Button)ctl).Text == cf.DeleteText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure Button?\")");
                                }


                                if (ctl is Button && ((Button)ctl).Text == cf.EditText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure EditText?\")");
                                }
                                break;
                            case ButtonType.Image:
                                if (ctl is ImageButton && ((ImageButton)ctl).ImageUrl == cf.DeleteImageUrl)
                                {
                                    ImageButton ib = (ImageButton)ctl;
                                    //ib.OnClientClick = "return confirm(\"Are you sure?\")";
                                    ib.Attributes.Add("onclick", "return confirm(\"Are you sure Image?\")");
                                }

                                if (ctl is Button && ((Button)ctl).Text == cf.EditText)
                                {
                                    Button btn = (Button)ctl;
                                    //btn.OnClientClick = "return confirm(\"Are you sure?\")";
                                    btn.Attributes.Add("onclick", "return confirm(\"Are you sure EditImage?\")");
                                }
                                break;
                            case ButtonType.Link:
                                if (ctl is LinkButton && ((LinkButton)ctl).Text == cf.DeleteText)
                                {
                                    LinkButton lb = (LinkButton)ctl;
                                    lb.Attributes.Add("onclick", "return confirm(\"Are you sure Link?\")");
                                }

                                if (ctl is LinkButton && ((LinkButton)ctl).Text == cf.EditText)
                                {
                                    LinkButton lb = (LinkButton)ctl;
                                    lb.Attributes.Add("onclick", "return confirm(\"Are you sure EditLink?\")");
                                }
                                break;
                        }

                    }
                }
            }
        }
    }

GridView - EmptyData Alignment.

grdView.BorderColor = System.Drawing.Color.White;
grdView.EmptyDataRowStyle.HorizontalAlign = HorizontalAlign.Center;

grdView.EmptyDataRowStyle.CssClass = "EmptyDataRowStyle";

We can set bordercolor for the gridview
grdView.Attributes.Add("bordercolor", "#acc0e9");

GridView - Show Image Empty Grid

In code behind file use this code

gvControl.EmptyDataText = "<img src="http://www.blogger.com/images/NoData.jpg%27" border="0" >";

GridView - Get and Set More than one DataKeyNames

To set more than one DataKeys in grid view,

<asp:GridView ID="grdView" runat="server" DataKeyNames="Name,Email">

To get the Value of the DataKey
DataKey myData = grdView.DataKeys[RowIndex++];

myData.Values["StarRate"];

OR

String value = grdView.DataKeys[RowIndex++].Values["StarRate"];

Empty grid to show the header

If our resultset is contains no rows, the gridview didn't show the header.

To show the header in the gridview ,

We have to desing the gridview header in the Table and put it inside the EmptyDataTemplate of the gridview as given below.

       <asp:GridView ID="GridView1" runat="server" CssClass="th">
           <EmptyDataTemplate>
               <table>
                   <tr>
                       <td>
                           <b>&Namelt;/b></td>
                   </tr>
               </table>
           </EmptyDataTemplate>
       </asp:GridView>

Sql Server Split Function

Fastest split function.

DECLARE @String VARCHAR(MAX),
        @xml AS xml,
        @Delimiter AS VARCHAR(10)
DECLARE @TEMP TABLE (LocID INT, Location VARCHAR(50))

INSERT INTO @Temp
SELECT 9, 'Chennai' UNION ALL
SELECT 13, 'Salem' UNION ALL
SELECT 42, 'Idappadi' UNION ALL
SELECT 32, 'Poolampatti' UNION ALL
SELECT 11, 'Chitoor'

SET @String    = '13,42,47,32,11,,9'
SET @Delimiter =','
SET @xml       = CAST((''+REPLACE(@String, @Delimiter, '')+'') AS XML)

--Split the string
SELECT   N.value('.', 'VARCHAR(MAX)') AS LocationID
FROM     @xml.nodes('x')              AS T ( N )
WHERE    N.value('.', 'VARCHAR(MAX)') <> ''
ORDER BY CAST(N.value('.', 'VARCHAR(MAX)') AS INT)

--How to use it in join with a table
SELECT     LocID,
           Location
FROM       @Temp
           INNER JOIN @xml.nodes('x') AS T ( N )
           ON         N.value('.', 'VARCHAR(MAX)') = LocID

Reference

By using the above batch of query, we can pass ,(comma) separated values as XML from the application.
The size doesn't matter, when we pass the variable as XML from the application,
This is very small query, so no need to write it in a function.
This increase the performance of the query.

--Split Function

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

-----------------------------------------------------------------------------Split Function With Identity Colum

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

---------------------------------------------------------------------------
--Simple split function in www.sqlteam.com

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) 
AS Item  FROM Pieces
)