April 19, 2011 at 6:30 am
I am using Sql Server 2005.
I have created one SP and this SP contains series of select,insert etc statements.My requirement is : If all the statement inside try clause execute successfully then commit the transaction else roll back all transaction.
But after execution i am getting the below error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. ---> System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
My SP is like this..please let me know if any changes require in below script.
CREATE Procedure [dbo].[InsertDataIntALL]
(
@ToValueDate bigint,
@FromValueDate bigint
)
AS
SET NOCOUNT on
BEGIN TRY
BEGIN TRANSACTION
SELECT A.* INTO #A FROM A with (nolock)
WHERE [ValueDate] = @FromValueDate
SELECT B.* INTO #B FROM B with (nolock)
WHERE [ValueDate] = @FromValueDate
SELECT C.* INTO #C FROM C with (nolock)
WHERE [ValueDate] = @FromValueDate
Delete from A where ValueDate = @ToValueDate
Delete from B where ValueDate = @ToValueDate
Delete from C where ValueDate = @ToValueDate
UPDATE A SET [ValueDate] = @ToValueDate
UPDATE B SET [ValueDate] = @ToValueDate
UPDATE C SET [ValueDate] = @ToValueDate
INSERT INTO A SELECT * FROM #A
INSERT INTO B SELECT * FROM #B
INSERT INTO C SELECT * FROM #C
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 --some error has occurred
ROLLBACK TRAN --so rollback all transactions
END CATCH
April 19, 2011 at 6:37 am
Karan_W (4/19/2011)
... If all the statement inside try clause execute successfully then commit the transaction else roll back all transaction...
How about posting the whole sproc?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 6:48 am
It looks as though you stored procedure is being invoked by something that has already started a transaction, (i.e. the transaction count is already 1 when the stored procedure starts) and you are executing a ROLLBACK.
When you begin another transaction inside your SP, the count will be increased to 2. If the SP COMMITS, this will be decremented to 1, and SQL Server is happy. If you ROLLBACK, the count is set to 0, which is why you are getting the message.
You can deal with this in a couple of ways...
Rather than rollback, signal to the calling process that it needs to rollback
or
Stick with what you have and accept the fact that a rollback will also generate this error.
For more information, read the error handling articles here http://www.sommarskog.se[/url%5D
April 20, 2011 at 1:28 am
Thanks Ten for reply.
The link you provided is really nice.
I have resolved that issue..I added the beloe statement for catch block to pass the actual error to client.And this returned me the actual error.
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK Tran
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply