I am trying to transfer data from a table in a database on one server to another table in a linked-server database, but I get this error:
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "GWTLMDEV2". A nested transaction was required because the XACT_ABORT option was set to OFF.
This is my code:
int count = 0;
foreach(Location_agency locAgency in Location_agencies.Skip(60000).ToList())
{
count++;
GEProdTest.EDILocationAgencies.InsertOnSubmit(new LINQPad.User.GEProdTestTypes.EDILocationAgency {
Company_id = locAgency.Company_id,
Agency_id = locAgency.Agency_id,
Location_id = locAgency.Location_id,
ExternalId = locAgency.Id
});
if (count == 10000)
{
SubmitChanges();
count = 0;
}
}
SubmitChanges();
Is there a way to programmatically set XACT_ABORT to ON or OFF, or a setting that can be adjusted within LINQPad?
Thanks!