SQL Server 2005std/2008web edition
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
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?
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.