OH MY!
-
This is my second go at working with transactions and thank goodness i am finally getting a little grip on this stuff.
I keep receiving this error when inserting into the employees table.
For one i know that the names that i am using are not Duplicates, and i am receing this error.
Can someone please shed some light one this for me!
**** All comments are very welcome!! Bring it on!!! ****
Thanks very much for the help!
Erik.....
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 3903, Level 16, State 1, Procedure My_First_Transaction, Line 30
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 50000, Level 1, State 50000
Employee Duplicate record could not be created Dummy!
(1 row(s) affected)
Stored Procedure: Northwind.dbo.My_First_Transaction
Return Code = -6
ALTER PROCEDURE My_First_Transaction
@FirstName varchar (50),
@LastName varchar (50)
AS
BEGIN TRANSACTION
INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName,@LastName)
IF @@ERROR = 0
BEGIN
INSERT INTO TableMessage (Message, FullName) VALUES('There has been a new update', @FirstName + ' ' + @LastName)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('AUDIOT OF EMPLOYEE FAILED.',1,1)
INSERT INTO tabErrorLog (ErrorType, Message)VALUES ('Audit Failure', 'Audit of Employee Creation Failed')
if @@ERROR <> 0
BEGIN
RAISERROR ('Error log couldn''t be updated', 1, 1)
END
END
END
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Employee Duplicate record could not be created Dummy!', 1,1)
INSERT INTO tabErrorLog (ErrorType, Message)VALUES ('Duplicate Record', 'Duplicate Employee Record could not be created! Dummy')
if @@ERROR <> 0
BEGIN
RAISERROR ('Error log couln not be updated.', 1,1)
END
END
** I have to step out for a couple of hours so i will be able to answer later this afternoon!
Dam again!
-
Erik,
Your SECOND ROLLBACK is always executed regardless of the path that the code took before!
I suggest you indent you code to get a better Idea of what's going on
Cheers,
* Noel -
same of above
-
It appears your missing an else Bolded below.
Hint Indent your code for readability.
ALTER PROCEDURE My_First_Transaction
@FirstName varchar (50),
@LastName varchar (50)
AS
BEGIN TRANSACTION
INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName,@LastName)
IF @@ERROR = 0
BEGIN
INSERT INTO TableMessage (Message, FullName) VALUES('There has been a new update', @FirstName + ' ' + @LastName)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('AUDIOT OF EMPLOYEE FAILED.',1,1)
INSERT INTO tabErrorLog (ErrorType, Message)VALUES ('Audit Failure', 'Audit of Employee Creation Failed')
if @@ERROR <> 0
BEGIN
RAISERROR ('Error log couldn''t be updated', 1, 1)
END
END
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Employee Duplicate record could not be created Dummy!', 1,1)
INSERT INTO tabErrorLog (ErrorType, Message)VALUES ('Duplicate Record', 'Duplicate Employee Record could not be created! Dummy')
if @@ERROR <> 0
BEGIN
RAISERROR ('Error log couln not be updated.', 1,1)
END
END
-
Noel beat me to the punch.
ALTER PROCEDURE My_First_Transaction
@FirstName varchar (50),
@LastName varchar (50)
AS
BEGIN TRANSACTION
INSERT INTO Employees( FirstName, LastName) VALUES( @FirstName,@LastName)
IF @@ERROR = 0
BEGIN
INSERT INTO TableMessage( Message, FullName)
VALUES( 'There has been a new update', @FirstName + ' ' + @LastName)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR( 'AUDIOT OF EMPLOYEE FAILED.', 1, 1)
INSERT INTO tabErrorLog( ErrorType, Message)
VALUES( 'Audit Failure', 'Audit of Employee Creation Failed')
IF @@ERROR <> 0
BEGIN
RAISERROR( 'Error log couldn''t be updated', 1, 1)
END
END
END
BEGIN
ROLLBACK TRANSACTION
RAISERROR( 'Employee Duplicate record could not be created Dummy!', 1, 1)
INSERT INTO tabErrorLog (ErrorType, Message)
VALUES( 'Duplicate Record', 'Duplicate Employee Record could not be created! Dummy')
IF @@ERROR <> 0
BEGIN
RAISERROR( 'Error log couln not be updated.', 1, 1)
END
END
I wasn't born stupid - I had to study.
-
Thanks alot for the help!
Could someone please tell me what the 1,1, is for in this raised error?
RAISERROR( 'AUDIOT OF EMPLOYEE FAILED.', 1, 1)
For this one there is two errors,, one for the Previous table, and then another one for the current update?
BEGIN
ROLLBACK TRANSACTION
RAISERROR( 'AUDIOT OF EMPLOYEE FAILED.', 1, 1)
INSERT INTO tabErrorLog( ErrorType, Message)
VALUES( 'Audit Failure', 'Audit of Employee Creation Failed')
IF @@ERROR <> 0
BEGIN
RAISERROR( 'Error log couldn''t be updated', 1, 1)
END
END
END
Dam again!
-
>>Could someone please tell me what the 1,1, is for in this raised error?<<
This is use to generate an error with no consequences (on client side). Meaning only informational nature like a warning type because the severity and the state are set to 1. It is used also with the "with no wait" flag at the end to report intermediate results when a loop is running and you want transcient data back to the client while the batch is still executing
Cheers!
* Noel -
Let me reach for my asprin
Dam again!
-
Also for the END,,,, Should i have an END for every IF,,, AND Begin Transaction???
Thanks,
Erik,.,..
Dam again!
-
you should have an END for every BEGIN
-
What about the IF? Do i need a END for any of the IF?
Keep receiving this error message,, can someone please tell me where i need the Begin?
And is it legal to not put a Transaction after the Begin?
Thanks!
Erik..
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Error================
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 3903, Level 16, State 1, Procedure My_First_Transaction, Line 28
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 50000, Level 1, State 50000
Employee Duplicate record could not be created Dummy!
Server: Msg 208, Level 16, State 1, Procedure My_First_Transaction, Line 30
Invalid object name 'tabErrorLog'.
Stored Procedure: Northwind.dbo.My_First_Transaction
ALTER PROCEDURE My_First_Transaction
@FirstName varchar (10),
@LastName varchar (20)
AS
BEGIN TRANSACTION
INSERT INTO Employees( FirstName, LastName) VALUES( @FirstName,@LastName)
IF @@ERROR = 0
BEGIN
INSERT INTO TableMessage( Message, FullName)
VALUES( 'There has been a new update', @FirstName + ' ' + @LastName)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR( 'AUDIOT OF EMPLOYEE FAILED.', 1, 1)
INSERT INTO tabErrorLog( ErrorType, Message)
VALUES( 'Audit Failure', 'Audit of Employee Creation Failed')
IF @@ERROR <> 0
BEGIN
RAISERROR( 'Error log couldn''t be updated', 1, 1)
END
END
END
BEGIN
ROLLBACK TRANSACTION
RAISERROR( 'Employee Duplicate record could not be created Dummy!', 1, 1)
INSERT INTO tabErrorLog (ErrorType, Message)
VALUES( 'Duplicate Record', 'Duplicate Employee Record could not be created! Dummy')
IF @@ERROR <> 0
BEGIN
RAISERROR( 'Error log couln not be updated.', 1, 1)
END
END
Dam again!
-
ALTER PROCEDURE My_First_Transaction @FirstName varchar (10 ) , @LastName varchar (20 ) AS BEGIN TRANSACTION INSERT INTO Employees( FirstName, LastName ) VALUES( @FirstName,@LastName ) IF @@ERROR = 0 BEGIN INSERT INTO TableMessage( Message, FullName ) VALUES( 'There has been a new update', @FirstName + ' ' + @LastName ) IF @@ERROR = 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK TRANSACTION RAISERROR( 'AUDIOT OF EMPLOYEE FAILED.', 1, 1) INSERT INTO tabErrorLog( ErrorType, Message) VALUES( 'Audit Failure', 'Audit of Employee Creation Failed' ) IF @@ERROR <> 0 BEGIN RAISERROR( 'Error log couldn''t be updated', 1, 1 ) END END END ELSE <<--- MISSING BEGIN ROLLBACK TRANSACTION RAISERROR( 'Employee Duplicate record could not be created Dummy!', 1, 1 ) INSERT INTO tabErrorLog (ErrorType, Message) VALUES( 'Duplicate Record', 'Duplicate Employee Record could not be created! Dummy' ) IF @@ERROR <> 0 BEGIN RAISERROR( 'Error log couln not be updated.', 1, 1 ) END END
The structure for IF ... ELSE should be
IF Boolean_expression BEGIN sql_statement END ELSE BEGIN sql_statement END
You can leave out the BEGIN ... END if you only have a single statement, but I think it's best practice to include them all the time.
--------------------
Colt 45 - the original point and click interface
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply