May 6, 2011 at 9:09 am
Afternoon All
This is somewhat perplexing.
I have a query (planning to build it into a sproc) that starts a transaction, does some inserts and if the number or records inserted adds up, commits it, or rolls back if not.
At least, that's what it's supposed to do. Instead it completes, reporting all is ok and happy, leaving the transaction open and leaving me scratch my head for an hour trying to work out why this table wont do anything.
BEGIN TRANSACTION
DECLARE @iCountBefore AS INT
DECLARE @iCountAfter AS INT
DECLARE @iCountAvailable AS INT
SELECT@iCountBefore = COUNT(*)
FROMtblTvlInvites
SELECT@iCountAvailable = COUNT(*)
FROMtblimportdaily_ageukdif_tvl
WHEREbProcessedInd = 0
INSERT INTO tblTvlInvites
(cPolicyNumber
,vSurname
,cPostcode
,sRenewalDate
,sLoadDate
,sTargetGlDate
)
SELECT [Policy_Ref]
,[Customer surname]
,[postcode]
,[renewal date]
,CONVERT(CHAR(11),[dWhenLoaded],106)
,dbo.funGIdate([renewal date])
FROMtblimportdaily_ageukdif_tvl
WHEREbProcessedInd = 0
SELECT@iCountAfter = COUNT(*)
FROMtblTvlInvites
IF (@iCountAfter = (@iCountBefore + @iCountAvailable))
BEGIN
COMMIT
UPDATEtblImportDaily_ageukdif_tvl
SETbProcessedInd = 1
WHEREbProcessedInd = 0
END
IF (@iCountAfter <> (@iCountBefore + @iCountAvailable))
BEGIN
ROLLBACK
END
Any ideas?
Thanks,
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 6, 2011 at 9:30 am
what are your counts? my guess would be one of them is null, so it will never get into either of your if statements.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 9:35 am
Did you try to run it from SSMS directly and as it's posted in here using a new query window?
It could be there is a transaction open before your BEGIN TRANSACTION. Add a SELECT @@TRANCOUNT as your very first statement to see if there are any open transactions.
In that case you're faced with a "Nesting Transactions" issue (see BOL for detais).
To make it short: Even though SQL Server allows for nested transactions, it'll simply only react on the outer most transaction. As long as @@TRANCOUNT<>1 neither COMMIT nor ROLLBACK will cause any effect. Edit: except for reducing the value of @@TRANCOUNT
May 8, 2011 at 12:56 pm
Your COMMIT and ROLLBACK commands are both under IF's and it can happen that none of them is executed (e.g. if one of the variable in logical expression is NULL or doue to an error in expression that set their value). Use ELSE instead.
Check for @@TRANCOUNT before running the code, it should be 0.
Before COMMIT or ROLLBACK you should always put "IF @@TRANCOUNT>0".
Read my post about transactions here:
http://www.sqlservercentral.com/Forums/Topic1095536-145-1.aspx#bm1099845
May 9, 2011 at 2:20 am
Thanks guys,
Everything on this server is supposed to run sequentially, however, I ran this at the same time as another batch of stored procs were running - it would be highly likely that another transaction was running at the time.
I'll probably pop an else in there instead of 2 if statements, or just coalesce the counts.
Thanks for your help.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 9, 2011 at 11:43 am
Others have explained what could cause the transactions to not commit, but I have to ask why you are counting rows before and after, and based on the value, either commit or rollback. Do you fear that records suddenly dissapear, or that new records are inserted/deleted while your insert is running? You know, SQL Server is ACID, it's all or nothing.
Can you please explain the reason for you row count checking. If you're afraid of other simultaneous modifications during you job, then you should consider chosing a more appropriate isolation level. But again, I don't know what you are trying to accomplish/prevent with your row count check.
May 9, 2011 at 3:46 pm
Why count at all ?
If you want to check that all rows from the source table (tblimportdaily_ageukdif_tvl) are successfully inserted into target table (tblTvlInvites) you could check if @@rowcount after the insert command is the same as number of rows in source table.
OUTPUT clause might also be very useful to you:
May 10, 2011 at 1:32 am
simply force of habit.
There is a fair amount of missing or incomplete data in my database. Most of my jobs on this server use temporary tables and there are lots of places where rows can go missing on inner joins. I use row counts and commits etc so that when we identify missing records we can 'fix' the data before re running the programs.
I realise it's not at all necessary here but seeing as it failed to commit I was curious as to why!
Thanks for your help with it.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply