从线程应用程序在SQL Server 2014上调用sp_setapprole时,我们会收到错误:无法在此批处理中调用"模拟会话安全上下文",因为同时有一个批处理调用了它。据我所知,连接未被池化,每次调用都会创建一个新连接,因此我不确定冲突发生在哪里。以下是创建错误的测试应用程序:
using Microsoft.ApplicationBlocks.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace PleaseBreak
{
class Program
{
static string ConnectionString = "Data Source=<SQL DB SERVER>; Initial Catalog=<SQL DB NAME>; user id=<USER NAME>; pwd=<PASSWORD>; Enlist=false; Persist Security Info=True; Pooling=false; MultipleActiveResultSets=True; APP=<APPNAME>;";
static int ThreadCount = 3;
static int LoopCount = 100000;
static void Main(string[] args)
{
var tasks = new List<Task>();
for (var i = 0; i < ThreadCount; i++)
{
tasks.Add(Task.Factory.StartNew(Work));
}
Task.WaitAll(tasks.ToArray());
}
static void Work()
{
for (var i = 0; i < LoopCount; i++)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
try
{
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "sp_setapprole", new SqlParameter[]
{
new SqlParameter { ParameterName = "@RETURN_VALUE", Direction = ParameterDirection.ReturnValue, DbType = DbType.Int32 },
new SqlParameter { ParameterName = "@rolename", Direction = ParameterDirection.Input, DbType = DbType.AnsiString, Value = <APPROLE NAME> },
new SqlParameter { ParameterName = "@password", Direction = ParameterDirection.Input, DbType = DbType.AnsiString, Value = <APP ROLE PASSWORD> },
});
}
catch (Exception ex)
{
}
}
}
}
}
}
问题似乎是MultipleActiveResultSets。一旦我们将MultipleActiveResultSets=False添加到连接中,问题就消失了。