No More Solutions

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Combine Rows into a Column

Happened to find a way to combine rows into a column. I find it quite useful, and it comes in handy.

For illustration, suppose we have UserGroup & Users. We want to list the users belonging to each group.

UserGroup:


Users:


If we do normal joining of the 2 tables, it will look like:


If we combine the values on the rows into a column, it will look like:


Here is the query to do so:

-- Create temporary table to store UserGroup
SELECT GroupID = 1, UserGroup = 'Group 1' INTO #UserGroup
INSERT INTO #UserGroup VALUES ( 2, 'Group 2' )
INSERT INTO #UserGroup VALUES ( 3, 'Group 3' )

-- Create temporary table to store Users
SELECT UserID = 'Superman', GroupID = 1 INTO #Users
INSERT INTO #Users VALUES ( 'Luck', 2 )
INSERT INTO #Users VALUES ( 'Himura', 1 )
INSERT INTO #Users VALUES ( 'Selvi', 1 )
INSERT INTO #Users VALUES ( 'Smarty', 2 )
INSERT INTO #Users VALUES ( 'Lucky', 3 )

-- Normal Join
SELECT a.UserGroup, b.UserID
FROM #UserGroup a
LEFT JOIN #Users b
 ON b.GroupID = a.GroupID
ORDER BY a.UserGroup

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH('') )
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

-- This is the key query to combine rows into column
SELECT UserID + ', ' FROM #Users
WHERE GroupID = 1
FOR XML PATH('')
Bookmark and Share

Mapping User Login and Database Roles to Existing User

Error:
User, group, or role 'xxx' already exists in the current database. (.Net SqlClient Data Provider)

Explanation:
This error happens when you try to add user mapping to the server logins using username which already exists in the database to be assigned.

In my case, the user exists in both server logins and database logins, but the user mapping and database roles on the server logins for this user cannot be modified although the user mapping is incorrect, because it already exists on the database logins.

Solution:
Run this script to update the user mapping accordingly.
USE <database_name>
EXEC sp_change_Users_login 'update_one', '<login_username>', '<login_username>'

This will update the user mapping and database roles on server logins to follow the ones on the database logins.
Bookmark and Share

Current Transaction Cannot be Committed

Error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This error occurs when you try to perform other operations when any transaction fails in try catch section, soon after it goes to catch section.

Solution:
You have to ROLLBACK first on the catch section before doing any other operations.
Bookmark and Share

Directory Lookup Failed on SQL Server

When executing script on SQL server, this error occurs:
Directory lookup for the file <filepath> failed with the operating system error 2(The system cannot find the file specified.)

Directory lookup for the file <filepath> failed with the operating system error 3(The system cannot find the path specified.)

Solution:
Check the directory used in the SQL script. Make sure that the directory used in the SQL script point to the server location, if the script is not directly executed from the server.
Bookmark and Share

Parameter Sniffing

Parameter sniffing has a weird symptom which can take your hours to find out what actually is happening. The stored procedure you created is running on one side, but when you deploy it on another machine or server, it never ends executing despite the fact that everything is the same.

This problem usually occurs when the query contains LIKE condition. However, it is not limited to this case. Query without LIKE condition can also face this problem.

For example, here is the normal stored procedure:
CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS
SELECT *
FROM dbo.Table1
WHERE Column1 = @Parameter1
AND Column2 LIKE '%' + @Parameter2 + '%'

To avoid parameter sniffing, there is a need to create new variables to store the values fetched from the parameters.
CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS

DECLARE
@DummyParameter1 INT = @Parameter1,
@DummyParameter2 VARCHAR(200) = @Parameter2

SELECT *
FROM dbo.Table1
WHERE Column1 = @DummyParameter1
AND Column2 LIKE '%' + @DummyParameter2 + '%'
Bookmark and Share