Cannot run sp_addrolemember

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Database.ExecuteNonQuery("EXEC sp_addrolemember N'WebAccessRole',
N'OneAbbWebAccess'");

Provider SqlServer 2005

This line causes an error "sys.sp_addrolemember cannot be executed within a
transaction" 

If I check the sp_addrolemember query in master database, the first line in
this procedure is to check weather @@TransCount >0.

Sigve Vågsnes 

Original issue reported on code.google.com by sigve.va...@gmail.com on 22 Oct 2008 at 11:56

[GoogleCodeExporter]

What I need, is to be able to run without a transaction and take the 
responsibility
my selve. 

Sigve

Original comment by sigve.va...@gmail.com on 22 Oct 2008 at 12:04

[GoogleCodeExporter]

Original comment by geoffl...@gmail.com on 22 Dec 2008 at 5:20

• Added labels: Milestone-9, Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

I'm using this extension method as a hacky work-around for SQL Server:

public static int ExecuteOutsideTransaction(this ITransformationProvider 
database, string sql)
{
    //I guess this is how we get the connection from MigratorDotNet... -Lance
    var connectionString = database.GetCommand().Connection.ConnectionString;
    var result = 0;

    using (var conn = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(sql, conn))
    {
        conn.Open();
        result = cmd.ExecuteNonQuery();
    }

    return result;
}

Original comment by lance.fi...@gmail.com on 8 Apr 2010 at 5:21

[GoogleCodeExporter]

Hmm... But if you are using a connection string which is not trusted (e.g. with 
a username and password) this will not work...

Original comment by chris.da...@sparcedge.com on 28 May 2011 at 1:43

[GoogleCodeExporter]

ExecuteOutsideTransaction works just fine even with connection strings that use 
sql authentication (have user/pw embedded).

Original comment by demchenk...@gmail.com on 26 Jul 2011 at 10:03