BUG: EM Err: Unable to Construct Column Clause for Article View (190208)

WORKAROUND

--- Begin Script ---
if exists (select * from sysobjects where id =
object_id('dbo.sp_articleview') and sysstat & 0xf = 4)
   drop procedure dbo.sp_articleview
GO
create procedure sp_articleview
    @publication varchar(30),        /* Publication name */ 
    @article varchar(30),            /* Article name */ 
    @view_name varchar (92) = NULL,  /* View name */ 
    @filter_clause text = ''         /* Article's filter clause */ 
as
    declare @pubid smallint
    declare @table_name varchar (30)
    declare @user_id int
    declare @user_name varchar (30)
    declare @qualified_table_name varchar (61)
    declare @columns varbinary (32)
    declare @name varchar (30)
    declare @col_clause1 varchar (255)
    declare @col_clause2 varchar (255)
    declare @col_clause3 varchar (255)
    declare @col_clause4 varchar (255)
    declare @col_clause5 varchar (255)
    declare @col_clause6 varchar (255)
    declare @col_clause7 varchar (255)
    declare @col_clause8 varchar (255)
    declare @retcode int
    declare @view_id int
    declare @type tinyint
    declare @table_id int
    declare @previous_view varchar (30)
    declare @colid int
    declare @site varchar(30)
    declare @db varchar(30)
    declare @owner varchar(30)
    declare @object varchar(30)
    declare @artid int
    declare @inactive tinyint

    select @inactive =0

    /*
    ** SecurityCheck.
    ** Only the System Administrator (SA) or the Database Owner (dbo) can
    *
    * add an article view.
    */ 

    if suser_id() <> 1 and user_id() <>1
       begin
              RAISERROR (15000, 14, -1)
          return (1)
           end

    /*
    ** Parameter Check: @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */ 

    if @publication is null
           begin
              RAISERROR (14043, 16, -1, 'The publication')
              return (1)
           END

    execute @retcode = sp_validname @publication
    if @retcode <> 0
            RETURN (1)

    select @pubid = pubid from syspublications where name =@publication
        if @pubid is null
           begin
            RAISERROR (15001, 11, -1, @publication)
           return (1)
           end

    /*
    *
    * Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */ 

    if @article is null
       begin
              RAISERROR (14043, 16, -1, 'The article')
              return (1)
           end

        execute @retcode = sp_validname @article
        if @retcode <> 0
       return (1)

    /*
    ** Get the article information.
    */ 

    select @artid = art.artid, @table_name =so.name,
       @user_id = uid, @user_name = USER_NAME(so.uid),
       @columns = art.columns, @type = art.type,
       @view_id = art.sync_objid, @table_id = art.objid
       from sysarticles art, sysobjects so
       where art.pubid = @pubid
       and art.name = @article
       and art.objid = so.id

    /*
    ** Fail if there is no article information.
    */ 

    if @artid is null
       begin
              RAISERROR (15001, 11, -1, @article)
             return (1)
       end

        /*
        ** Only unsubscribed articles may be modified.
        */ 
        if exists (select * from syssubscriptions where artid = @artid
          and status <> @inactive)
          begin
         RAISERROR (14092, 11, -1)
             RETURN (1)
          end

    /*
    ** Create a table of all the articles columns.
    */ 

    create table #tmp (colid int, name varchar(30), published bit)
    if @@error <> 0
       return (1)

    create unique index ind1 on #tmp(colid)
    if @@error <> 0
       begin
       drop table #tmp
       return (1)
       end

    insert into #tmp select colid, name,
       convert(bit, substring(@columns, convert(tinyint,
        32 - floor((colid-1)/8)), 1) & POWER(2, ((colid-1)%8)))
       from syscolumns
       where id = (select id from sysobjects where name = @table_name and
          uid = @user_id and type ='U')

    /* Break out the specified view name and get the non-ownerqual'd name,
      ** then validate that.

   */ 
    execute sp_namecrack @view_name, @site OUTPUT, @db OUTPUT,
     @owner OUTPUT, @object OUTPUT
    execute @retcode = sp_validname @object
    if @retcode <> 0
       return (1)

    /* If no non-published columns, we'll select all and avoid the 510-byte
    ** limit on column strings.
    */ 
    if not exists (select * from #tmp where published = 0) begin
       select @col_clause1 = null
       select @col_clause2 = null
       select @col_clause3 = null
       select @col_clause4 = null
       goto CreateView
    end

    /*
    ** Construct the column list based on all published columns inthe
    ** article.
    */ 

    execute ('declare hC scroll cursor for select colid, name from #tmp
       where published = 1')
    open hC
    fetch hC into @colid, @name
    while (@@fetch_status <> -1)
        begin

if @col_clause1 is null or
       ((datalength(@name) + datalength(@col_clause1) + 2) < 255)
       if @col_clause1 is null
        select @col_clause1 = @name
       else
        select @col_clause1 = @col_clause1 + ', ' +@name

        else if @col_clause2 is null
       or ((datalength(@name) + datalength(@col_clause2) + 2) < 255)
       begin

        if @col_clause2 is null
         select @col_clause2 = ','+ @name
        else
         select @col_clause2 = @col_clause2 + ', '+
              @name
               end

       else if @col_clause3 is null
       or

       ((datalength(@name) + datalength(@col_clause3) + 2) <255)
       begin
        if @col_clause3 is null
         select @col_clause3 = ','+ @name
        else
         select @col_clause3 = @col_clause3 + ', '+
              @name
       end
       else if @col_clause4 is null
       or
       ((datalength(@name) + datalength(@col_clause4) + 2) < 255)
       begin
        if @col_clause4 is null
         select @col_clause4 = ','+ @name
        else
         select @col_clause4 = @col_clause4 + ', '+
              @name
       end
       else
        /*
       ** The procedure only support ~510 bytes for the column list
       */ 
       begin
        RAISERROR (14039, 16, -1)
        close hC
        deallocate hC
        drop table #tmp
        return (1)
        end
        fetch hC into @colid, @name
        end
    close hC
    deallocate hC

CreateView:

    /*
    ** If the article has a generated view (not manually created),then
    ** drop the current view before creating the new one.
    */ 

    if ((@type & 0x5) <> 0x5) and @view_id <>0
       and @view_id <> @table_id
       begin
          select @previous_view = object_name (@view_id)
          if @previous_view is not null and
                 exists (select * from sysobjects where name =
                 @previous_view
                and type ='V')
             exec ('drop view ' + @previous_view)
       end

    /*
    ** If a view is going to be created. Make sure a valid @view_name
    ** was provided.
    */ 

    if @col_clause1 is not null or @col_clause2 is not null
       begin
          if @view_name is null
             begin
                RAISERROR (14043, 16, -1, 'The view_name')
                return (1)
             end
        end

    /*
    ** make an owner qualified table name for these operations name
    */ 

    select @qualified_table_name = @user_name + '.' +@table_name

    /*
    ** Construct and execute the view creation command.
    */ 

    if @col_clause4 is not null
       begin
       if datalength(@filter_clause) > 1
        exec ('create view ' + @object + ' as select ' + @col_clause1 +
          @col_clause2 + @col_clause3 + @col_clause4 + ' from  ' +
         @qualified_table_name + ' where ' + @filter_clause)
       else
        exec ('create view ' + @object + ' as select '+ @col_clause1
         + @col_clause2 + @col_clause3 + @col_clause4 + ' from   ' +
         @qualified_table_name)

       if @@error <>0
          return (1)
       end

       else if @col_clause3 is not null
       begin
       if datalength(@filter_clause) > 1
        exec ('create view ' + @object + ' as select ' +
         @col_clause1 + @col_clause2 + @col_clause3 + ' from ' +
         @qualified_table_name + ' where ' + @filter_clause)

       else
        exec ('create view ' + @object + ' as select '+
         @col_clause1 + @col_clause2 + @col_clause3 + ' from ' +
         @qualified_table_name)

       if @@error <>0
          return (1)
       end
    else if @col_clause2 is not null
       begin
       if datalength(@filter_clause) > 1
        exec ('create view ' + @object + ' as select ' +
         @col_clause1 + @col_clause2 + ' from ' +
         @qualified_table_name + ' where ' + @filter_clause)

       else
        exec ('create view ' + @object + ' as select '+
         @col_clause1 + @col_clause2 + ' from ' +
         @qualified_table_name)

       if @@error <>0
          return (1)
       end
    else if @col_clause1 is not null
      begin

       if datalength(@filter_clause) >1
        exec ('create view ' + @object + ' as select ' +
         @col_clause1 + ' from ' +  @qualified_table_name +
         ' where ' + @filter_clause)
       else
        exec ('create view ' + @object + ' as select '+
         @col_clause1 + ' from ' +  @qualified_table_name)
       if @@error <> 0
          return (1)
       end
    else
       begin
        if datalength(@filter_clause) >1
         exec ('create view ' + @object + ' as select * from ' +
              @qualified_table_name + ' where ' + @filter_clause)
        if @@error <> 0
           return (1)

       end
    /*
    ** Update the article's sync_objid with the new view or the base
    ** table id.
    */ 

    if @col_clause1 is null and datalength(@filter_clause) =1
       select @view_id = object_id(@qualified_table_name)
    else
 begin
          select @view_id = id from sysobjects where name = @object and
             type = 'V'
          if @view_id is null or @view_id = 0
             begin
                RAISERROR (15001, 11, -1, @object)
                return (1)
             end
      end

    /* Update article definition*/ 
    update sysarticles set sync_objid = @view_id where
       pubid = @pubid and
       name = @article

    /*
    ** Set new sync_objid and @filter_clausevalue
    */ 

    if datalength(@filter_clause) >1
       update sysarticles set sync_objid = @view_id,
          filter_clause = @filter_clause
          where pubid = @pubid
          and name = @article
    else

       update sysarticles set sync_objid =@view_id,
          filter_clause = NULL
          where pubid = @pubid
          and name =@article

       drop table #tmp

      /*
       ** Force the article cache to be refreshed with the new
       ** definition.
      */ 
       EXECUTE sp_replflush
GO
--- End Script ---