?
Solved

How to Rollback

Posted on 2003-04-01
17
Medium Priority
?
321 Views
Last Modified: 2009-07-29
I haven't write a Rollback command. I need some advices.
I need to make to actions:              

1.StrSql.Format(_T("DELETE MY_TABLE"));
  db->ExecuteSQL(StrSql);

2.StrSql="INSERT INTO MY_TABLE SELECT * FROM MY_REMOTE_TABLE");
   db->ExecuteSQL(StrSql);

number 1 should be executed before number 2, because number 2 may insert MY_TABLE
the same records (It will cause primary key violation).
actually I need to roolback the two steps if number 2 is not avalable.

How do I roolback ????

Thanks in advance
0
Comment
Question by:kukiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 

Expert Comment

by:Aschruman
ID: 8245776
the Rollback command is used together with the Begin Tran command

The Begin Tran command creates a transaction in the SQL Server, after Deleting, Inserting, Updating and all that you can Rollback or Commit your Acctions.

Once you have commited, All the Insertions, deletions and Updates will take place.

Don't worry about other users doing the same thing, SQL server will Lock other users from doing changes while you are in a transaction.

I don't know in witch language you are programming but you will be able to find it on most of the languages.

In Query Analyser

Begin Tran

Delete TableX

insert into TableX values (a,b,c)

if @@rowcount > 0
   Commit
else
   RollBack


0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 8245798
I am not shure which programming language you use, thus the code below might need to be adjusted...

StrSql.Format(_T("BEGIN TRANSACTION  DELETE MY_TABLE   INSERT INTO MY_TABLE SELECT * FROM MY_REMOTE_TABLE   COMMIT TRANSACTION");
  db->ExecuteSQL(StrSql);

You might want to insert some carriage return characters in between the sql statements in the string...

another option is:

try {
  db->BeginTrans;
 
  StrSql.Format(_T("DELETE MY_TABLE"));
  db->ExecuteSQL(StrSql);

  StrSql="INSERT INTO MY_TABLE SELECT * FROM MY_REMOTE_TABLE");
  db->ExecuteSQL(StrSql);

  db->CommitTrans;
}
catch (e)
{ db->Rollback; }

0
 

Author Comment

by:kukiya
ID: 8246383
Thanks,
angelIII- I use the MFC CDatabase.
I tried your code:
---------------------------------------------------

try {
 db->BeginTrans;
 StrSql.Format(_T("DELETE MY_TABLE"));
 db->ExecuteSQL(StrSql);
 StrSql="INSERT INTO MY_TABLE SELECT * FROM MY_REMOTE_TABLE");
 db->ExecuteSQL(StrSql);
 db->CommitTrans;
}
catch (e)
{ db->Rollback; }
---------------------------------------------------
But it takes time, deactivate the running thread, and then gets into the catch, althogh it shouldt.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8246439
What is the exception that is raised?
0
 

Author Comment

by:kukiya
ID: 8247028
The exception I got is as following:
" The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction"

Thanks !!!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8252528
is the MSDTC service running on the server?
is the MSDTC enabled in the SQL Server Settings?
0
 

Author Comment

by:kukiya
ID: 8252547
How do I check this ???
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8252936
1) check in the services applet of NT if the m$ distributed transaction coordinator service is running (and set to automatic startup)

AND
 Edit the properties of the server in the Enterprise Manager of SQL Server (mmc console).
goto tab General, and enable the option "Autostart MSDTC"

2) Edit the properties of the server in the Enterprise Manager of SQL Server (mmc console).
goto tab Connections, and enable the option "Enforce Distributed Transactions"
0
 

Author Comment

by:kukiya
ID: 8253869
Thanks angelIII,
I set all the above steps, but it still gets the exception.
I dont know why.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8259181
Is the SQL server behind a firewall? I have seen (documentation for) this error when the port 135 is blocked by a firewall...
0
 

Author Comment

by:kukiya
ID: 8259199
No, We dont have a firewall here...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8259220
Ok:
MY_REMOTE_TABLE
how is this defined?
0
 

Author Comment

by:kukiya
ID: 8259256
Here some more information:

MY_REMOTE_TABLE is located in a cluster SQL server
(Is that could be it ???).
I set it and also the Local SQL Server  (2000) to MSDTC.
some of the checkboxes that you said to mark, were already marked.

Thank you for your patient !!!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8259288
Did you setup SQL Server correctly to be recognized by the Cluster (I guess it's M$ Cluster)?
CHeers
0
 

Author Comment

by:kukiya
ID: 8259384
angelIII, The query I wrote DOES work without the transaction statements :
db->BeginTrans,
db->CommitTrans and
db->Rollback

So I guess SQL Server is correctly to be recognized by the Cluster .
0
 

Author Comment

by:kukiya
ID: 8259388
Anyway, I will try to implement this in another way.
Thanks for your dedication !!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question