Link to home
Start Free TrialLog in
Avatar of kukiya
kukiya

asked on

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
Avatar of Aschruman
Aschruman

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


ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kukiya

ASKER

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.
What is the exception that is raised?
Avatar of kukiya

ASKER

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 !!!
is the MSDTC service running on the server?
is the MSDTC enabled in the SQL Server Settings?
Avatar of kukiya

ASKER

How do I check this ???
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"
Avatar of kukiya

ASKER

Thanks angelIII,
I set all the above steps, but it still gets the exception.
I dont know why.
Is the SQL server behind a firewall? I have seen (documentation for) this error when the port 135 is blocked by a firewall...
Avatar of kukiya

ASKER

No, We dont have a firewall here...
Ok:
MY_REMOTE_TABLE
how is this defined?
Avatar of kukiya

ASKER

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 !!!
Did you setup SQL Server correctly to be recognized by the Cluster (I guess it's M$ Cluster)?
CHeers
Avatar of kukiya

ASKER

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 .
Avatar of kukiya

ASKER

Anyway, I will try to implement this in another way.
Thanks for your dedication !!!