CODEWITHSUNDEEP
asp.netsql-serversql-server-2005sql-server-2008stored-procedures
Putnik
Putnik

Reputation: 6794

SQL Server 'Could not find stored procedure' without schema name

SQL Server 2005/2008, db=user=schema=owner='John', ASP.net 2.0.50727, vb, IIS7

I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.

    Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)
    Dim dtVehicle As New DataTable

    With sqlCmdVehicle
        .Parameters.AddWithValue("FullStockNo", "N102010")
        .CommandType = CommandType.StoredProcedure
    End With

    sqlConn.Open()
    sqlAdapter.SelectCommand = sqlCmdVehicle
    sqlAdapter.Fill(dtVehicle)

DB is backed up and successfully restored on new server. When I try to run with new SQL Server 2008 I got an error:

Could not find stored procedure 'SP_Name'. pointing at last line.

I can see procedures with 'MS SQL management studio' not as dbo.SP_Name but as John.SP_Name

When I change

System.Data.SqlClient.SqlCommand("SP_Name", sqlConn)

into

System.Data.SqlClient.SqlCommand("John.SP_Name", sqlConn)

all works well, but it's the same with all other procedures and a lot of such kind places in code :(

I got http://msdn.microsoft.com/en-us/library/ms189915.aspx, but caller's default schema is correct. Again, all works well on old box.

What should I fix in SQL to allow run SP without explicitly mentioned user/schema name?

Thank you.

=======================================

Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project. Thanks to all participants.

Upvotes: 1

Views: 14111

Answers (7)

chatay
chatay

Reputation: 164

@putnik has already choosen the answer but there is a solution for this.

Please take reference @Joe P answer. You should not create a user with sysAdmin. Rather than that you have to remove dbo from default schema and choose which schemas you are going to use.

In SQL Server => database => database Name => Security > Users

Select Properties of related user => select General => Select Default Schema =>

Click three dot => browse for objects => choose related schemas without choosing dbo schema.

enter image description here

Upvotes: 0

Joe P
Joe P

Reputation: 21

We discovered today that, if you change your users permissions to be a sysadmin on the SQL Server, even though you have specified the default schema for your user in the database, the default schema is ignored if you try to run the stored procedure without specifying the schema name, it will think that the stored procedure is under the dbo schema! When you remove sysadmin rights from the login user, then it will run the stored procedure using the default schema set up for that database for the user, if you specify no schema name in your EXEC command.

Upvotes: 2

Pintu
Pintu

Reputation: 11

In SQL Server> database> Your database Name> Security > Schemas

Select dbo Schemas > properties > Select Permission >

In that find "Users and role" section

Add your login User Name and permission for username give check on Execute.

Upvotes: 1

Putnik
Putnik

Reputation: 6794

Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project. Thanks to all participants.

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88054

The default schema for SQL Server 2008 is "dbo". This is why it's trying to hit dbo.SP_Name

You need to set a default schema for the user. See the following question: Can you set default Schema for SQL 2008 Query

Upvotes: 0

YvesR
YvesR

Reputation: 6222

I am not 100% sure, but you probably have a connection to the database using sql username/pw and then user the username "john".

In this case your default schema is "john" as well and you directly have to use dbo.SP_name to access your procedure correctly.

What also works is to setup the default shema for the user john for the user database to "dbo". You can do this in the properties menu of the user.

Sitenote: Don't use sp_. If you create your own procedures use e.g. proc_ instead. Using sp_ always make the SQL server looks in the master database first. This slow down the performance.

Upvotes: 2

user596075
user596075

Reputation:

You will know exactly what is happening if you run SQL Profiler and view the trace data. You'll see what the exact call is to the database and what's happening.

I know you've already mentioned this, but I'd double check on what the user is that your code uses has as a default schema. If no schema is specified, SQL Server will first use the default schema of the user, and if it can't find that DB object then it'll try the dbo schema, and if that doesn't work, you'll get that error you see.

Upvotes: 1

Related Questions