SQL SERVER 系統表無效

[Dear SQL(燊)]

大家好,我的SQL SERVER(2005)數據庫執行所有ALTER(ALTER TABLE,ALTER PROC,ALTER FUNCTION) 語句都報以下錯誤
Msg 208, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 82
Invalid object name 'sysarticles'.

謝謝!

[發糞塗牆]

这是源代码，按照我的猜测，你的库兼容级别应该还是2000，所以没有这个系统表

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create procedure sys.sp_MStran_ddlrepl 
(
	@EventData xml
	,@procmapid int
)
AS
begin
    set nocount on
	declare @retcode int

    -- validate the procmapid
    if @procmapid not in (1,2,3,4)
    begin
        raiserror(15021, 16, -1, '@procmapid')
        goto FAILURE
    end
    --if transactional replication is not enabled for this db, don't do anything
    if (sys.fn_MSrepl_istranpublished (db_name(),0) != 1)
        return 0
	
    declare @object_name sysname
                ,@object_owner sysname
                ,@qual_object_name nvarchar(512) --qualified 2-part-name			
                ,@objid	int
                ,@objecttype varchar(32)
                ,@encrypted nvarchar(32)
                ,@pass_through_scripts nvarchar(max)
                ,@eventDoc int
                ,@dbname sysname
                ,@targetobject nvarchar(51)
                ,@debug_print bit
		
	if object_id('MSrepl_debug_DDL') is not null
		set @debug_print = 1
	else
		set @debug_print = 0

    set	@targetobject = N''

    select @object_name  = event_instance.value('ObjectName[1]', 'sysname')
        ,@object_owner = event_instance.value('SchemaName[1]', 'sysname')
        ,@objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')
        ,@encrypted = event_instance.value('(TSQLCommand/SetOptions/@ENCRYPTED)[1]', 'nvarchar(32)')
        ,@pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')
        ,@targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')
    FROM       @EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

	if @debug_print = 1
		select 'stage' = 'xmlnoderefs : '
					, '@object_name' = @object_name
					, '@object_owner' = @object_owner 
					, '@objecttype' = @objecttype 
					, '@encrypted' = @encrypted 
					, '@pass_through_scripts' = @pass_through_scripts
					, '@targetobject' = @targetobject
	
    -- If the object being manipulated is a database level trigger that is owned by replication, raise error
    if exists (select * from sys.triggers where name = @object_name and parent_class = 0 and @objecttype = 'TRIGGER' and @object_name in (N'tr_MStran_altertable', N'tr_MStran_alterview', N'tr_MStran_alterschemaonly', N'tr_MStran_altertrigger'))
    begin
        raiserror(21598, 16, 1)        
        goto FAILURE
    end
    
    -- If the object being manipulated is a database level trigger that is not owned by replication, return immediately
    if exists (select * from sys.triggers where name = @object_name and parent_class = 0 and @objecttype = 'TRIGGER' and @object_name not in (N'tr_MStran_altertable', N'tr_MStran_alterview', N'tr_MStran_alterschemaonly', N'tr_MStran_altertrigger'))
        return 0

    select @qual_object_name = quotename(@object_owner) + N'.' + quotename(@object_name)
    select @objid = object_id(@qual_object_name)
    select @dbname = db_name()

	if @debug_print = 1
		select 'stage' = 'quotename : '
					, '@qual_object_name' = @qual_object_name
					, '@objid' = @objid
					, '@dbname' = @dbname

    --can not alter to 'with encrypted' if object is published
    --return immediately if object is not published
    if UPPER(@encrypted) = N'TRUE'
    begin	
        if (UPPER(@objecttype) != 'TRIGGER' and 
            exists (SELECT * FROM dbo.sysextendedarticlesview WHERE objid = @objid) 
        or UPPER(@objecttype) = 'TRIGGER' and 
            exists (select * from sysarticles a join sys.objects o on a.objid = o.parent_object_id
                                    where o.object_id = @objid and cast (a.schema_option as int) & 256 = 256) )
        begin 
            raiserror(21815, 16, 1, @qual_object_name)		
            goto FAILURE
        end
        else
            return 0
    end
	
    --stored procedures published as "proc execution" article cannot contain
    --table value parameters (Note: proc exec articles are only listed in 
    --dbo.sysarticles
    if exists (select * 
                 from sys.parameters sp 
           inner join dbo.sysarticles a
                   on sp.object_id = a.objid
                where sp.system_type_id = 243
                  and sp.object_id = @objid
                  and a.type in (0x08, 0x18))
    begin
        raiserror(25023,16,-1)
        goto FAILURE
    end
    select @pass_through_scripts = sys.fn_replgetparsedddlcmd(@pass_through_scripts
														,N'ALTER'
														,@objecttype
														,@dbname
														,@object_owner
														,@object_name
														,@targetobject)

	if @debug_print = 1
		select 'stage' = 'sys.fn_replgetparsedddlcmd output : '
				, '@pass_through_scripts' = @pass_through_scripts

	-- sys.fn_replgetparsedddlcmd will return empty string if DDL contains 
	-- syntax that we don't currently handle (after Katmai DDL
	-- improvement)
	--It will also handle a duplicate trigger in the case of "alter table switch"
	if @pass_through_scripts = N''
		return 0

    -- deal with alternate dest table/owner for alter table inside sp_MStran_altertable
    if UPPER(@objecttype) != N'TABLE' and UPPER(@objecttype) != N'TRIGGER'
    begin
        select @pass_through_scripts = N'ALTER ' + @objecttype + N' '
    								+ @qual_object_name + N' '
    								+ @pass_through_scripts
    end
																																																						
    --if object is not published, don't do anything, unless SWITCH is the following keyword
    if (UPPER(@objecttype) != 'TRIGGER' and exists (SELECT * FROM dbo.sysextendedarticlesview WHERE objid = @objid) 
    	or UPPER(@objecttype) = 'TRIGGER' and exists (select * from sysarticles a join sys.objects o on a.objid = o.parent_object_id
    					where o.object_id = @objid ) 
		or UPPER(@objecttype) = N'TABLE' and LEFT(@pass_through_scripts, LEN(N'SWITCH')) = N'SWITCH')
    begin
        declare @proc_name sysname

	    --  Security Check
	    EXEC @retcode = sys.sp_MSreplcheck_publish
	    IF @@ERROR <> 0 or @retcode <> 0
			goto FAILURE

        select @proc_name = case 
                    when (@procmapid = 1) then 'sys.sp_MStran_altertable' 
                    when (@procmapid = 2) then 'sys.sp_MStran_alterview' 
                    when (@procmapid = 3) then 'sys.sp_MStran_alterschemaonly' 
                    when (@procmapid = 4) then 'sys.sp_MStran_altertrigger' 
                    end

        exec @retcode = @proc_name @qual_object_name, @objid, @pass_through_scripts, @targetobject 
		if @retcode <>0 or @@ERROR<>0
			goto FAILURE
    end
    return 0
FAILURE:
    rollback tran 
    return 1
end
GO

[唐诗三百首]

dbcc checkdb检查一下数据库是否有错误..

use master
go

dbcc checkdb('[数据库名]')

[Dear SQL(燊)]

我的數據庫兼容级别是2005的,是上次建了發布,但發布有問題 後來就出現了這樣的錯誤

[唐诗三百首]

引用 3 楼 chenshanliang 的回复:

我的數據庫兼容级别是2005的,是上次建了發布,但發布有問題 後來就出現了這樣的錯誤

删除该数据库的发布,

exec sp_removedbreplication '[数据库名]'

[Dear SQL(燊)]

引用 4 楼 ap0405140 的回复:

[quote=引用 3 楼 chenshanliang 的回复:] 我的數據庫兼容级别是2005的,是上次建了發布,但發布有問題 後來就出現了這樣的錯誤

删除该数据库的发布,

exec sp_removedbreplication '[数据库名]'

[/quote] 执行了，还是报一样的错

[Dear SQL(燊)]

引用 11 楼 ap0405140 的回复:

恭喜lz,不过我还是不明白为何alter语句会隐含执行sp_MStran_ddlrepl?

是啊,在幫助文檔中沒有這個

[Dear SQL(燊)]

現在發布都刪除了,還是報錯

[Dear SQL(燊)]

引用 2 楼 ap0405140 的回复:

dbcc checkdb检查一下数据库是否有错误..

use master
go

dbcc checkdb('[数据库名]')

CHECKDB沒有錯誤啊

[唐诗三百首]

停用复制功能后再试试,SQL Server Management Studio-->实例-->复制-->右键-->停用复制..

[Dear SQL(燊)]

引用 8 楼 ap0405140 的回复:

停用复制功能后再试试,SQL Server Management Studio-->实例-->复制-->右键-->停用复制..

這個也停用了

[Dear SQL(燊)]

不過已經解決了 在幫助裡找到對應表的字段重建表就可以了 但這個在是在用戶表列裡

[唐诗三百首]

恭喜lz,不过我还是不明白为何alter语句会隐含执行sp_MStran_ddlrepl?