Secret Orange Web Log

Wednesday, February 10, 2010 by Administrator

A quick TSQL Nugget.

I recently needed to pull from the database a list of top 3 comments for all "objects"

Using the TSQL ROW_NUMBER() function with PARTITION makes this easy!

SELECT 
  ObjectID, CommentID, ROW_NUMBER() OVER ( PARTITION BY ObjectID ORDER BY CommentID DESC ) AS RowNumber
FROM 
  dbo.Comment

Tuesday, December 29, 2009 by Administrator

Ok, just a quick one...this had me stumped for over an hour!

I wanted to simply update one column on a "user" object in the database. So, I retrieved the object from the DB, set the new value and called SubmitChanges. I then get hit with a ChangeConflictException.

It was a fresh DataContext and the property in the DBML file looked OK. I then checked the output of the SQL and it looked something like:

UPDATE User
SET CalendarID = @p0
WHERE 0 = 1

What was the 0 = 1 where clause all about? As it turns out I think its meant to be some kind of LINQ to SQL optimisation gone wrong...anway this started to lead me down the right path and I ended finding out that it was another "user" property in the DBML file that was causing the problem. I had a column name "DisplayName" which was set as NULL in the DB but not nullable in the DBML file. Fixing this seems to have solved the problem...even though the DisplayName property had nothing to do with the UPDATE.

Friday, December 18, 2009 by Administrator

I just got the following error when trying to UNION two LINQ to SQL queries:

Could not translate expression 'Table(EventMembership).......blah blah blah...' into SQL and could not treat it as a local expression.

To see what was going on I ToList() the two queries individually and pasted the generated output into SQL Server Management Studio and then tried a UNION ALL on the two queries.

Guess what? The SQL blew up!

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

But the shapes of my LINQ classes are identical...whats going on? Why is the translated SQL dropping some of the columns and causing the shapes of the two queries to be different?

I then noticed that the second query was referencing the same column twice...something like:

select new { FirstName = e.EventName, LastName = e.EventName }

Now, when LINQ to SQL translates to TSQL it spots the identical columns and thinks to itself "ok, I could be smart here" and decides to only pull back one of the columns and fixes it clientside. This is good for performance but NOT when you want to UNION the result sets!!! Presumably, LINQ to SQL should check first if a UNION operator is be used and if so then makes sure the shape of the data for all queries involved is the same...but it looks as if we are going to have to live with this bug for now.

So, to get round the problem you are going to have to ensure that each property of your select object is referencing a different column or atleast do something that will force LINQ to SQL to include it in its translated SELECT list.

Tuesday, December 01, 2009 by Administrator

Quite often you need to kill all SQL Server connections to a database...perhaps for performing a backup or restore.

Here is a snippet of SQL to kill connections to a given database:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YourDatabaseName'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL 
EXEC(@SQL)