none
TransactionScope not working for NT Authentication RRS feed

  • Question

  • I am trying to use transaction scope with two databases. It is working fine when the DB connection string has the uid and pwd values set. But for Trusted_connection = true, it is not working as desired. 

    My code is as follows: I have two stored procedures in two different databases which i iterate through a loop and execute within a transaction scope. I do a force fail in the second stored procedure by calling (RAISERROR) within the second stored procedure. So it has to rollback the data in the first database as well as the second. But it is not working when my connection string is as shown below: Server=myServer;Database=myDatabase;TRUSTED_CONNECTION=YES;

     but it works fine when my connection string has the uid and pwd mentioned in it instead of Trusted_Connection.

    Platform: SqlServer 2008 R2 and .net 4.0

    Any help is appreciated.

     



    Tuesday, May 17, 2011 2:28 PM

All replies

  • Hi bpreddy,

    Thank you for posting.

     

    Based on your description, typically, this question is often occurred when ADO.NET objects weren’t included in one TransactionScope. Like this thread.

    In addition, you used RAISERROR in your store procedure as you mentioned. Could you please provide more detailed information about code snippet of RAISERROR? AFAIK, using RAISERROR and client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. Syntax like:

    RAISERROR ( { msg_id | msg_str } { , severity , state }
        [ , argument [ ,...n ] ] )
        [ WITH option [ ,...n ] ]

    Please check this document from Microsoft for more detailed information.

     

    Hope this helps. If you have any finding, please feel free to let me know.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 19, 2011 1:57 PM
  • Hi Larcolais Gong,

    //  My code is as shown below:

    // This code works with connectionstring having uid and pwd, but not working with the trusted_connection=yes

     

     

    //the queries which needed to be executed are added to a list queryList.

    //appTypeList is another list which holds my application type value based on which i can determine my database connection string.

    //which is done in getDBContext().

     

    //DBFactory class is within a serviced component.

    //so within a scope i will be accessing my component to do the database transactions.

           

     

    using (TransactionScope scope = new TransactionScope())

    {

        //no of connections

        for (int i = 0; i <= queryList.Count - 1; i++)

            {

                ContextFactory contextFactory = new ContextFactory();

                // get the context for each connection (may be different databases)

                DBContext dbContext = contextFactory.getDBContext(appTypeList[i]);

                using (DBFactory factory = new DBFactory())

                {

                    // DBFactory is a serviced component

                    factory.SetContext(dbContext.DoLog, dbContext.LogPath, dbContext.Cnstr);

                    // open the connection and execute the query.

                    int res = factory.ExecuteNonQueryForScope(queryList[i]);

                }

                scope.Complete();

            }

    }

     

    -------------------------------------------------------

            //code  within the serviced component is as shown below:

     

            private DbConnection connection;

            private DbCommand command;

            private DBContext context;

            public void SetContext(bool doLog, string logPath, string cnstr)

            {

                context = new DBContext();

                context.Cnstr = cnstr;

                context.DoLog = doLog;

                context.LogPath = logPath;

              

                connectionString = cnstr;

                factory = SqlClientFactory.Instance;

                connection = factory.CreateConnection();

                command = factory.CreateCommand();

                command.CommandType = CommandType.Text;

                connection.ConnectionString = connectionString;

                command.Connection = connection;

            }

     

           public int ExecuteNonQueryForScope(string query)

            {

                command.CommandText = query;

                int i = -1;

                try

                {

                    if (connection.State == System.Data.ConnectionState.Closed)

                    {

                        connection.Open();

                    }

     

                    i = command.ExecuteNonQuery();

                }

                catch (SqlException ex)

                {

                    if (ex.Number == customSQLErrorNo)

                    {

                        HandleCustomException(ex);

                    }

                    else

                    {

                        throw;

                    }

                }

                return i;

            }

     

    -------------------------------------------------------

    Best Regards,

    BPReddy

    Thursday, May 19, 2011 3:34 PM
  • Hi bpreddy,

    From your code snippet, it looks OK.

    Your question is related with your connection string as I mentioned. Your question should be concerned about your connection string. So could you please provide more detailed information when your connection is failed.

     

     

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Sunday, May 22, 2011 4:47 PM