Run process in SQL Server CLR stored procedure but Process.OutputDataReceived not firing

user_7740197 0
Viewed 90

I want to run process in SQL Server CLR this my code:

[SqlProcedure]
private static int RunExecutable()
{
     SqlDataRecord sqlDataRecord = new SqlDataRecord(new SqlMetaData("message", SqlDbType.NVarChar, 1L));
     SqlContext.Pipe.SendResultsStart(sqlDataRecord);
     int lineCount = 0;

     Process process = new Process();
     process.StartInfo.FileName = "ipconfig.exe";
     process.StartInfo.UseShellExecute = false;
     process.StartInfo.RedirectStandardInput = true;
     process.StartInfo.RedirectStandardOutput = true;
     process.StartInfo.CreateNoWindow = true;
     process.EnableRaisingEvents = true;
     process.OutputDataReceived += new DataReceivedEventHandler((sender, e) =>
            {
                    sqlDataRecord.SetString(0, "OnDataReceived");
                    SqlContext.Pipe.SendResultsRow(sqlDataRecord);

                    if (!String.IsNullOrEmpty(e.Data))
                    {
                        lineCount++;
                        sqlDataRecord.SetString(0, "[" + lineCount + "]: " + e.Data);
                        SqlContext.Pipe.SendResultsRow(sqlDataRecord);
                    }
            });

     process.Start();
     process.BeginOutputReadLine();

     while (!process.HasExited)
     {
         sqlDataRecord.SetString(0, "process WaitForExit ");
         SqlContext.Pipe.SendResultsRow(sqlDataRecord);
         process.WaitForExit(300);
     }
}

and ipconfig.exe runs (I see in results "process WaitForExit"), but the OutputDataReceived event is not triggered.

The assembly was created in SQL Server 2019 Enterprise with PERMISSION_SET = UNSAFE;. If I run the same code as the standard console application everything works fine

sqlclr sql-server c#
2 Answers

That's going to require a background thread to run the event while you block the session's thread on WaitForExit. I'm not surprised it doesn't work in SQLCLR, which is a very different .NET Framework host than a console application.

And even if the event fires, you could not access SqlContext.Pipe from a thread other than the thread that called into the method.

Instead perform blocking reads of StandardOutput using the thread that called into your method, like this:

static IEnumerable<string> GetOutputLines(string exeName, string args = null)
{ 
    Process process = new Process();
    process.StartInfo.FileName = exeName;
    process.StartInfo.Arguments = args;
    process.StartInfo.UseShellExecute = false;
    process.StartInfo.RedirectStandardInput = true;
    process.StartInfo.RedirectStandardOutput = true;
    process.StartInfo.CreateNoWindow = true;
    process.EnableRaisingEvents = true;

    process.Start();
    
    while ( true )
    {
        var line = process.StandardOutput.ReadLine();
        if (line == null)
            break;
        yield return line;
    }

    process.WaitForExit();
}
user_72977000

+1 to David for answering the general question of how to best capture and return command-line output.

However, for your specific scenario of running ipconfig.exe and returning all of the output, I think you would be far better served by simply using:

NetworkInterface.GetAllNetworkInterfaces()

I would try that first. This might require you to load the System.Net.NetworkInformation.dll Framework library as UNSAFE in the same database, but you are already doing "unsafe" operations by shelling out to the OS, so at least this is handled in managed code.

user_5777650
Related