This Blog Has Been Moved !
This Blog Has been moved to http://aleemkhan.wordpress.com
Error Handling has always been cumbersome in SQL server, with all the unstructured @@Error checks scattered all around the place, this makes the T-SQL code harder to read and many errors go unaddressed due to simple coding mistakes. SQL Server 2005 comes to the rescue with the new TRY…CATCH block, but it is important how the try catch block is used. Also, recently I have had many issues with the Nested Transactions and the Nested Stored Procedures each using its own transaction.
The issues I am referring to are the things like the TRANCOUNT check on the entry and exit of each stored procedure. For example you are in a stored procedure which is called by another outer procedure (both using BEGIN TRAN), when you call Rollback in the second procedure, SQL Server will throw the following error on the exit of the second procedure and will leave the transaction is a unconsistent state
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count =0. "
Similarly, as I mentioned in a previous post a single ROLLBACK will set the @@TRANCOUNT to zero and any subsequent ROLLBACK in an outer procedure will give the following error
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."
Infact, all these small things are very trivial in T-SQL and there are several ways to solve them, for example some people try not to use the transactions at database at all, they take the TRANSACTION Management completely to their data layer and manage it through MTS. (This was the case in one of the projects I worked on last year). Similarly, some people would always take care that Transaction is being used in only the outer procedure which is to be called by the data layer etc. However, this can easily break if you are not in control of all the T-SQL code you are writing,like if you have an encrypted third-party stored procedure, you wouldn’t know if it calls the ROLLBACK/COMMIT which may break your logic in the outer procedure.
There should be some standard pattern for the Error Handling and writing standard stored procedure, and especially with the availability of the new Error Handling features in SQL Server 2005. So, I came up with the following Pattern for a standard stored procedure using an independent transaction. This stored procedure will not break on the behavior of the procedure it calls and is returning the error messages to its caller in a standard way (RAISERROR)
ALTER PROCEDURE [dbo].[StandardTSQLProcedure]
AS
The issues I am referring to are the things like the TRANCOUNT check on the entry and exit of each stored procedure. For example you are in a stored procedure which is called by another outer procedure (both using BEGIN TRAN), when you call Rollback in the second procedure, SQL Server will throw the following error on the exit of the second procedure and will leave the transaction is a unconsistent state
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count =0. "
Similarly, as I mentioned in a previous post a single ROLLBACK will set the @@TRANCOUNT to zero and any subsequent ROLLBACK in an outer procedure will give the following error
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."
Infact, all these small things are very trivial in T-SQL and there are several ways to solve them, for example some people try not to use the transactions at database at all, they take the TRANSACTION Management completely to their data layer and manage it through MTS. (This was the case in one of the projects I worked on last year). Similarly, some people would always take care that Transaction is being used in only the outer procedure which is to be called by the data layer etc. However, this can easily break if you are not in control of all the T-SQL code you are writing,like if you have an encrypted third-party stored procedure, you wouldn’t know if it calls the ROLLBACK/COMMIT which may break your logic in the outer procedure.
There should be some standard pattern for the Error Handling and writing standard stored procedure, and especially with the availability of the new Error Handling features in SQL Server 2005. So, I came up with the following Pattern for a standard stored procedure using an independent transaction. This stored procedure will not break on the behavior of the procedure it calls and is returning the error messages to its caller in a standard way (RAISERROR)
ALTER PROCEDURE [dbo].[StandardTSQLProcedure]
AS
BEGIN TRY
DECLARE
@TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE
@TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
-------------------------------------------------------
--Your Standard T-SQL Code for the Procedure Comes Here
-------------------------------------------------------
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();
--Your Standard T-SQL Code for the Procedure Comes Here
-------------------------------------------------------
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
Check out this Channel9 video with Jason Zander on the renaming of WinFX to .NET 3.0 and what it contains. He has tried to remove the confusion with a nice diagram explaining different technology stacks and their relationship to each other.
I don’t remember how many times I have written this, nearly for every ASP.NET project I have to write this, so I am posting so I can get it back. It is a simple method of the getting the control with the server side id. ASP.NET changes the Id’s of nested server side controls when page is rendered.
This function simply compares the last part of the control id with the server side id of the control (passed as parameter)
function findObjWithClientId(Id)
{
var ctrls = document.all;
for(var count = 0; count < ctrls.length ; count ++)
{
var index = ctrls[count].id.indexOf(Id);
if(index != -1)
{
if((ctrls[count].id.length - index) == Id.length)
{
return ctrls[count];
}
}
}
return null;
}
Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:
- XML types cannot convert to text or ntext data types.
- No data type other than one of the string types can be cast to XML.
- XML columns cannot be used in GROUP BY statements.
- Distributed partitioned views or materialized views cannot contain XML data types.
- Use of the sql_variant instances cannot include XML as a subtype.
- XML columns cannot be part of a primary or foreign key.
- XML columns cannot be designated as unique.
- Collation (COLLATE clause) cannot be used on XML columns.
- XML columns cannot participate in rules.
- The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.
- Tables can have only 32 XML columns.
- Tables with XML columns cannot have a primary key with more than 15 columns.
- Tables with XML columns cannot have a timestamp data type as part of their primary key.
- Only 128 levels of hierarchy are supported within XML stored in the database.
Vertscape Infotech (our company) won 3 awards at the Microsoft Partner Conference this year. The awards are as follows
- Technology Innovation Partner of the Year
- Winning Customer Award (Application Platform - .NET)
- CRN Technology Innovation Partner of the Year
See the results here
http://www.microsoft.com/Partner/events/wwpartnerconference/awards_finalists.htm
Recently, I had an issue with nested transaction in SQL Server, pretty simple thing but I realized that I was'nt taking care of this for most of the T-SQL code I write. Fortunately, never had a problem but I thought I shoud post about this so I could remeber this.
Try running the following T-SQL code in SQL Server
BEGIN TRANSACTION
BEGIN TRANSACTION
-- Some Code
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
This code simpley throws an error "
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". Hey thats strange, I have two BEGIN statements so a I need a couple of ROLLBACKS to Roll them back, it seems logical but actually its not.
Transaction are managed in SQL Server through a transaction count, each new BEGIN TRAN statement adds one to the TRAN Count, and a single Rollback rightly makes the TRAN Count zero, so everything is Rolled back with one Rollback.
I can definately take care of this in my own code, but what happens if I am using a third party proceudre which ROLLBACKS the transaction, how do I know I have to put a ROLLBACK in my own code or not. If an encrypted third party procedure calls Rollback before my code, my procedure will throw error when it calls its Rollback.
The Best Pattern to do this is to use the @@TRANCOUNT variable before EACH ROLLBACK, so the the way ROLLBACK should be called is
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
Neat thing to learn !
Try running the following T-SQL code in SQL Server
BEGIN TRANSACTION
BEGIN TRANSACTION
-- Some Code
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
This code simpley throws an error "
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". Hey thats strange, I have two BEGIN statements so a I need a couple of ROLLBACKS to Roll them back, it seems logical but actually its not.
Transaction are managed in SQL Server through a transaction count, each new BEGIN TRAN statement adds one to the TRAN Count, and a single Rollback rightly makes the TRAN Count zero, so everything is Rolled back with one Rollback.
I can definately take care of this in my own code, but what happens if I am using a third party proceudre which ROLLBACKS the transaction, how do I know I have to put a ROLLBACK in my own code or not. If an encrypted third party procedure calls Rollback before my code, my procedure will throw error when it calls its Rollback.
The Best Pattern to do this is to use the @@TRANCOUNT variable before EACH ROLLBACK, so the the way ROLLBACK should be called is
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
Neat thing to learn !
Recently, working in the SQL server Management Studio, I spent a couple of hours searching for the Stored Procedure Debugging. As the Management Studio replaces both Query Analyzer and Enterprise Manager from the previous version of the SQL Server Tools, it is very common to assume that there must be some Debugging option available in the new Management Studio.
However there isn’t any, and you need Visual Studio 2005 for stored Procedure Debugging, this is not only for the SQL-CLR procedures written in a .NET language but even for the T-SQL Procedures. You need to connect the “Server Explorer” to the SQL Server, select your procedure and on right click you will find the “Step Into Procedure” option which will let you step into the procedure for debugging.
Pretty easy, if you know to debug with Visual Studio in the first place, but there are issues with the remote debugging. You need to check if the Remote Debugging Service is running on the server and you need to have the network credentials for the server, preferably your account should be a member of server administrators group.
So, finally after wasting half day, I got everything working.
:)
However there isn’t any, and you need Visual Studio 2005 for stored Procedure Debugging, this is not only for the SQL-CLR procedures written in a .NET language but even for the T-SQL Procedures. You need to connect the “Server Explorer” to the SQL Server, select your procedure and on right click you will find the “Step Into Procedure” option which will let you step into the procedure for debugging.
Pretty easy, if you know to debug with Visual Studio in the first place, but there are issues with the remote debugging. You need to check if the Remote Debugging Service is running on the server and you need to have the network credentials for the server, preferably your account should be a member of server administrators group.
So, finally after wasting half day, I got everything working.
:)
