question

The messagebox would only appear on a server screen so you'd have to Remote-Desktop to the server to press the button. There is no way that a CLR routine could make this happen on the Client PC, sadly. When you are hitting the execute key, you are sending a batch, or batches separated by the GO command, to a remote server to be executed. You will hear back at the client end with messages such as warnings or PRINT messages, while the batch is being processed, and you can termintate it if it all goes wrong, but otherwise your interactivity is very limited.

Even if this is at all possible, and I'm pretty sure it isn't. You can't fire off a message box for a user from the server in this fashion, it's a horrific idea. The locks taken out by the T-SQL prior to this will be held until that box gets checked. That has huge and far-reaching repercussions. Don't do it. Figure some other approach.

As Phil and Grant mentioned, I also don't think SQL Server will allow this and as it was mentioned, event it was possible, then it was fired on the Server side. If you need a user interaction, especially for DBA, then I suggest to rewrite the parts of CLR in PowerShell with e.g. combination of T-SQL Code. Eventually you can reuse parts of your CRL library directly in the PowerShell. PowerShell than can interact with DBA and execute appropriate actions against the SQL Server.

First the not-so-good-yet-interesting news, then the good news: Yes, but Really No --- Technically speaking, **Yes**, it _is_ possible to use SQLCLR to pop up a MessageBox (I was able to get it to work). And just to clarify the effect of doing such a thing: while the modal dialog was on the screen waiting for a response (I left it up for several minutes), `sys.dm_exec_requests` did show the SPID having a constant `[status]` of "running". However, contrary to my expectations of said situation, the scheduler was _not_ locked up and was able to process other requests. So, interesting. BUT, all of that being said... Pragmatically speaking, this is only possible in such a limited context that it is effectively a useless solution and hence the answer, at least in terms of SQLCLR, is: **No**. The primary issue is actually not what everyone seems to think it would be (namely that the client -- SSMS or SQLCMD -- is typically not the same machine as the server hosting SQL Server). That issue (i.e. processes being on separate machines) would be an issue if it weren't for the real issue: _a system-level, batch-oriented background processes has no display context_. Meaning: given that Windows is a multi-user OS, even if the client application were running on the same server as SQL Server, the SQL Server process would have no idea which display to send the `dialog.Display` (or whatever) request to. There could be several people logged into the OS at the same time (even using the same Login) and SQL Server has no information about which terminal session is running the client application that made the request to do the silly pop-up. Testing this on my laptop with SQL Server also running on my laptop resulted in the Stored Procedure returning immediately without either the pop-up or an error in the following scenarios: * NT Service\{InstanceName} * {Domain}\\{MyLogin} * Local System account (with the "Allow service to interact with desktop" option checked) [ I thought this one would work ] So how did I get it to work? I used SQL Server Express LocalDB (replaces User Instances; and no, I did not test User Instances) which also runs as a background process, but attached to your terminal session. Hence the theory does work, but with very limited application. BUT, all of that being said... Well, Actually Yes --- Put aside for a moment that the request was specific to SQLCLR. The goal is to prompt the user for a response. This can be done without SQL Server being involved, or even knowing anything about it, by using SQLCMD commands. This requires two steps: 1. Shell out to execute a CMD that prompts the user for something, and then saves the response in a file that is formatted to be a SQLCMD command to set a SQLCMD variable. The MSDN documentation for SQLCMD commands in SSMS does state that you should not prompt a user for input since the request is being handled in the background and there won't be an opportunity for a user to see what is being run by the `!!` shell-exec command. This is generally true, which is why getting the prompt to work in this context requires two CMD scripts (see below). 2. Read the file that was just created, which will set the variable to the specified value Things to keep in mind: * All SQLCMD commands within a batch are executed before the T-SQL in the batch is submitted to SQL Server for execution. Therefore, there must be at least one `GO` between the reading of the dynamically created file and the using of the variable that it sets. * Using this construct requires no additional steps when running via SQLCMD.EXE, but does require enabling "SQLCMD Mode" in SSMS via the Query menu. * The prompt to the user has no impact on SQL Server (assuming that it is not done within the middle of a transaction, and it was stated that it would indeed be between transactions) as SQL Server will just be waiting for another command to be sent. It will be SQLCMD or SSMS that will be waiting for the user's response. Place both CMD scripts in the same folder, something like: **C:\Temp\GetUserResponse** **CMD Script 1** * Called by SQL script (see below) * Calls CMD script 2 (see below) * Named: **GetResponse.cmd** Code: @ECHO OFF START "Get Response" /D C:\Temp\GetUserResponse /MAX /WAIT Get_%1.cmd %2 **CMD Script 2** * Called by CMD script 1 (see above) * Creates file with the response * Named: **Get_YesNo.cmd** Code: @ECHO OFF TITLE Answer Me. NOW! SET ResponseTempFile=C:\Temp\GetUserResponse\_tmp_var.tmp IF EXIST %ResponseTempFile% DEL %ResponseTempFile% :DaBeginnin SET /P MyTestVar="Please enter a value to prove that this works ([Y]es / [N]o): " IF /I "%MyTestVar%" EQU "Yes" GOTO EsBueno IF /I "%MyTestVar%" EQU "Y" GOTO EsBueno IF /I "%MyTestVar%" EQU "No" GOTO EsBueno IF /I "%MyTestVar%" EQU "N" GOTO EsBueno REM Else go back to da beginnin and ask again ECHO Invalid response. Please try again (and this time, try a valid response ;-). ECHO. GOTO DaBeginnin :EsBueno ECHO :setvar %1 "%MyTestVar%" > %ResponseTempFile% REM pause **Sample SQL Script** * Calls CMD script 1 (see above) Code: SET NOCOUNT ON; IF (OBJECT_ID(N'tempdb..##UserInput') IS NOT NULL) BEGIN DROP TABLE ##UserInput; END; CREATE TABLE ##UserInput (EventTime DATETIME2 DEFAULT (GETDATE())); GO INSERT INTO ##UserInput DEFAULT VALUES; -- First GO INSERT INTO ##UserInput DEFAULT VALUES; -- Second !!C:\Temp\GetUserResponse\GetResponse.cmd YesNo SillyRabbit GO INSERT INTO ##UserInput DEFAULT VALUES; -- Third GO SELECT * FROM ##UserInput; GO :r C:\Temp\GetUserResponse\_tmp_var.tmp GO SELECT '$(SillyRabbit)' AS [TaDa!];