Timeout Errors Prevention

After a while, you may end up getting complaints or request dealing with timeout issues. I’m going to use this post to help collect some ways to increase the timeout seconds and prevent errors from happening.

*Note: Even though you increase the timeout seconds, timeouts might still occur but with a longer delay then before. This might make your customers even more upset because of the longer wait for the error to display.

• In code, increase the sqlCommand timeout. Ex:
   

Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)

myCommand.CommandType = CommandType.StoredProcedure
'change default time out setting
myCommand.CommandTimeout = 120

• In code, increase the connection’s string timeout by appending “Connection Timeout=” to it. Ex:
  
  Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120
  
• On SQL-Server 2005, In management studio’s Tools > Option > Designers Increase the “Transaction time-out after:” even if  “Override connection string time-out value for table designer updates” checked/unchecked. 
  
• Make (non-dynamic) stored procedures instead of using inline sql statements within the code. This will also allow for easy fixes instead of having to recompile and deploy for future changes.

 

If you have any other suggestions, please contact me so I can add them to the list.

Quick Reminder: Parsing in T-Sql

When a set of values are sent in as a varchar and need to be parsed. Ex: '1,2,3,4'
In this example the values are sent into the stored procedure's variable @atInvNum.

DECLARE @aInvNum varchar(4000)

SET @aInvNum = LTRIM(RTRIM(@atInvNum))
DECLARE @pos INT
DECLARE @piece varchar(500)

DECLARE @tInvNum TABLE (ID int)

IF right(rtrim(@aInvNum),1) <> ','
SET @aInvNum = @aInvNum + ','

SET @pos = patindex('%,%' , @aInvNum)
WHILE @pos <> 0
BEGIN
SET @piece = left(@aInvNum, @pos - 1)

INSERT INTO @tInvNum SELECT cast(@piece as int)

SET @aInvNum = stuff(@aInvNum, 1, @pos, '')
SET @pos = patindex('%,%' , @aInvNum)
END


---------------------A better way---------------------

IF (LEN(RTRIM(LTRIM(@atTaskIDs))) > 0)
SET @atTaskIDs = ',' + @atTaskIDs + ','

CHARINDEX(',' + Convert(varchar, SRT.TASK_ID) + ',', @atTaskIDs) > 0 )

Rollbacks and Commits in Stored Procedures

So, I ran into this problem of my transaction counts being offset. The problem was that store procedure A has a "Begin Transaction" then calls store procedure B which also has it's transactions of begin, commit, and rollback.

Microsoft says:

If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. This can happen in two ways:

A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a ROLLBACK TRANSACTION statement. @@TRANCOUNT decrements to 0 and causes an error 266 when the stored procedure completes.


A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a COMMIT TRANSACTION statement. @@TRANCOUNT decrements by 1 and causes an error 266 when the stored procedure completes. However, if BEGIN TRANSACTION is executed after the COMMIT TRANSACTION, the error does not occur.

-- http://msdn2.microsoft.com/en-us/library/ms187844.aspx

DECLARE @LocalTransActive Bit
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION Trans_Discharge
SET @LocalTransActive = 1
END

For the commit part:
IF @LocalTransActive = 1
BEGIN
COMMIT TRANSACTION Trans_Discharge
END

For the rollback part:
IF @@TRANCOUNT > 0
BEGIN
IF @LocalTransActive = 1
BEGIN
ROLLBACK TRANSACTION Trans_Discharge
END
END