SQL Server: How to call stored procedure without schema name?

Question

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.

Answer 1

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;

• Putnik: Please excuse me if I said unclear or misunderstand something. As far as I understood this request would transfer mySP_Name from John to dbo.
• gbn: @Putnik: yes, correct. This is how you avoid specifying schema usually...
• Putnik: .. But I after that I'll have to fix a lot inside schema because of a lot 'Invalid object name' errors. It's not my development, and it works well on old server. I just want to fix something in new server to run it without patching a lot of code or SPs. Thanks.

Answer 2

Did you want the John schema? Do you still have a dbo schema? This may be connected to the other issue you posted.

In regards to the question I dont think you can set a procedure to ignore a schema. If you are operating inside a schema (dbo for example) and are trying to call an object in another schema (john) I believe you have to use the schema name.

You can move the stored procedure to dbo using the command

ALTER SCHEMA dbo TRANSFER nameandschemaoftheSP;

It sounds like you have a number of stored procedures that would need to be moved however. The code below should generate the scripts necessary to move each stored procedure within the john schema over to the dbo schema. Then you can decide which ones you want to run.

SELECT
 'ALTER SCHEMA john TRANSFER ' + s.Name + '.' + p.Name 
FROM 
 sys.Procedures p 
INNER JOIN 
 sys.Schemas s 
ON p.schema_id = s.schema_id 
WHERE s.Name = 'dbo';

Beyond this I'd take a look at the dbo schema and do some research to see why stored procedures that used to be in dbo are unexpectedly showing up in john.

• Putnik: I have dbo schema but it's wrong fay for me. If I would move my SPs from John schema to dbo I'll have to fix a lot inside schema because of a lot 'Invalid object name' errors. It's not my development, and it works well on old server. I just want to fix something in new server to run it without patching a lot of code or SPs. Thanks.
• Putnik: These procedures didn't "unexpectedly showing up in john". They are located by the unknown programmer intentionally, for some unknown reason. It works on old server exactly at that way.

Answer 3

Given the comments you left, you might consider creating synonyms for the objects. I haven't used them much in SQL Server, so I'm not sure if they'll help, but I used them a lot in Oracle. This article sounds like it might do what you want.