Search

Monday, March 25, 2013

Left Padding Numerics with Leading Zeros

Many times you may need to show numeric data with a fixed length. If you cast an int to a string datatype you often get a bunch of trailing spaces. What if you convert the int to a fixed length string with STR(), thereby right-aligning the int data, and then change the leading spaces to zero characters with REPLACE(). 

Check example to show the integer 24 as a six character fixed length string with leading zeros:

DECLARE @i int
SET @i = 24
SELECT REPLACE(STR(@i, 6, 0), ' ', '0')

Monday, March 18, 2013

Split Comma-Separated Strings into Table

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.SplitString(@String VarChar(8000), @Delimiter Char(1))
returns @TempTable TABLE (Data VarChar(8000))
AS
BEGIN
    DECLARE @iTemp Int
    DECLARE @Value VarChar(8000)

    SELECT @iTemp = 1       
        IF len(@String) < 1 or @String is null return

    WHILE @iTemp != 0
   BEGIN
   SET @iTemp = CharIndex(@Delimiter, @String)

       IF @iTemp != 0
       SET @Value = Left(@String, @iTemp - 1)
ELSE
SET @Value = @String

       IF (len(@Value) > 0)
       INSERT INTO @temptable(Data) Values(@Value)

       SET @String = Right(@String, len(@String) - @iTemp)
   IF len(@String) = 0 Break
END
return
END

Aboce function can be used as

SELECT TOP 5 * FROM dbo.SplitString('Lake Town,Bangur Avenue,Kestopur, Baguiati',',')  

Monday, March 11, 2013

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

You must have seen below error while renaming or deleting database. 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

Above error normally occurs when the database is in Multi User mode where users are accessing your database or some objects are referring to your database. 

To resolve this error.
First set the database in Single user mode. 
ALTER DATABASE DemoData SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Perform the required action.

Again set the database in multi user mode.

ALTER DATABASE DemoData SET MULTI_USER WITH ROLLBACK IMMEDIATE

Monday, March 4, 2013

Restart SQL Server from command prompt

Sometime time you may need to restart SQL server from command prompt. Below command is used to stop and start SQL Server: 


Net Stop mssql$YourSQLServerInstanceName
Net Start mssql$YourSQLServerInstanceName