SQLCLR does not appear to start Windows Application

Question

I'm trying to start a Windows program from SQL Server using SQLCLR. The procedure called does not error, but the program does not appear to start.

Any ideas what I might be doing wrong?

Here is my .NET code for the procedure:

using System;
using Microsoft.SqlServer.Server;
using System.Diagnostics;

namespace MyUtility
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void MyUtilityExecute()
        {
            string result;

            try
            {
                Process myProcess = new Process();
                myProcess.StartInfo.FileName = "C:\\Windows\\System32\\notepad.exe";
                myProcess.StartInfo.UseShellExecute = false;
                myProcess.StartInfo.CreateNoWindow = false;
                myProcess.Start();

                result = "Success! Time = "
                        + DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss");
                SqlContext.Pipe.Send(result);
            }
            catch
            {
                result = "There was an error!";
                SqlContext.Pipe.Send(result);
            }
        }
    }
}

Here is my T-SQL code to create the assembly and wrapper object:

USE [MyDatabase]
GO

CREATE ASSEMBLY MyUtility
AUTHORIZATION [dbo]
FROM 'C:\MyPath\MyUtility\bin\Debug\MyUtility.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE usp_MyUtilityExecute
AS EXTERNAL NAME 
    MyUtility.[MyUtility.StoredProcedures].MyUtilityExecute
GO

EXECUTE usp_MyUtilityExecute;

Answer 1

Did you check Task Manager, or Process Explorer, or any other way of seeing local processes to determine if the program actually started? I bet it did. The issue is that SQL Server is a completely separate background process from your (Windows) user session and does not have access to your desktop (even if SQL Server is running on the same computer that you are logged into, and quite often it's actually running on another computer that you are merely sending requests to, just like how web sites are remote). The only way that you would see a GUI app or window is if you were using SQL Server Express LocalDB since that is a user-mode process that runs in your session as a background process, and it does have access to your desktop. So, notepad should be running, you just can't see it. And if you keep executing this stored procedure, you will likely spawn a new, separate notepad process each time. You might want to kill those ;-).

If you want to test to see if SQL Server really can spawn an OS-based process / app, just create a simple .cmd script in C:\temp that does the following:

ECHO %TIME% >> C:\TEMP\_SQLCLR_test.txt

Then change the first StartInfo line to be:

myProcess.StartInfo.FileName = @"C:\TEMP\_SQLCLR_test.cmd";

and maybe also:

myProcess.StartInfo.CreateNoWindow = true;

This will test the overall concept without requiring UI interaction (assuming that the logon account running the SQL Server process has permissions to access and write-to / modify the C:\TEMP directory).

!! IMPORTANT NOTE !!

Process is a disposable object, so that needs to be created in a using() construct, or you need to implement the try / finally to manage it calling Dispose() regardless of what happens. In your current setup, if there is an exception, you will have an orphaned external resource which will hold onto memory / file system handles / etc until garbage collected, and not sure when that will happen as I don't believe that SQL Server calls it very often.