Sql-server – SQL Server: How to call stored procedure without schema name

sql-server-2005sql-server-2008stored-procedures

SQL Server 2005std/2008web edition
db=user=schema=owner='John'

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("mySP_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 'mySP_Name'.

pointing at last line.
I can see procedures with 'MS SQL management studio' not as dbo.mySP_Name but as John.mySP_Name

When I change

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

into

System.Data.SqlClient.SqlCommand("John.mySP_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.

Best Answer

Best practice is always qualify schema for all object references. See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

But you can run this if you choose to ignore best practice:

ALTER SCHEMA dbo TRANSFER John.mySP_Name;
Related Question