none
Error 71561: Unresolved reference to object RRS feed

  • Question

  • 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 the StackOverflow post titled "Unresolved Reference Error SQL71561 with SQL Server Data Tools 2010" (I can't post links because my account isn't verified), 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, '')
        );


    Monday, October 28, 2019 6:24 PM

All replies

  • You may need to add CWNS_Migration as a database reference.

    A Fan of SSIS, SSRS and SSAS

    Monday, October 28, 2019 7:02 PM
  • Already done. That was one of the first things I tried.
    Monday, October 28, 2019 7:57 PM
  • Did you create the schemas you used in your script, which are not the dbo?

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Monday, October 28, 2019 8:54 PM
    Monday, October 28, 2019 8:53 PM
  • Yes. As I said, all of this code runs fine in SQL Server Management Studio. The views have been created. This error is only happening in the SSDT project.
    Monday, October 28, 2019 9:01 PM
  • I mean the schema files in SSDT.

    A Fan of SSIS, SSRS and SSAS

    Monday, October 28, 2019 9:16 PM
  • Not sure what you mean. I've imported all the schemas and objects into a project and that project is referenced in the project where I'm receiving the errors.
    Monday, October 28, 2019 9:57 PM
  • Replace your referenced database names with SQLCMD variables, for example, replace CWNS_Migration with $(CWNS_Migration). 

    A Fan of SSIS, SSRS and SSAS

    Tuesday, October 29, 2019 2:16 PM
  • Tried that. Not working.
    Wednesday, October 30, 2019 8:16 PM
  • I would suggest to rename the CTE as something else (not src) since src is used inside CTE.

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Wednesday, October 30, 2019 8:45 PM
    Wednesday, October 30, 2019 8:45 PM
  • Again, this works when run in SSMS. Also, there are errors all over the place, not just with this CTE.
    Wednesday, October 30, 2019 9:29 PM
  • So I've now managed to resolve all of the errors using database references and variables. Now I have a bunch of warnings about unresolved references to system objects. I've seen other posts saying that you need to add a reference to master in order to use system objects such as sys.objects and sys.schemas. I have added a reference to master, but the problem is these objects are referencing the system objects in other databases. For example:

    create procedure [dbo].[usp_AddTrackingTrigger] (
    	@schema_name nvarchar(100)
    	, @table_name nvarchar(255)
    ) as begin
    
    	declare
    		@sql nvarchar(max)
    		, @tbl_full nvarchar(255)
    		, @tbl_full_nm nvarchar(255)
    		, @ModifiedDateTimeField nvarchar(255)
    		, @ModifiedUserIdField nvarchar(255)
    		, @pk_col nvarchar(255)
    		, @trigger_name nvarchar(255);
    
    	select
    		@tbl_full = @schema_name + '.' + @table_name
    		, @tbl_full_nm = @schema_name + @table_name
    		, @ModifiedDateTimeField = 'ModifiedDateTime'
    		, @ModifiedUserIdField = 'ModifiedUserId';
    
    	select
    		@trigger_name = @tbl_full_nm + '_ModifiedFields';
    
    	if not exists (select 1 from sys.triggers where [name] = @trigger_name) begin
    
    		if exists (
    			select 1 
    			from [$(CWNS_Migration)].sys.objects tbl 
    				join [$(CWNS_Migration)].sys.columns col on col.[object_id] = tbl.[object_id]
    				join [$(CWNS_Migration)].sys.schemas sch on sch.[schema_id] = tbl.[schema_id]
    			where
    				sch.[name] = @schema_name
    				and tbl.[name] = @table_name
    				and col.[name] = @ModifiedDateTimeField) begin
    
    			select 
    				@pk_col = ccu.COLUMN_NAME
    			from 
    				[$(CWNS_Migration)].sys.key_constraints kc
    				join [$(CWNS_Migration)].information_schema.constraint_column_usage ccu ON kc.[schema_id] = schema_id(ccu.TABLE_SCHEMA) and ccu.CONSTRAINT_NAME = kc.[name]
    				join [$(CWNS_Migration)].sys.objects tbl on tbl.[object_id] = kc.parent_object_id
    			where
    				kc.[type] = 'PK'
    				and ccu.TABLE_SCHEMA = @schema_name
    				and tbl.[name] = @table_name;
    
    			if @pk_col is not null begin
    				set @sql = '
    				use CWNS;
    				go
    
    				create trigger ' + @trigger_name + '
    				on ' + @tbl_full + '
    				after update
    				as
    					update
    						' + @tbl_full + '
    					set 
    						' + @ModifiedDateTimeField + ' = getdate()
    						, ' + @ModifiedUserIdField + ' = suser_sname()
    					from 
    						inserted i
    					where
    						' + @tbl_full + '.' + @pk_col + ' = i.' + @pk_col;
    
    				exec sp_executesql @sql;
    
    				print 'Trigger ' + @trigger_name + ' created.';
    			end
    		end
    	end
    end
    I get an unresolved reference warning on [$(CWNS_Migration)].sys.objects. Any idea how to resolve cross-database references to system objects? Just to reiterate: this SQL works in SSMS, so it's not an issue with the code per se; it's something in SSDT that's treating it as an error/warning.

    Thursday, October 31, 2019 4:13 PM
  • I would say not worrying about those warnings. By the way, in your dynamic sql statement, you need to remove the GO statement after USE CWNS.

    A Fan of SSIS, SSRS and SSAS

    Thursday, October 31, 2019 4:49 PM
  • Unfortunately, even though it's just a warning, it still interferes with the build process that we're using, so I can't ignore it.

    As far as the dynamic SQL, the GO is necessary because "create trigger" has to be the only statement in a batch. Anyway it works.

    Thursday, October 31, 2019 5:22 PM
  • Actually it will throw an error: Incorrect syntax near 'GO'.

    A Fan of SSIS, SSRS and SSAS



    • Edited by Guoxiong Thursday, October 31, 2019 6:34 PM
    Thursday, October 31, 2019 6:27 PM