?
Solved

Message box in stored procedured

Posted on 2005-04-26
27
Medium Priority
?
258 Views
Last Modified: 2010-03-19
Hai Expert

I have a bundle of file name need to notify the user when it is not exist... i am trying to use netsend to do this for me but it just cannot fit in.. and i realize that it is impossible to use message box in the stored procedure but i just need this badly.. can anyone please suggest.. Thanx in advance

Cheers,
Yee
0
Comment
Question by:mingfattt
  • 13
  • 11
  • 3
27 Comments
 
LVL 11

Expert Comment

by:lluthien
ID: 13865159
you could use raiserror to raise an exception on the client.

it doesnt give a very neat way of error handling by itself,
but you can catch those exceptions at the client.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13865172
can you show it clearer since i never try it before.. well just a newbie in this, for your information i am currently using the cursor to extract the file that not exist in one table, the maximum of file will be 40 which i need to trace... thanx in advance for explanation..

Yee
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13865300
How are you running the Stored Proc? You'll need to notify the user in this client app.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:lluthien
ID: 13865317
could you post the stored procedure you are using?
that might clear things up a bit.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13865319
i am running it by using scheduling in job management... which it will be run every day in particular time, i need this facility because i need to detect whether the file is ready for load, if in case certain file is not there, so i need to delay my code and notify the user by using 'message box'
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13865326
ok

SELECT @name = sum(exist)FROM testing Where exist = 0            --testing for the file not there
set @name1 =''

if @name is not null
begin
      
      DECLARE MyCursor CURSOR STATIC FOR
            SELECT Filetitle FROM testing Where exist = 0
            OPEN MyCursor

            FETCH NEXT FROM MyCursor INTO @FileTitleNotExist  --temporary variable

      WHILE @@FETCH_STATUS=0
      
      BEGIN
        set @name1 =  @name1+', '+@FileTitleNotExist
      FETCH NEXT FROM MyCursor INTO @FileTitleNotExist
      END
      
      set @netsend = 'NET SEND agmpimo258 Unknown File'+ @name1 + '-and the shortage will delay for 5 minutes'
      EXEC master..xp_cmdshell @netsend, no_output
      
      drop table testing
      WAITFOR DELAY '00:02:00'
      
      CLOSE MyCursor
      DEALLOCATE MyCursor


end

This is just a example which i m using netsend in this case which still can afford to fit in the number of file, thanx very much
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13866375
instead of trying to send the message from the stored procedure,
just let the job-step fail with raiserror, see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_5ooi.asp
for the syntax.

raising an error in the stored procedure will cause the job to fail.
in the job manager you can arrange _what_ to do when a job fails.
for instance, run another stored procedure
or send a message.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13871630
Further to lluthiens comment, if you poke around in the job you'll find there is a built in mechanism for sending net sends and emails.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13872150
can i add in the not existing file name?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13872288
Not in the notifications tab, you'd have to go back to mingfattt's suggestion.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13872315
?? i mean whether i can show the not existing file name as pop up or any others to notify the user regarding the inicomplete file issue
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13872321
Yes you'd have to go back to mingfattt's suggestion to do that.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13872341
you mean my own suggestion? by using net send? well if yes as i mention the netsend boxes cannot show all the text and file name... because of the space limitation
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13872377
Oops sorry, you are mingfatt!!

Does it absolutely have to be a IM solution?

Would email be sufficient to alert the other person?

If so you can set up SQLMail then use xp_sendmail to send a more detailed message.

Alternatively, does the entire path need to appear in the net send? Can you just send the filename, asusming that the person knows what path it should be in?


The only other alternative I can think of is that you write all these details to a log table then have apolling application on the users machine.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13872405
ha, actually i just send the file name only (its kinda weird in this case!!) moreover the file will be up to 40 files name, and net send definitely can not be use in this case... can you please clarify the last alternative?in sense of email i am doubt regarding the timing issue, which FYI the file need to be load as soon as possible every monday morning and the user need the immediate notification.... thanx in advance.. and for the quick reply

me, yee
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13872482
1. Your job tries to find the file
2. If it doesn't it writes to a table in SQL Server



You have an app running the client machine which checks this table every minute. If it finds an entry it pops it up on the screen.


Alternatively, you could just use a text file (which means that you don't need SQL Serve Access)

0
 
LVL 2

Author Comment

by:mingfattt
ID: 13881802
i am sorry, can you make it clear please or maybe there is some other way like doing net send which provide the bigger writing space, or else maybe can call the table to pop up immediately after the detection. thanx
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 13881878
OK heres one method:

Your original SP altered:



SELECT @name = sum(exist)FROM testing Where exist = 0          --testing for the file not there
set @name1 =''

if @name is not null
begin
     
     DECLARE MyCursor CURSOR STATIC FOR
          SELECT Filetitle FROM testing Where exist = 0
          OPEN MyCursor

          FETCH NEXT FROM MyCursor INTO @FileTitleNotExist  --temporary variable

     WHILE @@FETCH_STATUS=0
     
     BEGIN
        set @name1 =  @name1+', '+@FileTitleNotExist
     FETCH NEXT FROM MyCursor INTO @FileTitleNotExist
     END
     
     set @WriteLogLine = 'echo %TIME% Unknown File '+ @name1 + '-and the shortage will delay for 5 minutes >> X:\LogFile.TXT'
     EXEC master..xp_cmdshell @WriteLogLine, no_output
     
     set @netsend = 'NET SEND agmpimo258 An issue has occured. Please check the log file on X drive.'
     EXEC master..xp_cmdshell @netsend, no_output


     drop table testing
     WAITFOR DELAY '00:02:00'
     
     CLOSE MyCursor
     DEALLOCATE MyCursor


end





There is no client app with a db connection to capture events.

Therefore the only way to make something pop up on the users machine is to:

1. Send somethnig to a client on the machine, such as net send, email etc.
2. Have something running on the client machine that polls for events, such as a VB Script.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13881961
i wrote it and i change the drive to c, the porblem is i didnt see any log file created, please help, i think this would be a good idea, and last question from me.. thanx.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13882011
1. Are you certain the cursor is returning rows? If not then nothing will be written.
2. Run the xp_cmdshell line manually in Query Analyzer without no_output and see if anything comes back in the results pane. It may give you an enlightening error message.



0
 
LVL 2

Author Comment

by:mingfattt
ID: 13882089
it come out half way only the line, but i still cannot find the log file in the default location, fyi i just run the two lines manually
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13882196
it show output null, after that
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13882217
That file is being created on C drive on the *SQL server box*. Is that where you're checking?
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13882254
you mean in the server? ha, i got it but can i create it in the local machine? because it is allocated in too far from my machine thanx
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13882307
xp_cmdshell runs things on the server.

Thats why I picked X as a drive. It should be a mapped drive that both the SQL Server and the End user can get to.

0
 
LVL 2

Author Comment

by:mingfattt
ID: 13882323
Thanx got it.... thanx again for the guide... youre great... see you again,

Cheers,
Yee
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13882336
No worries, I hope it helped.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question