Link to home
Start Free TrialLog in
Avatar of mingfattt
mingfattt

asked on

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
Avatar of lluthien
lluthien

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.
Avatar of mingfattt

ASKER

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
How are you running the Stored Proc? You'll need to notify the user in this client app.

could you post the stored procedure you are using?
that might clear things up a bit.
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'
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
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.
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.
can i add in the not existing file name?
Not in the notifications tab, you'd have to go back to mingfattt's suggestion.
?? 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
Yes you'd have to go back to mingfattt's suggestion to do that.
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
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.
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
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)

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
ASKER CERTIFIED SOLUTION
Avatar of nmcdermaid
nmcdermaid

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.



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
it show output null, after that
That file is being created on C drive on the *SQL server box*. Is that where you're checking?
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
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.

Thanx got it.... thanx again for the guide... youre great... see you again,

Cheers,
Yee
No worries, I hope it helped.