March 4, 2004 at 1:19 am
Hi All,
I am getting an error saying that there is no "Begin" statement, even though it is present in the stored procedure.
Server: Msg 3902, Level 16, State 1, Procedure CategoryDet_SP_Ins, Line 81
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
CREATE PROCEDURE CategoryDet_SP_Ins
@in_intServid int,
@in_intCatid int,
@in_vcCatname varchar(200),
@in_vcCatdesc varchar(8000),
@in_dtExpdate datetime,
@in_btEnable bit,
@ou_intRetvalue int OUTPUT
AS
BEGIN
SET NOCOUNT ON
-- LOCAL VARIABLE DECLARATION.
DECLARE
@intError INT,
@vcCname VARCHAR(200),
@dtExpiry DATETIME
SELECT @vcCname = [NAME], @dtExpiry = EXPIRY_DATE FROM CATEGORY_DETAILS WHERE CATEGORY_ID = @in_intCatid
BEGIN TRANSACTION cat_details
IF (@vcCname != '' AND @dtExpiry IS NOT NULL)
BEGIN
IF (@vcCname != @in_vcCatname AND @dtExpiry != @in_dtExpdate)
BEGIN
IF (@in_intServid = 0)
BEGIN
INSERT INTO CATEGORY_DETAILS VALUES (@in_intCatid, NULL, @in_vcCatname, @in_vcCatdesc, @in_dtExpdate, @in_btEnable)
SELECT @intError = @@error
END
ELSE
BEGIN
INSERT INTO CATEGORY_DETAILS VALUES (@in_intCatid, @in_intServid, @in_vcCatname, @in_vcCatdesc, @in_dtExpdate, @in_btEnable)
SELECT @intError = @@error
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION cat_details
SELECT @ou_intRetvalue = -1
END
END
ELSE
BEGIN
IF (@in_intServid = 0)
BEGIN
INSERT INTO CATEGORY_DETAILS VALUES (@in_intCatid, NULL, @in_vcCatname, @in_vcCatdesc, @in_dtExpdate, @in_btEnable)
SELECT @intError = @@error
END
ELSE
BEGIN
INSERT INTO CATEGORY_DETAILS VALUES (@in_intCatid, @in_intServid, @in_vcCatname, @in_vcCatdesc, @in_dtExpdate, @in_btEnable)
SELECT @intError = @@error
END
END
IF (@intError != 0)
BEGIN
ROLLBACK TRANSACTION cat_details
SELECT @ou_intRetvalue = -2
END
ELSE
BEGIN
COMMIT TRANSACTION cat_details
SELECT @ou_intRetvalue = 0
END
END
Can you guys look into it and help me come out of the prob.
Lucky
March 4, 2004 at 3:39 am
Hi,
I could solve the problem.
Lucky
March 5, 2004 at 2:49 pm
"END" statement is missing.
March 8, 2004 at 3:15 pm
I have this same problem with a procedure of mine! I have about 12 procedures with the same layout that run on a weekly basis, and one of them returns this error, but the rest run just fine. The transaction is committed just fine - it just tells me that a COMMIT exists without a BEGIN TRANSACTION. Strange - does anybody know if this is an SQL bug?
Ryan
March 9, 2004 at 2:44 am
I haven't got your immediate solution however I find that problems with missing commits and begins are normally due to errors in the preceeding SQL statements. I copied your code and stripped it down to just the begin and commit/rollback statements without the intervening code and query analyser was quite happy with it. Suggest you make sure all your queries are returning properly.
BEGIN
declare @interror int
set @interror = 1
BEGIN TRANSACTION cat_details
IF (@intError != 0)
BEGIN
ROLLBACK TRANSACTION cat_details
END
ELSE
BEGIN
COMMIT TRANSACTION cat_details
END
END
March 9, 2004 at 8:50 am
Yes I too feel, the syntax is fine, however there should be some issue with the query, I do not see any place where a END is missing.
Prasad Bhogadi
www.inforaise.com
March 9, 2004 at 8:55 am
In my procedure, if I remove the BEGIN TRANSACTION and COMMIT TRANSACTION, the script runs just fine and commits at the end. The reason that I do the conditional commit is that I'm clearing a warehouse table and repopulating it. If the repopulate fails for some reason, I want the original data to remain in the table. It's strange - the code runs great without the transaction, and the transaction runs great without the code, but the two can't run well together.
Ryan
March 9, 2004 at 9:02 am
Hi Ryan,
Well you definitely need to have this wrapped in a transaction however you may want to check if you have proper error handling taken care after insert and update statements.
Prasad Bhogadi
www.inforaise.com
March 9, 2004 at 10:06 am
Is there a better way to handle this? I could do error handling after each statement - something like:
--------------------------------------------
IF @@error 0
GOTO LABEL_ROLLBACK
--------------------------------------------
I would have that statement after each TSQL statement in my procedure. At the end of the procedure, I could have the following code:
--------------------------------------------
COMMIT TRANSACTION
GOTO LABEL_END
LABEL_ROLLBACK:
ROLLBACK TRANSACTION
LABEL_END:
GO
--------------------------------------------
This code would allow for the rollback if there were any errors, and if there weren't, it would get all the way to the end and commit the transaction, then jump to the end of the procedure. This seems a little tedious though, but maybe it's the only way to do it. I checked, but I didn't see any sort of an SQL flag that would get marked as TRUE (or to any other non-null value) if an error occurs anywhere in the procedure, although that's more what I'm looking for.
Thanks for the help.
Ryan
March 9, 2004 at 1:36 pm
I agree with other comments made about error in one of the sql statements within the transaction.
As I understand it if you get an error an implicit rollback takes place so you cannot then commit.
You would need to deal with this in your error handling because if the transaction has been implicitly rolled back you will get an error when you try to explicitly rollback.
Regards
John
March 9, 2004 at 3:14 pm
I checked more thouroghly through the code, and I don't get any errors at all, so I'm totally clueless as to why this is happening. Is there a chance that some types of errors that might begin a rollback of sorts aren't being displayed? I imagine that if a rollback in initiated, I would be notified - it wouldn't just happen without me knowing about it.
I wonder if it's a SQL bug that's causing it.
Ryan
March 9, 2004 at 3:34 pm
If a you have a SELECT statement within a transaction than produces an error the transaction is rolled back without any specific indication. You should however get the error message from the SELECT statement.
Regards
John
March 9, 2004 at 3:55 pm
The problem isn't syntax - it is data related - if the following statement is false then the error will occur
IF (@vcCname != @in_vcCatname AND @dtExpiry != @in_dtExpdate)
I have copied the code into query analyzer and it seems to me that if you reach the first ROLLBACK statement, where you set @ou_intRetvalue = -1, you have now lost your transaction but the query will still attempt to commit at the end - giving you your error.
Regards
John
March 9, 2004 at 4:33 pm
In doing some investigation on my own code, I've encountered some REALLY strange transaction behavior in SQL2K. When I ran just the "BEGIN TRANSACTION", followed by a "SELECT @@TRANCOUNT", I got a value of 1. If I then grabbed the code from my procedure, which included some select statements to variables, a select into, and 3 create temporary tables, with the begin transaction and the select @@trancount being the last thing in my selection, select @@trancount told me that there were 0 transactions open. In other words, the begin transaction statement immediately preceeding the select @@trancount had failed to begin a transaction. This was the behavior that was causing my error. I had to copy all the code up to that point into a separate window and play around with it.
After about 15 minutes of playing around with the code (renaming the tables, opening transactions, creating tables, closing the transaction, changing the tables from temporary to perm one at a time, and other misc. changes), the code suddenly worked, successfully begining the transaction. When this code (which matched the old code CHARACTER-FOR-CHARACTER) was copied back to the original script, it ran perfectly. I really hate running into phantom code bugs, where SQL just doesn't like the way the code's written, even though the code itself is perfect. Does anybody else ever exerience this type of thing?
Ryan
March 10, 2004 at 2:50 am
Unfortunately I don't think I've ever written any perfect code so I'm affraid I can't empathise.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply