Microsoft KB Archive/190208

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

Article ID: 190208

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q190208



BUG #: 15432 (SQLBUG_65)



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: kbbug kbpending KB190208

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.