• 在sql_sever 中不能truncate某表


    truncate table  t_test
     消息 4711,级别 16,状态 1,第 1 行
    无法截断表 't_test',因为该表已为复制而发布。 
    错误表转自http://technet.microsoft.com/zh-cn/library/cc645613(v=sql.105).aspx
     

    4711

    16

    无法截断表 '%.*ls',因为该表已为复制发布或者已启用了变更数据捕获。

     
    查了老半天,有下面一个解决方法,不过好像是08才可以,现在在用05好像有点没戏...
     
     
    转自:
     
     
     

    SQL SERVER – CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

    Few days ago, I got the great opportunity to visit Bangalore Infosys. Please read the complete details for the event here: SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. I mentioned during the session that CDC is asynchronous and it reads the log file to populate its data. I had received a very interesting question during the session.

    The question is as follows: does CDC feature capture the data during the truncate operation? Answer: It is not possible or not applicable. Truncate is operation that is not logged in the log file, and if one tries to truncate the table that is enabled for CDC, it will right away throw the following error.

    Msg 4711, Level 16, State 1, Line 1
    Cannot truncate table because it is published for replication or enabled for Change Data Capture.

    Let us create the scenario that will generate the above error.

    /***** Set up TestTable *****/
    USE AdventureWorks
    GO
    -- Create Table
    CREATE TABLE dbo.TestTable (ID INT)
    GO
    -- Insert One Hundred Records
    -- INSERT 1
    INSERT INTO dbo.TestTable (ID)
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.nameRowID
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
    GO
    /***** Enable CDC *****/
    --The following script will enable CDC in AdventureWorks database.
    USE AdventureWorks
    GO
    EXEC sys.sp_cdc_enable_db
    GO
    --Following script will enable CDC on dbo.TestTable table.
    USE AdventureWorks
    GO
    EXEC sys.sp_cdc_enable_table
    @source_schema N'dbo',
    @source_name N'TestTable',
    @role_name NULL
    GO
    /* Attempt to Truncate Table will thrown following error
    Msg 4711, Level 16, State 1, Line 1
    Cannot truncate table 'TestTable' because it is published for replication or enabled for Change Data Capture.

    */
    TRUNCATE TABLE TestTable
    GO
    /***** Clean up *****/
    --Disabling Change Data Capture on a table
    USE AdventureWorks;
    GO
    EXECUTE sys.sp_cdc_disable_table
    @source_schema N'dbo',
    @source_name N'TestTable',
    @capture_instance N'dbo_TestTable';
    GO
    --Disable Change Data Capture Feature on Database
    USE AdventureWorks
    GO
    EXEC sys.sp_cdc_disable_db
    GO
    -- Drop Table
    DROP TABLE TestTable
    GO

    The workaround for this is to either use DELETE statement instead of TRUNCATE or to disable CDC first and then enable the CDC on the table after the TRUNCATE operation is completed.

    I am interested to know if you have any interesting fact of above CDC. I will publish the information on this blog with due credit.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

     

    另外还有一篇.

    http://www.sqlservercentral.com/blogs/jeffrey_yao/2008/04/22/difference-between-truncate-and-delete/

    This is an old topic and well documented, just google it and I believe you will get a lot,

    However, there is one difference that seems not being mentioned and actually I find it out myself only two hours ago. So here is the point

    If a table is published for replication, it cannot be truncated, while it can still be deleted.

    Otherwise, you will get the following msg

    Msg 4711, Level 16, State 1, Line 1

    Cannot truncate table 'xxx' because it is published for replication.

    My environment is SQL Server 2K5 SP2 (developer edition)

    Next time, when you are asked this question in an interview, just give this answer to impress your interviewer. [:)]

     

  • 相关阅读:
    html页面自动运行php文件
    在数据库原有字段后增加新内容
    mysql判断字段不等于空
    php中的日期加减
    js验证只能输入中文
    session_start()关于Cannot send session cache limiter headers already sent错误解决方法
    php记录前一页面url
    打印功能
    js验证email
    用php获取本周,上周,本月,上月,本季度日期
  • 原文地址:https://www.cnblogs.com/vimmer/p/2501717.html
Copyright © 2020-2023  润新知