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.

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.

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.

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.

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