При выполнении функции clr я получаю ошибку NET framework error, возникшую во время выполнения пользовательской подпрограммы или агрегата "spexecuteparallel"
Проблема
Я работаю на sql server 2012 при реализации функции clr я получаю ошибку
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "spExecuteParallel": System.NullReferenceException: Object reference not set to an instance of an object. System.NullReferenceException: at SqlStoredProcedureClr.spExecuteParallel(String DB, Int32 MaxDOP, String TSQL, Int32 msDelay, Int32 Retries) .
он фактически выполняется один раз только для вставки и вставки данных но после этого отображается ошибка выше
select dbo.spExecuteParallel ( N'Test',8, N'Insert into TestTable (messagesData, LogDateValues) values (''Test'', GetDate())', 0, 1)
функция sql clr это
Create FUNCTION [dbo].[spExecuteParallel](@DB [nvarchar](200), @MaxDOP [int], @TSQL [nvarchar](4000), @msDelay [int], @Retries [int]) RETURNS [bigint] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT AS EXTERNAL NAME [StoredProcedures].[SqlStoredProcedureClr].[spExecuteParallel]
Что я уже пробовал:
public static class SqlStoredProcedureClr { [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)] public static SqlInt64 spExecuteParallel(string DB, int MaxDOP, string TSQL, int msDelay, int Retries) { // Initialize Variables SqlConnection oConn = new SqlConnection(); SqlCommand oCmd = new SqlCommand(); List<string> oErrorString = new List<string>(); object oLocker = new object(); string sServer = null; List<Thread> oThread = new List<Thread>(); StringCollection sStopped = new StringCollection(); oConn = new SqlConnection("context connection = true;"); oConn.Open(); oCmd = oConn.CreateCommand(); oCmd.CommandText = "SELECT @@SERVERNAME"; sServer = oCmd.ExecuteScalar().ToString(); oCmd.Dispose(); oConn.Close(); oConn.Dispose(); // Execute Threads int iCurrentThread = 0; while (iCurrentThread < MaxDOP) { ExecuteSQL Executer = new ExecuteSQL (sServer, DB, TSQL.Replace("?", DB.ToString().Trim()), Retries, ref oErrorString, ref oLocker); Thread oItem = new Thread(Executer.Process); oItem.Name = "ExecuteSQL " + DB.ToString().Trim(); oItem.Start(); oThread.Add(oItem); SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() + " : Start : " + oItem.Name.Replace("ExecuteSQL ", "")); Thread.Sleep(msDelay); while (RunningThreads(ref oThread, ref sStopped) >= MaxDOP) { Thread.Sleep(1000); } iCurrentThread++; } // Wait for all Threads to Stop while (RunningThreads(ref oThread, ref sStopped) > 0) { Thread.Sleep(1000); } SqlContext.Pipe.Send("All Thread have Stopped with " + oErrorString.Count.ToString() + " Error/s "); if (oErrorString.Count > 0) { try { foreach (string sIndividualErrors in oErrorString) { SqlContext.Pipe.Send(sIndividualErrors.ToString()); } } catch (Exception ex) { ex.ToString(); } throw new Exception("Error Occurred."); } return 0 - oErrorString.Count; } public static int RunningThreads(ref List<Thread> oThread, ref StringCollection oStops) { int iRunningCount = 0; foreach (Thread oIndividualThread in oThread) { if (oIndividualThread.IsAlive) { iRunningCount += 1; } else if (!oStops.Contains(oIndividualThread.Name)) { oStops.Add(oIndividualThread.Name); SqlContext.Pipe.Send(DateTime.Now.ToLongTimeString() + " : Stop : " + oIndividualThread.Name.Replace("ExecuteSQL ", "")); } } return iRunningCount; } } class ExecuteSQL { private List<string> oExecuteErrors; private object oExecuteLocker; private string sExecuteServer; private string sExecuteDB; private string sExecuteTSQL; private int iExecuteRetries; public ExecuteSQL(string sServer, string sDB, string sTSQL, int iRetries, ref List<string> oErrors, ref object oLocker) { this.sExecuteServer = sServer; this.sExecuteDB = sDB; this.sExecuteTSQL = sTSQL; this.iExecuteRetries = iRetries; this.oExecuteErrors = oErrors; this.oExecuteLocker = oLocker; } public void Process() { int iTries = 1; SqlConnection oConn = new SqlConnection(); Retry: oConn = new SqlConnection("Data Source=" + sExecuteServer + ";Initial Catalog=" + sExecuteDB + ";Integrated Security=SSPI;"); try { oConn.Open(); if (oConn.State == ConnectionState.Open) { SqlCommand oCmd = oConn.CreateCommand(); oCmd.CommandText = sExecuteTSQL; oCmd.CommandTimeout = 0; oCmd.ExecuteNonQuery(); oCmd.Dispose(); oConn.Close(); oConn.Dispose(); } else { throw new Exception("SQL Server not Found or Unable to Connect to SQL Server"); } } catch (Exception ex) { if (oConn.State != ConnectionState.Closed) oConn.Close(); oConn.Dispose(); if (iTries <= iExecuteRetries) { Thread.Sleep(5000); iTries += 1; goto Retry; } else { lock (oExecuteLocker) { char cSpace = char.Parse(" "); oExecuteErrors.Add(this.sExecuteDB.PadRight(16, cSpace) + " : " + ex.Message); } } } }