Old error checking still in use. @@ERROR

SQL 2000 error checking with @@ERROR and @@ROWCOUNT is still in use in many applications. Even with the advent of SQL 2005's TRY-CATCH feature you may still find this in old T-SQL or it might be the best fit for some applications. BUT make sure you understand how this works and fix those older legacy TSQL constructs that may have problems.

I found this pattern in SQL Stored Procedures for error checking at a client.

Example-1 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
rollback tran
return @@error
End

Does this work the way the original author expected? NO.
The intent is rollback an open transaction and report the error number encountered to the the caller of the procedure. What will always(99%) get reported is a SUCCESS value of Zero (0).
Why?
Because @@error ONLY maintains the condition of the “Last” SQL statement executed. In this case the ROLLBACK statement. This is also a problem when you introduce constructs that have IF conditions.

Example-2 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
if @@rowcount = 0
Begin
return @@error
End

Again the @@rowcount check resets the error condition and @@error returns 0.
Here are some rewrites of the above examples:

Example-1 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
If @@trancount > 0 rollback tran –-a good idea
return @errnum
End

And the proper way to check for row counts and error codes. You must grab both at the same time or you’ll get the wrong value in the 2nd variable.

Example-2 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
Select @ErrorVar = @@error, @RowCountVar = @@rowcount
if @RowCountVar = 0
Begin
return @ErrorVar
End

In SQL 2005 and above you should consider using TRY-CATCH for all Insert-Update-Delete SQL operations. These constructs are still popular and appropriate in some cases.
Cheers, Dave Winters

SQL Azure Application locks

I'm working with a complex database and seeing how easy it is to port the application to SQL Azure. My initial impressions of the platform are positive. But complex and "interesting" SQL Server applications that leverage advanced features require some close scruity and care when operating in the Cloud world.

Microsoft makes it very clear that connections to the Azure database are not 100% continous. And this can have a subtle effect on transaction and locking features of traditional SQL 2008 applications.

Take for example the use of the SQL Server application lock facility. Look at this sequence:

----begin----
exec sp_getapplock @Resource='ALockTest',@LockMode='Exclusive',@LockOwner ='session'

SELECT APPLOCK_MODE('public', 'ALockTest', 'session');

exec sp_releaseapplock 'ALockTest', 'session'
----end----

If done "quickly" this will work as expected. BUT ... SQL Azure doesn't guarantee that the session is continous.

Try the above sequence again and put a long delay(I used 5 minutes) between the 1st step( sp_getapplock ) and the last two steps.

You'll probably get the following message from the sp_releaseapplock procedure.

Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
Cannot release the application lock (Database Principal: 'public', Resource: 'ALockTest') because it is not currently held.


As you can guess this is not what is expected.
There are other ways of implementing application locks. More on that later.

So if you are moving to SQL Azure you must take issues like this into consideration for the Cloud environment that is Azure.

Dave W

SQL Server x64 memory performance

I have a client who sells medical software and some of their clients are complaining about performance on SQL Server x64. A little research showed that the permissions for the account running the server were incorrect.

Looking at Microsoft's support site there are two links you should check out.
The first:
http://support.microsoft.com/kb/918483
Describes the problem in detail and permission and setting changes to help.

The second is a link the to hotfix that is available.
http://support.microsoft.com/kb/970070

Don't forget about enabling trace flag 845.

Dave

The Seldon Vault Group

Group page for forums and files.
http://groups.google.com/group/theseldonvault

Stored procedure with memory

Sometimes its helpful if a stored procedure can remember the last time it was run.
This is useful for processing data that is time stamped and has been added to a table since the last time the proc was run.

Most of the time keeping track of last runs is done by using a table. This proc demonstrates keeping information in the extended properties of the stored procedure.
Orginal source code is at: http://groups.google.com/group/theseldonvault

Dave

--


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.usp_RememberWhen') IS NOT NULL
DROP PROCEDURE dbo.usp_RememberWhen
go
-- ==========================================================================================
-- Author: Dave Winters
-- Create date: November 2009
-- URL: http://theseldonvault.blogspot.com/
-- Description: Demonstrate stored procedure keeping
-- the time it was last run without using a user table.
-- This technique could also be used to keep track of past parameters.
-- Performance of this technique has not been checked.
-- ==========================================================================================
CREATE PROCEDURE dbo.usp_RememberWhen
AS
BEGIN

SET NOCOUNT ON;
DECLARE @tdate DATETIME
DECLARE @lastdate DATETIME

SET @tdate = GETDATE()

IF NOT EXISTS(SELECT * FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL) )
EXEC sys.sp_addextendedproperty @name=N'last_run', @value=@tdate ,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
ELSE
BEGIN
SELECT @lastdate=CONVERT(DATETIME,[value]) FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL)
EXEC sys.sp_updateextendedproperty @name=N'last_run', @value=@tdate,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
END
--
-- Print out the value from last run
SELECT @lastdate AS "I RAN WHEN"
END
GO

--
-- Execise the solution
--
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)
EXEC usp_RememberWhen
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)



--

Welcome to Terminus

Hey - I just picked something I could remember for a name. For those of you SciFi fans the name is obvious.

Dave