CODEWITHSUNDEEP
visual-studiosql-server-data-tools
Mike Caputo
Mike Caputo

Reputation: 69

Error 71561: Unresolved reference to object

In Visual Studio 2017, SSDT 2015, I am receiving the "unresolved reference to object" error. This error is occurring 2,589 times, so it's not an issue with one particular piece of code. All of the code compiles and runs in SQL Server directly.

I've tried everything from this post, except for disabling "extended Transact-SQL verification for common objects" because that option has been removed.

I've tried pretty much everything else I can find on Google; I've been banging away on this for two days now.

Here is an example of some of the code that's getting the error. It runs fine on SQL Server directly. The error comes up in the FROM clause on each line that contains a database reference. I can't remove those references or change the location of the view. This is also happening in stored procedures that reference the views.

CREATE view [Migration].[vwCHILDS_Allegation_AllegationTrackingCharacteristics]
as
with src as (
    select
        (select AllegationId from CWNS_Migration.Allegation.Allegation where SourceSystemId = cast(1 as int) and SourceSystemIdentifier = cast(a.IDNBR as nvarchar(64))) AllegationId
        , (select TrackingCharacteristicsId from CWNS_Migration.Allegation.TrackingCharacteristics where Code = dfrvmi1.DestinationDataFieldReferenceValueCode) TrackingCharacteristicsId
        , (select VerificationStatusId from CWNS_Migration.Allegation.VerificationStatus where Code = dfrvmi2.DestinationDataFieldReferenceValueCode) VerificationStatusId
        , cast(1 as int) SourceSystemId
        , cast(src.IDNBR as nvarchar(64)) SourceSystemIdentifier
        , src.IDNBR SourceSystemIdentifier_Numeric
        , case when src.CRET_DT_TM = '0001-01-01' then null else src.CRET_DT_TM end SourceSystemCreatedDateTime
        , (
            select
                max(pe.PersonId) 
            from
                CWNS_Migration.PersonIdentity.PersonIdentifier pe
                join CHILDSDB2.VLCHA.STAFF_USERID st on cast(st.FK_STAFFFK_PERSID as nvarchar(64)) = pe.Identifier
                    and pe.PersonIdentificationSystemId = 4
            where
                st.USERID = ltrim(rtrim(src.CRET_USER_ID))) SourceSystemCreatedPersonId
    from
        CHILDSDB2.VLCHA.RPT_TRKNG_CHAR src
        join CHILDSDB2.VLCHA.ALGTN a on a.FK_RPTRPT_NBR = src.FK_RPTRPT_NBR
        join CHILDSDB2.VLCHA.FINDING f on f.FK_ALGTNID = a.IDNBR
        join DataCatalog.dbo.DataFieldReferenceValueMappingInfo dfrvmi1 on dfrvmi1.SourceDataFieldReferenceValueDataFieldId = 5438
            and dfrvmi1.SourceDataFieldReferenceValueCode = src.FK_TRKNG_CHAR_TCD
            and dfrvmi1.DestinationDataFieldReferenceValueDataFieldId = 20586
        join DataCatalog.dbo.DataFieldReferenceValueMappingInfo dfrvmi2 on dfrvmi1.SourceDataFieldReferenceValueDataFieldId = 1775
            and dfrvmi2.SourceDataFieldReferenceValueCode = f.FINDING_DET_CD
            and dfrvmi2.DestinationDataFieldReferenceValueDataFieldId = 55983
)
select
    src.*
from
    src
    left join CWNS_Migration.Allegation.AllegationTrackingCharacteristics tgt on tgt.SourceSystemId = src.SourceSystemId and tgt.SourceSystemIdentifier = src.SourceSystemIdentifier
    left join CWNS_Migration.Quarantine.AllegationTrackingCharacteristics q on q.SourceSystemId = src.SourceSystemId and q.SourceSystemIdentifier = src.SourceSystemIdentifier
        and q.QExecutionId = 1
where
    q.QExecutionId is null
    and (
        isnull(src.AllegationId, 0) <> isnull(tgt.AllegationId, 0)
        or isnull(src.TrackingCharacteristicsId, 0) <> isnull(tgt.TrackingCharacteristicsId, 0)
        or isnull(src.VerificationStatusId, 0) <> isnull(tgt.VerificationStatusId, 0)
        or try_cast(isnull(src.SourceSystemCreatedDateTime, '') as datetime) <> isnull(tgt.SourceSystemCreatedDateTime, '')
        or isnull(src.SourceSystemCreatedPersonId, '') <> isnull(tgt.SourceSystemCreatedPersonId, '')
    );

Upvotes: 1

Views: 502

Answers (1)

Tolu
Tolu

Reputation: 175

Add a Database reference to your project:

You will need to refactor your code to use:

[$(*ReferencedDatabaseName*)].schemaName.tableName

Upvotes: 1

Related Questions