none
When executing clr function on sql server I get error NET framework error occurred during execution of user-defined routine or aggregate "spexecuteparallel" RRS feed

  • Question

  • Problem
    I work on sql server 2012 windows 7 when implement clr function i get error

    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)
    .

    it is actually executed one time only for insert and insert data but after that error above display

    select dbo.spExecuteParallel
    ( N'Test',8, N'Insert into TestTable (messagesData, LogDateValues) values (''Test'', GetDate())', 0, 1)




    function sql clr is
    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]




    What I have tried:


    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);
                        }
                    }
                }
            }
    
    
    

    Tuesday, November 5, 2019 1:20 AM

All replies

  • Hi , 

    Thank you for your issue.

    I think you issue might be more related to  .NET. I will advice you post you issue in the corresponding forum . And you will get more professional help. .NET forum

    If you have post your issue in the corresponding forum ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.



    Best Regards,

    Rachel 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 5, 2019 8:46 AM