Distributed SQL Transaction???

[querry]

Hi all,

I am writing a stand alone app. in C#. In this app I need to read some values from a database on server A and Insert these values in another database on server B.

I have a number of INSERT statements. I want to group them all in a transaction so that if anyone of them fails the transaction can be rolled back.

It looks something like this.

SqlConnection RmtSrv = new SqlConnection();
RmtSrv.ConnectionString = "Data Source = server B; " +
"Initial Catalog = master;" +
"User id = **;" +
"Password = ***;";
RmtSrv.Open();
SqlCommand SqlCmd = new SqlCommand("EXEC Master.dbo.xp_servicecontrol " +
"'QUERYSTATE', 'MSDTC'",RmtSrv);
SqlDataReader SqlRead = SqlCmd2.ExecuteReader();
SqlRead.Read();
string sMSDTCStatus = SqlRead.GetValue(0).ToString(); //string to collect the current state of the MSDTC.
SqlRead.Close();

if (sMSDTCStatus == "Stopped.")
{
MessageBox.Show("MsDTC current state - Stopped. \nTo start MSDTC click OK.");
SqlCmd.CommandText = ("EXEC Master.dbo.xp_servicecontrol " +
"'START', 'MSDTC'");
SqlCmd.ExecuteNonQuery();
}
RmtSrv.Close();

//
SqlTransaction trans;
trans = connForServerA.BeginTransaction();

try
{
new SqlCommand("SQL INSERT 1", connForServerA, trans).ExecuteNonQuery();
new SqlCommand("SQL INSERT 1", connForServerA, trans).ExecuteNonQuery();
trans.Commit();
}
catch(Exception e)
{
MessageBox.Show(e.Message);
trans.Rollback();
}


Yes, I have linked servers. In the insert statements I Select the values from one database table and Insert them into another database on a different server like this.

INSERT INTO [SERVER_B].[DatabaseName].dbo.tableName
(COL1, COL2, COL3)
SELECT COL1, COL2, COL3 FROM tableName(This is on server_A)

But this doesnot work.

Any suggestions.