LINQ to SQL: XElement Not Being Updated in the Database


If you are using LINQ to SQL to manage the interaction between your database and your application, you will notice that any columns you have defined as an XML SQL data type are represented using the XElement class in the generated classes. This makes working with XML data stored in a database very easy and convenient. But, today I ran into a problem while trying to update the XML stored in the database. My code followed the logic shown below.

// Get a report for the database
Report report = (from r in db.Reports
                 where r.Id == id
                 select r).First();

// The Report.Definition property is a representation of an XML column
// Get the first 'Group' element and add a new SubGroup child
XElement element = report.Definition.Elements("Group").First();
element.Add(new XElement("SubGroup", value));

// Commit the changes to the database
db.SubmitChanges();

The problem was that the changes made to the XML found in the Definition property were committed to the DataContext but not to the database.

After some research it appears that when SubmitChanges is called on the DataContext, it checks the entities it has retrieved to see if any changes were made. To do this, the DataContext must cache the original values it retrieved from the database. The problem with the code above is that when you access and change the report.Definition property directly, you are changing the value cached by the DataContext. Thus, when you call the SubmitChanges method and comparisons are made to the cached value, it appears nothing has changed as you edited the cached value. To solve this, do the following

// Get a report for the database
Report report = (from r in db.Reports
                 where r.Id == id
                 select r).First();

// The Report.Definition property is a representation of an XML column
// Get the first 'Group' element and add a new SubGroup child
XElement element = report.Definition.Elements("Group").First();
element.Add(new XElement("SubGroup", value));

// Force a reference inequality
report.Definition = new XElement(report.Definition);

// Commit the changes to the database
db.SubmitChanges();

Now when the DataContext checks to see if anything has changed, there will be a reference inequality between the original Report.Definition and the new XElement you assigned to Report.Definition. Unfortunately the LINQ to SQL classes don’t handle the XElement.Changed event and flag that something has changed.

Posted in LINQ, SQL. Tags: , . 1 Comment »

SQL: SQL Server Management Studio – Clear Recent Server List


SQL Server Management Studio will store the names of the servers you have connected to so that you can easily connect at a later date without having to re-enter the IP Address or name of the server. This is a great feature but sometimes my list gets populated with a bunch of servers that I don’t use frequently. Unfortunately, there is no tool that can be used to clear out the list. Luckily there is still a way to accomplish this. Just navigate to the corresponding directory below, depending on your operating system, and delete the SqlStudio.bin file.

Windows XP
C:\Documents and Settings\[User Name]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell

Windows 7
C:\Users\[User Name]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

After deleting the SqlStudio.bin file, the list will be cleared.

SQL Server Management Studio 2005 Users: If you are using SQL Server Management Studio 2005 then the file you will delete is named mru.dat. Further, to find the file replace 100 with 90 in the above file paths.

Posted in SQL. Tags: , . Leave a Comment »

SQL: Making a Stored Procedure Available to All Databases


I needed to create a stored procedure today that could be used on any database within a given SQL Server instance. After some digging I found that there are three requirements for such a stored procedure:

  1. The stored procedure must be created in the master database.
  2. The name of the stored procedure must start with “sp_“.
  3. The stored procedure must be marked as a system object.

The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.


-- 1. Create the procedure in the master database
USE master
GO

-- 2. Create the procedure with the prefix sp_
CREATE PROCEDURE sp_[Stored_Procedure_Name]
AS
BEGIN
     -- Insert the logic of your stored procedure here
END
GO

-- 3. Mark the stored procedure as a system object
EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]

After you complete the above three steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.

Note: If you need to “unmark” the procedure as a system object just simply drop the procedure and recreate it.

Posted in SQL. Tags: . 1 Comment »

SQL: Get Login Database and Server Role Membership


When trying to query information about server and database role membership, SQL Server ships with some helpful stored procedures like sp_helpsrvrolemember and sp_helprolemember. These are helpful when you are looking for the members of a given role, but not as helpful when you are looking for the reverse; the roles for which a given database user is a member.

After some searching, I found the sp_helpuser stored procedure which will give you a bunch of information about a database user including its group membership. But, I had two problems with this, it was a stored procedure so I could not preform any operations on the results without inserting them into a temporary table and it didn’t provide me with any information about the server roles of which the associated login was a member. Unsatisfied with the stock stored procedures provided I turned to the system views. Using the sys.server_role_members and sys.database_role_members I was able to get the needed information.

Both of the mentioned views return two columns, one for the role principal id and one for the member principal id. Thus, to get a list of server and database roles for which a given login or database user is a member, all we need to do is select the rows associated with the login or user.

Server Role Membership

DECLARE @login sysname
SET @login= 'nick'

SELECT SUSER_NAME(SRoleMembers.role_principal_id) AS [Role]
FROM sys.server_role_members as SRoleMembers
WHERE SUSER_NAME(SRoleMembers.member_principal_id) = @login

Database Role Membership

DECLARE @username sysname
SET @username = 'nick';

-- USE a Common Table Expression to build a recursive list
-- of roles for which this user is a member
WITH UserDBRoles (role_principal_id)
AS
(
	SELECT DBRoleMembers.role_principal_id 
	FROM sys.database_role_members AS DBRoleMembers
	WHERE USER_NAME(DBRoleMembers.member_principal_id) = @username
  UNION ALL
	SELECT DBRoleMembers.role_principal_id
	FROM sys.database_role_members AS DBRoleMembers
	INNER JOIN UserDBRoles ON DBRoleMembers.member_principal_id = UserDBRoles.role_principal_id
)

SELECT USER_NAME(role_principal_id) [Role]
FROM UserDBRoles

Both of these use the SUSER_NAME and USER_NAME functions which convert a principal id to its associated login, user, or role where SUSER_NAME is used for server level principals and USER_NAME is used for database level principals. This conversion could have also been doing with a join using the sys.server_principals and sys.database_principals views but I figured this syntax was a bit easier to read.

Retrieving the roles for the database user is a bit more tricky as you can have nested roles. In this example, the database user nick is a member of the Manager role and in turn the Manager role is a member of the Leasing role. If we were to follow the same logic as the statement for the server roles, the Leasing role would not be returned in our list. To solve this we can use a Common Table Expression to create a recursively defined table that only will exists for the duration of the current query.

Posted in SQL. Tags: . Leave a Comment »

SQL: Using a Cursor in a Loop


While SQL was designed for set operations and not loops, it does still support them. Given this, I rarely use cursors and loops when performing SQL operations and thus I always have to look up the syntax. I’m posting this here mainly for my benefit.

-- Note that we are only selecting one column here but a cursor just points 
-- to a row in a set so we could select multiple columns
DECLARE curEmployeeIds CURSOR FOR (SELECT Id FROM Employees)

-- Declare the variables that will be used in the loop that 
-- correspond to one record's values
DECLARE @Id int

OPEN curEmployeeIds

-- If you need multiple values from a single record, simply
-- enter a comma delimited list of variable names after INTO
FETCH NEXT FROM curEmployeeIds INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN

-- Perform the needed operations

FETCH NEXT FROM curEmployeeIds INTO @Id
END

-- Release the resources
CLOSE curEmployeeIds
DEALLOCATE curEmployeeIds
Posted in SQL. Tags: . 2 Comments »

SQL: Order By with NULL Values Last


I ran across a situation today where I needed to sort a result set on a column that could contain NULL values. By default, all rows with a NULL value in the sorted column are returned first but in this case, I wanted to have all the NULL values show up last in the result set.

Click here to jump to the correct solution or keep reading for a detail explanation of failed attempts at solving this problem.

For example, imagine you have an Employees table as defined below and with the indicated data.

Running a simple query ordering by the nullable DepartmentId column produces the following result (NULL values at the top).

SELECT * 
FROM Employees
ORDER BY DepartmentId

Ordering the result set by DepartmentId descending obviously won’t work as even though the NULL values will appear at the bottom all the other rows will be in descending order as well.

SELECT * 
FROM Employees
ORDER BY DepartmentId DESC

I have seen some people attempt to solve this problem using the following query.

SELECT Id, FirstName, LastName,
	   (CASE 
			WHEN DepartmentId IS NULL THEN 99999
			ELSE DepartmentId
		END) AS DepartmentId	   
FROM Employees 
ORDER BY DepartmentId

While this will most likely sort the rows as desired, this presents multiple problems. First, you are specifying a DepartmentId of 99999 for rows that really don’t have a DepartmentId and second, you have to ensure that you never have a DepartmentId over 99999 in your Employee records.

You can eliminate the first problem of the query above by doing the following:

SELECT *	 	   
FROM Employees 
ORDER BY ISNULL(DepartmentId, 99999)

The ISNULL function will replace a NULL DepartmentId with the value 99999, thus giving the desired ordering while still preserving the NULL value in the result set. But, this still leaves us with the second issue mentioned above.

The Solution

Both issues can be resolved by a simple case statement in the ORDER BY clause as shown below.

SELECT *	   
FROM Employees 
ORDER BY (CASE 
			WHEN DepartmentId IS NULL THEN 1 
			ELSE 0 
	      END), 
	     DepartmentId

Here, the result set is first sorted by a temporary column with the value of 1 if the associated row’s DepartmentId value is NULL and 0 if it is not. Doing such will ensure that all records with a NULL DepartmentId appear after records with a non-NULL DepartmentId. The two groups of records (ones with a NULL DepartmentId and ones with a non-NULL DepartmentId) are then sorted by the DepartmentId which ensures that all records with a DepartmentId are sorted correctly.

A simple solution to a random problem.

Posted in Sorting, SQL. Tags: , . 4 Comments »

SQL: String Concatenation Using FOR XML PATH(”)


Every so often I have the need to concatenate a column of strings returned from a SQL query. I have always used the COALESCE function to do this, something like the following:

SELECT @names = COALESCE(@names + ',', '') + FirstName 
FROM Residents

I looked up the documentation for COALESCE and realized that it is supposed to be used to return the first nonnull expression among those passed to it. While this works for concatenation, using it in such a fashion is more of a hack rather that what it is actually supposed to be use for.

With the introduction of SQL Server 2005, we are able to combine both SQL and XML much easier. Among the new features is the FOR XML PATH function that is intended for string concatenation when generating XML.

SELECT ', ' + FirstName 
FROM Resident
FOR XML PATH ('')

-- Output
-- ------
-- , Shireda, William, Melissa, Kelly, Clarence

To get rid of the leading comma and space we can use the STUFF function. From MSDN:

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF (character_expression , start , length ,character_expression)

The final code becomes:

SELECT STUFF((SELECT ', ' + FirstName 
			  FROM Resident
			  FOR XML PATH ('')), 
			  1, 2, '')

-- Output
-- ------
-- Shireda, William, Melissa, Kelly, Clarence

Some may argue that using the FOR XML PATH function for string concatenation is just as much as a hack as using the COALESCE function as we aren’t actually generating XML but using the FOR XML PATH function seems a bit more intuitive to me.

Posted in SQL. Tags: . 1 Comment »
Follow

Get every new post delivered to your Inbox.

Join 69 other followers