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

• Microsoft SQL Server version 6.5

SYMPTOMS

If you create an article using vertical partitioning and the accumulated byte value of the columns names exceeds 510 bytes, the following error will appear when you click Add in the Edit Publication dialog box:

Error 14039:[SQL Server] Unable to construct column clause for article
view. Reduce the number of columns or create the view manually.

CAUSE

The creation of published columns is done in the stored procedure SP_ARTICLEVIEW when using vertical partitioning. This stored procedure creates the view that bulk copy uses during the synchronization process. In sp_articleview, a temporary table is created and a cursor is used to collect each column name. The column name is fetched and added to a VARCHAR (255) variable called col_clause1. When col_clause1 is full and there are more column names to fetch, a second VARCHAR (255) variable called col_clause2, is used. Now, if your column names are greater than 510 bytes, the error above will be reported.

WORKAROUND

To work around this problem, modify sp_articleview to include additional col_clause variables. The following example adds two additional col_clause variables (col_clause3, col_clause4). This expands the total size of column names to 1,020 bytes.



Run the following script from an ISQL window, to drop and re-create the stored procedure "sp_articleview":

--- 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 --- 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: sproc view col list size limitation limit

Keywords : SSrvRep kbbug6.50
Version : WINNT:6.5
Platform : winnt
Issue type : kbbug