Msgbox in store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Msgbox in store procedure

Author

Topic

micsak
Starting Member

5 Posts

Posted - 2015-03-26 : 12:07:05

I want to send a msgbox to user through store procedure.
Is there any way to do it?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 12:09:24

no.

Think about it. The stored procedure is running on a server, possibly located on the other side of the world. How would the server be able to pop up a message box on your workstation?

OTOH, what do you want to see? Perhaps there's another way.

micsak
Starting Member

5 Posts

Posted - 2015-03-26 : 14:40:56

I need similar event as "RAISERROR". The problem with raiserror is when I call it there is rollback of my previous commands.
Example: When user is trying to do something, to give a message to user and update a record in a table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 14:46:23

You probably just need to change how you are using RAISERROR. But the app needs to be the one to send the message. The app will receive the message from RAISERROR and then the app will pass it to the user.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 14:49:34

RAISERROR('message',0,0) WITH NOWAIT

will not roll back your work. The trick is to set severity in the range 0-10 or negative

micsak
Starting Member

5 Posts

Posted - 2015-03-27 : 03:26:53

I tried RAISERROR('message',0,0) WITH NOWAIT
Indeed there is no rollback, but there is no message also to the user

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 05:26:46

quote:
Originally posted by micsak

I tried RAISERROR('message',0,0) WITH NOWAIT
Indeed there is no rollback, but there is no message also to the user

Yep. You have to capture the RAISERROR message in your application first. And then send a message to the user through your application. SQL Server can't do it by itself.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 06:27:13

is the SQL code running as a scheduled task perhaps? rather than as a result of some SQL that the Application launched?

That is more difficult to communicate to a user. You'd need to store the message in a table, and then have something running on the User's computer that "checked" for any new messages - every second or so.

Or use a queueing system of some sort - Service Broker perhaps? User's Application will still have to be running and checking etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 13:29:36

If it's a scheduled task, I'd just send an email through Database Mail.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

micsak
Starting Member

5 Posts

Posted - 2015-03-28 : 00:32:06

It is not running as scheduled task.

quote:

is the SQL code running as a scheduled task perhaps? rather than as a result of some SQL that the Application launched?

That is more difficult to communicate to a user. You'd need to store the message in a table, and then have something running on the User's computer that "checked" for any new messages - every second or so.

Or use a queueing system of some sort - Service Broker perhaps? User's Application will still have to be running and checking etc.

micsak
Starting Member

5 Posts

Posted - 2015-03-28 : 00:35:15

The application is developed in devexpress environment and I do not have tools to capture the "RAISERROR" in my application. I'm looking for a solution only via SQL

quote:
quote:
Originally posted by micsak

I tried RAISERROR('message',0,0) WITH NOWAIT
Indeed there is no rollback, but there is no message also to the user

Yep. You have to capture the RAISERROR message in your application first. And then send a message to the user through your application. SQL Server can't do it by itself.

Tara Kizer

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-28 : 03:40:06

you are out of luck. There is simply no way for sql to pop up a message on a desktop. Think about what you are asking.

Kristen
Test

22859 Posts

Posted - 2015-03-28 : 11:07:09

quote:
Originally posted by micsak

I'm looking for a solution only via SQL

We send "user information messages" back to the application using a SELECT statement.

We do have some code in our APP so that if the name of the first column (in ANY recordset returned to the APP) is "_ERROR_" then the contents of that column are displayed to the user as an error message (we do this within a <DIV> in HTML so it i within the page, but you could do a Dialog Box popup instead ... or something else.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources