Generating a comma separated list by an SQL Query

Today I am showing you a quick and easy way to concatenate rows of textual data in a single string. This is particularly useful when you need a comma separated list of items, products etc. and you don’t want to loop the whole dataset for performance reasons.

Following is the sample code to demonstrate this. Concept is very simple and anyone can easily understand it by running the following code.

Set Nocount On
Print ‘Start’
Print ‘Creating Sample Items table’
Go
CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO
Print ‘Inserting data in sample table’
Go

Insert into dbo.tblItems (ItemName) Values (‘Item1′)
Insert into dbo.tblItems (ItemName) Values (‘Item2′)
Insert into dbo.tblItems (ItemName) Values (‘Item3′)
Insert into dbo.tblItems (ItemName) Values (‘Item4′)
Go
Print ‘Following is the comma separated list of Items’
Go
Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=”
Select @m_ItemsList=@m_ItemsList+ItemName+‘,’ from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)
Go
Print ‘Dropping sample table’
Drop table dbo.tblItems
Go
Print ‘Finished’

Display data in your own way: Custom Report Item in Reporting Services 2005

Displaying a progress bar for each row in a report or displaying a traffic light type status indicator for each row in SQL Server Reporting Services (SSRS) is not possible directly because SSRS just provide you a set of very basic report items like Line, Image etc.

But since Microsoft always design products with extensibility in mind, so we have a solution here which is Custom Report Item or CRI.

Custom Report Items are there to provide you a framework in .NET which you can use to implement your own graphical controls to display your data in your own way. These controls can be used to do virtually anything under .NET framework.

Following are some excellent resources to start with:

Jazz up your data Using custom report items
http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx

Chris Harys Reporting Services Weblog
http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx

How to change user name in ASP.NET 2.0 Membership Provider

Hi Guys,

Although changing the UserName in ASP.NET 2.0 Membership provider is not recommended and Microsoft has made it read only but sometime it becomes necessary to change the incorrect or misspelled login name or user name. To solve this issue, I have created the following stored procedure which can easily change the old Login Name to New Login Name. I have tested it and it is running perfectly fine. It is in SQL Server 2005 TSQL format.

NOTE: Before executing, replace tblUsers and its respective columns with your own User Table.

Following is the code:
Create Procedure uspUsers_ChangeLoginName
@p_OldName varchar(20),
@p_NewName varchar(20)
as
Begin
Set Nocount On
BEGIN TRY
–Check if old user exists
if exists(Select Login from tblUsers where Lower(Login)=Lower(@p_OldName))
Begin
Begin Transaction
Update tblUsers set Login=@p_NewName where Login=@p_OldName
Update aspnet_Users Set
UserName=@p_NewName,
LoweredUserName=Lower(@p_NewName)
Where LoweredUserName=Lower(@p_OldName)
Commit Transaction
Print @p_OldName + ‘ has been successfuly changed to ‘ + @p_NewName
End
Else
Begin
Print ‘Error: Old User Name “‘+@p_OldName + ‘” does not exist. Cannot change the User Name.’
End
END TRY
BEGIN CATCH
–Rollback any tranction, if it was started
If @@Trancount > 0 Rollback Transaction
Print ‘Error occured while trying to replace old UserName to New UserName:’
Print ‘Error No:’ + Convert(varchar,ERROR_NUMBER())
Print ‘Error Line:’ + Convert(varchar,ERROR_LINE())
Print ‘Error Message:’ + Convert(varchar,ERROR_MESSAGE())
END CATCH;
End

Search columns in SQL Server 2005 database

Since SQL Server 2005 Management Studio lacks the Object Search feature, here is the simple query to find any column in a database

Select O.name objectName, C.name ColumnName from sys.columns C inner join sys.objects O ON C.object_id=O.object_idwhere C.name like ‘%ColumntoFind%’order by O.name,C.name

This query works for SQL Server 20005. Just replace “ColumnToFind” with your required column name.