BUG: EM Err: Unable to Construct Column Clause for Article ViewID: Q190208
|
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.
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.
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 --- 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 Last Reviewed: April 20, 1999