Transaction noob

Transaction noob

Post by Andre » Wed, 19 Mar 2003 08:44:01



Hey all,

I am trying to get a handle on performing a Transaction stored procedure,
but seem to still have a couple pieces missing.  I have created a small test
SP to simulate what I eventually want to do, and am trying to make it break
so I can code for that possibility.  However, SQL Server (or possibly Query
Analyzer) is not playing nice.

I created two small tables (TestTable and TestTable2) each with a couple of
fields in them.  I perform an INSERT command on the first, grab the newly
created ID number, then place that ID in the second table along with some
additional text.  If all goes well I want to COMMIT, but if an error happens

in a variable right after the INSERT is called (per the BOL), and then at
the end of everything I test the Error values to see if anything broke.  If
so do the ROLLBACK, if not COMMIT.

Sounds simple enough, and according to the BOL, this is how to do it.  But
as I said before I want to purposely break the thing so I can see how the
error trapping I created is sent back out.  So, I set the "SomeText" field
in TestTable2 to be 5 characters max, and tried passing in a longer text
string.  Query Analyzer returns:

"Server: Msg 8152, Level 16, State 9, Procedure spTestTrans, Line 21
String or binary data would be truncated.
The statement has been terminated."

It does not, apparently, finish the TRANSACTION and perform the error
trapping I wrote.

Is there something I need to do to force the error handling to run through
the SQL I wrote?  Is this simply because I am creating/testing in Query
Analyzer?  Did I miss something in the SQL SP?

I ask for your help, and please, this is my first attempt at Transactions,
so the more straightforward the responses the better I will be able to
follow you.  Thanks for any input!

-- Andrew

The test Transaction SP:
CREATE PROCEDURE spTestTrans
(



)
AS




 BEGIN TRANSACTION

 SET NOCOUNT ON
 INSERT INTO TestTable (Job_Desc, Min_Lvl, Max_Lvl)



 INSERT INTO TestTable2 (tt1ID, SomeText)




  BEGIN
  COMMIT TRANSACTION

  END
 ELSE
  BEGIN
  ROLLBACK TRANSACTION


  SELECT 'ErrorMsg' = 'An error occurred during execution of the TestTable
Table INSERT statement.'


  SELECT 'ErrorMsg' = 'An error occurred during execution of the TestTable2
Table INSERT statement.'

  END
GO

 
 
 

Transaction noob

Post by Nigel Rivet » Wed, 19 Mar 2003 09:10:54


What you have looks ok.
Your error is probably terminating the proess.
Try it with a primary key error.

create table tx (i int primary key)
go

create proc tx_ins
as


begin tran

insert tx select 1

insert tx select 1


begin
rollback tran

select 'failed insert 1'

select 'failed insert 2'
end
else
commit tran
go

exec tx_ins

you should get

(1 row(s) affected)

Server: Msg 2627, Level 14, State 1, Procedure tx_ins, Line 10
Violation of PRIMARY KEY constraint 'PK__tx__44CA3770'. Cannot insert
duplicate key in object 'tx'.
The statement has been terminated.

---------------
failed insert 2

(1 row(s) affected)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Transaction noob

Post by Nigel Rivet » Wed, 19 Mar 2003 09:21:10


By the way it is usual to stop as soon as you get an error

and having a single error flag

create proc tx_ins
as


begin tran

insert tx select 1


begin

goto errhnd
end
insert tx select 1


begin

goto errhnd
end
commit tran
return

errhnd:
rollback tran

go

or setting a context before the inserts

create proc tx_ins
as


begin tran

insert tx select 1



goto errhnd

insert tx select 1


goto errhnd
commit tran
return

errhnd:
rollback tran

go
.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Noob question, probably very simple (sorry I don't know this myself)

I wanted to know how to do the following in SQL.

I have a table with a column (of text) called "Answer"

In answer is an XML substring that I'd like to be able to update, for
example:

If Answer was equal to:

"This is my answer <SOME TAG>Stuff</SOME TAG> and that was my tag"

and I wanted to change this to:

"This is my answer <NEW TAG>Other Stuff</NEW TAG> and that was my tag"

Now, I know I could simply get the original answer, modify it and simply
update it; however, I'd like to be able to update just based upon what tags
I need to change, not knowing the answer in its whole.

How do I make a SQL query that simply replaces <SOME TAG>Stuff</SOME TAG>
with the new one in every answer that has <SOME TAG> in it...?

    Thanks,

            WTH:)

2. Dynamic menus (user-spesific)

3. Stored procedure noob

4. SQL Stack Dump

5. Sql search by date? Help-Noob

6. AIX 4.3.3 and postgresql 7.0.3

7. replication noob--rhymes w/ boob

8. java.io.NotSerializableException: com.microsoft.jdbc.base.BaseResultSet

9. Sort of a noob question...

10. 2 tiny questions of a Noob

11. noob - DSN connection to SQL Server using SQLOLEDB

12. distributed transaction,nested transactions,sp_getapplock

13. SQL Server transaction and ADO transaction