Detect and Kill Session/ Exclusively Locked Session in SQL Server using SQL

This following query retrieves active Sessions in specific Database.


-- Get Active Sessions 
SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'DATABASE'
--AND name ='Your Database Name'
ORDER BY name

Continue reading

SQL Server Schedule Jobs

Here, I will share some screenshots to show how to set up SQL Server Jobs using SQL server Management Studio.
SQL Server Schedule Jobs Step 1
Step 1: Create New Job. Continue reading

Number to Word Function in SQL Server 2005

I found this handful SQL Script for User Defined Function to convert number to word.


USE [NUBDB]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_Num_ToWords]    Script Date: 10/17/2011 15:45:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_Num_ToWords] (

	@Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000) 

AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) 
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero Taka Only'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3  0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'   

WHILE @counter <= @loops BEGIN

	-- get chunks of 3 numbers at a time, padded with leading zeros
	SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

	IF @chunk  '000' BEGIN
		SELECT @tensones = SUBSTRING(@chunk, 2, 2)
		     , @hundreds = SUBSTRING(@chunk, 1, 1)
		     , @tens = SUBSTRING(@chunk, 2, 1)
		     , @ones = SUBSTRING(@chunk, 3, 1)

		-- If twenty or less, use the word directly from @NumbersTable
		IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
			SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
		    END
		 ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
					         + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
		END

		-- now get the hundreds
		IF @hundreds  '0' BEGIN
			SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
					            + ' hundred ' 
                                + @outputString
		END
	END

	SELECT @counter = @counter + 1
	     , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)

RETURN @outputString + ' Taka Only'
END


Continue reading

firing client validation before client confirmation in asp.net

Client Side Confirmation is simple one using Java Script, Just write this single line in OnClienClick Properties of your Submit Button and set up Client Confirmation Box at Button Click Event.


return confirm('Do you want add this Problem?');

Now, when you have Java Script validation in your page this kind of Client Confirmation will create difficulties. More specifically, at Button Click Confirmation Box will will appear first, if client click Ok Button, Web Page will be submitted to server skipping Client Side Validation. Continue reading

Adding reference to System.Configuration in Visual Studio 2008

To add a reference of System.Configuration/ Configuration in Visual Studio 2008
# In Solution Explorer, right-click the project node and click Add Reference.
# In the Add Reference dialog box, select the .NET tab and select System.Configuration from the list and then click OK.
Continue reading

Oracle Database shutdown and start up Commands

Sometimes, Programmers may have to go through some Database Administrative Tasks on Oracle Development Database. Here, I tried to put together Some Linux/Oracle Commands useful to shutdown and start up Oracle Database.

First of all, Log on to System(Linux) as “oracle” user or su to “oracle” using the following command.


$ su - oracle

Continue reading

Cookie with ASP.NET (C#)

Here, I am providing some helpful code snippets to work with Cookie in ASP.NET. if you don’t have any idea on Cookie, please surf here. This will help to get good idea on Cookie.

This one shows how to set and save values in Cookie.


//Set Cookie
HttpCookie userCookie = new HttpCookie("UserInfo");
userCookie["userName"] = TextBox1.Text;
userCookie["userPassword"] = TextBox2.Text;
userCookie.Expires = DateTime.Now.AddDays(3);
Response.Cookies.Add(userCookie);

Continue reading