July 15, 2010
SQL Server object_name still takes int value for object_id
If you're using the, not so new anymore, DMVs and pulling any bigints like resource_associated_entity_id you'll get an arithmetic overflow if you try to pass this value to object_name, because the object_id param to the function is still defined as int even in SQL Server 2008.
For example if you're looking at sys.dm_tran_locks to see what locks can't be granted during a block you need to only pass the resource_associated_entity_id if the resource_type is OBJECT otherwise pass NULL to get NULL back.
Granted looking up the object_name of a PAGE or KEY wouldn't give back anything useful, but it would be nice if it didn't blow up the query.
July 2, 2010
Tricky SQL XML support for binary values
In setting up "Event" based block notifications for SQL 2005/2008 I had to get the binary SQL handle out of the XML provided by the event. This of course seems rather simple, except you can't just supply varbinary(64) as the type to @xml.value because that would too easy for an MS product. Trying this gives you back NULL instead of your binary value.
As this page tells you, you need to use an XQuery conversion, xs:hexBinary, to do instead. But wait, you're not off the hook yet, because xs:hexBinary doesn't understand 0x delimited binary, and doesn't tell you that in any way shape or form. Instead you get back your old buddy NULL. And that's when you notice buried in the XQuery that it's actually chopping off the 0x if the value has it (even though the test value in the code doesn't).
So the whole chunk you end up needing looks like this:
Process.value( 'xs:hexBinary( substring((frame/@sqlhandle)[1],3))', 'varbinary(64)' )
May 6, 2010
Empty User Names despite authenticating successfully
Had an issue today where System.Web.HttpContext.Current.User.Identity.Name was returning String.Empty even though I was prompted to authenticate against the server when hitting the web page. After a few Googles I realized it was because the web.config was set to <authentication mode="None"/>. Changing it to "Windows" fixed the problem. I've never understood what that web.config line did in the past, since Auth is done at the IIS level and ASP.NET shouldn't really care. I guess now I know...
