Security in the cloud…

Well, not really. While searching for articles related to security integration in the cloud I ended up reading these interesting pieces regarding other aspects of security that are in the public eye.

A thoughtful article on improving the TSA procedures at airports from an experienced police officer: http://www.hlswatch.com/2009/10/15/“do-i-have-the-right-to-refuse-this-search”/

A different take on the Afganistan debate: http://www.drewconway.com/zia/?p=1433

Windows 7 – all good so far

I installed windows 7 this week on a Dell Latitude XT.  This laptop is a little bit old (it has a “designed for Windows XP” sticker on it), so I wasn’t sure about the support. Also, the ATI video driver has caused problems when there’s 3GB of RAM installed.

So far, it has been great:

Installation went almost without a hitch. Initial installation went smoothly. Loading the ATI driver manually from the ATI site caused a BSOD. After rebooting, windows 7 loaded in generic SVGA mode and suggested a new video driver which has been working great so far!

Other parts of the tablet including multi-touch support for the screen and support for the special buttons seem to have been installed correctly, although I’m not sure when that happened. Seems like magic! I’m surprised that support for this laptop is so good.

One big upgrade from XP is also the handwriting recognizer which is much easier to use, and more importantly years ahead in accuracy. This alone made the upgrade worth it.

Now to try it on my netbook…

LinqToSql and Views with INSTEAD OF INSERT triggers

LinqToSql is a convenient way to provide a light object mapping layer for your Sql Server database. Like many tools, funky things happen when you leave the main use case.

In our database, access is controlled through views, which provide a layer of indirection that facilitates schema refactoring and optimization, as well as making data security a little easier for the DBA and database developers to manage.

These views are typically constructed with INSTEAD OF triggers. In the case of a INSTEAD OF INSERT trigger, LinqToSql will have trouble finding any autogenerated identity values that are present on the table.

Here’s what the trigger might look like:

ALTER TRIGGER [dbo].[tr_v_TransactionItem_Insert]
   ON  [dbo].[v_TransactionItem]
   INSTEAD OF INSERT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT dbo.TransactionItem
    (
		TransactionId,
		InventoryItemId,
		QuantityChange,
		PriceEach,
		PriceAdjustment,
		Notes
	)
	SELECT
		TransactionId,
		InventoryItemId,
		COALESCE(QuantityChange, 0),
		COALESCE(PriceEach, 0),
		COALESCE(PriceAdjustment, 0),
		Notes
	FROM inserted
END

I ran across this using the .net RIA services July 2009 preview, where you get a cryptic message of this sort:

The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

This actually shows up as a System.Windows.Ria.Data.EntityOperationException when handling the ChangesSubmitted event on the client side.

So what’s happening? Using SQL profiler, you can see that LinqToSql attempts to use SCOPE_IDENTITY() to access the identity value of the item inserted.

exec sp_executesql N'INSERT INTO [dbo].[v_TransactionItem](
    [BottleSize], [SupplierName], [ProductName], [OrderId], [ProductionYear],
    [BottleDate], [TransactionId], [InventoryItemId], [QuantityChange],
    [PriceEach], [PriceAdjustment], [Notes])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',
    N'@p0 varchar(8000),@p1 varchar(8000),@p2 varchar(8000),@p3 nchar(10),
    @p4 int,@p5 datetime,@p6 int,@p7 int,@p8 int,@p9 money,@p10 money,
    @p11 varchar(8000)',
    @p0=NULL,@p1=NULL,@p2=NULL,@p3=NULL,@p4=NULL,@p5=NULL,@p6=27,
    @p7=10,@p8=0,@p9=$0.0000,@p10=$0.0000,@p11=NULL
    

Running this query in management studio shows that SCOPE_IDENTITY() returns NULL. That’s what’s generating the error message. So, what can we do about this?

A typical method to handle this would be to create a stored procedure and use a custom method to invoke the stored procedure. MSDN documents this
here:

http://msdn.microsoft.com/en-us/library/bb882646.aspx.

This didn’t seem entirely satisfactory since it seemed like a whole bunch of
extra work was needed to write the stored procedure. However, with some experimentation, it seems that
if the identity key values are explicitly selected in the trigger, the insert
works correctly.

In SQL Server 2008, it is a simple as inserting the OUTPUT clause in the
trigger:

ALTER TRIGGER [dbo].[tr_v_TransactionItem_Insert]
   ON  [dbo].[v_TransactionItem]
   INSTEAD OF INSERT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT dbo.TransactionItem
    (
		TransactionId,
		InventoryItemId,
		QuantityChange,
		PriceEach,
		PriceAdjustment,
		Notes
	)
	OUTPUT inserted.TransactionItemId
	SELECT
		TransactionId,
		InventoryItemId,
		COALESCE(QuantityChange, 0),
		COALESCE(PriceEach, 0),
		COALESCE(PriceAdjustment, 0),
		Notes
	FROM inserted
END