Paresh's Blog

SQL Pivoting on multiple columns

2016-03-30T04:33:00.000-07:00

We can create pivoting on multiple columns as below.

Query:

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL BEGIN

DROP TABLE #tbl

END

CREATE TABLE #tbl(

Dt DATE,

Acnt INT,

BCnt INT,

CCnt INT

)

DECLARE

@coldata VARCHAR(500),

@sql varchar(max)

INSERT INTO #tbl

VALUES('2016-01-01',10,20,30),

('2016-01-02',40,50,60)

select * from #tbl

SELECT @coldata = COALESCE(@coldata + '], [', '') + FORMAT(Dt, 'yyyy-MM-dd') FROM #tbl

ORDER BY dt desc

SELECT @coldata = '[' + @coldata +']'

SET @sql = 'SELECT Dt=col, ' + @coldata + '

FROM

( SELECT FORMAT(Dt, ''yyyy-MM-dd'') AS Dt, col, value FROM #tbl

CROSS APPLY

(

SELECT ''Acnt'',cast(Acnt as varchar(10)) UNION ALL

SELECT ''BCnt'', cast(BCnt as varchar(10)) UNION ALL

SELECT ''CCnt'', cast(CCnt as varchar(10))

) c (col,value)

) d

PIVOT

(

MAX(value) FOR Dt IN (' + @coldata + ')

) AS piv'

EXECUTE (@SQL);

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL BEGIN

DROP TABLE #tbl

END

Result:

Actual result

With Pivot

How to Split the string value into different column value

2013-08-08T01:49:00.000-07:00

We have split string and populate its value into rows. For example “A, B, C” is split and converted into rows with single column value like:

But how can we split the string and put their values in different columns. Suppose we have person name in single column and his name is separated using comma for first name, last name and middle name, we need to split that string from that column and need to populate all these three values in individual column.

It can be done using the REPLACE() and PARSENAME() SQL functions.

Here is the example that demonstrates the use of PARSE function for splitting the string values and put that into the different column values.

DECLARE @name NVARCHAR(100)

SET @name='FName,MName,LName'

Now we replace the comma value with “.”, that is require for parsing the string like array in C#.

Replace the string with “.” Instead “,” using:

SET @name=REPLACE(@name,',','.')

Now finally we parse the person name into different column values by PARSENAME() function.

SELECT

PARSENAME(@name,3) FName,

PARSENAME(@name,2) MName,

PARSENAME(@name,1) LName

Execute the whole query and it gives the result as:

Fetch data from remote server using OPENROWSET in SQL Server

2013-06-20T23:38:00.000-07:00

We are fetching the data from table in database with in server or fetching data from different database with in server. But sometimes we require fetching data from different database which is not in same server but it is in different server.

For different database but with in same server we can get records easily by “.(dot)” like:

SELECT * FROM DatabaseName.Schema.Table

Now let see how we can get the records from table from database which is not in same server and it is situated at different server.

This can be possible by the Linked Server also. However if we do not want to create link server and need to get the data then we have option to use “OpenRowSet”.

There are three parameters use in OpenRowSet which are:

Provider
Server (IP Address) with credentials (username/password) and database
Query (which is going to be executed with that server’s database)

Here is the syntax use to get data by OpenRowSet

OPENROWSET('SQLNCLI','Server=serverIP;Uid=UserName;Pwd=password;Database=DBName;','SELECT * FROM tablename')

Before executing this query we need to set some option which is requires to run the OpenRowSet query.

Set this option:

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO;

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO;

After setting these option run the below query with proper required details:

SELECT

FS.*

FROM OPENROWSET('SQLNCLI','Server=serverIP;Uid=UserName;Pwd=password;Database=DBName;','SELECT * FROM tablename') AS FS;

This will get the records from table which is not on same server but from remote server which is created in database on given Server.

This way it works without creating Linked Server or without using the Import/Export option which already given to export the data from one server to other server by specifying the source and destination.

We can import/export the data from one database server to other database server’s table by:

Right click on database >> Go to Task >> Go to Import/Export

Hope this helps…..

Allow saving changes that require table re-creation while changing table design

2013-06-12T23:51:00.002-07:00

Sometimes we have a situation that we need to change design of table either changing the size of column or type of column or we need to add new column into the existing table.

If we are doing table changes through wizard and we face an issue sometimes that SQL doesn't allow us to save the changes and give the restriction message like “saving changes is not permitted” because of either tables has data and it requires to be re created or something that not allowed with SQL rules.

See the below screen that comes when you change anything in table structure and trying to save. It not comes every time but sometimes it validates the table structure with internal mapping of type, constraints and data.

In above image we can see that it prevents to save table changes and gives the validation message that it needs to be recreated and the option to allow is not selected for this kind of message.

So here we should allow this by setting the option in SQL Server “Tool” menu.

Go to the Tool and then go to the Option, click on the Designer item in left tree then select the Table and Database Designer. We will get the right side some of the option as showing in to the below screen.

In above screen unchecked the “Prevent saving changes that require table re-creation” option and then click on OK.

After setting this option again try to save table structure changes, Now that saving permission message has been removed and it allow for the further changes.

Delete Vs Truncate in SQL Server

2013-06-11T23:18:00.000-07:00

We will see in this post How delete and truncate will work with temp table.

We have use many times truncate and delete command with Database Table and it works perfectly. It will allow truncating the table if that table doesn't have any records or if it has records then that table must not have any reference with other table.

If the table has reference with other table then we can not truncate table without deleting or truncating the reference table records. But we can delete the records from the reference table.

Now here we see how the truncate and delete command works with the Temp Table and see the difference between them.

We will see with the below examples.

Truncate:

Create one temp table with an identity field like below:

CREATE TABLE #Test

(

ID INT IDENTITY(1,1),

Name VARCHAR(10)

)

Insert some recors into #Test Table

INSERT INTO #Test

VALUES('PP'),('AA')

See the output using:

SELECT * FROM #Test

Here you can see there two records are inserted with identity value. Now we are truncating the temp table and inserting again these records and sees what will happen.

It has truncated the table and also reset the identity value to its original seed.

Delete:

Now we delete the records from temp table instead truncate and then again insert the records into temp table and see what will happen.

Let see:

We can see in above result that after deleting the records it has next identity value in ID field and it will insert the next increment value into the ID field.

Remove the table after execution using this:

DROP TABLE #Test

So here we conclude that the Truncate command will delete the records from table and also reset the identity value if any field has an identity set and reset it to the original seed value. While Delete command just deletes the record from table and not resetting any value for identity column.

Check the size of database files in SQL Server

2013-06-11T02:04:00.002-07:00

How can we check the size of particular database files like .ldf and .mdf?

We can check the size which is occupied by the database files using the sys.database_files view.

To check the size of database files .ldf and .mdf use the below query:

use databasename

SELECT

Name AS DatabaseName

,Physical_name AS DBPath

,(size * 8)/1024 AS SizeMB

FROM sys.database_files

Output:

Here it shows the Database file name with location of file and size in MB unit.

Default character limit for VARCHAR DataType

2013-06-09T23:20:00.001-07:00

We use many time even mostly VARCHAR DataType in SQL Server. But some time we get issue or confusion with the default limit or character size of VARCHAR datatype, and that comes to the logical error with your queries or in your compile code (Stored Procedure) when execute it and gives the wrong output.

We will see how it works when we do not define size with VARCHAR datatype and give us the unexpected output.

See the below two scenarios how VARCHAR datatype works with query:

Scenario 1:

1. VARCHAR datatype With Variable

--Varchar used with variable

DECLARE @a VARCHAR

SET @a = 'Paresh'

SELECT @a AS Value

Output:

Now here in this scenario we can see it gives output as only 1 character because when we use VARCHAR DataType with variable and not define any size it takes only single character size default.

Scenario 2:

2. VARCHAR datatype with Convert and Cast function

--Varchar used with CONVERT or CAST Fucntion

SELECT CONVERT(VARCHAR,'aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh') AS Value

SELECT CAST('aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh' AS VARCHAR) AS Value

Output:

Here we can see that when VARCHAR DataType use with Convert or Cast function then it takes the 30 character as default size for it and give the output with only 30 characters.

So here we need to keep in mind that when we use VARCHAR Datatype then MUST give the size whatever it requires as per the use.

DROP multiple tables using single DROP statement

2013-06-07T02:25:00.000-07:00

We know that how to drop a table. But we can also drop multiple tables using only single drop statement. How? Let see using the below example:

Just create multiple tables like:

CREATE TABLE t1(ID1 INT)

CREATE TABLE t2(ID2 INT)

CREATE TABLE t3(ID3 INT)

Try to select the table data to confirm that tables are created successfully by:

SELECT * FROM t1

SELECT * FROM t2

SELECT * FROM t3

Tables are created, Now we are going to drop those tables using single drop statement as below:

DROP TABLE t1,t2,t3

That’s work perfectly..

Insert default value for newly adding column in existing records in table in SQL Server.

2013-05-31T23:24:00.001-07:00

Here we see how we insert default values in newly added column in table which already has some records in it.

Sometime we are in situation that we need to add new column and we need to assign default property to that column. If the table in which we add new column is empty then we don’t have an issue to set default values but if that table already having some records and we are adding new column with default value then either we need to be empty the table or update the records with default values.

Now in this case if there is lots of record in table then it is not easy to do this. But we have one simple way to come out of this very easily.

Simply create one table with two columns, insert some records into it:

CREATE TABLE TestTable

(

ID INT,

Name VARCHAR(10)

)

INSERT INTO TestTable

VALUES(1,'PP'),(2,'AA')

SELECT * FROM TestTable

Output:

ID	Name
1	PP
2	AA

Now we are adding new column with default values in existing records using:

ALTER TABLE TestTable

ADD newColumn BIT DEFAULT(0) WITH VALUES

SELECT * FROM TestTable

Output:

ID	Name	newColumn
1	PP	0
2	AA	0

Display count for number of column in table with non zero values for each rows

2013-03-07T23:09:00.002-08:00

declare @t table (ID INT IDENTITY(1,1),a int,b int,c int,d int)

insert into @t
values(1,0,0,0),(0,1,0,0),(1,2,0,0),(1,2,3,4),(1,2,3,0)

;with cte as
(
select * from @t
unpivot
(colvalue for colname in(a,b,c,d)) unpvt
)

select t.*,cnt from @t t
INNER JOIN (
select ID,COUNT(*) as cnt
from cte
WHERE colvalue > 0
GROUP BY ID
) x ON x.ID = t.ID

Output :

Remove non alphabetic character from string in SQL Server

2012-11-04T22:33:00.000-08:00

Query to remove non alphabetic character from string in sql server:

DECLARE @str varchar(100) = 'Paresh@#$Patel#$!@#888'

SELECT CAST(
(SELECT CASE
WHEN SUBSTRING(@str, n, 1) LIKE '[A-Z]'
THEN SUBSTRING(@str, n, 1)
ELSE ''
END
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 50) AS Number(n)
WHERE n <= LEN(@str)
FOR XML PATH('')) AS NVARCHAR(100))

Run this query you will get the output like :

PareshPatel

Find sequential discharge date from multiple discharge date

2012-09-03T23:42:00.003-07:00

Create one table variable and insert the data as need :

DECLARE @table table(patID INT,AdmDate Datetime,DisDate Datetime)

INSERT INTO @table
values(1,'2012-01-10','2012-01-15'),
(1,'2012-01-16','2012-01-20'),
(1,'2012-01-21','2012-01-25'),
(2,'2012-01-10','2012-01-15'),
(2,'2012-01-17','2012-01-20')

After inserting data here is the query to find the sequential discharge date.

;WITH cte (PatientId, AdmDt, Disdt, AdmDt2, Rnk) AS
(
SELECT t1.PatientID, t1.AdmissionDate, t1.DischargeDate, t2.AdmissionDate, ROW_NUMBER() OVER(PARTITION BY t1.PatientId ORDER BY t1.PatientId, t1.AdmissionDate, t1.DischargeDate) AS Rnk
FROM @table t1
LEFT JOIN @table t2 ON (t1.PatientID = t2.PatientID AND DATEADD(DAY, 1, t1.DischargeDate) = t2.AdmissionDate)
),
cte1 (PatientId, AdmDt, DisDt, NextRnk) AS
(
SELECT D1.PatientId, D1.AdmDt, D1.Disdt,
CASE WHEN D1.AdmDt2 IS NULL THEN D1.Rnk ELSE MIN(D2.Rnk) End AS NextRnk
FROM cte D1
LEFT JOIN cte D2 ON (D1.PatientId = D2.PatientId AND D2.Rnk > D1.Rnk AND D2.AdmDt2 IS NULL)
GROUP BY D1.PatientId, D1.AdmDt, D1.Disdt,D1.AdmDt2, D1.Rnk
)
SELECT D1.PatientId, MIN(D1.AdmDt) AS AdminssionDate, MAX(D1.DisDt) AS DischargeDate
FROM cte1 D1
GROUP BY D1.PatientId, D1.NextRnk
ORDER BY D1.PatientId, D1.NextRnk

This will gives the output as :

Find physical path for Database backup file in SQL Server

2012-05-07T06:13:00.001-07:00

This is the query to find the physical location for backed up database file.

SELECT
physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE database_name = 'Testdb'
ORDER BY backup_finish_date DESC

Output :

Get XML Response From Web URL

2012-04-19T05:48:00.003-07:00

First set the following option in sql :
sp_configure 'show advanced option',1
reconfigure

Sp_configure 'Ole Automation Procedures',1
Reconfigure

Then after run the following script by passing your web URL to get the XML response.

DECLARE @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object

--load into Xml
Declare @XmlResponse as xml;

select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse

Find exact Age using Birth date

2012-03-27T01:16:00.002-07:00

Create this scalar function to get the exact Age.

ALTER FUNCTION GetAgeByBirthDate(
@BirthDate DATETIME,
@CurrentDate DATE
)
RETURNS VARCHAR(200)
BEGIN
DECLARE @Year INT,
@Month INT,
@Day INT,
@Age VARCHAR(200)

IF MONTH(@BirthDate) <> MONTH(@CurrentDate) BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)-1
SELECT @Month = DATEDIFF(mm,DATEADD(yy,@Year,@BirthDate),@CurrentDate)

END ELSE BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)
SELECT @Month = 0
END

IF DAY(@BirthDate) <= DAY(@CurrentDate) BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END ELSE BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month-1,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END

SELECT @Age = 'Your age is ' + CAST(@Year AS VARCHAR(4)) + ' YEARS ' + CAST(@Month AS VARCHAR(4)) + ' MONTHS and ' + CAST(@Day AS VARCHAR(4)) + ' DAYS.'

RETURN @Age
END

After creating this function run this query :
SELECT dbo.GetAgeByBirthDate('1981-12-19',GETDATE())

First argument is the birth date and second is the current date.

Recursive CTE in SQL SERVER

2011-10-05T03:01:00.001-07:00

This shows the use of recursive cte that displays a next six month based on passing the values (Year and Month).

DECLARE @year int,
@month int

SELECT @year = 2010,
@month = 9

;WITH cte AS (
SELECT
DATEADD(MONTH,@month-1,DATEADD(year,@year-1,cast('00010101' as date))) as date1
UNION ALL
SELECT DATEADD(month,1,date1)
FROM cte
WHERE date1 < DATEADD(MONTH,@month + 4,DATEADD(year,@year-1,cast('00010101' as date)))
)

SELECT * FROM cte
GO

And Output will be :

date1
-------------
2010-09-01
2010-10-01
2010-11-01
2010-12-01
2011-01-01
2011-02-01

UNPIVOT in SQL SERVER with Example

2011-10-04T03:58:00.000-07:00

Here in this example it shows the Max column value for the specific ID
how it is get by UnPIVOT let see :

DECLARE @Test AS TABLE(id INT, col1 INT, col2 INT, col3 INT, col4 INT)
INSERT INTO @Test VALUES(1, 100, 500, 300, 200)
INSERT INTO @Test VALUES(2, 600, 300, 500, 400)
INSERT INTO @Test VALUES(3, 400, 100, 800, 300)

SELECT ID,MAX(data) AS MaxValue
FROM
(
SELECT ID,Data
FROM
(SELECT ID, col1, col2, col3, col4
FROM @Test
) p
UNPIVOT
(Data FOR Datavalue IN
(col1, col2, col3, col4)
)AS unpvt
) AS x
GROUP BY ID
GO

The output will be :
-------------
ID MaxValue
----- ------------
1 500
2 600
3 800

Count the difference of character in two string in SQL Server

2011-09-18T22:10:00.000-07:00

Create the sql function and use it like below :

--Select dbo.DiffCount('BOB', 'BOB')
CREATE FUNCTION dbo.DiffCount(@Str1 varchar(500), @Str2 varchar(500))
Returns int As
BEGIN
DECLARE @len1 INT,
@len2 INT,
@DiffLen INT,
@CharIndex INT,
@Inc INT,
@diffcount INT

SELECT @len1 = DATALENGTH(@str1),
@len2 = DATALENGTH(@str2),
@DiffLen = @len1 - @len2,
@diffcount = 0,
@Inc = 1

IF @len1 <= @len2 BEGIN

WHILE @Inc <= @len2 BEGIN

IF SUBSTRING(@str1,@inc,1) = NULLIF(SUBSTRING(@str2,@inc,1),' ') BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END
END ELSE IF @len1 > @len2 BEGIN
WHILE @Inc <= @len1 BEGIN

IF NULLIF(SUBSTRING(@str1,@inc,1),' ') = SUBSTRING(@str2,@inc,1) BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END

END
RETURN @diffcount
END

Retrieve comma separated values into a table

2011-09-14T21:49:00.001-07:00

Hi ,

You can use this table vale function to get the comma separated string into form of table retrieve value.

just create this function and select result from that .

--SELECT * from dbo.CommaSeparatedToTableVale('app,birds,cow,paresh,dhaval')
CREATE FUNCTION CommaSeparatedToTableVale
(
@str VARCHAR(100)
)
RETURNS @Table
TABLE(rec VARCHAR(50))
AS
BEGIN
DECLARE @len INT
DECLARE @inc int
DECLARE @offset INT
DECLARE @newstr VARCHAR(100)

select @len = DATALENGTH(@str),
@inc = 0,
@offset = 0

while @inc <= @len+1 begin

IF ISNULL(@newstr,'') = '' BEGIN
SELECT @offset = charindex(',',@str)
INSERT INTO @Table
SELECT SUBSTRING(@str,0,@offset)
SELECT @newstr = SUBSTRING(@str,@offset+1,@len)
END ELSE BEGIN
SELECT @offset = charindex(',',@newstr)
if @offset = 0 begin
INSERT INTO @Table
SELECT @newstr
break
end else begin
INSERT INTO @Table
SELECT SUBSTRING(@newstr,0,@offset)
end
SELECT @newstr = SUBSTRING(@newstr,@offset+1,@len)
END
SET @len = @len - @offset
SET @inc = @inc + 1
end
--RETURN @table
return
END

And you may also refer this for using XML :

http://sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html

Query to search text of all sql server database objects

2010-05-26T03:53:00.000-07:00

here the sql query to search releated objects those includes this text:

select
ss.name as [schema],
so.name as [Object],
so.type_desc as [objectType]
FROM sys.objects so
Inner join sys.schemas ss on ss.schema_ID = so.schema_Id
inner join syscomments sc On sc.Id = so.object_ID
where is_ms_shipped =0 ANd sc.text like '%test%'

Read XMLColumn value and Get it in Table format

2008-12-19T21:26:00.000-08:00

for Example the XML value is Like :

bbb
vvv
aaa

SELECT r1.value ('./@id', 'varchar(100)') as Attribute, '' as Value
FROM [Table Name] CROSS APPLY [ColumnName].nodes ('Root/D') AS x (r1)

SQL Function for Comma seperator value to return in Table format

2008-12-19T21:23:00.000-08:00

Create FUNCTION List_to_vc_table
(@list varchar(8000))
RETURNS @tbl Table (number varchar(1000) not null)
AS
--Returns The Given Comma Specified list in Varchar Table
BEGIN
DECLARE @ID varchar(20), @Pos int

SET @list = LTRIM (RTRIM (@list) )+ ','
SET @Pos = CHARINDEX(',' , @list, 1)
IF REPLACE (@list, ',', ' ') <> ''
BEGIN
WHILE @Pos >0
BEGIN
SET @ID = LTRIM(RTRIM(LEFT(@list, @Pos - 1)))
IF @ID <> ' '
BEGIN
INSERT @tbl (number) values (@ID)
END
SET @list = RIGHT(@list, LEN (@list) - @Pos)
SET @Pos = CHARINDEX(',' , @list, 1)
END
END
return
END

XML Serialization/Deserialization of Collection of Objects

2008-11-20T01:49:00.000-08:00

Make one sEmployee Class with their Property. And one Employeecollection class of that sEmployee Class.

public class sEmployee
{
public string FirstName;
public string LastName;
public int Age;
public sEmployee()
{
}
public sEmployee(string first, string last, int age)
{
this.FirstName = first;
this.LastName = last;
this.Age= age;
}
}

Implement the CollectionBase for adding multiple employee item in collection by override their Add Method.

public class EmployeeCollection : CollectionBase
{
public EmployeeCollection() { }
public EmployeeCollection(EmployeeCollection value) { this.AddRange(value); }
public EmployeeCollection(sEmployee[] value) { this.AddRange(value); }
public sEmployee this[int index] { get { return ((sEmployee)(List[index])); } set { List[index] = value; } }
public int Add(sEmployee value) { return List.Add(value); }
public void AddRange(sEmployee[] value) { for (int i = 0; i < i =" 0;">

Now create one sample page for serialization and Deserialization.here i have took one .aspx page and code behind i have wrote this simple code,and saving that data into XML file.

Serialization :

EmployeeCollection empCollection = new EmployeeCollection();
empCollection.Add(new sEmployee("Paresh0", "Patel0", 20));
empCollection.Add(new sEmployee("Paresh1", "Patel1", 21));
empCollection.Add(new sEmployee("Paresh2", "Patel2", 22));

string op = string.Empty;
using (StringWriter writer = new StringWriter(new StringBuilder()))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
xs.Serialize(writer, empCollection);
op = writer.ToString();
}
File.WriteAllText("c:\\emp.xml", op);

Deserialization :

string ip = string.Empty;
EmployeeCollection collection = new EmployeeCollection();
ip = File.ReadAllText("c:\\emp.xml");
using (StringReader reader = new StringReader(ip))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
collection = (EmployeeCollection)xs.Deserialize(reader);
}

Get Comma Seperator Value in one Filed

2008-11-20T01:47:00.000-08:00

select STUFF ( (SELECT ',' + [Column_name]
FROM [TableName] WHERE [Criteria]
FOR XML PATH('')), 1, 1, '') AS [DIsplay Name]

Automatically Restore Database ( Backup databse)

2008-11-13T01:22:00.000-08:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[DBA_Recovery_FULL]
as
/*
Table Used :- master.dbo.sysdatabases
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Restore Full Backup of OldDatabase Database to another Database
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Begin
declare
@spid as nvarchar(4),
@killcommand as nvarchar(255) ,@unc as nvarchar(255)
while (select count(*) from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')<>0
BEGIN
set @spid = (select top 1 pc.spid from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame <> 'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')
set @killcommand = 'kill ' + @spid
--print 'Killing ' + @spid
exec(@killcommand)
END

SELECT top 1 @unc = M.physical_device_name
FROM [msdb].[dbo].[backupset] S
JOIN [msdb].[dbo].[backupmediafamily] M ON M.media_set_id=S.media_set_id
WHERE S.database_name = 'OldDatabse'
and S.type ='D'
and M.physical_device_name like 'S:\TempBackup\Full_Backup%'
and convert(char(10),S.backup_start_date,120) = convert(char(10),getdate() ,120)
order by s.backup_finish_date desc

restore database NewDatabase
from disk = @unc
with replace,
move 'oldDatabase' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabse.mdf',
move 'oldDatabase_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabase_log.ldf'

ENd