locked
Accessing table in DBO schema from Stored Procedure in other Schema RRS feed

  • Question

  • I am experiencing a strange issue related to schema qualification at a customer. No matter what we've tried we cannot recreate the issue internally. I am hoping someone can help.

    We create a Stored Procedure on a schema called "Import". In this stored procedure, we have numerous references to tables on the "dbo" schema, but we have not qualified them (I know this is not best practice, but it's legacy code and we're not going to change it right now). This has been used at other customers without issue. This one new customer is getting an issue where it does not seem to find the dbo table when trying to create the stored procedure. If we take the query out of a stored proceure and just run it as a T-SQL script, it works.

    So, the code below works

    select * 
    
    from Import.Dirsync a
    
    join Inventory i
    
     on a.Audit_InventoryID = i.InventoryID
    
    

    But this does not. It complains that the column "InventoryID" on the table "Inventory" does not exist.

    create procedure Import.Test as
    
    select * 
    
    from Import.Dirsync a
    
    join Inventory i
    
     on a.Audit_InventoryID = i.InventoryID
    
    

    My understanding is that any object on the dbo schema will be resolved.

    Again, this is working on all other servers with our databases.

    Any suggestions?

    Thanks


    Craig Bryden - Please mark correct answers
    Wednesday, September 8, 2010 2:16 AM

Answers

  • Perhaps you have more than one table named Inventory.  And the T-SQL script is finding one (maybe the one in dbo) and the procedure is finding another (maybe in schema Import).  Try running

    select t.name As TableName, s.name As SchemaName
    from sys.tables t
    Inner Join sys.schemas s On t.schema_id = s.schema_id
    where t.name = 'Inventory'
    

    and see if you get more than one table named Inventory.  You might also have a table named Inventory in one schema and a view in another schema named Inventory.  So also run

    select t.name As TableName, s.name As SchemaName
    from sys.views t
    Inner Join sys.schemas s On t.schema_id = s.schema_id
    where t.name = 'Inventory'
    
    

    And, also look in the master database by running these queries in master. 

    Of course you will have to run them on your customer's server since that is where the problem is.

    Tom

    • Proposed as answer by Naomi N Wednesday, September 8, 2010 3:00 AM
    • Marked as answer by Craig BrydenMVP Wednesday, September 8, 2010 3:35 AM
    Wednesday, September 8, 2010 2:56 AM

All replies

  • Perhaps you have more than one table named Inventory.  And the T-SQL script is finding one (maybe the one in dbo) and the procedure is finding another (maybe in schema Import).  Try running

    select t.name As TableName, s.name As SchemaName
    from sys.tables t
    Inner Join sys.schemas s On t.schema_id = s.schema_id
    where t.name = 'Inventory'
    

    and see if you get more than one table named Inventory.  You might also have a table named Inventory in one schema and a view in another schema named Inventory.  So also run

    select t.name As TableName, s.name As SchemaName
    from sys.views t
    Inner Join sys.schemas s On t.schema_id = s.schema_id
    where t.name = 'Inventory'
    
    

    And, also look in the master database by running these queries in master. 

    Of course you will have to run them on your customer's server since that is where the problem is.

    Tom

    • Proposed as answer by Naomi N Wednesday, September 8, 2010 3:00 AM
    • Marked as answer by Craig BrydenMVP Wednesday, September 8, 2010 3:35 AM
    Wednesday, September 8, 2010 2:56 AM
  • Thanks a million Tom. One of my colleagues had created some views in the Import Schema that were not meant to be deployed

    Great help!


    Craig Bryden - Please mark correct answers
    Wednesday, September 8, 2010 3:36 AM