Using dialog Windows in SQL external procedures

Question

I wrote dll for my SQL database which takes BLOB data from tables and converting it into pdf file. It works good, but i try to add dialog window for choosing pdf saving path and it gives me next message:

System.InvalidOperationException: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.

May be someone know what's problem?

dll code where issue is:

public static void Run(SqlInt32 i)
        {          
            FolderBrowserDialog fd = new FolderBrowserDialog();
            fd.ShowDialog();
            string save_pth = fd.SelectedPath+'\\';
            if (gener_pdf(gener_jpg(i, save_pth), save_pth))
            {
                List<string> to_del = gener_jpg(i, save_pth);
                for (int p = 0; p < to_del.Count(); p++) { File.Delete(to_del[p]);}
            }           
        }

I've seen few ideas that are as bad as this one (except maybe opening a user dialog in a transaction...). You are doing it wrong! — Mitch Wheat, Oct 23 '18 at 06:28
@MitchWheat what do u mean "opening a user dialog in a transaction"? — User_unknown, Oct 23 '18 at 06:39
SQL Server is a *server*. Most of the time, it's going to be running on a machine where *nobody* is logged in interactively. Who do you expect is going to be *seeing* this dialog? — Damien_The_Unbeliever, Oct 23 '18 at 06:51
I'm voting to close this question as off-topic because it's just bonkers! — Mitch Wheat, Oct 23 '18 at 06:59
@Damien_The_Unbeliever i supposed that opendialog shows local directories, and not realtes from server at all — User_unknown, Oct 23 '18 at 07:00
@MitchWheat y? i need to show user directory dialog, idk how to do this server-side or dll side to make this works. u dont have any suggestions? — User_unknown, Oct 23 '18 at 07:02
The fact you have to ask why, suggest you really shouldn't be doing this.... — Mitch Wheat, Oct 23 '18 at 07:02
It's being suggested that your misguided in your attempts here because you really are throwing all concepts of *layering* out of the window. SQL Server itself doesn't have any UI. It runs in the background as a service. There ought to be at least a couple of layers between your UI and the database. Suddenly having the *database* trying to display a dialog is just, well, wrong. — Damien_The_Unbeliever, Oct 23 '18 at 07:42

score 0 · Answer 1 · answered Oct 23 '18 at 08:51

The exception message already tells you quite precisely what the problem is:

Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation.

As pointed out in the comments, SQL Server is not a user interactive process; it's a service.

You say that you "need" to do this (presumably, that you need to show the user a dialog box to choose the save path), but quite simply, doing so is wrong on every level. Not least of which being that, as also pointed out in comments, it's extremely unlikely that anyone will be logged in to the machine running the SQL Server process, so there's no one to respond to the dialog prompt.

There are apparently ways to display a dialog box from a non-user-interactive process, but that still assumes that someone is waiting for it to be displayed on (in this case) the database server machine. The odds of this appear to be slim.

Reconsider what you are trying to do, and how. If you absolutely need the user to provide a location and file name on the database server to which to save the generated report, gather that information up front via whatever UI they're working with instead of during the execution of the database code.

More likely you should just put the PDF somewhere (anywhere) with a randomly generated name (which in turn is stored in the database), or even as simply a binary blob in the database, and then offer the user a way to download the PDF thus generated via whatever interface you already have for the user to interact with the data. Doing so will completely circumvent the problem, while maintaining a clear layer separation.

Using dialog Windows in SQL external procedures

1 Answers1