Revision error 63195 of revision set 1090 when upgrading from 2.1 to 2.91

When upgrading BBDM from 2.1 to 2.91, error at revision 1090.63195:
 
Operand type clash: uniqueidentifier is incompatible with int
 
Revision text:
set nocount on;
 
        declare @RECORDSOURCEID uniqueidentifier;
        declare @CONSTITUENTSEGMENTTABLE nvarchar(128);
        declare @SEGMENTATIONID uniqueidentifier;
        declare @SEGMENTID uniqueidentifier;
        declare @TESTSEGMENTID uniqueidentifier;
        declare @DATATABLE nvarchar(128);
        declare @SQL nvarchar(max);
        declare @RECORDSOURCETYPE tinyint;  /* 1=Record Source, 2=List/Duplicate List, 3=Consolidated List */
        declare @LISTID uniqueidentifier;
        declare @ISVENDORMANAGED bit;
        declare @DONORQUERYVIEWCATALOGID uniqueidentifier;
        declare @SOURCECODEMAPID uniqueidentifier;
        declare @LISTIDSQL nvarchar(128);
        declare @WHERESQL nvarchar(max);
        declare @LISTMATCHBACKTABLE nvarchar(128);
 
        declare RECORDSOURCECURSOR cursor local fast_forward for
          select [ID]
          from dbo.[MKTRECORDSOURCE]
          where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;
 
        open RECORDSOURCECURSOR;
        fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
 
        while (@@FETCH_STATUS = 0)
        begin
          /* Get the CONSTITUENTSEGMENT table name */
          set @CONSTITUENTSEGMENTTABLE = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@RECORDSOURCEID);
          set @LISTMATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME](@RECORDSOURCEID)
 
          /* Loop through the segments to add */
          declare SEGMENTCURSOR cursor local fast_forward for
            with [MAILINGS] ([ID], [DATATABLE]) as
            (
              select
                [MKTSEGMENTATION].[ID],
                dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID])
              from dbo.[MKTSEGMENTATION]
              inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = @RECORDSOURCEID
              and [MKTSEGMENTATION].[SOURCECODEID] is null
              and [MKTSEGMENTATION].[ACTIVE] = 1
              and [MKTSEGMENTATION].[ACTIVATEDATE] > '2009-11-20T00:00:00.000'
            )
            select
              [MAILINGS].[ID],
              [MAILINGS].[DATATABLE],
              [MKTSEGMENTATIONSEGMENT].[ID],
              null
            from [MAILINGS]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MAILINGS].[ID]
            where not exists(select 1 from dbo.[CONSTITUENTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [TESTSEGMENTID] is null)
 
            union all
 
            select
              [MAILINGS].[ID],
              [MAILINGS].[DATATABLE],
              [MKTSEGMENTATIONSEGMENT].[ID],
              [MKTSEGMENTATIONTESTSEGMENT].[ID]
            from [MAILINGS]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MAILINGS].[ID]
            inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
            where not exists(select 1 from dbo.[CONSTITUENTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and [TESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]);
 
          open SEGMENTCURSOR;
          fetch next from SEGMENTCURSOR into @SEGMENTATIONID, @DATATABLE, @SEGMENTID, @TESTSEGMENTID;
          
          while (@@FETCH_STATUS = 0)
          begin
            /* Gather some info so we can build the SQL */
            select distinct
              @LISTID = [MKTSEGMENTLIST].[LISTID],
              @ISVENDORMANAGED = dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]),
              @RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
              @DONORQUERYVIEWCATALOGID = (case when dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) = 2 then [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] else @RECORDSOURCEID end),
              @SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
            left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
            left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
            left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
            inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
            inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
            left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and (([MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null and [MKTSEGMENTATIONTESTSEGMENT].[ID] is null) or ([MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]))
            where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
            and (@TESTSEGMENTID is null or [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID)
            and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
            and (([MKTSEGMENTLIST].[LISTID] is null and [MKTSOURCECODEMAP].[LISTID] is null) or ([MKTSOURCECODEMAP].[LISTID] = (case when dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) = 2 then [MKTSEGMENTLIST].[LISTID] else @RECORDSOURCEID end)));
 
 
            set @SQL = 'insert into dbo.[' + @CONSTITUENTSEGMENTTABLE +'] ([CONSTITUENTID], [SEGMENTID], [TESTSEGMENTID], [SOURCECODEMAPID])' + char(13);
 
 
            if @RECORDSOURCETYPE = 2 or @RECORDSOURCETYPE = 3  --List/Duplicate List or Consolidated List
              /* See if we need to cast the person ID from the list matchback table(s) in the list joins... */
              select @LISTIDSQL = (case when [DATA_TYPE] = 'uniqueidentifier' then '[LISTDONORS].[ID]' else 'cast(isnull([LISTDONORS].[ID],'''') as varchar(36))' end)
              from [INFORMATION_SCHEMA].[COLUMNS]
              where [TABLE_SCHEMA] = 'dbo'
              and [TABLE_NAME] = @DATATABLE
              and [COLUMN_NAME] = 'DONORID';
 
 
            if @RECORDSOURCETYPE = 1 or @RECORDSOURCETYPE = 2  --Record Source or List/Duplicate List
              begin
                if @RECORDSOURCETYPE = 1  --Record Source
                  /* Select directly from the activated data table */
                  set @SQL = @SQL + '  select distinct [DONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
                                    '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13);
                else  --List/Duplicate List
                  /* Join to the matchback table for this list. */
                  set @SQL = @SQL + '  select distinct [LISTDONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
                                    '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
                                    '  inner join dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID] and [LISTDONORS].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13);
 
                set @SQL = @SQL + '  where [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
                                  '  and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + char(13) +
                                  '  and [DONORS].[DONORQUERYVIEWCATALOGID] ' + (case when @ISVENDORMANAGED = 1 or @DONORQUERYVIEWCATALOGID is null then 'is null' else '= @DONORQUERYVIEWCATALOGID' end) + char(13) +
                                  '  and not exists(select 1 from dbo.[' + @CONSTITUENTSEGMENTTABLE +'] where [CONSTITUENTID] = ' + (case when @RECORDSOURCETYPE = 1 then '[DONORS].[DONORID]' else '[LISTDONORS].[DONORID]' end) + ' and [SEGMENTID] = @SEGMENTID and [TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')';
              end
            else
              begin  --Consolidated List
                set @WHERESQL = '  where [DONORS].[SEGMENTID] = @SEGMENTID' + char(13) +
                                '  and [DONORS].[TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end);
 
                /* Select the counts from the list matchback table first, then union to the counts from the record source.         */
                /* Performing these as separate 'select' statements and unioning them together increases performance dramatically. */
                set @SQL = 'with [LISTDONORS]([ID], [DONORID], [DONORQUERYVIEWCATALOGID], [SOURCECODEMAPID]) as' + char(13) +
                           '(' + char(13) +
                           '  select distinct' + char(13) +
                           '    [LISTMATCHBACK].[ID],' + char(13) +
                           '    [LISTMATCHBACK].[DONORID],' + char(13) + 
                           '    [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] as [DONORQUERYVIEWCATALOGID],' + char(13) +
                           '    (select [MKTSOURCECODEMAP].[ID]' + char(13) +
                           '     from dbo.[MKTSOURCECODEMAP]' + char(13) +
                           '     where [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = @SEGMENTID' + char(13) +
                           '     and ((@TESTSEGMENTID is null and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null) or ([MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID))' + char(13) +
                           '     and [MKTSOURCECODEMAP].[LISTID] = [MKTSEGMENTLIST].[LISTID]) as [SOURCECODEMAPID]' + char(13) +
                           '  from dbo.[' + @LISTMATCHBACKTABLE + '] as [LISTMATCHBACK]' + char(13) +
                           '  inner join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [LISTMATCHBACK].[ID]' + char(13) +
                           '  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTLISTDATA].[SEGMENTLISTID]' + char(13) +
                           '  where [LISTMATCHBACK].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
                           ')' + char(13) +
                           @SQL +
                           '  select distinct [LISTDONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, [LISTDONORS].[SOURCECODEMAPID]' + char(13) +
                           '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
                           '  inner join [LISTDONORS] on ' + @LISTIDSQL + ' = [DONORS].[DONORID]' + char(13) +
                           @WHERESQL + char(13) +
                           '  and [DONORS].[DONORQUERYVIEWCATALOGID] in (select distinct [DONORQUERYVIEWCATALOGID] from [LISTDONORS])' + char(13) +
                           '  and not exists(select 1 from dbo.[' + @CONSTITUENTSEGMENTTABLE +'] where [CONSTITUENTID] = [LISTDONORS].[DONORID] and [SEGMENTID] = @SEGMENTID and [TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')' + char(13) +
                           char(13) +
                           '  union all' + char(13) +
                           char(13) +
                           '  select distinct [DONORS].[DONORID], @SEGMENTID, @TESTSEGMENTID, @SOURCECODEMAPID' + char(13) +
                           '  from dbo.[' + @DATATABLE + '] as [DONORS]' + char(13) +
                           @WHERESQL + char(13) +
                           '  and [DONORS].[DONORQUERYVIEWCATALOGID] = @DONORQUERYVIEWCATALOGID' + char(13) +
                           '  and (not exists(select top 1 1 from [LISTDONORS]) or [DONORS].[DONORID] not in (select ' + @LISTIDSQL + ' from [LISTDONORS]))' + char(13) +
                           '  and not exists(select 1 from dbo.[' + @CONSTITUENTSEGMENTTABLE +'] where [CONSTITUENTID] = [DONORS].[DONORID] and [SEGMENTID] = @SEGMENTID and [TESTSEGMENTID] ' + (case when @TESTSEGMENTID is null then 'is null' else '= @TESTSEGMENTID' end) + ')';
              end
 
            /* Insert the constituent, segment, test segment ID, and list source code part IDs into the CONSTITUENTSEGMENT table */
            exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @SEGMENTID uniqueidentifier, @TESTSEGMENTID uniqueidentifier, @DONORQUERYVIEWCATALOGID uniqueidentifier, @SOURCECODEMAPID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @SEGMENTID = @SEGMENTID, @TESTSEGMENTID = @TESTSEGMENTID, @DONORQUERYVIEWCATALOGID = @DONORQUERYVIEWCATALOGID, @SOURCECODEMAPID = @SOURCECODEMAPID;
 
            fetch next from SEGMENTCURSOR into @SEGMENTATIONID, @DATATABLE, @SEGMENTID, @TESTSEGMENTID;
          end
 
          close SEGMENTCURSOR;
          deallocate SEGMENTCURSOR;
 
          fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
        end
 
        close RECORDSOURCECURSOR;
        deallocate RECORDSOURCECURSOR;
Download and install the latest patch, which contains all fixes from previous patches. If you are running an older version, download and install the latest version and then the patch. 

Environment

 2.9.1001, patch 56 ; 2.92.537, patch 3 ; 2.91.1535, patch 16

Was this article helpful?