Replication error

You may get the following error on distribution agent, Cannot insert duplicate key row in object 'xxxx' with unique index 'yyyy'.(Source: ServerName(Data source); Error number: 2601). Why this has happend and how to resolve this ?

The casue of the above error could be new row has been added at the publisher, but a row with the same key already exists at 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.

To resolve this issue do the following,
Identify the row at the subscriber with the same unique key as the one in your distribution agent log,delete identified rows at the subscriber and restart the publication agent for that subscriber

The above steps should fix the problem.

Enable replication agents for logging to output

To find out how to enable replication agents for logging to output files in SQL Server Enable replication agents for logging output.
The following options can be used in replication agents to enable logging to an output file:
-Output C:\ReplOutput.txt -OutputVerboseLevel [0|1|2]

VerboseLevel 0 - prints only the error messages
VerboseLevel 1 - Prints all the progress report messages
VerboseLevel 2 - is the defualt on and it prints both messages for level 0 and 1.

DTSRun command line decryption utility

Have you ever had problem identifying what the package name of DTSRun /~Z0xF2E216E36948A6C83A….. . I had recently had problem identifying what package a schedule task is running. When you use Wizard to schedule a task, it actually encrypts. I came across this useful tool that will decrypt and tell you the package name, server and etc.

You can download the tool (DTSRUNDEC) to decrypt encrypted values. http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26

Steps that you need to follow:
1. Download the DTSRUNDEC tool
2. Get the exe and put on c:3. Copy the encrypted values from Z onwards
4. C:\DTSRUNDEC

This will give you detailed description of the package.

sp_helpDB give you error

Some times when you try to use sp_helpDB you may get the following problem. It can result in your database even not backuped up.

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

This error might be because your database have an invalid owner. You can identify the databases with this issue using the followng script.
use master
go
SELECT name, SUSER_SNAME(sid) FROM sysdatabases WHERE SUSER_SNAME(sid) IS NULL.

The above statement will produce databases with invalid database owner. You can fix this by running the following system stored procedure. To fix this problem run sp_changedbowner 'sa'. You can replace sa with a vaild user.

distribution database corrupted

If you have got a corrupt distribution database and unable to remove or configure your publication then follow the following steps.

1. SELECT * from msdb..msdistributiondbs
2. If a row is returned delete it.
3. Then, reconfigure replication.

What if the above steps didn't work, the one option I think of is to remove the replication from the server. If you have any other way , please let me know.

use master
go
sp_configure 'allow update', 1
go
reconfigure with override
go
DELETE master..sysservers WHERE srvname = 'repl_distributor'

go
sp_configure 'allow update', 0
go
reconfigure with override
go