• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

How to Rollback

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
kukiya
Asked:
kukiya
  • 8
  • 8
1 Solution
 
AschrumanCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kukiyaAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What is the exception that is raised?
0
 
kukiyaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the MSDTC service running on the server?
is the MSDTC enabled in the SQL Server Settings?
0
 
kukiyaAuthor Commented:
How do I check this ???
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kukiyaAuthor Commented:
Thanks angelIII,
I set all the above steps, but it still gets the exception.
I dont know why.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Is the SQL server behind a firewall? I have seen (documentation for) this error when the port 135 is blocked by a firewall...
0
 
kukiyaAuthor Commented:
No, We dont have a firewall here...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Ok:
MY_REMOTE_TABLE
how is this defined?
0
 
kukiyaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Did you setup SQL Server correctly to be recognized by the Cluster (I guess it's M$ Cluster)?
CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
kukiyaAuthor Commented:
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
 
kukiyaAuthor Commented:
Anyway, I will try to implement this in another way.
Thanks for your dedication !!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now