/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Thursday, May 29, 2014

Full path URL for images for ASP.NET

C# SOLUTION
imgLogo.Src = System.Web.HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + System.Web.HttpContext.Current.Request.ApplicationPath + "/images/logo.png";

How do I turn a relative URL into a full URL?

public string ConvertRelativeUrlToAbsoluteUrl(string relativeUrl)
{


return string.Format("http{0}://{1}{2}",(Request.IsSecureConnection) ? "s" : "", Request.Url.Host, Page.ResolveUrl(relativeUrl));

}

Reference:- http://ckeditor.com/forums/FCKeditor-2.x/C-SOLUTION-Full-path-URL-for-images-for-.NET-connector

Get Current Page URL Address

Request.ApplicationPath:                   /virtual_dir

Request.CurrentExecutionFilePath:  /virtual_dir/webapp/page.aspx

Request.FilePath :                              /virtual_dir/webapp/page.aspx

Request.Path :                                    /virtual_dir/webapp/page.aspx

Request.PhysicalApplicationPath :   d:\Inetpub\wwwroot\virtual_dir\

Request.QueryString :                       /virtual_dir/webapp/page.aspx?q=qvalue

Request.Url.AbsolutePath :               /virtual_dir/webapp/page.aspx

Request.Url.AbsoluteUri : http://localhost:2000/virtual_dir/webapp/page.aspx?q=qvalue

Request.Url.Host :                             localhost

Request.Url.Authority :                     localhost:80

Request.Url.LocalPath :                    /virtual_dir/webapp/page.aspx

Request.Url.PathAndQuery :            /virtual_dir/webapp/page.aspx?q=qvalue

Request.Url.Port :                             80

Request.Url.Query :                         ?q=qvalue

Request.Url.Scheme :                       http

Request.Url.Segments :                    /virtual_dir/webapp/page.aspx

Hopefully you will find this useful!
Read More »

Friday, May 23, 2014

How to get the cell value by column name not by index in GridView in asp.net

GridView does not act as column names, as that's it's `datasource` property to know those things.

If you still need to know the index given a column name, then you can create a helper method to do this as the `gridview` Header normally contains this information.

    int GetColumnIndexByName(GridViewRow row, string columnName)
    {
        int columnIndex = 0;
        foreach (DataControlFieldCell cell in row.Cells)
        {
            if (cell.ContainingField is BoundField)
                if (((BoundField)cell.ContainingField).DataField.Equals(columnName))
                    break;
            columnIndex++; // keep adding 1 while we don't have the correct name
        }
        return columnIndex;
    }

remember that the code above will use a `BoundField`... then use it like:

    protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int index = GetColumnIndexByName(e.Row, "myDataField");
            string columnValue = e.Row.Cells[index].Text;
        }
    }

I would strongly suggest that you use the `TemplateField` to have your own controls, then it's easier to grab those controls like:


   
       
           
               
                   
               
           
       
   


and then use

    string columnValue = ((Label)e.Row.FindControl("lblName")).Text;

Read More »

Thursday, May 22, 2014

identity increment and identity seed in sql server

Syntax
IDENTITY [ ( seed , increment ) ]
Arguments
seed
Is the value that is used for the very first row loaded into the table.
increment
Is the incremental value that is added to the identity value of the previous row that was loaded.
You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Read More »

Wednesday, May 21, 2014

Using Merge query in sql server

Using Merge query in sql server

Use [example]

Target Table

CREATE TABLE dbo.Customers
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  [address]     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);
INSERT INTO dbo.Customers(custid, companyname, phone, address)
  VALUES
  (1, 'cust 1', '(111) 111-1111', 'address 1'),
  (2, 'cust 2', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (4, 'cust 4', '(444) 444-4444', 'address 4'),
  (5, 'cust 5', '(555) 555-5555', 'address 5');

Source Table

CREATE TABLE dbo.CustomersStage
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);
INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
  VALUES
  (2, 'AAAAA', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (5, 'BBBBB', 'CCCCC', 'DDDDD'),
  (6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
  (7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

Using merge query

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid
WHEN MATCHED THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.[address] = SRC.[address]
WHEN NOT MATCHED THEN
  INSERT (custid, companyname, phone, [address])
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.[address])
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT
  $action, deleted.custid AS del_custid, inserted.custid AS ins_custid;
  select * from Customers
  select * from CustomersStage


Read More »

Merge query in sql server 2008

Create a target table

CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO

Insert records into target table

INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO

Create source table

CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO

Insert records into source table

INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

Synchronize the target table with refreshed data from source table

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)

--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate

--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

--When there is a row that exists in target table and same record does not exist in source table 
--then delete this record from target table

WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
Read More »

Select Top nth salary from table in sql server salary query

Top 3rd Amt Query

SELECT MIN(Amt) AS Amt FROM Salary WHERE Amt IN (SELECT TOP 3 Amt FROM Salary ORDER BY Amt DESC)

Second Highest Amount

SELECT MAX(Amt) FROM Salary WHERE Amt NOT IN (SELECT MAX(Amt) FROM Salary)

Top Nth Highest Amt

SELECT TOP 1 Amt FROM (SELECT DISTINCT TOP N Amt FROM B ORDER BY Amt DESC)PARA ORDER BY Amt

SELECT TOP 1 Amt FROM (SELECT DISTINCT TOP 5 Amt FROM B ORDER BY Amt DESC)PARA ORDER BY Amt

Using Row_Number() Method

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (order by Amt DESC) AS RANK FROM Salary) v where RANK = 5;

SELECT Amt FROM (SELECT *, ROW_NUMBER() OVER (order by Amt DESC) AS RANK FROM Salary) v where RANK = 5;

Read More »

Part-2 Grouping Sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

Part-2 Grouping Sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

CREATE TABLE tbl_Employee 

      Employee_Name varchar(25), 
      Region varchar(50), 
      Department varchar(40), 
      sal int 
)

truncate table tbl_Employee

INSERT into tbl_Employee( 
                              Employee_Name, 
                              Region, 
                              Department, 
                              sal 
                        ) 

VALUES 

('Shujaat', 'North America', 'Information Technology', 9999), 
('Andrew', 'Asia Pacific', 'Information Technology',  5555), 
('Maria', 'North America', 'Human Resources', 4444), 
('Stephen', 'Middle East & Africa', 'Information Technology', 8888), 
('Stephen', 'Middle East & Africa', 'Human Resources', 8888)

select * from tbl_Employee

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      GROUPING SETS 
      ( 
            (Region, Department), 
            (Region), 
            (Department) , 
            ()          
      )
      
---------------------------------------------------      
SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      Region, Department 
UNION 


SELECT Region, NULL, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Region 

UNION 

SELECT NULL, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Department 

UNION 

SELECT NULL, NULL, avg(sal) Average_Salary 

from tbl_Employee   
-----------------------------------------------   


---- CUBE subclause for grouping This is used to return the power n to 2 for n elements.-----
SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      CUBE (Region, Department)
---------------------------------------------------

------ROLLUP subclause for grouping---------------
SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      ROLLUP (Region, Department)
---------------------------------------------------

---------------------------------------------------
SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Grouping Sets 
      ( 
            (Region, Department), 
            (Region), 
            () 
      )
---------------------------------------------------
Read More »

Grouping sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

Grouping Sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATETIME   NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20060802', 3, 'A', 10), (10001, '20061224', 1, 'A', 12),
  (10005, '20061224', 1, 'B', 20), (40001, '20070109', 4, 'A', 40),
  (10006, '20070118', 1, 'C', 14), (20001, '20070212', 2, 'B', 12),
  (40005, '20080212', 4, 'A', 10), (20002, '20080216', 2, 'C', 20),
  (30003, '20080418', 3, 'B', 15), (30004, '20060418', 3, 'C', 22),
  (30007, '20060907', 3, 'D', 30);

select * from Orders

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
  ( custid, empid, YEAR(orderdate) ),
  ( custid, YEAR(orderdate)        ),
  ( empid, YEAR(orderdate)         ),
  () );

--CUBE produces 2^n grouping sets.
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty FROM dbo.Orders
GROUP BY cube(custid,empid,orderdate)

--The ROLLUP subclause produces only the grouping sets that have business value
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty FROM dbo.Orders
GROUP BY ROLLUP(custid,empid,orderdate)

--Using GROUPING_ID() function
SELECT 
  GROUPING_ID(
    custid, empid,
    YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ) AS grp_id,
  custid, empid,
  YEAR(orderdate) AS orderyear,
  MONTH(orderdate) AS ordermonth,
  DAY(orderdate) AS orderday,
  SUM(qty) AS qty
FROM dbo.Orders
GROUP BY
  CUBE(custid, empid),
  ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate))
  order by grp_id;





Read More »

Using sql server generating random number

Using sql server generating random number

SELECT randomNumber
FROM (SELECT ABS(CAST(NEWID() AS binary(6)) %50) + 1 randomNumber FROM sysobjects) sample GROUP BY randomNumber
--ORDER BY randomNumber

Declare @Random varchar(10)
set @Random = CONVERT(varchar(10), right(newid(),10))
SELECT TOP 50 randomNumber
FROM (SELECT CAST(ABS(CAST(NEWID() AS binary(6)) %50) + 1 AS VARCHAR(10)) + CONVERT(varchar(10), right(newid(),10)) randomNumber
FROM sysobjects) sample
GROUP BY randomNumber

SELECT 20*RAND()   

----Method 1 : Generate Random Numbers (Int) between Rang
------ Create the variables for the random number generation
--DECLARE @Random INT
--DECLARE @Upper INT
--DECLARE @Lower INT

------ This will create a random number between 1 and 999
--SET @Lower = 1 ---- The lowest random number
--SET @Upper = 50 ---- The highest random number
--SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @Random
Read More »

Some important user defined function in sql server

Creating user defined function for splitting string values with comma separated

CREATE FUNCTION [fn_SplitComma](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
        declare @idx int
        declare @slice varchar(8000)
    

        select @idx = 1
                if len(@String)<1 font="" is="" nbsp="" null="" or="" return="" tring="">
    

        while @idx!= 0
        begin
                set @idx = charindex(@Delimiter,@String)
                if @idx!=0
                        set @slice = left(@String,@idx - 1)
                else
                        set @slice = @String
                

                if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)


                set @String = right(@String,len(@String) - @idx)
                if len(@String) = 0 break
        end
return
end
_________________________________________________________________________________

Read More »

Using sql server search column name in all tables within a database

Search column name in all tables in complete database

SELECT 
t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE 'ID%'
ORDER BY schema_name, table_name;
Read More »

Using sql server deleting duplicate rows from a table

--Deleting duplicates rows from a table in different way--

select * from t1
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')

-- Query 1 using Group By and Having --

SELECT col1, col2, count(*) FROM t1
GROUP BY col1, col2 HAVING count(*) > 1

-- Query 2 using common type expression CTE

With temptable as   
(  
Select ROW_NUMBER() over (PARTITION BY col1, col2 ORDER BY col1) AS rownumber,* FROM t1
)  
delete from temptable where rownumber > 1  
 
-- Query 3 using sub queries --   

select * from t1 where col1 not in(select min(col1) from t1 group by col1,col2)   
delete from t1 where col1 not in(select min(col1) from t1 group by col1,col2) 


-- Query 4 using row_number() over function --  

Delete t From
(
Select row_number() Over (Partition By col1, col2 Order By col1) As sno, * From t1
) As t
Where sno>1

-- Query 5 using distinct --

SELECT DISTINCT * INTO t2 FROM t1 
DROP TABLE t1
EXEC sp_rename t2,t1


Read More »

Deleting whole data from database using sql server query

--***************  DELETE ALL DATA FROM DATABASE  ***************--

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable ' 
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 

GO
--*********************  End of Query   *********************--
Read More »

Handling deadlock conditions in sql server


First create database structure: Run the following complete query:

--- db script start from here ---

utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140325

USE [master]
GO
CREATE DATABASE TestDB
 ON PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB)
GO

-----------------------------------------------------------------------------------------------------

USE [master]
GO
/****** Object:  Database [TESTDB]    Script Date: 05/21/2014 15:22:06 ******/
CREATE DATABASE [TESTDB] ON  PRIMARY 
( NAME = N'TESTDB', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TESTDB.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TESTDB_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TESTDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TESTDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TESTDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TESTDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TESTDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TESTDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TESTDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TESTDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TESTDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TESTDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TESTDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TESTDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TESTDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TESTDB] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TESTDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TESTDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TESTDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TESTDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TESTDB] SET  DISABLE_BROKER
GO
ALTER DATABASE [TESTDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TESTDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TESTDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TESTDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TESTDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TESTDB] SET  READ_WRITE
GO
ALTER DATABASE [TESTDB] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TESTDB] SET  MULTI_USER
GO
ALTER DATABASE [TESTDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TESTDB] SET DB_CHAINING OFF
GO
USE [TESTDB]
GO
/****** Object:  Table [dbo].[Sales]    Script Date: 05/21/2014 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Sales](
[EmpId] [int] NULL,
[Yr] [int] NULL,
[Sales] [money] NULL,
[CreatedDate] [datetime] NOT NULL,
[AddedBy] [varchar](200) NULL,
[CreatedBy] [varchar](200) NULL,
[CreatedOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SalaryType]    Script Date: 05/21/2014 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalaryType](
[ID] [varchar](10) NULL,
[Descrp] [varchar](50) NULL,
[CreatedDate] [datetime] NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[SalaryType] ADD [AddedBy] [varchar](200) NULL
ALTER TABLE [dbo].[SalaryType] ADD [DeletedBy] [varchar](200) NULL
ALTER TABLE [dbo].[SalaryType] ADD [CreatedBy] [varchar](200) NULL
ALTER TABLE [dbo].[SalaryType] ADD [CreatedOn] [datetime] NOT NULL
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Customers]    Script Date: 05/21/2014 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] [nvarchar](10) NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[CustomerAddress] [nvarchar](50) NOT NULL,
[LastModified] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
[CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Customers] ON
INSERT [dbo].[Customers] ([CustomerId], [CustomerCode], [CustomerName], [CustomerAddress], [LastModified]) VALUES (2, N'A15C6E', N'John Doe', N'Evergreen 1234', CAST(0x0000A30300D27AAD AS DateTime))
INSERT [dbo].[Customers] ([CustomerId], [CustomerCode], [CustomerName], [CustomerAddress], [LastModified]) VALUES (3, N'A15C6E', N'John Doe', N'Evergreen 1234', CAST(0x0000A30300D2860D AS DateTime))
INSERT [dbo].[Customers] ([CustomerId], [CustomerCode], [CustomerName], [CustomerAddress], [LastModified]) VALUES (5, N'A15C6E', N'John Doe', N'Evergreen 1234', CAST(0x0000A30300D632CE AS DateTime))
INSERT [dbo].[Customers] ([CustomerId], [CustomerCode], [CustomerName], [CustomerAddress], [LastModified]) VALUES (6, N'A15C6E', N'John Doe', N'Evergreen 1234', CAST(0x0000A30300D63E46 AS DateTime))
INSERT [dbo].[Customers] ([CustomerId], [CustomerCode], [CustomerName], [CustomerAddress], [LastModified]) VALUES (7, N'A15C6E', N'John Doe', N'Evergreen 1234', CAST(0x0000A33200FC6957 AS DateTime))
SET IDENTITY_INSERT [dbo].[Customers] OFF
/****** Object:  Default [DF__Customers__LastM__03317E3D]    Script Date: 05/21/2014 15:22:06 ******/
ALTER TABLE [dbo].[Customers] ADD  DEFAULT (getdate()) FOR [LastModified]
GO

-----------------------------------------------------------------------------------------------------

USE TestDB
GO
IF OBJECT_ID('dbo.Customers','U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE dbo.Customers(
  CustomerId        INT NOT NULL IDENTITY(1,1),
  CustomerCode      NVARCHAR(10) NOT NULL,
  CustomerName      NVARCHAR(50) NOT NULL,
  CustomerAddress   NVARCHAR(50) NOT NULL,
  LastModified  DATETIME NOT NULL DEFAULT  GETDATE()
  PRIMARY KEY CLUSTERED (CustomerId)
)
GO

---RUN This query in two different windows
USE TestDB
GO
BEGIN TRANSACTION
INSERT INTO dbo.Customers ( CustomerCode ,
          CustomerName ,
          CustomerAddress
        )
VALUES  ( N'A15C6E' ,
          N'John Doe' ,
          N'Evergreen 1234'
        )
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Customers
COMMIT TRANSACTION

USE TestDB
GO
TRUNCATE TABLE dbo.Customers

-----------------------------------------------------------------------------------------------------

USE TestDB
GO
DECLARE @RetryCount INT
DECLARE @Success    BIT
SELECT @RetryCount = 1, @Success = 0
WHILE @RetryCount < =  3 AND @Success = 0
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
      -- This line is to show you on which execution 
      -- we successfully commit.
      SELECT CAST (@RetryCount AS VARCHAR(5)) +  'st. Attempt'
  
      INSERT  INTO dbo.Customers
      ( CustomerCode ,
      CustomerName ,
      CustomerAddress
      )
      VALUES  ( N'A15C6E' ,
      N'John Doe' ,
      N'Evergreen 1234'
      )

      -- This Delay is set in order to simulate failure
      -- DO NOT USE IN REAL CODE!
      WAITFOR DELAY '00:00:05'
  
      SELECT * FROM dbo.Customers
  
      COMMIT TRANSACTION
  
      SELECT 'Success!'
      SELECT @Success = 1 -- To exit the loop
   END TRY

   BEGIN CATCH
      ROLLBACK TRANSACTION

      SELECT  ERROR_NUMBER() AS [Error Number],
      ERROR_MESSAGE() AS [ErrorMessage];     
  
      -- Now we check the error number to 
      -- only use retry logic on the errors we 
      -- are able to handle.
      --
      -- You can set different handlers for different 
      -- errors
      IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
                              1205, -- SqlDeadlockVictim
                              1222 -- SqlLockRequestTimeout
                              )
      BEGIN
            SET @RetryCount = @RetryCount + 1  
            -- This delay is to give the blocking 
            -- transaction time to finish.
            -- So you need to tune according to your 
            -- environment
            WAITFOR DELAY '00:00:02'  
      END 
      ELSE    
      BEGIN
           --THROW 50001, 'Your custom error message.', 1;
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop
            --THROW 50001; -- THROW Statement is introduced in SQL Server 2012
            --RAISERROR('TEST',12,1);
           
            DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
  @ErrorSeverity, -- Severity.
  @ErrorState -- State.
  );                
            
      END
   END CATCH
END

-----------------------------------------------------------------------------------------------------
Reference:- http://www.mssqltips.com/sqlservertip/3188/implementing-sql-server-transaction-retry-logic-for-failed-transactions/?



Read More »

Using cursor in sql server

This cursor will return all the stored procedures within a database


DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name FROM sysobjects s WHERE type = 'P'
OPEN @getprocName
FETCH NEXT FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_HelpText @procName
FETCH NEXT FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
Read More »

Creating temporary table in sql server

Here is the use of temporary table inside a stored procedure: 

create procedure [dbo].[usp_CreatingTemporaryTable]
as 
begin
--creating temporary table here
create table #temp
(
name varchar(50),
address varchar(150)
)

--select * from yourtablename
insert into #temp select column1, column2 from yourtablename 

select distinct column1 from #temp
select * from #temp
end
--Finally drop temporary table
drop table #temp
Read More »

Using char varchar in sql server

Char Vs Varchar

Few example showing the difference between char and varchar:

DECLARE @CharName Char(30) = 'Yogesh Chandra Upreti',
@VarCharName VarChar(30) = 'Yogesh Chandra Upreti'
SELECT DATALENGTH(@CharName) CharSpaceUsed,DATALENGTH(@VarCharName) VarCharSpaceUsed


DECLARE @FirstName Char(20) = 'YogeshChandra',
@LastName Char(20) = 'Upreti
IF @FirstName + ' ' + @LastName = 'YogeshChandra Upreti
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...' 
SELECT  @FirstName + ' ' + @LastName AS Name, len(@FirstName + ' ' + @LastName) AS Length


DECLARE @FirstName VarChar(20) = 'YogeshChandra',
@LastName VarChar(20) = 'Upreti
IF @FirstName + ' ' + @LastName = 'YogeshChandra Upreti
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...' 
SELECT  @FirstName + ' ' + @LastName AS Name, len(@FirstName + ' ' + @LastName) AS Length






Read More »

SQL SERVER Add common column to each tables in sql server

JUST WRITE THIS QUERY :


print 'Add  Column for each user table'

EXEC sp_msforeachtable
 '
---- add column
exec(''
 declare @tableName as nvarchar(max)
 set @tableName = ''''_?_''''

 IF (CHARINDEX(''''aspnet_'''',@tableName) = 0) -- no aspnet membership
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''?'''') AND type in (N''''U'''')
 AND name = (''''YOUR_TABLE_NAME_HERE'''')

 )
 BEGIN

 IF columnproperty(object_id(''''?''''), ''''CreatedBy'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedBy] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''CreatedOn'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [CreatedOn] [datetime]  NULL
 END

 IF columnproperty(object_id(''''?''''), ''''UpdatedBy'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [UpdatedBy] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''UpdatedOn'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [UpdatedOn] [datetime]  NULL
 END

 IF columnproperty(object_id(''''?''''), ''''deletedby'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [deletedby] [nvarchar](100) NULL
 END

 IF columnproperty(object_id(''''?''''), ''''deletedon'''', ''''ColumnId'''') is null
 BEGIN
 ALTER TABLE ? ADD [deletedon] [datetime]  NULL
 END

 END
 END
'')

' ;

print ' Column is created for each user table'

Read More »

Tuesday, May 20, 2014

SQL Server Transaction Retry Logic for failed transactions

Implementing SQL Server Transaction Retry Logic for failed transactions


Problem
Under heavy contention your transactions could be the victim of a deadlock and therefore be rolled back. In this tip I will show you how to implement retry logic to re-run the failed transaction.
Solution
We all know that every RDBMS system has to guarantee the ACID principle (Atomicity, Consistency, Isolation and Durability). A transaction must be either committed or rolled back entirely (Atomicity). SQL Server cannot commit half a transaction because doing so will violate the second principle (Consistency). To keep consistency, concurrent transactions must be independent of each other (Isolation) and changes must persist (Durability).
Although this makes database systems reliable in most circumstances, following these properties is difficult and drastic measures are sometimes taken by SQL Server or any other RDBMS.  That's where deadlocks come to light.
A deadlock happens when two or more tasks block each other because each task has a lock on a resource that the other task(s) are trying to lock. Although you can set deadlock priority for each transaction by using SET DEADLOCK_PRIORITY option, one of them will be killed and you will get this error 1205: "Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
But transaction retry logic isn't limited to correcting deadlocks; there are several other circumstances in which you may want to retry a failed transaction like server timeouts, errors due to concurrent schema modification operations and so on.

SQL Server 2014's Memory-Optimized Tables and Transaction Retry
In SQL Server 2014, the In-Memory OLTP Engine (Hekaton) uses a lock free and latch free optimistic concurrency control and deadlocks cannot occur. But transactions in Hekaton have a validation phase that can set a transaction into a doomed state because of commit dependency failures or isolation level conflicts.

Here is a table with the error numbers you can face by using Memory-Optimized tables.


Error Number
Description
41302
The current transaction attempted to update a record that has been updated since the transaction started.
41305
The current transaction failed to commit due to a repeatable read validation failure.
41325
The current transaction failed to commit due to a serializable validation failure.
41301
A previous transaction that the current transaction took a dependency on has aborted, and the current transaction can no longer commit

SQL Server Transaction Retry Logic

The approach is really simple and requires little code modification. Basically it consists of enclosing the transaction into aTRY ...CATCH block. The TRY ...CATCH block was made available with versions 2005 and above, so if you are still using SQL Server 2000 this is a good reason to migrate.
Without getting into much detail, a TRY ...CATCH block consists of two sections: one contains the actions you want to perform (the TRY section), and the other is what to do if something goes wrong with those actions (the CATCH section). The power of TRY ...CATCH blocks relies on allowing us to trap errors without aborting the batch.
At this point we have half of the problem solved: we are able to handle the exception without aborting the batch and the other half is to find a way to re-execute the batch.
In programming when we talk about repeating an action, we talk about loops. And this is the way: we set a maximum retries count, say three and then repeat our transaction either until it commits or until the maximum retry count is reached. To do so, we have two possibilities: WHILE loops or GOTO unconditional jumps.
The following image is a graphical representation of previous statements, so you can see that retry logic can be easily implemented without too much code.











For those of you who are more into structured programming the next image represents a flow chart of our transaction retry algorithm.



Sample SQL Server Transaction Retry Code

Now it's time to do our coding. I will guide you through all the steps.

1 - First we create our sample database

USE [master]
GO
CREATE DATABASE TestDB
 ON PRIMARY
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED ,
FILEGROWTH = 1024KB)
GO

2 - This will be our test table

USE TestDB
GO
IF OBJECT_ID('dbo.Customers','U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE dbo.Customers(
  CustomerId        INT NOT NULL IDENTITY(1,1),
  CustomerCode      NVARCHAR(10) NOT NULL,
  CustomerName      NVARCHAR(50) NOT NULL,
  CustomerAddress   NVARCHAR(50) NOT NULL,
  LastModified  DATETIME NOT NULL DEFAULT  GETDATE()
  PRIMARY KEY CLUSTERED (CustomerId)
)
GO

3 - Simulating our deadlock

I am making it very simple: a single transaction consisting of an INSERT to Customers table followed by a delay of ten seconds and then a SELECT to the same table.

You must paste this code in two separate windows and run it simultaneously.

USE TestDB
GO
BEGIN TRANSACTION
INSERT INTO dbo.Customers ( CustomerCode ,
          CustomerName ,
          CustomerAddress
        )
VALUES  ( N'A15C6E' ,
          N'John Doe' ,
          N'Evergreen 1234'
        )
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Customers
COMMIT TRANSACTION

Here is a screen capture with the execution of the previous code from two different query windows (session 56 and session 58).





As you can see only one window successfully committed and the other one failed to commit due to Error 1205 which is Transaction was deadlocked and has been chosen as the deadlock victim.

4 - To keep our testing clean let's truncate our sample table

USE TestDB
GO
TRUNCATE TABLE dbo.Customers

5 - Sample Retry Logic Implementation

At first this code may be scary by its length, but keep in mind that I added more steps than needed in order to explain how it works.

As before, you must paste this code in two separate windows and run it simultaneously.

USE TestDB
GO
DECLARE @RetryCount INT
DECLARE @Success    BIT
SELECT @RetryCount = 1, @Success = 0
WHILE @RetryCount < =  3 AND @Success = 0
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
      -- This line is to show you on which execution
      -- we successfully commit.
      SELECT CAST (@RetryCount AS VARCHAR(5)) +  'st. Attempt'
 
      INSERT  INTO dbo.Customers
      ( CustomerCode ,
      CustomerName ,
      CustomerAddress
      )
      VALUES  ( N'A15C6E' ,
      N'John Doe' ,
      N'Evergreen 1234'
      )

      -- This Delay is set in order to simulate failure
      -- DO NOT USE IN REAL CODE!
      WAITFOR DELAY '00:00:05'
 
      SELECT * FROM dbo.Customers
 
      COMMIT TRANSACTION
 
      SELECT 'Success!'
      SELECT @Success = 1 -- To exit the loop
   END TRY

   BEGIN CATCH
      ROLLBACK TRANSACTION

      SELECT  ERROR_NUMBER() AS [Error Number],
      ERROR_MESSAGE() AS [ErrorMessage];    
 
      -- Now we check the error number to
      -- only use retry logic on the errors we
      -- are able to handle.
      --
      -- You can set different handlers for different
      -- errors
      IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
                              1205, -- SqlDeadlockVictim
                              1222 -- SqlLockRequestTimeout
                              )
      BEGIN
            SET @RetryCount = @RetryCount + 1 
            -- This delay is to give the blocking
            -- transaction time to finish.
            -- So you need to tune according to your
            -- environment
            WAITFOR DELAY '00:00:02' 
      END
      ELSE   
      BEGIN
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop
            THROW;
      END
   END CATCH
END

The next two images speak for themselves. We have successfully committed both transactions without errors or warnings.








Read More »

Recent Posts

My Blog List