Select and update selected data
-
Hi, I am using MSSQL 2000 and MS.NET 2.0 I have some doubt.My requirment is I need to select some data (ie status=3) from webserver's database and insert it into my local machine's Database.Table structure in both server are same.And at the same time i need to update the status of the selected rows to 4. Now my idea is, a class contain two functions.One is for
DownloadWeband other isInsertLocal. The DownloadWeb return a dataset(selected data) and InsertLocal insert the the table in dataset to local database. My doubt is where i place"update status=4"query.. I write one sp in my webserver likeBEGIN TRANSACTION BEGIN SELECT data END BEGIN UPDATE status=4 END IF @@ERROR <> 0 BEGIN -- Rollback the transaction ROLLBACK RETURN -1 END COMMITor any other idea???? Be very grateful for any advice. RegardsThey laugh at me; they think I’m different. I laugh at them, ‘coz they are all same
CrazySanker -
Hi, I am using MSSQL 2000 and MS.NET 2.0 I have some doubt.My requirment is I need to select some data (ie status=3) from webserver's database and insert it into my local machine's Database.Table structure in both server are same.And at the same time i need to update the status of the selected rows to 4. Now my idea is, a class contain two functions.One is for
DownloadWeband other isInsertLocal. The DownloadWeb return a dataset(selected data) and InsertLocal insert the the table in dataset to local database. My doubt is where i place"update status=4"query.. I write one sp in my webserver likeBEGIN TRANSACTION BEGIN SELECT data END BEGIN UPDATE status=4 END IF @@ERROR <> 0 BEGIN -- Rollback the transaction ROLLBACK RETURN -1 END COMMITor any other idea???? Be very grateful for any advice. RegardsThey laugh at me; they think I’m different. I laugh at them, ‘coz they are all same
CrazySankerwhat if the Sql Transaction (Insert) to Local Server fails? You can't Rollback the Update transaction happened on the other SQLServer. what about this alternative(using ADO.NET SQLTransaction object)? 1. DownLoadWeb (select data from Server1) 2. InsertLocal (Insert data to Server2) - SQLTransaction object 3. UpdateStatus (update Status in Server1) Use a SQLTransaction object for the Insert operation. make sure (2) and (3) are successfull and then commit the transaction otherwise Rollback.
Regards
- J O H N -
-
what if the Sql Transaction (Insert) to Local Server fails? You can't Rollback the Update transaction happened on the other SQLServer. what about this alternative(using ADO.NET SQLTransaction object)? 1. DownLoadWeb (select data from Server1) 2. InsertLocal (Insert data to Server2) - SQLTransaction object 3. UpdateStatus (update Status in Server1) Use a SQLTransaction object for the Insert operation. make sure (2) and (3) are successfull and then commit the transaction otherwise Rollback.
Regards
- J O H N -