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
I need to make to actions:
1.StrSql.Format(_T("DELETE
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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 !!!
" 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?
is the MSDTC enabled in the SQL Server Settings?
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"
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"
ASKER
Thanks angelIII,
I set all the above steps, but it still gets the exception.
I dont know why.
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...
ASKER
No, We dont have a firewall here...
Ok:
MY_REMOTE_TABLE
how is this defined?
MY_REMOTE_TABLE
how is this defined?
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 !!!
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
CHeers
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 .
db->BeginTrans,
db->CommitTrans and
db->Rollback
So I guess SQL Server is correctly to be recognized by the Cluster .
ASKER
Anyway, I will try to implement this in another way.
Thanks for your dedication !!!
Thanks for your dedication !!!
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