August 26, 2008 at 10:24 am
Hi,
Newbie here... Encountered a strange a situation where my Java App (on JBoss) hit the "Could not find stored procedure" but I could see the stored procedure in the Enterprise Manager. The database is SQL Server 2000 . Even if i restart my JBoss application server, still the same problem. The problem was only solved after scripting and executing the stored procedure. The weird thing is I could script it from teh Enterprise Manager (supposingly not found) but once executed, the application could locate the stored procedure.
So, considered problem solved for now. However, my customer needs an explanation to the incident and question if the incident would happen again. Does anybody have any idea or possible reason for this incident? Any help would be appreciated. Thanks in advance.
August 26, 2008 at 1:05 pm
Could it have been owned by a different user? Maybe it was originally created by userA, so it was named userA.myproc, and when userB went to execute it, it couldn't be found. When you recreated the procedure, you either created it as userB, or created as someone with dbo rights so it became either userB.myproc or dbo.myproc, either of which userB would find?
Hope this helps,
Chad
August 26, 2008 at 11:49 pm
it's all dbo... furthermore, if it's a different owner, the rescripting and execution should remain the same owner, thus seems like it's very unlikely to be this problem.
August 26, 2008 at 11:56 pm
did u "refresh" your enterprise manager and check whthr the procedure still existed.
"Keep Trying"
August 27, 2008 at 12:09 am
yes, it was refreshed also. if it wasn't there, i might not be able to script it right? and it only happened to 1 sp... (as far as i know). i know in oracle, there's a invalid/valid status... i wonder if sqlserver has that also?
August 27, 2008 at 3:09 am
Ng Kean Tuck (8/27/2008)
yes, it was refreshed also. if it wasn't there, i might not be able to script it right? and it only happened to 1 sp... (as far as i know). i know in oracle, there's a invalid/valid status... i wonder if sqlserver has that also?
Yes... Since your application could not find the procedure was it connecting to the right database. Any typos when calling the procedure.
"Keep Trying"
August 27, 2008 at 8:00 am
cannot be spelling wrong or pointing to wrong DB because it has been running daily. Suddenly couldn't find. furthermore, just re-executed the SP, there was no change to the application. Any other ideas?
August 27, 2008 at 8:01 am
You're right. I didn't think about EM scripting out the owner as well. If Chirag's idea about the database isn't right (maybe due to some kind of caching?), and you can get it to happen again, perhaps a profiler trace would shed some light on where it's looking for the proc.
Chad
August 28, 2008 at 12:42 am
Any change in User rights of the user connecting to server.
"Keep Trying"
August 28, 2008 at 1:17 am
there was no change in user rights. but if there was any, it shouldn't affect only 1 SP but the whole lot.
August 28, 2008 at 11:26 pm
Chirag (8/28/2008)
Any change in User rights of the user connecting to server.
Ooops i meant rights on that particular procedure if they have been set that way. Seems like we are at a dead end here... let me try searching the net 🙂
"Keep Trying"
August 29, 2008 at 3:05 pm
The only other thing I can think of is some kind of corruption, but it would have to be so specific that I doubt that would be it. I hate to hope that it will happen again, but if it does you'll have an opportunity to run some diagnosis. I still think that profiler would be a good thing to look at. All the evidence points to something that _doesn't_ get scripted out, but I'm not sure what that would be.
Chad
September 1, 2008 at 6:49 am
Thanks Chad/Chirag.... I hate to hope that it happens again but if it ever does, i'll try to get the profiler log. cheers!
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply