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
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
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
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.
that might clear things up a bit.
ASKER
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'
ASKER
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
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.
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.
ASKER
can i add in the not existing file name?
Not in the notifications tab, you'd have to go back to mingfattt's suggestion.
ASKER
?? 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.
ASKER
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.
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.
ASKER
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
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)
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)
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
ASKER
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?
ASKER
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.
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.
ASKER
Thanx got it.... thanx again for the guide... youre great... see you again,
Cheers,
Yee
Cheers,
Yee
No worries, I hope it helped.
it doesnt give a very neat way of error handling by itself,
but you can catch those exceptions at the client.