Data loss in Transactional Replication

We have setup transactional replication across several databases using SQL Server 2000 spread across multiple sites in a fully connected network. There is one main table from which data is replicated from the publisher to the destination. Horizontal filtering is being used on this table to enable sending/routing of the records to the correct DB(site). It has been observed that the documents/records are getting lost between some sites. Say 10 documents are being sent fron the publishing database but only 5 are being received at the destination database although the sent history for all the 10 documents is available at the publishing database.

Can anyone guide on how to analyse and resolve this problem? Can unreliable network be the issue, If the network is not reliable and the connection is lost during replication how does replication ensure that no data is lost?

Hi,

If you didn't see replication reports error, unreliable network should not be the cause of data loss. Replication delivers the transaction with order. If it can't deliver it for whatever reason, it will keep retry or report error unless you explicitly specify skiperror.

To troubleshoot, you first need to see if your horizontal filter actually filters the data at publisher. You can also use sp_browsereplcmds to see if the transactions is in distribution DB. From here, you can determine if it is a logreader issue or distribution agnet issue.

Please let me know if you need more assistance.

Peng

Data Flow Task SQL strings

Hi,

I just wanna ask:

I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement

SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95)

But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this?

I tried using proper(), trim() and ucase() like in MSAccess but no success.

Please help. Thanks in advance.

The OLE DB Source has to use the same syntax as used by the underlying DB engine - in this case SQL Server.

Have a look in BOL for RTRIM(), LTRIM(), SUBSTRING(), UPPER() LEFT(), REPLACE() to get you going.

Alternatively you could carry out this work in the SSIS data-flow using a Derived Column component.

-Jamie

|||Thanks for your reply. I was able to do it using Derived Column. Thanks again and more power...