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

Friday, January 08, 2010 by Administrator

Setting the value of a hidden field with jQuery is easy. Simply use the val() function.

$("#MyHiddenField").val("my value");

Thursday, January 07, 2010 by Administrator

How do you perform a Control - Alt - Delete when using Remote Desktop?! Simple, just use Control + Alt + End instead....easy!

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.

Thursday, December 17, 2009 by Administrator

I noticed recently that IIS7 wasn't compressing my static files even though IIS looked as if it was configured correctly.

After an hour of googling and tinkering I had found a great feature in IIS which allows you to view the cycle of a request to which helped diagnose the problem.

First of all I had to install and enable failed request tracing.

Once you have enabled failed request tracing select your site node and then in the features view, click 'failed request tracing rules'. Click add, next, enter 200, next, click finish.

Now, request a file which you expect to be compressed...this should generate some log files.

By default you should be able to find them in c:\inetpub\logs\FailedReqLogFiles\w3svcx. Open some of the files in IE and find one which is a request for a static file. Next, click the 'request details' tab and have a look at the request cycle.

Search for STATIC_COMPRESSION_START

On my server I then found the following trace:

STATIC_COMPRESSION_NOT_SUCCESS

Reason 14
Reason  NOT_FREQUENTLY_HIT

The NOT_FREQUENTLY_HIT reason seemed like that could be the issue as the website in question isnt busy at all. As is turns out it looks as if IIS doesn't bother compressing files which aren't frequently hit...which I dont really see the point of (but I'm sure there must be one!) as long as the server has enough spare CPU why not send back a compressed response?

Anyway, I ended up finding the config element below:

<serverRuntime frequentHitThreshold="1" />

Which I set in:

C:\Windows\System32\inetsrv\config\applicationHost.config

Setting frequentHitThreshold="1" seemed to do the trick...all my js/css files etc are now compressed. Happy days.

Monday, December 14, 2009 by Administrator

Have you been using:

$(document).ready(function() {
   // Do Stuff Here
});

Did you know you can simply just use the following syntax:

$(function() {
   // Do Stuff Here
});

Friday, December 11, 2009 by Administrator

Damn, Umbraco keeps on trying to take over my static content!

How can I quickly upload some standard html or aspx pages which DONT require any Umbraco jiggery pokery (JP)?

Don't worry, its easy...simply add a few "reserved" paths to your web.config.

Look for something like:

<add key="umbracoReservedPaths" value="/umbraco,/install/" />

And change to suit your needs. For example:

<add key="umbracoReservedPaths" value="/umbraco,/install/,/my-custom-directory/" />

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)

Sunday, August 30, 2009 by Administrator

This is very cool!