SQL 2014 Stored Procedure can't be found

1

We upgraded our SQL Server and moved our stored procedures - that went fine and I can access my stored procedures from my PHP page without problem. But when I create a new stored procedure, set up the same way, I get the following message: Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 2812 [code] => 2812 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Could not find stored procedure

I have have the USE DB statement in my stored procedure, I have the permissions set the same way and I call the Stored Procedure in the same way from the page. Anyone have any ideas on what could be happening?

2

Can you see the SP in a tool like SSMS? If so, then the likely problem is permissions (the id trying to execute doesn't have the right permissions)

3

That is the strange thing, I can execute the script through SSMS under the web user and it gives results, no problem...

4

A couple of things to check:

  • Connection String - Are you specifying the database or relying on the default?
  • Schema - When you invoke the stored procedure are you specifying the schema explicitly? Perhaps when you migrated the sprocs an unexpected default schema now has ownership or your new User has an unexpected default schema.
5

OK, this is how I call the sproc from php

$tsql_callSP = "{call [dbo].[ranked_search_body_count]( ?, ?,

So I tried exporting my working sproc and then recreating it - no dice - anything newly created is not accessible via web, yet I can execute the sproc in SQL and it works!

6

Just doing Belt and Braces check:

If in SSMS you RightClick : Properties and then select "Permissions"

Schema = dbo ?

"Users or Roles" section contains your Web User (or some Role that includes your Web User) and has explicit permission for Execute?

Perhaps compare the Permission Properties for one of your new SProcs with an old one to see if there is something obviously different.

1 Like
7

Thanks - you are right - it was an all over check that finally led me to the answer... one of the connection strings on my php page was going to an old server... the string name was so close to the new connection that it got by me! Thanks for your suggestion and help and to all who answered!