Is there a way to get sqlclr code to call MessageBox.Show() ?

[sherifffruitfly]

1) You can't add a reference to system.win.forms to a SQL Server
Project
2) You can't add a referenceto a non-SQL Server Project to a SQL
Server Project

Is there any other way to get a call to MessageBox from sqlclr code?


Thanks for any options,

cdj

 

[Marc Gravell]

Why would you do that? And where would you expect the message-box to
appear? A database usually runs as a background service, without any
UI - and you certainly wouldn't want to interrupt the flow with
something like user input.

If the system (quite sensibly IMO) won't let you add such a reference,
then let it alone..

Marc

 

[sherifffruitfly]

Why would you do that?

100% irrelevant to my question.

And where would you expect the message-box to
appear?

Another interesting question, which is also 100% irrelevant to what I
asked.

A database usually runs as a background service, without any
UI - and you certainly wouldn't want to interrupt the flow with
something like user input.

If the system (quite sensibly IMO) won't let you add such a reference,
then let it alone..

Ugh, somebody save me from the peanut gallery.

*Why* I want to do it is my business, and is not part of my question.
I hope your oh-so-righteous criticism made *you* feel better, because
that's *all* it achieved.

 

[Arne Vajhøj]

100% irrelevant to my question.


Another interesting question, which is also 100% irrelevant to what I
asked.

*Why* I want to do it is my business, and is not part of my question.
I hope your oh-so-righteous criticism made *you* feel better, because
that's *all* it achieved.

If you want to decide what is relevant, then hire a consultant
for 100 USD/hour or so.

If you post your questions to usenet to get free advice, then
you do not decide what is irrelevant.

Arne

PS: Marc's questions seems to be very good advice. If you manage
to call MessageBox then the two most likely thing to happen
is 1) nothing 2) crash. We are able to provide you with
much easier code to achieve those tasks !

 

[Jeff Johnson]

Ugh, somebody save me from the peanut gallery.

*Why* I want to do it is my business, and is not part of my question.
I hope your oh-so-righteous criticism made *you* feel better, because
that's *all* it achieved.

Too damn bad. We're not answer-dispensing Pez machines. We're here to help
people write good code. If we think you're trying to do something dumb,
we're not going to go out of our way to help you achieve it. So yes,
sometimes you DO have to justify what you're doing to get the answers you
want.

 

[Mr. Arnold]

1) You can't add a reference to system.win.forms to a SQL Server
Project
2) You can't add a referenceto a non-SQL Server Project to a SQL
Server Project

Is there any other way to get a call to MessageBox from sqlclr code?


Thanks for any options,

cdj

SQLCLR is not a Windows forms solution. So, you're not going to get a
Messagebox to show, like a Windows forms solution. The best you can do is
write messages to a log file or to the Event log. The other thing you could
do is use the NETSEND command in code possibly and send a message to the
console for the SQL Server machine, or you can use the NETSEND to send a
message to a particular machine on the network. The messagebox from NETSEND
will contain the message with an OK button, and the program using the
NETSEND will continue to process and not wait for a reply.

I have used NETSEND in code such as code in a DLL or a Windows NT service
that are not Windows form solutions so I could send debug messages to the
console to see what was happening.

 

[sherifffruitfly]

SQLCLR is not a Windows forms solution.

I'm aware of that.

So, you're not going to get a
Messagebox to show, like a Windows forms solution.

Not directly, in one-step, at any rate.

The best you can do is
write messages to a log file or to the Event log. The other thing you could
do is use the NETSEND command in code possibly and send a message to the
console for the SQL Server machine, or you can use the NETSEND  to senda
message to a particular machine on the network. The messagebox from NETSEND
will contain the message with an OK button, and the program using the
NETSEND will continue to process and not wait for a reply.

I have used NETSEND in code such as code in a DLL or a Windows NT service
that are not Windows form solutions so I could send debug messages to the
console to see what was happening.

Interesting idea on the netsend. I'm currently looking into making
service calls from sqlclr.

Thanks for the thoughts!

cdj

 

[sherifffruitfly]

If we think you're trying to do something dumb,
we're not going to go out of our way to help you achieve it.

Since you have no idea whatsoever why I'm asking, this just amounts to
"I don't understand why you're doing that; therefore it's dumb."

Talk about dumb. Oy.

So yes,
sometimes you DO have to justify what you're doing to get the answers you
want.

I'm already covered on the 'daddy' front - thanks anyway.

If you don't want to answer, then, um, don't answer.

 

[Christophe Lephay]

"sherifffruitfly" <[email protected]> a écrit dans le message de
groupe de discussion :
(e-mail address removed)...

*Why* I want to do it is my business, and is not part of my question.
I hope your oh-so-righteous criticism made *you* feel better, because
that's *all* it achieved.

The reason why you want to do that could help to understand and solve your
problem. It is not hard to understand that how to have a message box from
sql server is not *the* problem.

 

[Jeroen Mostert]

1) You can't add a reference to system.win.forms to a SQL Server
Project
2) You can't add a referenceto a non-SQL Server Project to a SQL
Server Project

Is there any other way to get a call to MessageBox from sqlclr code?

Yes, but it won't do you much good.

The fact that the Windows Forms assemblies can't be referenced isn't an
obstacle; SQL CLR asssemblies can (unfortunately) still P/Invoke directly to
Win32 functions, so you can call MessageBox() directly.

The more pressing reason is that SQL Server runs as a service, and services
can't interact with the desktop unless they're flagged as such (and no
system administrator worth his money is going to do that, because it opens
up security holes). You can try this for testing, but SQL Server will need
to be restarted, and having it run as an interactive service is totally
unacceptable for production environments.

Another post mentioned NET SEND. Be aware that Windows XP SP2 has the
Messenger service that's required for this to work disabled by default, and
the firewall is set to block it as well. From Vista and Server 2008, the
service is no longer supported at all. Basically, it's a dead-end
technology. If you can get it to work, great, but don't become reliant on it.

Alternatives to message boxes depend on what you need them for:

- If you want to trace values as your code is executing, write it to
somewhere else instead. You can use the Application event log, or open a
text file and append to that. You could even insert records in a table, as
long as you're running on a database server... You may need to fiddle with
permissions to get these things to work, but they're much better choices
than single-stepping through everything.

- If you want to stop your code and wait for a keypress for testing
purposes, that's... just really not appropriate for SQL Server-hosted code.
If you need to test, do it outside SQL Server -- properly separate your
classes and set up a separate project for testing. If timing is an issue,
you can try a delay -- just not something that requires input.

- If your code needs a user to confirm something (it's not just for
testing), don't host it in SQL Server period. You can run it as an
application or a service on the same machine as SQL Server if you need to be
close to the database, but hosting is not an appropriate solution in this case.

 

[Jeroen Mostert]

Another interesting question, which is also 100% irrelevant to what I
asked.

Actually, it's quite relevant. What he's getting at is that even if you did
succeed in calling it, the message box would not actually appear, because
services typically can't interact with the desktop (as I detailed in another
post).

Even when they *can* interact, it still requires a person to be actually
logged in on the server to respond to any message boxes that are popped up.
And since any number of users can be logged in, which one would you like to
be notified? "Where would you expect it to appear" is probably the most
relevant question you can ask, if you think about it.

*Why* I want to do it is my business, and is not part of my question.

But it's so much more interesting than your actual question that blaming us
for going after it is disingenuous.

And if people do go after that, you can take it as a sign that they're
trying to warn you not to stick your hand in a hornet's nest. "It's my hand
and I'll stick it where I want it" is likely to get you no further help at
all, not even on how to injure yourself most proficiently.

 

[Arne Vajhøj]

The fact that the Windows Forms assemblies can't be referenced isn't an
obstacle; SQL CLR asssemblies can (unfortunately) still P/Invoke
directly to Win32 functions, so you can call MessageBox() directly.

Hm.

I thought it was just a VS issue.

Are you saying that the C# compiler somehow does not allow
refs to System.Windows.Forms.dll ?

I find that hard to believe and if that is not the case,
then building it as an ordinary class library seems better
than DllImport the Win32 API.

Arne

 

[Jeroen Mostert]

Slight correction to this -- SQL Server does in fact prevent this (no
DllImportAttribute allowed) unless you invoke the CREATE ASSEMBLY statement
with UNSAFE. That's no joke, either -- UNSAFE assemblies can destabilize SQL
Server pretty easily, something managed hosting is supposed to prevent.

I thought it was just a VS issue.

VS is just preventing you from shooting yourself in the foot. See below.

Are you saying that the C# compiler somehow does not allow
refs to System.Windows.Forms.dll ?

No, it's not psychic. It doesn't know you're going to be hosting the
resulting assembly in SQL Server.

I find that hard to believe and if that is not the case,
then building it as an ordinary class library seems better
than DllImport the Win32 API.

If you build it as a regular class library and reference "forbidden"
assemblies that way, SQL Server is just going to barf when you try to import
the assembly. It has its own loading mechanism and will disallow binding to
any assembly other than those from a restricted set and those you import
yourself. And I don't think you can import System.Windows.Forms (you might
be able to, but you'd need to import a whole lot of other assemblies beside,
and all as unsafe ones), but I have to admit I never tried.

 

[sherifffruitfly]

Slight correction to this -- SQL Server does in fact prevent this (no
DllImportAttribute allowed) unless you invoke the CREATE ASSEMBLY statement
with UNSAFE. That's no joke, either -- UNSAFE assemblies can destabilize SQL
Server pretty easily, something managed hosting is supposed to prevent.


VS is just preventing you from shooting yourself in the foot. See below.


No, it's not psychic. It doesn't know you're going to be hosting the
resulting assembly in SQL Server.


If you build it as a regular class library and reference "forbidden"
assemblies that way, SQL Server is just going to barf when you try to import
the assembly. It has its own loading mechanism and will disallow binding to
any assembly other than those from a restricted set and those you import
yourself. And I don't think you can import System.Windows.Forms (you might
be able to, but you'd need to import a whole lot of other assemblies beside,
and all as unsafe ones), but I have to admit I never tried.

Thanks a jllion for the thoughts - very useful!