Replication Error: 4138

If our subcribers try to synchronize the following error occurs. The Upload of the data works but the download failes.

Error messages:
The merge process was unable to perform data validation on
article 'tbl_xTableFieldValue'. Check for SQL Server
errors in the Windows application event log or retry at a
later time. (Source: MSSQL_REPL, Error number:
MSSQL_REPL2147766343)
Get help: http://help/MSSQL_REPL2147766343

Conflicting locking hints are specified for
table "fncReplicationFilterTableFieldValue". This may be
caused by a conflicting hint specified for a view.
(Source: MSSQLServer, Error number: 4138)
Get help: http://help/4138

What does this mean and how can we resolve this?

Best regards,
Markus

This error means that when the merge agent attempted to validate that the replicated data at the Publisher and Subscriber was the same, it was not the same. From the "Validating Replicated Data" topic in SQL Server Books Online:

"Is the fact that validation failed an issue for your application? If the validation failure is an issue, manually update the data so that it is synchronized, or reinitialize the subscription:

"Data can be updated using the tablediff Utility. For more information about using this utility, see How to: Compare Replicated Tables for Differences (Replication Programming).

"For more information about reinitializaton, see Reinitializing a Subscription."

Phillip Garding
Senior Program Manager
Microsoft SQL Server Replication Management Tools

sql

Replication between SQL Server 2000 and SQL Server 2005

Do you know if it is possible to synchronize a database in SQL Server 2000 SP3a (Publisher) and a database in SQL Server 2005 (Subscriber)?

Has anyone tried it? What are the most common problems you faced? Are there many differences from the Replication process from SQL Server 2000 (publisher) to SQL Server 2000 (subscriber)?

Thanks Very Much

Yes for transactional replication, no for merge replication. Since you're limited to functionality of a SQL 2000 publication, you won't gain much in terms of replication with a SQL 2005 subscriber. But if you use your subscriber for other purposes, like reporting, then you get to take advantage of SQL 2005 features there.|||

Is there anything special you are doing to setup replication between SQL Server 2000 SP3(a) and SQL Server 2005 SP1? Both are enterprise versions and I am using transactional replication.

So far, I have not been able to make replication work. Using the same SQL 2K database and replicationg to another SQL 2K database, replication worked fine.

I just cannt seem to get SQL 2K to publish and SQL 2L5 to subscribe.

Is there something I am overlooking?

Thanks,

Roy

|||Is there an error you're getting?|||

Not sure if this is the issue you hit:

Make sure to use SQL 2005 Management studio to create publication and add subscription for SQL 2000 publisher, you can not connect to SQL 2005 from SQL 2000 Enterprise manager thus will not be able to add SQL 2005 subscription from SQL 2000 enterprise manager.

Or you can TSQL script to setup replication programmatically.

Thanks,

Zhiqiang Feng

|||

Does the SQL Server 2005 server have o be the distributor? I tried creating the distributor using SS 2K5 and was messaged that a subscription could not be created by 2005 on an older (2000) server.

Am I supposed to create the distributor and publisher using 2000 and the subscriber using 2005?

I really appreciate any and all help/advice

Roy

|||

For transactional replication, you can have SQL 2000 sp3+ publisher/distributor with SQL 2005 subscriber. But for merge replication, you must have distributor >= publisher >= subscriber, from your original posting, seems like you're using transactional replication, right?

By the way, what's the version number of your SQL 2000 publisher? (8.00.760 or up?)

Can you describe step by step what you did and exactly what error you're getting?

I just went through the process of setup SQL 2000 publisher & distributor from SQL 2005 management studio and didn't hit any issues.

Thanks,

Zhiqiang Feng

|||

MY SQL Server 2000 version number is 8.0.818

I went to the publisher server (mase), it is SQL2K and created the distributor, then the publisher. However, one thing that makes me wonder is having to check the box Server SQL Server 2000 on the form that is requesting what type of server will be subscribing. Obviously, 2K came before 2005 and did not have a box for 2005. Is the 2000 check box what I need to check?

I will post the rest of the the steps as I go through them.

Thanks a lot for your help.

Roy

|||

If you create SQL 2000 tran publication from SQL 2000 Enterprise manager, yes, it doesn't know SQL 2005 so you have to pick the SQL 2000 option for subscription, but that doesn't prevent you from adding a SQL 2005 subscriber from SQL 2005 mgmt studio which is officially supported. However, if you create SQL 2000 tran publication from SQL 2005 mgmt studio (use mgmt studio connecting to the remote SQL 2000 box), you will not see that prompt. That's why I recommend you setup everything from SQL 2005 management studio for the SQL 2000 publisher/distributor.

Thanks,

Zhiqiang Feng

|||

When I attempt to create a publisher using SS2K5, on SS 2K, I get a message saying the server 'mase' is an older version of SQL Server and cannot be the Distributor for server 'sql2'.

Maybe this is my problem, does the distributor need to be on SS2K5?

Also, to create a publisher on a SS2K server using SS2K5 Management Studio do you right clisck on Local Publication?

I created the subscriber using SS2K5 and all looks good. When I right click the subscriber and click View Synchronization Status I get this message:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).

When I look at History I see this message: Message
Unable to start execution of step 1 (reason: Error authenticating proxy PMS\svcsqladmin, system error: Logon failure: unknown user name or bad password.). The step failed.

I know the password is correct for this login as we tested it just before attempting to create replication.

Is there anyyhnig I am doing incorrectly? I would love to get this set up. On top of everything else, this is the first replication I have ever tried to configure.

Thanks again,

Roy

|||

hi,

right now I facing the same problem.

you have said that the only thing that work is using transactional replication.

the problem is my db don't have primary key.

do you have other option, so we can have the same or at least munites delay database.

please advice

thanks

Replication between SQL Server 2000 and SQL Server 2005

Do you know if it is possible to synchronize a database in SQL Server 2000 SP3a (Publisher) and a database in SQL Server 2005 (Subscriber)?

Has anyone tried it? What are the most common problems you faced? Are there many differences from the Replication process from SQL Server 2000 (publisher) to SQL Server 2000 (subscriber)?

Thanks Very Much

Yes for transactional replication, no for merge replication. Since you're limited to functionality of a SQL 2000 publication, you won't gain much in terms of replication with a SQL 2005 subscriber. But if you use your subscriber for other purposes, like reporting, then you get to take advantage of SQL 2005 features there.|||

Is there anything special you are doing to setup replication between SQL Server 2000 SP3(a) and SQL Server 2005 SP1? Both are enterprise versions and I am using transactional replication.

So far, I have not been able to make replication work. Using the same SQL 2K database and replicationg to another SQL 2K database, replication worked fine.

I just cannt seem to get SQL 2K to publish and SQL 2L5 to subscribe.

Is there something I am overlooking?

Thanks,

Roy

|||Is there an error you're getting?|||

Not sure if this is the issue you hit:

Make sure to use SQL 2005 Management studio to create publication and add subscription for SQL 2000 publisher, you can not connect to SQL 2005 from SQL 2000 Enterprise manager thus will not be able to add SQL 2005 subscription from SQL 2000 enterprise manager.

Or you can TSQL script to setup replication programmatically.

Thanks,

Zhiqiang Feng

|||

Does the SQL Server 2005 server have o be the distributor? I tried creating the distributor using SS 2K5 and was messaged that a subscription could not be created by 2005 on an older (2000) server.

Am I supposed to create the distributor and publisher using 2000 and the subscriber using 2005?

I really appreciate any and all help/advice

Roy

|||

For transactional replication, you can have SQL 2000 sp3+ publisher/distributor with SQL 2005 subscriber. But for merge replication, you must have distributor >= publisher >= subscriber, from your original posting, seems like you're using transactional replication, right?

By the way, what's the version number of your SQL 2000 publisher? (8.00.760 or up?)

Can you describe step by step what you did and exactly what error you're getting?

I just went through the process of setup SQL 2000 publisher & distributor from SQL 2005 management studio and didn't hit any issues.

Thanks,

Zhiqiang Feng

|||

MY SQL Server 2000 version number is 8.0.818

I went to the publisher server (mase), it is SQL2K and created the distributor, then the publisher. However, one thing that makes me wonder is having to check the box Server SQL Server 2000 on the form that is requesting what type of server will be subscribing. Obviously, 2K came before 2005 and did not have a box for 2005. Is the 2000 check box what I need to check?

I will post the rest of the the steps as I go through them.

Thanks a lot for your help.

Roy

|||

If you create SQL 2000 tran publication from SQL 2000 Enterprise manager, yes, it doesn't know SQL 2005 so you have to pick the SQL 2000 option for subscription, but that doesn't prevent you from adding a SQL 2005 subscriber from SQL 2005 mgmt studio which is officially supported. However, if you create SQL 2000 tran publication from SQL 2005 mgmt studio (use mgmt studio connecting to the remote SQL 2000 box), you will not see that prompt. That's why I recommend you setup everything from SQL 2005 management studio for the SQL 2000 publisher/distributor.

Thanks,

Zhiqiang Feng

|||

When I attempt to create a publisher using SS2K5, on SS 2K, I get a message saying the server 'mase' is an older version of SQL Server and cannot be the Distributor for server 'sql2'.

Maybe this is my problem, does the distributor need to be on SS2K5?

Also, to create a publisher on a SS2K server using SS2K5 Management Studio do you right clisck on Local Publication?

I created the subscriber using SS2K5 and all looks good. When I right click the subscriber and click View Synchronization Status I get this message:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).

When I look at History I see this message: Message
Unable to start execution of step 1 (reason: Error authenticating proxy PMS\svcsqladmin, system error: Logon failure: unknown user name or bad password.). The step failed.

I know the password is correct for this login as we tested it just before attempting to create replication.

Is there anyyhnig I am doing incorrectly? I would love to get this set up. On top of everything else, this is the first replication I have ever tried to configure.

Thanks again,

Roy

|||

hi,

right now I facing the same problem.

you have said that the only thing that work is using transactional replication.

the problem is my db don't have primary key.

do you have other option, so we can have the same or at least munites delay database.

please advice

thanks

sql

Replication between SQL Server 2000 and SQL Server 2005

Do you know if it is possible to synchronize a database in SQL Server 2000 SP3a (Publisher) and a database in SQL Server 2005 (Subscriber)?

Has anyone tried it? What are the most common problems you faced? Are there many differences from the Replication process from SQL Server 2000 (publisher) to SQL Server 2000 (subscriber)?

Thanks Very Much

Yes for transactional replication, no for merge replication. Since you're limited to functionality of a SQL 2000 publication, you won't gain much in terms of replication with a SQL 2005 subscriber. But if you use your subscriber for other purposes, like reporting, then you get to take advantage of SQL 2005 features there.|||

Is there anything special you are doing to setup replication between SQL Server 2000 SP3(a) and SQL Server 2005 SP1? Both are enterprise versions and I am using transactional replication.

So far, I have not been able to make replication work. Using the same SQL 2K database and replicationg to another SQL 2K database, replication worked fine.

I just cannt seem to get SQL 2K to publish and SQL 2L5 to subscribe.

Is there something I am overlooking?

Thanks,

Roy

|||Is there an error you're getting?|||

Not sure if this is the issue you hit:

Make sure to use SQL 2005 Management studio to create publication and add subscription for SQL 2000 publisher, you can not connect to SQL 2005 from SQL 2000 Enterprise manager thus will not be able to add SQL 2005 subscription from SQL 2000 enterprise manager.

Or you can TSQL script to setup replication programmatically.

Thanks,

Zhiqiang Feng

|||

Does the SQL Server 2005 server have o be the distributor? I tried creating the distributor using SS 2K5 and was messaged that a subscription could not be created by 2005 on an older (2000) server.

Am I supposed to create the distributor and publisher using 2000 and the subscriber using 2005?

I really appreciate any and all help/advice

Roy

|||

For transactional replication, you can have SQL 2000 sp3+ publisher/distributor with SQL 2005 subscriber. But for merge replication, you must have distributor >= publisher >= subscriber, from your original posting, seems like you're using transactional replication, right?

By the way, what's the version number of your SQL 2000 publisher? (8.00.760 or up?)

Can you describe step by step what you did and exactly what error you're getting?

I just went through the process of setup SQL 2000 publisher & distributor from SQL 2005 management studio and didn't hit any issues.

Thanks,

Zhiqiang Feng

|||

MY SQL Server 2000 version number is 8.0.818

I went to the publisher server (mase), it is SQL2K and created the distributor, then the publisher. However, one thing that makes me wonder is having to check the box Server SQL Server 2000 on the form that is requesting what type of server will be subscribing. Obviously, 2K came before 2005 and did not have a box for 2005. Is the 2000 check box what I need to check?

I will post the rest of the the steps as I go through them.

Thanks a lot for your help.

Roy

|||

If you create SQL 2000 tran publication from SQL 2000 Enterprise manager, yes, it doesn't know SQL 2005 so you have to pick the SQL 2000 option for subscription, but that doesn't prevent you from adding a SQL 2005 subscriber from SQL 2005 mgmt studio which is officially supported. However, if you create SQL 2000 tran publication from SQL 2005 mgmt studio (use mgmt studio connecting to the remote SQL 2000 box), you will not see that prompt. That's why I recommend you setup everything from SQL 2005 management studio for the SQL 2000 publisher/distributor.

Thanks,

Zhiqiang Feng

|||

When I attempt to create a publisher using SS2K5, on SS 2K, I get a message saying the server 'mase' is an older version of SQL Server and cannot be the Distributor for server 'sql2'.

Maybe this is my problem, does the distributor need to be on SS2K5?

Also, to create a publisher on a SS2K server using SS2K5 Management Studio do you right clisck on Local Publication?

I created the subscriber using SS2K5 and all looks good. When I right click the subscriber and click View Synchronization Status I get this message:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).

When I look at History I see this message: Message
Unable to start execution of step 1 (reason: Error authenticating proxy PMS\svcsqladmin, system error: Logon failure: unknown user name or bad password.). The step failed.

I know the password is correct for this login as we tested it just before attempting to create replication.

Is there anyyhnig I am doing incorrectly? I would love to get this set up. On top of everything else, this is the first replication I have ever tried to configure.

Thanks again,

Roy

|||

hi,

right now I facing the same problem.

you have said that the only thing that work is using transactional replication.

the problem is my db don't have primary key.

do you have other option, so we can have the same or at least munites delay database.

please advice

thanks

Replication Advice needed

Hello,

We are developing a software for a logistic company, which will have around 1000 branches, and we need to synchronize the database(SQL Server) between all the branches using the database at web server, i.e the branches can get the new data from the web server,as well as push the data at the web server, please tell us how can we accomplish this task,

ThanksThis wont work if I'm reading this correctly. You want to have a server both receiving and pushing data. Besides using it as the web server and replication on one server to feed 1000 sites. I say get 4 deal Racs Use
1. database server
2. replication server
3. webserver
4. backupserver.
at sites, have 2 server interfaces and one for live and the other for replication. Users should never get the live data. They only see the replication and thats transparent to them. Hope this helps. I'd also checkout lazydba.com real good info

Replicating Only Objects, no data

I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.

Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.

If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.

any ideas.

Replication support replicate DDL changes such as table, SP, and function changes. But replication support either both dml and ddl changes, or dml without tracking ddl changes. As your case, you only need ddl change without dml change, replication does not have parameter settings to support this. But, I am sure you can find workaround, as use View instead of Table, which can not query out data. Add View, stored procedure, and functions as articles in replication.

Hope it will help.

Thanks

Yunjing