Replication failed

Hi,
We are using transactional replication and it failed and
showing following error
Error:
The process could not bulk copy into table 'TableName'.
Error Number:
22001
Category:ODBC
Source: ODBC
Full Message:
String data, right truncation
Is any one having solution for this?
Regards,
Manoj
Manoj,
can you check if the schemas are identical on publisher and subscriber if
you are doing a nosync initialization.
If you are sending over the table schema, can you ensure that you take the
collation.
Regards,
Paul Ibison

Replication Errors

Hello,
I am getting the following error when trying to sync from my local site to
my dr site.
Could not locate entry in sysdatabases for database '10.1.200.70'. No entry
found with that name. Make sure that the name is entered correctly.
(Source: 10.1.200.70 (Data source); Error number: 911)
I am trying to setup replication from my local site to my DR site. I can
register my dr sql box using the IP but not the hostname.
Any thoughts on this error?
Lontae,
try creating an alias for the server's IP address. You might also need an
entry in the hosts/lmhosts (I can't remember which to locate the snapshot
share.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Alias in DNS?
"Paul Ibison" wrote:

> Lontae,
> try creating an alias for the server's IP address. You might also need an
> entry in the hosts/lmhosts (I can't remember which to locate the snapshot
> share.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Lontae,
alias in the server network utility (I'm sure you now this but if not it's
located at start, programs, microsoft sql server...).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think Paul means client network utility
Easy mistake to make, I've made it thousands of times, myself.
For netbios resolution use lmhosts (ie a unc snapshot), for ftp use hosts.
name resolution goes through a sequence so it really doesn't matter as both
files are checked, but there are slight performance increases (milli
seconds) if you make the correct entries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:umx%23oCGvEHA.940@.TK2MSFTNGP14.phx.gbl...
> Lontae,
> alias in the server network utility (I'm sure you now this but if not it's
> located at start, programs, microsoft sql server...).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks Hilary,
I'm not sure about the setup, but Lontae is registering
the DR site at the local site so I was thinking it was
push rather than pull.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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 error thrown during normal T-SQL processing

Dear All,
During a long running piece of SQL script I received the following error:
Error Number: 2601
Error: "The job name '%s' was not generated for this replication agent,
delete the job manually when it is no longer in use."
There is no replication set up on this server at all, and the T-SQL is pure
with no calls to system stored or extended procedures.
Has anybody experience this before? A search of Google and MSDN has
returned no results at all for the assosiated message!
Spec:
Microsoft SQL Server 2000 - 8.00.760
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
HP ProLiant dual 3ghz HT Zeon
4gb ram
Thanks
Phil StandenOn my server,
SELECT *
FROM master..sysmessages
WHERE Error = 2601
returns:
Cannot insert duplicate key row in object '%.*ls' with unique index
'%.*ls'.
and
SELECT *
FROM master..sysmessages
WHERE Error = 21695
returns:
The job name '%s' was not generated for this replication agent, delete
the job manually when it is no longer in use.
Do you get the same results on your box?
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil Standen" <PhilStanden@.discussions.microsoft.com> wrote in message
news:BEE3ED3E-FA80-4D98-A85D-36B4E37B374A@.microsoft.com...
> Dear All,
> During a long running piece of SQL script I received the following error:
> Error Number: 2601
> Error: "The job name '%s' was not generated for this replication agent,
> delete the job manually when it is no longer in use."
> There is no replication set up on this server at all, and the T-SQL is
> pure
> with no calls to system stored or extended procedures.
> Has anybody experience this before? A search of Google and MSDN has
> returned no results at all for the assosiated message!
> Spec:
> Microsoft SQL Server 2000 - 8.00.760
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> HP ProLiant dual 3ghz HT Zeon
> 4gb ram
> Thanks
> Phil Standen

Replication Error Message

I get the following error message when I run replication that I have set up
on one of our servers. I have no ideal why, any help would be appreciated.
This error is from the application log on the replication server to our web
server. I think it is because it is trying to login as user "null" but i
have no ideal where to set this.
Thank you.
Greg
Event Type: Information
Event Source: SQLAgent$INST2DB
Event Category: Job Engine
Event ID: 203
Date: 11/15/2005
Time: 5:03:45 PM
User: N/A
Computer: VS2DB
Description:
SubSystem Message - Job 'VS2DB\INST2DB-Charity-WEBSERVER-13'
(0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection. The
process could not connect to Subscriber 'WEBSERVER'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
It looks like you are trying to logon to a SQL Server using SQL
authentication and this server is not configured for mixed mode
authentication.
"greg" <anomious@.microsoft.com> wrote in message
news:O9UGJqj6FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I get the following error message when I run replication that I have set up
>on one of our servers. I have no ideal why, any help would be appreciated.
>This error is from the application log on the replication server to our web
>server. I think it is because it is trying to login as user "null" but i
>have no ideal where to set this.
> Thank you.
> Greg
> Event Type: Information
> Event Source: SQLAgent$INST2DB
> Event Category: Job Engine
> Event ID: 203
> Date: 11/15/2005
> Time: 5:03:45 PM
> User: N/A
> Computer: VS2DB
> Description:
> SubSystem Message - Job 'VS2DB\INST2DB-Charity-WEBSERVER-13'
> (0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection. The
> process could not connect to Subscriber 'WEBSERVER'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
|||Hillary, thanks for the response, I check both servers and they are set up
to user both SQL and Windows authentication. Do you have any other ideas?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%237M1YNk6FHA.1140@.tk2msftngp13.phx.gbl...
> It looks like you are trying to logon to a SQL Server using SQL
> authentication and this server is not configured for mixed mode
> authentication.
> "greg" <anomious@.microsoft.com> wrote in message
> news:O9UGJqj6FHA.2040@.TK2MSFTNGP14.phx.gbl...
>

replication error 21258

Hi,

When I'm trying to subscribe at a publication with webservice method and RMO, i have the following error :

"You must first drop all existing merge publications to add an anonymous or local subscription to

database MyDB. Only members of the sysadmin fixed server role can perform this operation without specifying @.job_login or

@.job_password. Changed database context to MyDB"

Any help is welcome

Thanks

This usually happens when subscription database also has a merge publication. Could you verify if this is the case?|||

That's right.

Thanks.

Replication error 18482

I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.
Have you configured your publisher to publish to the distributor? Connect to the distributor in EM and configure it in the Tools - Replication - Configure Distributor, Publishers and Subscribers, do it in the Publishers tab. Ensure you enter an administrative link password.
Then on your publisher disable replication and then re-enable it to use the remote distributor. Enter the administrative link password in this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:OLPnLFjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.
|||Good morning Hilary,
Yes I enabled SERVER1 as the distributor and set a link password. I then went to SERVER2 and tried to publish a database I set the link password through the wizard. I am trying to setup SERVER1 as the distributor for SERVER2.
I am still receiving the error I have both servers setup as remote servers on both systems.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
Thanks,
Chris T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uYGrpLjaHHA.4772@.TK2MSFTNGP05.phx.gbl...
Have you configured your publisher to publish to the distributor? Connect to the distributor in EM and configure it in the Tools - Replication - Configure Distributor, Publishers and Subscribers, do it in the Publishers tab. Ensure you enter an administrative link password.
Then on your publisher disable replication and then re-enable it to use the remote distributor. Enter the administrative link password in this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:OLPnLFjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.
|||In this output:
sp_helpserver on SERVER1
this entry does not look correct:
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
It appears that SERVER1 has been defined as ser on this server. What does select * from sysservers look like on SERVER1?
What does select @.@.servername return on SERVER1?
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:%23FiEphjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
Good morning Hilary,
Yes I enabled SERVER1 as the distributor and set a link password. I then went to SERVER2 and tried to publish a database I set the link password through the wizard. I am trying to setup SERVER1 as the distributor for SERVER2.
I am still receiving the error I have both servers setup as remote servers on both systems.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
Thanks,
Chris T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uYGrpLjaHHA.4772@.TK2MSFTNGP05.phx.gbl...
Have you configured your publisher to publish to the distributor? Connect to the distributor in EM and configure it in the Tools - Replication - Configure Distributor, Publishers and Subscribers, do it in the Publishers tab. Ensure you enter an administrative link password.
Then on your publisher disable replication and then re-enable it to use the remote distributor. Enter the administrative link password in this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:OLPnLFjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.
|||Randy,
What is ser ?
Answer 1:
select @.@.servername returns SERVER1
Answer 2:
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Any ideas?
Thanks,
Chris T.
"Rand Boyd" <rboyd@.hotmail.com> wrote in message news:uZiZ1jlaHHA.4508@.TK2MSFTNGP03.phx.gbl...
In this output:
sp_helpserver on SERVER1
this entry does not look correct:
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
It appears that SERVER1 has been defined as ser on this server. What does select * from sysservers look like on SERVER1?
What does select @.@.servername return on SERVER1?
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:%23FiEphjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
Good morning Hilary,
Yes I enabled SERVER1 as the distributor and set a link password. I then went to SERVER2 and tried to publish a database I set the link password through the wizard. I am trying to setup SERVER1 as the distributor for SERVER2.
I am still receiving the error I have both servers setup as remote servers on both systems.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
Thanks,
Chris T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uYGrpLjaHHA.4772@.TK2MSFTNGP05.phx.gbl...
Have you configured your publisher to publish to the distributor? Connect to the distributor in EM and configure it in the Tools - Replication - Configure Distributor, Publishers and Subscribers, do it in the Publishers tab. Ensure you enter an administrative link password.
Then on your publisher disable replication and then re-enable it to use the remote distributor. Enter the administrative link password in this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:OLPnLFjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.
|||It was listed in the output. It maybe that the output got cut off. The first column in the output is the internal SQL Server name. But if @.servername is correct then it should be OK.
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:u6L4zulaHHA.5020@.TK2MSFTNGP05.phx.gbl...
Randy,
What is ser ?
Answer 1:
select @.@.servername returns SERVER1
Answer 2:
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Any ideas?
Thanks,
Chris T.
"Rand Boyd" <rboyd@.hotmail.com> wrote in message news:uZiZ1jlaHHA.4508@.TK2MSFTNGP03.phx.gbl...
In this output:
sp_helpserver on SERVER1
this entry does not look correct:
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
It appears that SERVER1 has been defined as ser on this server. What does select * from sysservers look like on SERVER1?
What does select @.@.servername return on SERVER1?
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:%23FiEphjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
Good morning Hilary,
Yes I enabled SERVER1 as the distributor and set a link password. I then went to SERVER2 and tried to publish a database I set the link password through the wizard. I am trying to setup SERVER1 as the distributor for SERVER2.
I am still receiving the error I have both servers setup as remote servers on both systems.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
Thanks,
Chris T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uYGrpLjaHHA.4772@.TK2MSFTNGP05.phx.gbl...
Have you configured your publisher to publish to the distributor? Connect to the distributor in EM and configure it in the Tools - Replication - Configure Distributor, Publishers and Subscribers, do it in the Publishers tab. Ensure you enter an administrative link password.
Then on your publisher disable replication and then re-enable it to use the remote distributor. Enter the administrative link password in this dialog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris T." <tatroc@.donotspam.gmail.com> wrote in message news:OLPnLFjaHHA.2436@.TK2MSFTNGP06.phx.gbl...
I am getting the following error and can not figure out how to fix it. Please help.
SQL Server Enterprise Manager could not configure 'SERVER1' as the distributor for 'SERVER2'
Error 18482: Could not connect to server 'SERVER1' because 'SERVER2' is not defined as a remote server.
sp_helpserver on SERVER1
SERVER2 SERVER2 rpc out,use remote collation 2 NULL 0 0
repl_distributor SERVER1 rpc,dist,rpc out,system,use remote collation 1 NULL 0 0
ser SERVER1 rpc,rpc out,data access,use remote collation 0 NULL 0 0
sp_helpserver on SERVER2
SERVER2 SERVER2 rpc,rpc out,use remote collation 0 NULL 0 0
PE3 PE3 rpc out,use remote collation 2 NULL 0 0
SERVER1 SERVER1 rpc out,use remote collation 3 NULL 0 0
repl_distributor SERVER1 rpc,rpc out,system,use remote collation 1 NULL 0 0
on SERVER2
use master
select * from sysservers
0 1089 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2003-11-19 01:44:51.920 NULL NULL NULL NULL 0 0 SERVER2 0 1 0 0 0 0 1 0 0 0 1 0 NULL
1 1601 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:33:17.007 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 0 0 1 0 0 1 1 0 NULL
3 1088 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-15 14:07:05.963 NULL NULL NULL NULL 0 0 SERVER1 0 0 0 0 0 0 1 0 0 0 1 0 NULL
on SERVER1
use master
select * from sysservers
0 1249 SERVER1 SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:20:40.747 NULL NULL NULL NULL 0 0 SERVER1 1 1 0 0 0 0 1 1 0 0 1 0 NULL
1 1609 repl_distributor SQL Server SQLOLEDB SERVER1 NULL NULL 2007-03-16 10:30:58.750 NULL NULL NULL NULL 0 0 SERVER1 0 1 0 0 1 0 1 0 0 1 1 0 NULL
2 1088 SERVER2 SQL Server SQLOLEDB SERVER2 NULL NULL 2007-03-16 10:24:04.343 NULL NULL NULL NULL 0 0 SERVER2 0 0 0 0 0 0 1 0 0 0 1 0 NULL
Thanks,
Chris T.

REPLICATION ERROR - retention-based meta data

Hi,
I am dealing with merge replication.
The following error occured several times since last 1 month.
The merge process could not perform retention-based meta data cleanup in
database 'databasename'.
(Source: Merge Replication Provider (Agent); Error number: -2147199467)
-----
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
(Source: DPMS (Data source); Error number: 0)
-----
1. What is reason for this error?
2. Is it possible to overcome this?
Thanks,
Soura
SouRa,
you could try increasing the QueryTimeout value, but the error may be
symptomatic of a bigger issue. Please could you run DBCC CHECKDB to diagnose
any storage issues. If there are some, you might need to run the same
command to repair the system.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul
I run the DBCC CHECKDB. But I get no error messages.
Rgds
SouRa
"Paul Ibison" wrote:

> SouRa,
> you could try increasing the QueryTimeout value, but the error may be
> symptomatic of a bigger issue. Please could you run DBCC CHECKDB to diagnose
> any storage issues. If there are some, you might need to run the same
> command to repair the system.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||OK - you could try reindexing some of the merge replication system tables:
dbcc dbreindex (MSmerge_contents)
dbcc dbreindex (MSmerge_tombstone)
dbcc dbreindex (MSmerge_replinfo)
dbcc dbreindex (MSmerge_genhistory)
, increase the querytimeout then restart.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We do a daily index of all replication tables before start of day. These
tables are indexed daily.
We have about 20 + databases on the server which are Merge replicated to a
remote server , concurrently. Our current query timeout is the default
timeout value.
How much query timeout we need to increase.?
Soura
"Paul Ibison" wrote:

> OK - you could try reindexing some of the merge replication system tables:
> dbcc dbreindex (MSmerge_contents)
> dbcc dbreindex (MSmerge_tombstone)
> dbcc dbreindex (MSmerge_replinfo)
> dbcc dbreindex (MSmerge_genhistory)
> , increase the querytimeout then restart.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||SouRa,
in your case I'd like to know if this solves the issue so I'd set it to an
extremely big number - just to see if the error still occurs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
I want to increase query timeout value but I don't how to do this. Please
advise me how to change timeout value. Is it possible to set this for a
particular database?
Thanks,
Soura.
"Paul Ibison" wrote:

> SouRa,
> in your case I'd like to know if this solves the issue so I'd set it to an
> extremely big number - just to see if the error still occurs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||Dear Paul,
We have modified the Merge agent profile to "High end server to server.."
After couple of days of this change, we came across a new error message "the
process is running and waiting for a response from one of the backend
connections", after this message, we noticed the normal error message "
...clean up".
Please provide any other areas we may need to review.
We "push" the subscription to the Subscriber. All our foreign keys are not
enabled for replication.
Thanks,
Soura
"SouRa" wrote:
[vbcol=seagreen]
> Hi Paul,
> I want to increase query timeout value but I don't how to do this. Please
> advise me how to change timeout value. Is it possible to set this for a
> particular database?
> Thanks,
> Soura.
> "Paul Ibison" wrote:
|||(replied to this above...)
Cheers,
Paul Ibison
sql

Replication Error

Hi all,

I have encountered an error during replication. The following is my code;

---

Try

m_SQlEngine = New SqlServerCe.SqlCeEngine("Data Source =\Project_JobLogger.sdf")

m_SQlEngine.CreateDatabase()

Dim SqlCeConnection As New SqlServerCe.SqlCeConnection("Data Source =\Project_JobLogger.sdf")

m_SQLConnection.Open()

Dim Repl As SqlServerCe.SqlCeReplication

Repl = New SqlServerCe.SqlCeReplication

Repl.InternetUrl = http://Ganges/PJLogger/sqlcesa30.dll

Repl.Publisher = "Ganges"

Repl.PublisherDatabase = "Project_JobLogger"

Repl.Publication = "PJLPublisher"

Repl.Subscriber = System.Net.Dns.GetHostName

Repl.SubscriberConnectionString = m_SQLConnection.ConnectionString

Repl.HostName = System.Net.Dns.GetHostName

Repl.ExchangeType = ExchangeType.BiDirectional

Repl.Synchronize()

Catch ex As Exception

MessageBox.Show(ex.ToString())

m_SQLConnection.Close()

m_SQLConnection.Dispose()

End Try

---

The error just displays - System.Data.SqlServerCe.SqlCeException;SqlCeException

Can anyone state what is the problem please?

Thanks

Matt

Matt,

There is a lot more information hiding inside the SqlCeException that is being thrown that you'll need to troubleshoot this situation. Here is some code to unwrap the exception and get specifics.

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren

|||

Thanks a lot Darren, I found your piece of code very useful. It managed to specify the exact error

Thanks Again ,

Matt

Replication error

Hi,
I get the following error when I select Replication --> Configure Publisher...
from EM
"the passed ordinal is out of range of the specified collection"
didnt find any article on KBHave you thought of trying this Op from a different Client & poss SQL Box

Please advise Version in use etc

All I get when I do this on my home box is SQL Server Replication does not allow nicknames, but I'm not bothered - I've no one to replicate with @. the Mo anyway ;-(

GW|||version is
SQL Server 2000 Evaluation Edition..

i had a testdb configured for repln ..
wanted to delete it...couldnt do tat cos it said 'db set for repln' bla bla..
learnt from a forum tat if u update sysdatabases's category to 0 it wud set it...
did tat...
now when i use EM for repln it gives me the error...

not sure if the above actions are responsible for it..

cos when i use a difft server i dont get that error...
same version...|||Please choose one or more

a) Format the Server and reinstall SqlServer and restore Backups ?

Good for Practice - but B Careful !!

b) Please stop F**ing about willy nilly with the System Tables

c) Change the sysdatabases's category back to what it used to be and if neccesary recreate the DB that you had marked for replication replicate it then unmount it properly.

Are you in a Live Production Environment ?

GW|||I re-installed SQL Server on the test m/c.
Now I have a snapshot repln set up.
I get the following error on the Subscriber.

'The license for this installation of SQL Server does not permit Log Reader Agent to run at the Distributor.'.|||OOooopps.

Your struck with Luck - I have no easy answer to this

GW|||Other than installing SP3 will probably sort it :-)

GW|||any document/article to support it??|||I would have serviced pack'd it up before doing anything with it first anyway

http://www.developmentnow.com/g/114_2004_1_0_0_443902/Strange-Licensing-error.htm

GW|||Thanks a lot..
Snapshot repln on track :)|||U Welcome

Can we assume that the Service Pack Sorted it ?

GW|||yeah SP3 did the trick...
i guess SP4 is out too..but few bugs been reported against it so applied SP3

replication error

we are replicating from SQL 2000 to Oracle via linked server. and are
getting the following error "The process encountered invalid column
data in bcp file", the bcp file is too big to investigate it, any one
has any insight of this?
Did this ever work? By default SQL Server maps the SQL Server datatypes to
the Oracle datatypes, so it should be able to construct the table on Oracle
and bcp the data there. You might want to try it with a null filter to see
if the problem exists with the schema or the data.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shunyen" <kate.s.chiang@.healthnet.com> wrote in message
news:1175209502.943214.313370@.d57g2000hsg.googlegr oups.com...
> we are replicating from SQL 2000 to Oracle via linked server. and are
> getting the following error "The process encountered invalid column
> data in bcp file", the bcp file is too big to investigate it, any one
> has any insight of this?
>
|||It worked before, the table got rebuilt and we have to recreate the
replication and this shows up. what do you meen to use a null
filter?
On Mar 30, 5:56 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Did this ever work? By default SQL Server maps the SQL Server datatypes to
> the Oracle datatypes, so it should be able to construct the table on Oracle
> and bcp the data there. You might want to try it with a null filter to see
> if the problem exists with the schema or the data.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "shunyen" <kate.s.chi...@.healthnet.com> wrote in message
> news:1175209502.943214.313370@.d57g2000hsg.googlegr oups.com...
>
>
> - Show quoted text -
|||A filter that looks like this
where 1=2
It always evaluates to false.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shunyen" <kate.s.chiang@.healthnet.com> wrote in message
news:1175268021.364120.267330@.p77g2000hsh.googlegr oups.com...
> It worked before, the table got rebuilt and we have to recreate the
> replication and this shows up. what do you meen to use a null
> filter?
>
> On Mar 30, 5:56 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Replication Error

Hi all,
Please help me with issue. I have the following error accured always when
i
run the merge agent. I have 3 database makes replication, 2 are running
and
is giving this error:
LAST COMMAND : {call sp_MSenumchanges(?,?,?,?,?,?,?,?)}
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
--
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
(Source: SERV-COMPAQ (Data source); Error number: 0)
--
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
--
any help is realy appreciated.
thanks all.
Samir R. Ibrahim
I have did that on both servers. with the same result.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:372e01c4a553$224b3800$a301280a@.phx.gbl...
> Samir,
> try setting the QueryTimeout to 0 in the merge agent's
> job.
> HTH,
> Paul IBison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Samir,
you could try running the merge agent again, but before
that, enable logging http://support.microsoft.com/?
id=312292
Rgds,
Paul Ibison
|||Thank you Paul for your help. i will give that a try.
Samir R. Ibrahim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2b2101c4a55a$a221cba0$a501280a@.phx.gbl...
> Samir,
> you could try running the merge agent again, but before
> that, enable logging http://support.microsoft.com/?
> id=312292
> Rgds,
> Paul Ibison
>

REPLICATION ERROR

Hi,
I am dealing with merge replication between local & remote servers.
Last week the following error occurs two times.
The merge process could not perform retention-based meta data cleanup in
database 'TEST'.
(Source: Merge Replication Provider (Agent); Error number: -2147199467)
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
(Source: TSERVER (Data source); Error number: 0)
First I gave 'start synchronizing' after that I restarted the merge agent
but no use .
Finally after restarting the server(publisher), it came to end. Now its
working fine.
My question is,
1. what is the reason for this error?
2. Is this a Symptom for future problems (ie., like database corruption)
but when I run CHECKDB command, no error has been shown. It says the DB
is fine.
Can any one advice me?
Thanks,
Soura.
This error is basically a timeout and can happen for many reasons. They
are usually assoicated with slow servers or subscribers but could also
be network problems. Reindexing your tables, especially
msmerge_contents, msmerge_Genhistory, msmerge_tombstone can help if you
have a lot of replication metadata. If you have a large amount of
metadata in those tables you can also run sp_mergecleanupmetadata. This
will cause you to generate and apply a new snapshot however. If you
choose to use the sp_mergecleanupmeta data and have a large number or
rows in your merge tables. You might want to truncate the
msmerge_contents and maybe the msmerge_genhistory table at the
publisher and subcribers before running as it will also get a timeout
doing the cleanup.
|||Hi Sounder,
I found this from One article.
In case of this error
stop the merge agent and do a dbcc dbreindex -- to all tables by using that
reindex procedure.
Thanks.
Herbert
"SouRa" wrote:

> Hi,
> I am dealing with merge replication between local & remote servers.
> Last week the following error occurs two times.
> The merge process could not perform retention-based meta data cleanup in
> database 'TEST'.
> (Source: Merge Replication Provider (Agent); Error number: -2147199467)
> ----
> --
> The merge process timed out while executing a query. Reconfigure the
> QueryTimeout parameter and retry the operation.
> (Source: TSERVER (Data source); Error number: 0)
> First I gave 'start synchronizing' after that I restarted the merge agent
> but no use .
> Finally after restarting the server(publisher), it came to end. Now its
> working fine.
> My question is,
> 1. what is the reason for this error?
> 2. Is this a Symptom for future problems (ie., like database corruption)
> but when I run CHECKDB command, no error has been shown. It says the DB
> is fine.
> Can any one advice me?
> Thanks,
> Soura.
|||Hi Sounder,
I find this from one article.
In case of these kind of error
stop the merge agent and do a dbcc dbreindex -- Do reindex to all tables
using that procedure
Thanks
Herbert
"SouRa" wrote:

> Hi,
> I am dealing with merge replication between local & remote servers.
> Last week the following error occurs two times.
> The merge process could not perform retention-based meta data cleanup in
> database 'TEST'.
> (Source: Merge Replication Provider (Agent); Error number: -2147199467)
> ----
> --
> The merge process timed out while executing a query. Reconfigure the
> QueryTimeout parameter and retry the operation.
> (Source: TSERVER (Data source); Error number: 0)
> First I gave 'start synchronizing' after that I restarted the merge agent
> but no use .
> Finally after restarting the server(publisher), it came to end. Now its
> working fine.
> My question is,
> 1. what is the reason for this error?
> 2. Is this a Symptom for future problems (ie., like database corruption)
> but when I run CHECKDB command, no error has been shown. It says the DB
> is fine.
> Can any one advice me?
> Thanks,
> Soura.

Replication design

Hi,
I have the following scenario, and need to configure
replication for this. Pls advice.
We have 3 SQL server databases, at 3 locations on SQL
server 2000 servers. They are connected over WAN by 2
MBPS link (triangle). I have to configure replication on
this. Database has around 200 tables, and size of
database would be around 200-500 MB each. Changes would
be minor on daily basis.
I have these servers at following locations.
Loc1 (HeadOffice)
Loc2 (branch office)
Loc3 (branch office)
Data has to replicate from Loc2 and Loc3 to Loc1 at short
intervals during the day.
Somewhere in midnight (off peak hours), data has to
replicate from Loc1 to Loc2 and Loc3 (for changes made at
Loc1 to be reflected in Loc2 and Loc3).
These database are modified only during the day, and
number of users at each location will not exceed 30.
I have following queries
- Can I configure Transactional replication for Loc2 ->
Loc1 and Loc3 -> Loc1 to replicate at short intervals.
And additionally Snapshot to happen at midnight from
Loc1 -> Loc2 and Loc3.
Or do I require Merge replication for this scenario. (or
any other solution you think would suit this requirement)
Pls suggest.
Thanx,
Shrikant,
the easiest scenario would be mege replication. Snapshot will copy over the
whole tables and unless the tables are small or all rows are always updated,
this is not recommended, and you'd have to set up replication each time
after the snapshot - possible but not so nice. Also, if the data is
partitioned then great, but if not then merge will take care of that with
the conflict resolvers. The only thing is that you won't be able to easily
prevent the download of data from Loc1 until the evening, as these changes
will propagate during the frequent synchronizations. If this is not at all
desirable, than you might have to consider a more radical solution - you
could have Loc1 as the publisher but use unidirectional merge with the
subscribers and reinitialize each evening.
HTH,
Paul Ibison
|||Hi Paul,
Thanx for your reply.
But would not be using the bi-directional transactional
replication solve this ?
Loc1 acts as Publisher with Loc2 and Loc3 as subscribers.
And then Loc2 and Loc3 as Publishers, and Loc1 as
Subscriber ?
regds,
Shrikant

>--Original Message--
>Shrikant,
>the easiest scenario would be mege replication. Snapshot
will copy over the
>whole tables and unless the tables are small or all rows
are always updated,
>this is not recommended, and you'd have to set up
replication each time
>after the snapshot - possible but not so nice. Also, if
the data is
>partitioned then great, but if not then merge will take
care of that with
>the conflict resolvers. The only thing is that you won't
be able to easily
>prevent the download of data from Loc1 until the
evening, as these changes
>will propagate during the frequent synchronizations. If
this is not at all
>desirable, than you might have to consider a more
radical solution - you
>could have Loc1 as the publisher but use unidirectional
merge with the
>subscribers and reinitialize each evening.
>HTH,
>Paul Ibison
>
>.
>
|||Shrikant,
this would be an option. If you expect conflicts then it can become terribly complicated, and the initial setup is not straightforward, but you're right - it is in some ways a simpler solution. To You might want to have a look at the most recent issue of
SQL Server magazine if you're thinking of going down this route - there is a nice article on setting it up.
Regards,
Paul Ibison

Replication Between Different Versions of SQL Server causes access denied error.

I am getting the following error:
SQL Server does not exist or access denied. Error number 17.
The publication and distributor is SQL Server 7.0 from internal lan.
The subscriber is SQL Server 2000 from external wan outside of the firewall.
Using SQL Server Enterprise Manager 7.0 on the publication server will not
allow me to register the subscriber as it says "You must upgrade your SQL
Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
connecti to this server". But if I use a non participanting SQL Server
Enterprise Manager 2000 within the internal lan it will allow me to register
and connect to the browser and I am able to view the database and its
tables.
In addition I can connect to the subscriber using Query Analyzer in version
7.0.
According to the MSDN under the section Replication Between Different
Versions of SQL Server, I fit the same criteria as Combination 2 for
snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
7.0/Subscriber SQL Server 2000.
So why does the push subscription fail?
Regards
Peter
Hello Peter,
This is expected behavior when trying to access SQL Server 2000 from SQL
7.0 version of SQL DMO. You will be able to connect to SQL Server 2000 from
SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
resolution is to upgrade the client tools to SQL 2000.
Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
on a different server to work around this issue.
Combination 2 you referred to does not indicate that you could configure
this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
you could only configure this from a SQL 2000 Enterprise Manager.
Hope this helps.
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>Subject: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Mon, 31 Oct 2005 16:30:49 +1000
>Lines: 32
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17479
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I am getting the following error:
>SQL Server does not exist or access denied. Error number 17.
>The publication and distributor is SQL Server 7.0 from internal lan.
>The subscriber is SQL Server 2000 from external wan outside of the
firewall.
>Using SQL Server Enterprise Manager 7.0 on the publication server will not
>allow me to register the subscriber as it says "You must upgrade your SQL
>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
>connecti to this server". But if I use a non participanting SQL Server
>Enterprise Manager 2000 within the internal lan it will allow me to
register
>and connect to the browser and I am able to view the database and its
>tables.
>In addition I can connect to the subscriber using Query Analyzer in
version
>7.0.
>According to the MSDN under the section Replication Between Different
>Versions of SQL Server, I fit the same criteria as Combination 2 for
>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
>7.0/Subscriber SQL Server 2000.
>So why does the push subscription fail?
>Regards
>Peter
>
>
>
>
|||I have created the publication on SQL Server 7.0 server via SQL Server 2000
Enterprise and created a push subscription. As soon as the snapshoot was
ready to send I get the same error "The process could not connect to
Subscriber" (SQL Server does not exist or access denied. Error number: 17)
even though I could setup the subscriber and select the destination
database.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This is expected behavior when trying to access SQL Server 2000 from SQL
> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
> from
> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
> resolution is to upgrade the client tools to SQL 2000.
> Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
> on a different server to work around this issue.
> Combination 2 you referred to does not indicate that you could configure
> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
> you could only configure this from a SQL 2000 Enterprise Manager.
> Hope this helps.
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default...national.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> access denied error.
> firewall.
> register
> version
>
|||Hello Peter,
This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
SP1 if it is on Win2000 server.
899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
http://support.microsoft.com/?id=899456
Also, please use SQL client network utility to make sure TCPIP and named
pipes are enabled. Also, you may want to create an Alais for SQL 2000
(subscriber) to test the situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Tue, 1 Nov 2005 10:09:26 +1000
>Lines: 117
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I have created the publication on SQL Server 7.0 server via SQL Server
2000[vbcol=seagreen]
>Enterprise and created a push subscription. As soon as the snapshoot was
>ready to send I get the same error "The process could not connect to
>Subscriber" (SQL Server does not exist or access denied. Error number: 17)
>even though I could setup the subscriber and select the destination
>database.
>Regards
>Peter
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
all[vbcol=seagreen]
https://partner.microsoft.com/US/tec...rview/40010469[vbcol=seagreen]
https://partner.microsoft.com/US/tec...pportoverview/[vbcol=seagreen]
not
>
>
|||The penny dropped!
Whilst I did configure SQL client network on my development pc to browse the
external database I didn't realise I had to configure it again on the
distributor server. The replication wizard sort of misled me as part of it
process what to nonimate the subscriber which it found on my development pc
whereas it should have failed it the distributor couldn't recognise it.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
> SP1 if it is on Win2000 server.
> 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
> http://support.microsoft.com/?id=899456
> Also, please use SQL client network utility to make sure TCPIP and named
> pipes are enabled. Also, you may want to create an Alais for SQL 2000
> (subscriber) to test the situation.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
> access denied error.
> 2000
> all
> https://partner.microsoft.com/US/tec...rview/40010469
> https://partner.microsoft.com/US/tec...pportoverview/
> not
>
|||Hello Peter,
Glad to hear the issue is resolved. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
<uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
<bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
| Date: Thu, 3 Nov 2005 10:00:07 +1000
| Lines: 190
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <Ow70RmA4FHA.3540@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 61.88.17.230
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17574
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| The penny dropped!
| Whilst I did configure SQL client network on my development pc to browse
the
| external database I didn't realise I had to configure it again on the
| distributor server. The replication wizard sort of misled me as part of
it
| process what to nonimate the subscriber which it found on my development
pc
| whereas it should have failed it the distributor couldn't recognise it.
|
| Regards
| Peter
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
| > Hello Peter,
| >
| > This seems to be a MDAC issue. I suggest that you install latest MDAC
2.8
| > SP1 if it is on Win2000 server.
| >
| > 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
| > http://support.microsoft.com/?id=899456
| >
| > Also, please use SQL client network utility to make sure TCPIP and named
| > pipes are enabled. Also, you may want to create an Alais for SQL 2000
| > (subscriber) to test the situation.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > ================================================== ===
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| >>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| > <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
| >>Subject: Re: Replication Between Different Versions of SQL Server causes
| > access denied error.
| >>Date: Tue, 1 Nov 2005 10:09:26 +1000
| >>Lines: 117
| >>X-Priority: 3
| >>X-MSMail-Priority: Normal
| >>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>X-RFC2646: Format=Flowed; Original
| >>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
| >>Newsgroups: microsoft.public.sqlserver.replication
| >>NNTP-Posting-Host: 61.88.17.230
| >>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
| >>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>
| >>I have created the publication on SQL Server 7.0 server via SQL Server
| > 2000
| >>Enterprise and created a push subscription. As soon as the snapshoot was
| >>ready to send I get the same error "The process could not connect to
| >>Subscriber" (SQL Server does not exist or access denied. Error number:
17)
| >>even though I could setup the subscriber and select the destination
| >>database.
| >>
| >>Regards
| >>Peter
| >>
| >>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| >>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
| >>> Hello Peter,
| >>>
| >>> This is expected behavior when trying to access SQL Server 2000 from
SQL
| >>> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
| >>> from
| >>> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
| >>> resolution is to upgrade the client tools to SQL 2000.
| >>>
| >>> Also, as you have mentioned, you could use a SQL 2000 Eneterprise
| >>> Manager
| >>> on a different server to work around this issue.
| >>>
| >>> Combination 2 you referred to does not indicate that you could
configure
| >>> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL,
| >>> actually
| >>> you could only configure this from a SQL 2000 Enterprise Manager.
| >>>
| >>> Hope this helps.
| >>>
| >>> Peter Yang
| >>> MCSE2000/2003, MCSA, MCDBA
| >>> Microsoft Online Partner Support
| >>>
| >>> When responding to posts, please "Reply to Group" via your newsreader
so
| >>> that others may learn and benefit from your issue.
| >>>
| >>> ================================================== ===
| >>>
| >>> Business-Critical Phone Support (BCPS) provides you with technical
phone
| >>> support at no charge during critical LAN outages or "business down"
| >>> situations. This benefit is available 24 hours a day, 7 days a week to
| > all
| >>> Microsoft technology partners in the United States and Canada.
| >>>
| >>> This and other support options are available here:
| >>>
| >>> BCPS:
| >>>
| >
https://partner.microsoft.com/US/tec...rview/40010469
| >>>
| >>> Others:
| > https://partner.microsoft.com/US/tec...pportoverview/
| >>>
| >>> If you are outside the United States, please visit our International
| >>> Support page:
| >>> http://support.microsoft.com/default...national.aspx.
| >>>
| >>> ================================================== ===
| >>>
| >>> This posting is provided "AS IS" with no warranties, and confers no
| >>> rights.
| >>>
| >>>
| >>> --
| >>>>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>>>Subject: Replication Between Different Versions of SQL Server causes
| >>> access denied error.
| >>>>Date: Mon, 31 Oct 2005 16:30:49 +1000
| >>>>Lines: 32
| >>>>X-Priority: 3
| >>>>X-MSMail-Priority: Normal
| >>>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>>>X-RFC2646: Format=Flowed; Original
| >>>>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| >>>>Newsgroups: microsoft.public.sqlserver.replication
| >>>>NNTP-Posting-Host: 61.88.17.230
| >>>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >>>>Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.replication:17479
| >>>>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>>>
| >>>>I am getting the following error:
| >>>>SQL Server does not exist or access denied. Error number 17.
| >>>>
| >>>>The publication and distributor is SQL Server 7.0 from internal lan.
| >>>>The subscriber is SQL Server 2000 from external wan outside of the
| >>> firewall.
| >>>>
| >>>>Using SQL Server Enterprise Manager 7.0 on the publication server will
| > not
| >>>>allow me to register the subscriber as it says "You must upgrade your
| >>>>SQL
| >>>>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
| >>>>connecti to this server". But if I use a non participanting SQL Server
| >>>>Enterprise Manager 2000 within the internal lan it will allow me to
| >>> register
| >>>>and connect to the browser and I am able to view the database and its
| >>>>tables.
| >>>>
| >>>>In addition I can connect to the subscriber using Query Analyzer in
| >>> version
| >>>>7.0.
| >>>>
| >>>>According to the MSDN under the section Replication Between Different
| >>>>Versions of SQL Server, I fit the same criteria as Combination 2 for
| >>>>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL
Server
| >>>>7.0/Subscriber SQL Server 2000.
| >>>>
| >>>>So why does the push subscription fail?
| >>>>
| >>>>Regards
| >>>>Peter
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>
| >>
| >>
| >>
| >
|
|
|

replication and identity problem

Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>

Replication Advice

Hi all,
I am in need of some replication advice...
We have the following scenario:
Publisher running SQL Server 2005 with SP1
Multiple subscribers (at the minute, 10, but there will eventually be
around 30), running a mixture of SQL Server 2005 Express and SQL
Server 2005.
Now, I am fairly inexperienced with replication via the web service
(IIS), and have started looking at this particular project at work
using replication, and noticed something that does not seem right.
Whenever any schema changes are committed, we are forced to regenerate
the complete snapshot, and as such, the subscribers are forced to
download and re-initialise their snapshot.
Surely this is incorrect? Is there any way of configuring incremental
snapshot changes?
The big problem we have is that there database is around 200Mb, and we
have users who pay per MB...
Sorry for lack of information, as I say, I'm fairly new to Server to
Client replication - I normally only configure Server to Server!
Regards,
Andy Neillans
I too would be interested in this reply because my databasr is 1.6gb and to
generate a snapshot every time a schema changes is a real pain in the
butt....
Is it possible to create a snapshot on the publisher and then 'part' copy
over the new schema and tables to the subscriber (in my case).
Thanks
"Andrew Neillans" wrote:

> Hi all,
> I am in need of some replication advice...
> We have the following scenario:
> Publisher running SQL Server 2005 with SP1
> Multiple subscribers (at the minute, 10, but there will eventually be
> around 30), running a mixture of SQL Server 2005 Express and SQL
> Server 2005.
> Now, I am fairly inexperienced with replication via the web service
> (IIS), and have started looking at this particular project at work
> using replication, and noticed something that does not seem right.
> Whenever any schema changes are committed, we are forced to regenerate
> the complete snapshot, and as such, the subscribers are forced to
> download and re-initialise their snapshot.
> Surely this is incorrect? Is there any way of configuring incremental
> snapshot changes?
> The big problem we have is that there database is around 200Mb, and we
> have users who pay per MB...
> Sorry for lack of information, as I say, I'm fairly new to Server to
> Client replication - I normally only configure Server to Server!
> Regards,
> Andy Neillans
>

Replication - Views/SPs etc

Hi,
Hitting the following problem - do not know how to get
around it.
Have the main application running on a central SQL server -
I take replica with me + need the latest update of the
views and stored procedures etc.
First I tried to replicate - but replica falls over - as I
have nested views + creation at the subscriber does not
build the views in a 'smart way' so it 'misses' views and
stops.
Now tried script all the views (probably the correct way
in the begining) - but again same happens - sequence of
creation of views does not work - due to nesting of views.
Only way I can get through this at the moment - is to
compare the views in the main db and the subscriber db and
make these one by one. This does not look like the view I
should do this ..... Also cannot see when the file was
last updated (I am the only one updating)- so not always
sure (monday morning) which is the latest version ......
Any simple way out of this?
Thanks for any help?
Tim
Tim,
the problem you have with the views is one of dependencies. Using sp_depends
on a nesting view you can see which views SQL Server thinks it depends on
and this is not always correct. You can use sp_refreshview to update the
dependencies then replication will add the views in the correct order.
HTH,
Paul Ibison
|||Hi Paul,
Thanks! - this makes sense that this should be part of the
SQL Server tool-box.
Unfortunately I cannot find sp_refreshview to correct the
dependancy list - so that when scripting the views - these
are rebuilt fully - or maybe I just do not understand ....
If you could point me in the right direction!!
Thanks
Tim

>--Original Message--
>Tim,
>the problem you have with the views is one of
dependencies. Using sp_depends
>on a nesting view you can see which views SQL Server
thinks it depends on
>and this is not always correct. You can use
sp_refreshview to update the
>dependencies then replication will add the views in the
correct order.
>HTH,
>Paul Ibison
>
>.
>
|||It should be, but you do have the potential to create circular views and
replication can not sort that out. There are also issues when dealing with
complex dependency chains. The best solution to this is to literally do it
yourself via a pre-script. You are the only one who knows what the precise
creation order needs to be, I would not leave it to an auto generated
process.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Hi Mike,
Maybe this is a better situation in the end - as it forces
a more structured approach to the management of views.
Thanks for the help!
Tim

>--Original Message--
>It should be, but you do have the potential to create
circular views and
>replication can not sort that out. There are also issues
when dealing with
>complex dependency chains. The best solution to this is
to literally do it
>yourself via a pre-script. You are the only one who
knows what the precise
>creation order needs to be, I would not leave it to an
auto generated
>process.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||Tim,
it's not actually a replication setup option, but a normal system stored
procedure. Syntax is in the form:
exec sp_refreshview titleview
If you run this on your views in the correct order, then replication will
take care of the rest.
HTH,
Paul Ibison

Replication -- New Subscription Wizard SQL Mobile error, please help..

I'm following the steps of sample SQL Server Mobile Tutorials..
error on my replication subscription wizard :

"Initialiazing SQL Server Reconciler has failed
HRESULT 0x80045003 (29045)

The initial snapshot for publication 'SQLMobile' is not yet available. Start the Snapshot Agent to generate the snapshot for this publication. If this snapshot is currently being generated, wait for the process to complete and restart the syncronization.
HRESULT 0x80045003 (0)"

error while waiting synchronizing Data in New Subcription Wizard!!
anyone can help?how to solve this problem?the error says the snapshot wasn't available. Did you create the snapshot already?|||of course I did create the snapshot (I succeed create publication on database engine), like I said..I exactly followed the instruction from the SQL Server Mobile tutorial (MSDN Help).
but when I came to New Subscription Wizard step, It fail to synchronize.

please help me "_"|||

Hi feby,

Check and do the following:

1) Open SQL Server 2005 Management Studio

2) In Object Explorer; expand Replication ==> Publications Tab

3) Right Click on the publication you are interested to subscribe to

4) Choose 'View Snapshot Agent Status'

5) Make sure that 'Snapshot is 100% generated'. If it is already, regenerate again!

Just finishing 'New Publication Wizard' does not complete generation of snapshot. When you complete the above wizard, a snapshot agent job is created + started. Please make sure that the snapshot agent job is completed with 100% with above 'View Snapshot Agent Status'.

If you are getting some errors in generating snapshot, check the credentials of snapshot agent and make sure that snapshot (share or folder) has write security permissions for that user.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

[Please move this thread to SQL Server Mobile forum]

|||Hi there
I've got the same problem: I can't generate the snapshot.
ErrorMessage:
The replication agent failed to create the directory '\\<pcname>\snapshot\unc\<pcname>_SQLMOBILE_SQLMOBILEPUBLICATION\20060426091803\'.

I 've set the security permission for the snapshot folder according to the tutorial:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
for both users (just to make sure)

What i am unsure of, is why they create 2 users: the snapshot_agent (for the snapshot folder) an the IUSR_pcname (for securing the publication)
Which user is doing what and are they related to each other?

According to the MS tutorial (securing Publication step 6) you should see the IUSR in this list, well I couldn't see that user until I gave him the db_owner role, so is that correct, does that user need more roles?
Well I couldn't retrieve the publication, so I added the snapshot_agent user in step 6 an received the errormessage above.

I would be glad, if anyone could help me!
Thanks
Florian Weber, University of Computerscience, Rapperswil, Switzerland

|||Hi
well it seems clearer now, the snapshot_agent is for acessing the snapshot folder an the IUSR_pcname for the anonymous accessing over the IIS.
I also reconfigured the login accounts according to the tutorial.

Unfortunately I still receive the error message that the replication agent fails to create the directory for the snapshot.
The sharing and security settings should be right (write access for the snapshot_agent and the IUSR_pcname)

Has anyone an idea what could be the problem?

Another thing which I am not sure is if the snapshot directory must be the same directory as the virtual directory of the IIS (well I suppose so, because I should access the snapshot from a mobile sql database)

Florian Weber, University of Computerscience, Rapperswil, Switzerland|||

Hi, it looks like you don't have the write permission to the snapshot share. you can find the what is the snapshot folder by running sp_helpdistributor or from the UI to check the publisher's properties.

you will need to give the accout that run the snapshot agent the write permission to this folder.

thanks

yunwen

|||Hi Yunwen
firstly thanks for your fast reply!
I've checked the publisher's properties and the permissions, the account does have the write permission. I even tried to use the admininstrator account, still the same problem.

In my desperation i tried it on another machine and surprisingly it was running perfectly. So it seems that I have some kind of configuration problem with windows on my machine. I think I have to set it up and try it again.
At least I know now how it's working. so thank you!

Greets Florian
|||now my replication & agent are running, but when I deployed the sample program from the tutorial it shows error "Could not connect the database server through IIS" (or error something like that).
I have VS2003 installed on my computer. after my replication running,
I can't create new web project from VS2003. it say : "The Web server reported the following error when attemping to create or open the Web project located at the following URL 'http://localhost/WebApplication'. 'A connection with the server could not be established'."
(like tag IMG below "http://img142.imageshack.us/img142/5429/errorestablished3ma.jpg") any idea why it happen?coz before adjusting replication I can create project, I tried make a virtual directory but it doesn't help (all I know the IIS is still running)|||any body here?|||

i have the exactly same problem and i just have made a clean install i have formated the hard drive but i still having that problem

any advise for solving it?

|||It seem my shared folder can't be accessed, it say about permission!but the strange is..when I plug my LAN cable, that share folder can be accessed!!any clue?|||

Seems like we are all having the same problem. I need to run LAN and Web Sync, so I am using RMO to build out the LAN system now. I have permissions for the share and system at full for testing. When the pull subscription is created from the subscriber machine the subscription is left uninitialized the only feedback I have is a failure to create a new snapshot directory.

Event Type: Error
Event Source: <MS SQL SERVER 2005 Instance Name>

Event Category: (2)
Event ID: 14151
Date: 9/20/2006
Time: 10:31:15 AM
User: N/A
Computer: <MS Windows Server 2003 Machine Name>
Description:
Replication-Replication Snapshot Subsystem: agent <merge replication snapshot agent name> failed.

The replication agent had encountered an exception.

Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException
Exception Message: The replication agent failed to create the directory <new sub directory name within replication shared directory>

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Replication -- New Subscription Wizard SQL Mobile error, please help..

I'm following the steps of sample SQL Server Mobile Tutorials..
error on my replication subscription wizard :

"Initialiazing SQL Server Reconciler has failed
HRESULT 0x80045003 (29045)

The initial snapshot for publication 'SQLMobile' is not yet available. Start the Snapshot Agent to generate the snapshot for this publication. If this snapshot is currently being generated, wait for the process to complete and restart the syncronization.
HRESULT 0x80045003 (0)"

error while waiting synchronizing Data in New Subcription Wizard!!
anyone can help?how to solve this problem?the error says the snapshot wasn't available. Did you create the snapshot already?|||of course I did create the snapshot (I succeed create publication on database engine), like I said..I exactly followed the instruction from the SQL Server Mobile tutorial (MSDN Help).
but when I came to New Subscription Wizard step, It fail to synchronize.

please help me "_"|||

Hi feby,

Check and do the following:

1) Open SQL Server 2005 Management Studio

2) In Object Explorer; expand Replication ==> Publications Tab

3) Right Click on the publication you are interested to subscribe to

4) Choose 'View Snapshot Agent Status'

5) Make sure that 'Snapshot is 100% generated'. If it is already, regenerate again!

Just finishing 'New Publication Wizard' does not complete generation of snapshot. When you complete the above wizard, a snapshot agent job is created + started. Please make sure that the snapshot agent job is completed with 100% with above 'View Snapshot Agent Status'.

If you are getting some errors in generating snapshot, check the credentials of snapshot agent and make sure that snapshot (share or folder) has write security permissions for that user.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Everywhere, Microsoft Corporation

[Please move this thread to SQL Server Mobile forum]

|||Hi there
I've got the same problem: I can't generate the snapshot.
ErrorMessage:
The replication agent failed to create the directory '\\<pcname>\snapshot\unc\<pcname>_SQLMOBILE_SQLMOBILEPUBLICATION\20060426091803\'.

I 've set the security permission for the snapshot folder according to the tutorial:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
for both users (just to make sure)

What i am unsure of, is why they create 2 users: the snapshot_agent (for the snapshot folder) an the IUSR_pcname (for securing the publication)
Which user is doing what and are they related to each other?

According to the MS tutorial (securing Publication step 6) you should see the IUSR in this list, well I couldn't see that user until I gave him the db_owner role, so is that correct, does that user need more roles?
Well I couldn't retrieve the publication, so I added the snapshot_agent user in step 6 an received the errormessage above.

I would be glad, if anyone could help me!
Thanks
Florian Weber, University of Computerscience, Rapperswil, Switzerland

|||Hi
well it seems clearer now, the snapshot_agent is for acessing the snapshot folder an the IUSR_pcname for the anonymous accessing over the IIS.
I also reconfigured the login accounts according to the tutorial.

Unfortunately I still receive the error message that the replication agent fails to create the directory for the snapshot.
The sharing and security settings should be right (write access for the snapshot_agent and the IUSR_pcname)

Has anyone an idea what could be the problem?

Another thing which I am not sure is if the snapshot directory must be the same directory as the virtual directory of the IIS (well I suppose so, because I should access the snapshot from a mobile sql database)

Florian Weber, University of Computerscience, Rapperswil, Switzerland|||

Hi, it looks like you don't have the write permission to the snapshot share. you can find the what is the snapshot folder by running sp_helpdistributor or from the UI to check the publisher's properties.

you will need to give the accout that run the snapshot agent the write permission to this folder.

thanks

yunwen

|||Hi Yunwen
firstly thanks for your fast reply!
I've checked the publisher's properties and the permissions, the account does have the write permission. I even tried to use the admininstrator account, still the same problem.

In my desperation i tried it on another machine and surprisingly it was running perfectly. So it seems that I have some kind of configuration problem with windows on my machine. I think I have to set it up and try it again.
At least I know now how it's working. so thank you!

Greets Florian
|||now my replication & agent are running, but when I deployed the sample program from the tutorial it shows error "Could not connect the database server through IIS" (or error something like that).
I have VS2003 installed on my computer. after my replication running,
I can't create new web project from VS2003. it say : "The Web server reported the following error when attemping to create or open the Web project located at the following URL 'http://localhost/WebApplication'. 'A connection with the server could not be established'."
(like tag IMG below "http://img142.imageshack.us/img142/5429/errorestablished3ma.jpg") any idea why it happen?coz before adjusting replication I can create project, I tried make a virtual directory but it doesn't help (all I know the IIS is still running)|||any body here?|||

i have the exactly same problem and i just have made a clean install i have formated the hard drive but i still having that problem

any advise for solving it?

|||It seem my shared folder can't be accessed, it say about permission!but the strange is..when I plug my LAN cable, that share folder can be accessed!!any clue?|||

Seems like we are all having the same problem. I need to run LAN and Web Sync, so I am using RMO to build out the LAN system now. I have permissions for the share and system at full for testing. When the pull subscription is created from the subscriber machine the subscription is left uninitialized the only feedback I have is a failure to create a new snapshot directory.

Event Type: Error
Event Source: <MS SQL SERVER 2005 Instance Name>

Event Category: (2)
Event ID: 14151
Date: 9/20/2006
Time: 10:31:15 AM
User: N/A
Computer: <MS Windows Server 2003 Machine Name>
Description:
Replication-Replication Snapshot Subsystem: agent <merge replication snapshot agent name> failed.

The replication agent had encountered an exception.

Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException
Exception Message: The replication agent failed to create the directory <new sub directory name within replication shared directory>

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.