How to Remove Restricted User in SQL Server 2005

The problem is that the database is in a single user mode, this usually happens after a restore process.

You have to execute the query below to change it back to multi user mode by executing the query below.

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

Split Function in Sql Server to break Comma-Separated Strings into Table

Sql Server does not (on my knowledge) have in-build Split function.
Split function in general on all platforms would have comma-separated string value to be split into individual strings.
In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value (‘abc,cde,fgh’) into a temp table with each string as rows.

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.Split(@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 or @String is null return

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



split function can be Used as

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

would return


Above Split function can be used to pass parameter to IN clause in sql server.

complex UPDATE uses ANOTHER TABLE as the SOURCE of the data

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.