• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Message box in stored procedured

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
mingfattt
Asked:
mingfattt
  • 13
  • 11
  • 3
1 Solution
 
lluthienCommented:
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
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
How are you running the Stored Proc? You'll need to notify the user in this client app.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lluthienCommented:
could you post the stored procedure you are using?
that might clear things up a bit.
0
 
mingfatttAuthor Commented:
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
 
mingfatttAuthor Commented:
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
 
lluthienCommented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
can i add in the not existing file name?
0
 
nmcdermaidCommented:
Not in the notifications tab, you'd have to go back to mingfattt's suggestion.
0
 
mingfatttAuthor Commented:
?? 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
 
nmcdermaidCommented:
Yes you'd have to go back to mingfattt's suggestion to do that.
0
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
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
 
mingfatttAuthor Commented:
it show output null, after that
0
 
nmcdermaidCommented:
That file is being created on C drive on the *SQL server box*. Is that where you're checking?
0
 
mingfatttAuthor Commented:
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
 
nmcdermaidCommented:
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
 
mingfatttAuthor Commented:
Thanx got it.... thanx again for the guide... youre great... see you again,

Cheers,
Yee
0
 
nmcdermaidCommented:
No worries, I hope it helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 13
  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now