Question when implement sqlclr function i get error on sql server 2012?

[ahmedsalah]

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

 

[NoUserHere]

We've been over this. When are you going to start posting your code in code tags. You're denying yourself help by not taking the proper measures to posting your question appropriately.

Spoiler: Flashback

There are likely a number of factors why people are refusing to help you.

• You are to brief with your explanations, and lack to ask a DIRECT question ie. - Why do I get formatException error on line 15 etc
• You do not put in enough effort into explaining your problem with your current code, or you are not elaborate enough with regards what you are trying to do
• You do not explain why your code does not work for you
• You do not explain if you are receiving any errors, if you are, you don't list them nor state if you receive any, yet at all
• You post on multiple forums, and speaking for myself here, people like me read those other forums every few hours. Posting the same issue on all forums sets the wrong tone. It also makes you look like a non-contributing help vampire
• You do not post your code in code tags, while you can manage to do it correctly on most other forums. You have failed do correct your habits on this website despite me telling you previously to use code tags.

With a combination of the above, you only have yourself to blame. Give your topic some proper structure, and spend time writing it out properly and in English. While we do know English is not everyone's first language, you have no excuse for a poorly worded topic when you can avail or many online language translators today. Now that you've read all that, I hope you remember for the next time you post a question.

Notice that all of the bullet points that Sheepings had above are covered by How to Ask Questions the Smart Way. I gave you a link to that same page when I responded in the other forum when you were asking about the rude practice of cross-posting. Take the 10-15 minutes to read the recommendations on that page about how to put together a good question.

sorry I recognize that and i will fix it

I believe you when I see it...

As for your error, it's rather clear and precise, and one that any quick google or bing search will tell you the cause of. More-so, look at where your error is appearing, and use your debugger and identify why the value is null.

 

[jmcilhinney]

I will not be formatting your code for you yet again. You need to edit your post and format the code properly. It takes a few seconds so it's pure laziness to not take the time and then expect us to spend more of our time to read the code and work out what's relevant. You have people here who are willing and even eager to volunteer their time to help complete strangers. We expect something from you in return though, i.e. to help us help you. If you're not prepared to do that, you're just not going to get the help you want. We're not here to be taken advantage of.