Article ID: 190208
Article Last Modified on 3/2/2005
--- 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 ---
Additional query words: sproc view col list size limitation limit
Keywords: kbbug kbpending KB190208