none
SQL71501 Unresolved Reference RRS feed

  • Question

  •  I am working with a database that contains sets of two synonyms for the same object in different schemas in the following form:

    Create Synonym Asdb.synMatchCache For AdvSuite.Asdb.MatchCache;

    CreateSynonym Aswc.synMatchCache For AdvSuite.Aswc.MatchCache;

    This allows me to access the object referenced by the synonym in the approriate schema based on the schema in which a stored procedure is executed. For example:

    Exec Aswc.SomeStoredProc will properly use the correct version of table MatchCache when the stored procedure contains a statement such as:

    Select * from synMatchCache

    The database was originally created from SQL scripts. I have now created a new project in VS 2012 from the existing database. When building the database I receive numerous errors and warning related to the use of the synonyms. The errors generally take the form:

    Error 1 SQL71501: Function: [Aswc].[SelectNoisePhrases] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [Aswc].[synNoisePhrases] or [dbo].[synNoisePhrases]. C:\Users\sacang\Documents\Planning\Telavance\Code\AdvSuite\AdvSuite\Aswc\Functions\SelectNoisePhrases.sql 12 7 AdvSuite

    I was unsuccesful finding a solution to this specifc problem. Please note that all of the objects including the synonyms are in the same database separated by schema names.

    Would appreciate any suggestions

    Thank You

    Sal


    • Edited by SalCang Sunday, December 9, 2012 12:36 AM
    Sunday, December 9, 2012 12:34 AM

Answers

  • Self referencing using a literal would only work if the project name is AdvSuite.

    You can shield yourself by using [$(DatabaseName)] when referencing yourself.

    This sample worked for me:

    create schema Asdb
    go
    create schema Aswc
    go
    create table Asdb.MatchCache
    (
    	id int not null
    )
    go
    create table Aswc.MatchCache
    (
    	id int not null
    )
    go
    create synonym Asdb.synMatchCache For [$(DatabaseName)].[Asdb].[MatchCache];
    go
    create synonym Aswc.synMatchCache For [$(DatabaseName)].[Aswc].[MatchCache];
    go
    create proc Asdb.p1
    as
    select * from synMatchCache
    go
    create proc Aswc.p1
    as
    select * from synMatchCache
    go


    -GertD @ www.sqlproj.com

    • Marked as answer by Janet Yeilding Monday, December 10, 2012 10:56 PM
    Monday, December 10, 2012 12:39 AM
    Moderator