From: Avi on
Hi all,



I have a sp that fails to execute the first attempt after it is created.
After the first attempt all other attempts succeed. I'm using sql server
2005. I do not know if it is related or not, but the sp that fails the
first time is dropping and creating other stored procedurs.





The error message:



Msg 2812, Level 16, State 1, Procedure sp_NMS_SyncNMStoMP, Line 39

Could not find stored procedure 'MP.dbo.sp_aaa'





Thanks,

Avi


From: Bob Barrows on
Avi wrote:
> Hi all,
>
>
>
> I have a sp that fails to execute the first attempt after it is
> created. After the first attempt all other attempts succeed. I'm
> using sql server 2005. I do not know if it is related or not, but
> the sp that fails the first time is dropping and creating other
> stored procedurs.
>
>
>
>
>
> The error message:
>
>
>
> Msg 2812, Level 16, State 1, Procedure sp_NMS_SyncNMStoMP, Line 39
>
> Could not find stored procedure 'MP.dbo.sp_aaa'
>
>
>
1. You should stop using the "sp_" prefix for procedures that are not
system stored procedures. This practice can lead to difficult-to-debug
symptoms since SQL Server assumes a procedure with that prefix is a
system procedure and therefore attempts to find it in the Master
database, even when you explicitly qualify the procedure with the name
of the database when calling it.

2. If changing your naming standard fails to solve your problem, you
need to look at the section of NMS_SyncNMStoMP that is relevant to the
creation of 'MP.dbo.aaa' (I've removed the "sp_" bits). It seems pretty
obvious that your code is attempting to run this procedure before
creating it. So search your code for calls to this procedure and verify
that they all appear _after_ the procedure is created.

--
HTH,
Bob Barrows


From: Tom on
On Mar 15, 11:40 am, "Avi" <remember...(a)yahoo.com> wrote:
> Hi all,
>
> I have a sp that fails to execute the first attempt after it is created.
> After the first attempt all other attempts succeed.  I'm using sql server
> 2005.  I do not know if it is related or not, but the sp that fails the
> first time is dropping and creating other stored procedurs.
>
> The error message:
>
> Msg 2812, Level 16, State 1, Procedure sp_NMS_SyncNMStoMP, Line 39
>
> Could not find stored procedure 'MP.dbo.sp_aaa'
>
> Thanks,
>
> Avi

You should not be naming your stored procedures with a prefix of sp_
this is a performance issue.
Read BOL.
"We strongly recommend that you not use the prefix sp_ in the
procedure name. This prefix is used by SQL Server to designate system
stored procedures. For more information, see Creating Stored
Procedures (Database Engine)."


It is difficult to identify your problem without the code but a guess
might be that you failed to check for the existence of a procedure
before you drop it and it is not there when your drop it.