January 6, 2011 at 3:41 am
Hello everyone,
I have a SP called p_ins_support_call, which, inside it, has an exec statement to another proc, p_sel_support_ticket, which returns the next support ticket available. Both are create within the db_user context, which is the owner for the entire database. Out of nothing, the following stopped working:
declare @support_ticket int
exec p_sel_support_ticket @support_ticket OUTPUT
It is not working only in production servers, and oddly, I tried executing it from Management Studio, it gave me the following error:
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'p_sel_support_ticket'.
BUT, if I do the following, it works fine:
exec db_user.p_sel_support_ticket @support_ticket OUTPUT
Keep in mind I still didn't try using the context in the procedure call because it needs approval from my boss, but I'd like to hear from the experts what could cause this kind of behavior...
Thanks in advance,
Marcelo
January 6, 2011 at 4:26 am
Hi Marcelo
Something must have happened.
You have described two ways you're executing the stored procedure:
1:
exec db_user.p_sel_support_ticket @support_ticket OUTPUT
2:
exec p_sel_support_ticket @support_ticket OUTPUT
As 1 works but 2 doesn't I suspect the issue might be schema related. Has the default schema of db_user been changed to dbo instead of db_user?
That would explain the issue, as there is no dbo.p_sel_support_ticket stored procedure, but there is a db_user.p_sel_support_ticket stored procedure. (Provided that the stored procedure was indeed created by a user with db_user as default schema)
Best regards
Lars Søe Mikkelsen
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply