Thursday 14 August 2008

SQL errors don't cause exceptions if nocount is off

We had a strange problem today: an end-user was getting unexpected results (no data), but no exceptions were occurring.

The cause was traced to incorrect database permissions, i.e.

Server: Msg 229, Level 14, State 5, Procedure xyz, Line 123
SELECT permission denied on object 'abc', database 'pqr', owner 'dbo'.

The question was, why did this not cause an exception? A possible cause is our use of SqlDataReader, but I was unhappy with the suggested fix. The underlying cause however was that nocount was set to off (its default setting). This caused the row count messages to mask the error, and thus no exception was raised.

Lesson learned: always set nocount on at the start and off at the end of each stored procedure, e.g.

create procedure xyz
as
begin

set nocount on

-- SP SQL

set nocount off

end

go

Tuesday 12 August 2008

God sees you now as one day we could be

These were the words of Brother Andrew, spoken at this year's New Wine Summer conference my family attended. These words are echoed by Paul and Rob Bell, in his book Velvet Elvis that I'm really enjoying at the moment.

A truly inspirational speaker.