Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
T
To minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA
|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestiss:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>
|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L
|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestiss:1138105823.735961.52530@.g44g2000cwa.goog legroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>
|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
TTo minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestissä:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestissä:1138105823.735961.52530@.g44g2000cwa.googlegroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but
it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
TTo minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun
it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun
it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestiss:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestiss:1138105823.735961.52530@.g44g2000cwa.googlegroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

commit and/or rollback transaction error

Hi,
I have a stored procedure that uses the commit and rollback transaction
functionalities.
However, it keeps on throwing me the error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 3, current count = 4.
Can someone explain to me what does it mean by "Previous count" please?
ThanksHi
Posting the code will help, you may have a path though it where the BEGIN
TRANSACTION is not paired correctly to a COMMIT/ROLLBACK. This may be in a
stored procedure called by the main procedure.
Check out
http://support.microsoft.com/defaul...kb;en-us;158325
http://tinyurl.com/7rmo6
You may want to also read:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
There are also many posts on Google regarding this:
http://tinyurl.com/bjmw2
John
"Tina" wrote:

> Hi,
> I have a stored procedure that uses the commit and rollback transaction
> functionalities.
> However, it keeps on throwing me the error:
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 3, current count = 4.
> Can someone explain to me what does it mean by "Previous count" please?
> Thanks
>
>

Commit and Rollback?

Hi,

I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:

Product:

--

productID

(...)

Acessories:

-

acessID

(...)

ProductAcess:

--

productID

acessID

So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess

fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

hi,

you can use transactions in Ado.Net as well, similarly to the trivial code snippet like

SqlConnection con = new SqlConnection(""); SqlCommand cmd = new SqlCommand(); SqlTransaction tx = con.BeginTransaction; cmd.Connection = con; cmd.Transaction = tx; int Res = cmd.ExecuteNonQuery(); cmd.Dispose; cmd = null; if (Res == 0) tx.Rollback; else tx.Commit; tx.Dispose; tx = null; con.Dispose; con = null;

regards

Commit and Rollback samples

Hi,
In my SQL Server 2000, I would like to test the COMMIT and ROLLBACK.
Would some give me some suggestions?
Thanks for help.
JasonBEGIN TRANSACTION
GO
USE pubs
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
GO
ROLLBACK TRANSACTION -- change ROLLBACK to COMMIT if you need to
commit the changes
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ODukuPipGHA.4368@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In my SQL Server 2000, I would like to test the COMMIT and ROLLBACK.
> Would some give me some suggestions?
> Thanks for help.
>
> Jason
>

Commit and Rollback samples

Hi,
In my SQL Server 2000, I would like to test the COMMIT and ROLLBACK.
Would some give me some suggestions?
Thanks for help.
JasonBEGIN TRANSACTION
GO
USE pubs
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
GO
ROLLBACK TRANSACTION -- change ROLLBACK to COMMIT if you need to
commit the changes
GO
SELECT * from roysched WHERE title_id LIKE 'Pc%'
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ODukuPipGHA.4368@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In my SQL Server 2000, I would like to test the COMMIT and ROLLBACK.
> Would some give me some suggestions?
> Thanks for help.
>
> Jason
>

commit and rollback problem

Hi,

I still haven't got a decent book on relational databases :-)

My stored procedure insert_wire inserts values into two tables (wire and
cablewire). The wire_ref (primary key) will be the same for both inserts.
However, if for any reason the first insert fails then I would like a
rollback system to take place. I have tried testing for an error (@.@.error
<> 0) after the 1st transaction but I just get a syntax error. Am I going
down the right lines here? Any tips appreciated.

Thanks, Mary.

CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standard
VARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),
in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, in
some_comment VARCHAR(32))
BEGIN
insert into cablewire
values(wire_ref, standard, a_color, material, metres, some_comment);
insert into wire
values(wire_ref, amps, volts, ni);
commit;
END!Mary Walker (123@.123.com) writes:
> I still haven't got a decent book on relational databases :-)
> My stored procedure insert_wire inserts values into two tables (wire and
> cablewire). The wire_ref (primary key) will be the same for both inserts.
> However, if for any reason the first insert fails then I would like a
> rollback system to take place. I have tried testing for an error (@.@.error
><> 0) after the 1st transaction but I just get a syntax error. Am I going
> down the right lines here? Any tips appreciated.

Probably not. Judging from the syntax in your posts, you are using
some other DB engine than Microsoft SQL Server, which is the RDBMS
this group is about. @.@.error, on the other hand is a feature in
MS SQL Server, that I would expect not appear anywhere else, except
for Sybase.

So I think you should first out what product you are using, and then
a forum for that product.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Commit and Rollback on a batch

Hi!

I am using VB.NET 2005 and/or SQL Server 2005 studio manager.

I have a string of about 20 or 30 inserts updates and deletes. I want to process all or nothing. If there is an error that prevents a single transaction from completeing, i want to roll back the entire batch.

One solution i read is to test fot @.@.error after each statement. This is not desirable as i receive the batch as a single string already made. I would have to separate all the statements and insert the error testing myself.

I would prefere to simply execute the batch as an all or nothing batch.

Certainly this is a common request. But the only solutions i can find involve extensive re-working of the source batch of transactions. I may have up to 100 statements in my batch.

Any Ideas?

Thank you

Jerry Cicierega

using System.Transactions;

...

using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
// Do stuff with your cmd object here such as running the aforementioned batch statements
}
}
ts.Complete()
}

If an error occurs during the processing, the whole thing will be rolled back. Of course, fill in the stuff you need for the connection and command objects in the using statements.

Commit and rollback

How are these placed exactly in the program. Do you run at 1 time or
different?
Example:
begin tran
delete
from enc
where epsid = '23232'
commit
I ran the above then ran the following to undo:
begin tran
rollback tran
That was it. I am not entirely sure how to put the transactions together to
enable the rollback if the data being changed it not correct.
Message posted via http://www.webservertalk.comTina,
Use the @.@.ERROR variable, like the following:
begin tran
delete from enc where epsid = '23232'
if @.@.ERROR = 0
commit tran
else
rollback tran
What this does is attempts to delete the record(s) from the enc table with
the given filter. If the query has a problem (fails, whatever), then the
@.@.ERROR variable will be set to some non-zero value. If the query completed
successfully, then the @.@.ERROR variable will be 0.
Hope that helps.
Richard
"tina miller via webservertalk.com" wrote:

> How are these placed exactly in the program. Do you run at 1 time or
> different?
> Example:
> begin tran
> delete
> from enc
> where epsid = '23232'
> commit
> I ran the above then ran the following to undo:
> begin tran
> rollback tran
> That was it. I am not entirely sure how to put the transactions together t
o
> enable the rollback if the data being changed it not correct.
> --
> Message posted via http://www.webservertalk.com
>|||I need something that I can do the rollback. This is in a training
environment and I need to be able to have it rollback.
Message posted via http://www.webservertalk.com

commit /rollback problem

the exeception as followAn unhandled exception of type 'System.NullReferenceException' occurred in system.windows.forms.dll

Additional information: Object reference not set to an instance of an object.

my code as followDim meetError As Boolean = False
Dim conn As Odbc.OdbcConnection
Dim trans As Odbc.OdbcTransaction
Dim cmd As Odbc.OdbcCommand
Dim da As System.Data.ODBC.OdbcDataAdapter
Dim ds As DataSet = New DataSet("dsTable")
Dim dt As DataTable
Dim i As Integer
'
Try
conn = New Odbc.OdbcConnection(connStr)
conn.Open()
cmd = conn.CreateCommand
cmd.Transaction = trans
'
Catch e As Odbc.OdbcException
MsgBox("open error " & e.Message)
meetError = True
Return False
End Try
'
'exec sql
Try
For i = 0 To in_stmt.GetUpperBound(0)
cmd.CommandText = in_stmt(i).value
da = New Odbc.OdbcDataAdapter(cmd.CommandText, conn)
cmd.ExecuteNonQuery()
'
Try
dt = New DataTable(i.ToString)
da.Fill(dt)
If dt.Columns.Count > 0 Then
ds.Merge(dt)
End If
Catch ex As Exception
MsgBox("merge error " & ex.Message)
End Try
Next
Catch ex As Odbc.OdbcException
MsgBox("execute error " & ex.Message)
meetError = True
End Try
'
If meetError Then
trans.Rollback()
Else
trans.Commit()
End If
'
'close connectoin
'
grd.DataSource = ds
Try
conn.Close()
conn = Nothing
Catch e As Odbc.OdbcException
MsgBox("close error " & e.Message)
End Try
Return True
thanks for help.I think the problem is
can da.fill(ds) after trans.begin but before trans.commit.
thanks

Commit & Rollback Logic in VB.NET

I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.

Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur.

I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.

TIA for any opinions, options, etc. Tom

you need to wrap a transaction around your deletes

read this:http://msdn.microsoft.com/msdnmag/issues/06/11/DataPoints/default.aspx

|||

Many thanks. That looks like the ticket.

Tom