SQL Server 2005 Replication Debugging Suggestions

replicationsql serversql-server-2005

I have a replication setup it was up and running yesterday, now I can't connect to the distributor and I don't know why.

The publisher is on a Domain and talks to the subscriber (not on the domain) via VPN.
I can ping the publisher from the subscriber and vice-versa.
I can log in to MSSQL on the publisher from the subscriber.
The publisher is in an error state because no activity is being logged from the subscriber.
The subscriber can't connect to the distributor.
Tracer tokens report a time for the distributor but only pending for the subscriber.
Assuming db changes by a third person might be causing troubles, I deleted the subscription and the publication and recreated them.
I can confirm that yesterday using the same tables/user accounts/servers etc the replication was working and replicating data.

If you have any suggestions to try to narrow down where the problem is, they would be most appreciated.

Answers to questions:

I am using one way transactional replication
The distributor is on the same machine as the publisher.
Both the publisher and the subscriber are 2003 SP2

Seems to be working again

I still don't know why the original working setup stopped working. But when I re-subscribed I had the wrong credentials for one of the connections. By correcting that problem the replication is working once again.

Best Answer

Can you execute T-SQL both directions? You say that the subscriber can't connect to the distributer, but can the distributer see both? Is the distributer on your network or the remote network? I'm just wondering if things are batching up on the distributer, and the disconnect is between there and your subscriber, or if the disconnect is publisher -> distributer, especially if all three are on different servers.

Just somewhere to start.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic