June 13, 2011 at 8:13 am
I have a temptable in my SP where I insert values into the temptable 7 times.i need to keep track of the inserted rows in a logtable.I am using @@rowcount but the value is 0 for all inserts in the logtable but the values are inserted into the temptable.
Any help on this
June 13, 2011 at 8:18 am
The logging must be the very next statement... (no print, if, NOTHING).
Can you post the code?
June 13, 2011 at 8:20 am
we'd have to see your code, but are you doing something like this? remember @@ROWCOUNT gets reset with EVERY command, including SET operations, so you have to be careful.
DECLARE @mycount int
INSERT INTO #TempTable(ColumnList)
SELECT ColumnList From SomeplaceElse
SET @mycount =@@ROWCOUNT
--'do something with loging @mycount
Lowell
June 13, 2011 at 8:21 am
Begin Transaction Tran7
insert into #t (...)
SELECT ..
FROM #hs t
INNER JOIN table1 on
If @@ERROR <> 0
Begin
Rollback Transaction Tran7
INSERT INTO LogTable Values (7,@@ROWCOUNT,'F',@Startdate,getdate())
End
Else
Begin
Commit transaction Tran7
INSERT INTO LogTable Values (7,@@ROWCOUNT,'S',@Startdate,getdate())
end
Can you tell me how to rollback the transaction if it fails?
June 13, 2011 at 8:28 am
the if resets the @@rowcount to 0.
You need to save it to local variable like Lowell showed you and it'll work 100% of the time.
June 13, 2011 at 8:42 am
I will try using that...
And how to rollback the transaction.Does the code i have given works fine?
June 13, 2011 at 8:45 am
Why would it not work fine? That would only depend on the requirements.
Another tidbit of information. You can use a table variable to log this info. It will NOT be affected by the rollback of the transaction.
June 13, 2011 at 8:49 am
Another tidbit. Naming of transactions is purely for documentation (with one rare exception). It doesn't affect what and how things are committed or rolled back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 8:51 am
GilaMonster (6/13/2011)
Another tidbit. Naming of transactions is purely for documentation (with one rare exception). It doesn't affect what and how things are committed or rolled back.
Ok I gotta know, what's the exception?
June 13, 2011 at 8:53 am
Ninja's_RGR'us (6/13/2011)
GilaMonster (6/13/2011)
Another tidbit. Naming of transactions is purely for documentation (with one rare exception). It doesn't affect what and how things are committed or rolled back.Ok I gotta know, what's the exception?
Knew you were going to ask. Let me double-check before I make a fool of myself.
Hint (if it's what I think it is) RESTORE STOPAT....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 9:23 am
GilaMonster (6/13/2011)
Ninja's_RGR'us (6/13/2011)
GilaMonster (6/13/2011)
Another tidbit. Naming of transactions is purely for documentation (with one rare exception). It doesn't affect what and how things are committed or rolled back.Ok I gotta know, what's the exception?
Knew you were going to ask. Let me double-check before I make a fool of myself.
Hint (if it's what I think it is) RESTORE STOPAT....
I give up... can't think of anything so I'll let the mcm to be tell me about this :w00t:.
June 16, 2011 at 11:36 am
Not trying to step in front of Gail...my shadow would maybe cover her pinky toe but the thread looked idle...
SAVE TRANSACTION is when naming a transaction becomes relevant.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 11:42 am
opc.three (6/16/2011)
Not trying to step in front of Gail...my shadow would maybe cover her pinky toe but the thread looked idle...SAVE TRANSACTION is when naming a transaction becomes relevant.
She's busy with a client with deadlock problems...
June 16, 2011 at 12:06 pm
opc.three (6/16/2011)
Not trying to step in front of Gail...my shadow would maybe cover her pinky toe but the thread looked idle...SAVE TRANSACTION is when naming a transaction becomes relevant.
Not what I was thinking about at all. For savepoints, the name of the transaction is still meaningless, the name of the savepoint is what's important.
CREATE TABLE T1 (
id int
)
BEGIN TRANSACTION -- Not named, no need.
INSERT INTO T1 (ID) VALUES (1)
SAVE TRANSACTION MySavePoint
INSERT INTO T1 (ID) VALUES (2)
ROLLBACK TRANSACTION MySavePoint
COMMIT TRANSACTION
go
SELECT * FROM T1
go
DROP TABLE T1
Ok, since people are still wondering, here's the rough details of what I meant (BoL for details for anyone interested.
BEGIN TRANSACTION ImportantTransaction WITH MARK
And it's other half
RESTORE LOG ImportantDB
FROM Disk = '' -- whatever
WITH RECOVERY,
STOPATMARK = 'ImportantTransaction';
Or
RESTORE LOG ImportantDB
FROM Disk = '' -- whatever
WITH RECOVERY,
STOPBEFOREMARK = 'ImportantTransaction';
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 12:10 pm
tx
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply