Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

3 Answers

up vote 3 down vote

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;
share|improve this answer
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. – Putnik Oct 27 '11 at 14:59
@Putnik: yes, correct. This is how you avoid specifying schema usually... – gbn Oct 27 '11 at 15:00
.. 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. – Putnik Oct 27 '11 at 15:07
up vote 2 down vote

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.

share|improve this answer
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 Oct 27 '11 at 15:11
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. – Putnik Oct 27 '11 at 15:14
up vote 0 down vote

Given the comments you left You might consider creating synonyms for the objects. I havent used them much in SQL Server so I'm not sure if they'll help but I used them a lot in Oracle. The article I've linked below sounds like this might do what you want.

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.