.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Implicit Transactions in Triggers

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

Here's my scenario:

First, I have two servers. In Server1 I have set up a Linked Server to Server2.

I have two databases on Server1. The first is the Customer database and the second is my Staging database. Server2 contains a database called Gateway.

I have AFTER INSERT, UPDATE triggers on tables in the Customer database that will insert records into tables in my Staging database.

The triggers on the tables in the Staging database will insert records into tables in the Gateway database on the Linked Server, Server2.

OK ... that is the setup. Now, the problem:

As you all probably know (and I didn't know until I started working with this), the statement in the Customer database that caused the trigger to fire, enlists everything in a transaction. If there is a problem several hops away with accessing the LinkedServer, then the entire thing is rolled back. IOW, the records in the table on the Customer database are never updated! This is bad (we can't mess with the functionality of our Customer's database, so our triggers there have to be fairly foolproof).

The trigger in the Customer database which INSERTS records into the Staging database should be ok and never fail (they are, after all, on the same Server). However, the trigger in the Staging database is where there could potentially be a problem if the LinkedServer is do

View Complete Post

More Related Resource Links

The Polyglot Programmer: ACID Transactions with STM.NET


STM.NET allows developers to mark their code with ACID-style transactional semantics, and leaves the grunt work of managing locks to the underlying environment.

Ted Neward

MSDN Magazine January 2010

Foundations: Easily Apply Transactions To Services


Managing state and error recovery using transactions is the topic of this month's installment of Foundations.

Juval Lowy

MSDN Magazine January 2009

Transactions: Build Scalable Systems That Handle Failure Without Losing Data


Systems that handle failure without losing data are elusive. Learn how to achieve systems that are both scalable and robust.

Udi Dahan

MSDN Magazine July 2008

NTFS: Enhance Your Apps With File System Transactions


Presented here is an overview of Transactional NTFS and how it revolutionizes transactions.

Jason Olson

MSDN Magazine July 2007

Data Points: Disabling Constraints and Triggers


What to do when you need to disable triggers and constraints to perform data synchronization and other tasks with an offline database.

John Papa

MSDN Magazine April 2007

Data Points: Revisiting System.Transactions


The System.Transactions namespace of the Microsoft .NET Framework makes handling transactions much simpler than previous techniques. Read all about it this month.

John Papa

MSDN Magazine November 2006

{ End Bracket }: Transactions for Memory


Injecting parallelism into your app is easy-managed code offers things like explicit threading and a thread pool for that. Ensuring your code remains correct when run in parallel, on the other hand, is not quite so simple.

Joe Duffy

MSDN Magazine January 2006

Can't Commit?: Volatile Resource Managers in .NET Bring Transactions to the Common Type


Here Juval Lowy discusses the benefits you'll get when you implement transactions in your non-database applications, with a little help from volatile resource managers in the .NET Framework 2.0.

Juval Lowy

MSDN Magazine December 2005

Data Points: ADO.NET and System.Transactions


The Microsoft® . NET Framework versions 1. 0 and 1. 1 represented major changes in software development. However, one important thing that did not change much was support for distributed transactions.

John Papa

MSDN Magazine February 2005

web services transactions (select statement)

 Hi everybody, i'm just getting started in web services and i have a problem connecting to a database. All i want is that my web service recieves a string parameter and return a dataset so that in the client aplication i just show the result of the web service in a datagrid. Here's the code of the web service:    <%@ WebService Language="C#" CodeBehind="Service1.asmx.cs" %> using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Linq;using System.Web;using System.Web.Services;using System.Web.Services.Protocols;using System.Xml.Linq;using System.EnterpriseServices;using System.Data.SqlClient;namespace Farmacia{    [WebService(Namespace = "http://localhost/MyWebServices")]    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]    [ToolboxItem(false)]    public class Farmacia : System.Web.Services.WebService    {        private SqlDataAdapter da;        [WebMethod(TransactionOption=TransactionOption.RequiresNew)]        public DataSet Consultas (string sel)        {            if (sel == "")&

Implicit-type lambda expression: is it possible?

Hi all, i was plaing around with LINQ and lambda expressions. I was trying to find duplicated files in my system. After a while a realized that duplicated could mean: - same name - same size and extension - ....   So i wrote this code: DirectoryInfo rootDirectory = ....; var groupFiles = selector => rootDirectory.EnumerateFiles("*", SearchOption.AllDirectories).GroupBy(selector).Select(g => new { g.Key, Info = g, Count = g.Count() }).Where(g => g.Count > 1); var groupsByName = groupFiles(f => f.Name); var groupsBySize = groupFiles(f => new { f.Length, f.Extension}); But i get the following error: Error 1 Cannot assign lambda expression to an implicitly-typed local variable So i tryed to make the type explicit and i realized that is not so easy. So what i'm missing?! IMPORTANT. 'm not tying to have the code working. I can solve the issue by duplicating code (even if i hate to do that) I'm rtying to improve my skills with LINQ and Lambda Expressions. Thanks all!!  

System.Transactions.TransactionManagerCommunicationException: Communication with the underlying tran

Hi there,I have an urgent issue at production.I hit the above mentioned exception when my application execute the line of System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)I also see this message from web server event viewer: MSDTC encountered an error (HR=0x80004005) while attempting to establish a secure connection with system <SQL Server macchine name>.I have configure the MSDTC servive as below at both web server and sql server:- Enable Network DTC Access- Allow Inbound- Allow Outbound- Select Mutual Authentication RequiredFirewall service is not running at both servers.One thing very interesting, I have exactly the same setting for development environment, it works perfectly at development environment, but not production.Please help!Thank you very much.Regards,Fenny

Can I use TCP sockets within managed funcs, triggers and stored procedures?

I have seen nothing prohibiting this, but have not yet had time to write some test code. Does anyone know for sure if SQL Server 2008 prohibits or allows TCP sockets for communications, inside a C# SQL/CLR function or method? I guess it must because this is implict when creating data connections and so on, which are obvioulsy allowed. Thanks Cap'n  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend