The following SQL contains a subtle bug that will always result in the text “No Rows Affected” being output.
SELECT ‘Hard Coded Row’ IF(@@ERROR <> 0) Print ‘Error Occured’ ELSE IF(@@ROWCOUNT = 0) Print ‘No Rows Affected’
The error occurs because the reference to @@ERROR in the first “IF” statement counts as a SQL statement; resetting the value held in @@ROWCOUNT. As the second “IF” statement checking @@ROWCOUNT is only evaluated if the first “IF” statement (@@ERROR) it will always return true! Note: Reversing the order of the two IF statements would hide any potential errors, as @@ERROR would be reset upon checking @@ROWCOUNT.
The safest way to evaluate this statement is to SELECT the contents of @@ERROR and @@ROWCOUNT into local variables within a single statement and then check the values of the local variables, in other words:
DECLARE @ErrorCode INT DECLARE @RowsAffected INT SELECT ‘Hard Coded Row’ SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT IF(@ErrorCode <> 0) Print ‘Error Occured’ ELSE IF(@RowsAffected = 0) Print ‘No Rows Affected’
Problem solved!
We just run into an interesting problem where starting the SQL Server Agent would start and then immediately stop. No errors were reported in the event log, but running the following via the command line returned “StartServiceCtrlDispatcher failed (error 6)“ “[[your SQL Path]]BinnSQLAGENT.EXE” -i Googling the error in question returned this forum post which contained the solution. We had reinstalled the service and the account that we were running under did not have the permissions . . .
As part of my current project, I’ve spent some time over the past couple of months trying to determine the best (cheapest) SQL Server configuration to support web servers running in a virtualised environment. As a quick disclaimer, the following are my thoughts on the subject and should be used as guidance for further research only! Firstly you need to figure out whether you are going to license using the “per user” or “per processor” . . .
Just got burned by this one today. Glad this article came up in my Google search. Thanks!