Alter a column with a default value (SQL SERVER)
|
|
you wanted to change a BIT column to a NVARCHAR column, and the BIT column has a default value set to 0 or 1. you can't run the following statement or you get a dependent object error. alter table TABLE1 alter column COL1 NVarChar(MAX) so i found this solution here, after you've run the query below,
you can alter the column as above.
DECLARE @df SYSNAME SET @df =
(SELECT OBJECT_NAME(cdefault)
FROM SYSCOLUMNS
WHERE id = OBJECT_ID('dbo.TABLE1')
AND name = 'COL1')
IF @df IS NOT NULL
BEGIN
EXEC sp_rename @df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.TABLE1 DROP CONSTRAINT df_to_drop
END
|
|
Skipping Errors in Transactional Replication
|
|
For transactional replication, there are two ways to skip errors if they are encountered during the distribution process: The -SkipErrors parameter of the Distribution Agent, which allows you to skip errors of a certain type. The transaction with the error is not committed, but subsequent transactions are.
The sp_setsubscriptionxactseqno stored procedure, which allows you to skip one or more transactions that cause errors. This option is not available for non-SQL Server Subscribers.
Important:Under typical replication processing, you should not experience any errors that need to be skipped. Errors should be skipped with caution and with the understanding of what the error condition is, why it is occurring, and why the error or specific transaction needs to be skipped rather than resolved. If transactions committed at the Publisher are skipped at the Subscriber, the two nodes will not be completely synchronized, which can lead to additional errors. The -SkipErrors Parameter By default, when the Distribution Agent encounters an error, the agent stops. If you use the -SkipErrors parameter, and specify expected errors or errors that you do not want to interfere with replication, the agent will log the error information and then continue running. For example, if you want to specify that the Distribution Agent should log duplicate key violations but continue to process subsequent transactions, specify that the agent should skip errors 2601 (Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.) and 2627 (Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.): -SkipErrors 2601;2627
The most common way to use the -SkipErrors parameter is to use the Distribution Agent profile titled Continue On Data Consistency Errors. The Distribution Agent will then skip errors 2601, 2627, and 20598 (The row was not found at the Subscriber when applying the replicated command). For more information, see Replication Agent Profiles. In addition to this predefined profile, you can specify the parameter in an agent profile you create or modify, or on the command line. For more information, see:
|
|
Distribution Agent: Cannot insert duplicate key row in object 'TTTT' with unique index 'IIII'
|
|
Symptom: A distribution agent has failed, the full error message text being::
Cannot insert duplicate key row in object 'TTTT' with unique index 'IIII' where 'TTTT' is the name of a table and 'IIII' the name of a unique index of that table. Cause: A new row has been added at the publisher, however a row with the same key has also been added at the subscriber. When the distribution agent runs and tries to insert the new row at the subscriber it fails because a row with the same unique key already exists. Remedy Before attempting to remedy the problem first identify why the problem arose. Distribution agents are only used for Merge and Transaction replication. These are suitable only for propagating changes made at the publisher. Tables which are replicated in this way should not have changes made to them at the subscriber. Be aware that the offending row may have been added at the subscriber any time prior to the error first being noticed. The error will only manifest itself when a row with the same unique key is added to the publisher. This could be days, weeks or even years before. To resolve the problem: Identify the row at the subscriber with the same unique key. This is actually quite easy. Within SQL Server Enterprise Manager bring up the Distribution Agent Error Details dialog. This will show the 'Last command', which will be something like:
{CALL sp_MSins_TTTT( ... values ...)}
where 'TTTT' is the name of the table and '... values ...' is a comma separated list of values. These are the values that it attempted to insert and failed on. Comparing these values with the primary keys or unique constraints applied to the table should allow you to identify the offending row at the subscriber. Consider if the contents of the row should be incorporated into the table at the publisher. This is a business decision. Delete the row at the subscriber (and only that row). Restart the Publication Agent for that subscriber. It should now run successfully
|
|
