replication error on script

I am trying to replicate a database between 2 servers...I
have others running fine between these servers so I know
user and password are fine...I get this error everytime I
try
Error:
The schema script '\\CORPSQL\C$\Program Files\Microsoft
SQL
Server\MSSQL\ReplData\unc\CORPSQL_IRIMSAdmin_IRIMSAdmin\200
30923132049\ChangeUserPassword_316.sch' could not be
propagated to the subscriber. The step failed.
Here is the code:
SET QUOTED_IDENTIFIER ON
GO
drop procedure [dbo].[ChangeUserPassword]
GO
CREATE PROCEDURE [dbo].[ChangeUserPassword]
@.UserID int,
@.NewPassword varchar(50)
AS
DECLARE @.PasswordHistory int
DECLARE @.PasswordCount int
DECLARE @.Password varchar(50)
DECLARE @.DuplicateFlag bit
DECLARE @.ErrorMessage varchar(200)
DECLARE Password_Cursor CURSOR FOR
SELECT Password
FROM tblHistoryPasswords
WHERE UserRef = @.UserID
ORDER BY CreatedDateTime DESC
--SELECT @.USERID=USERID FROM USERDATA WHERE NTLOGON LIKE
@.NTLogon
SET @.DuplicateFlag=0
SET @.PasswordCount=0
SELECT @.PasswordHistory = CAST (varvalue AS int) FROM
tblsystemvariables WHERE varid = 'PasswordHistory'
OPEN Password_Cursor
FETCH NEXT FROM Password_Cursor
INTO @.Password
WHILE @.@.FETCH_STATUS = 0 AND @.DuplicateFlag = 0
BEGIN
SET @.PasswordCount = @.PasswordCount + 1
IF @.PasswordCount <= @.PasswordHistory
BEGIN
IF @.Password = @.NewPassword
SET @.DuplicateFlag=1
END
IF @.DuplicateFlag=0
BEGIN
IF @.PasswordCount = @.PasswordHistory
BEGIN
UPDATE tblHistoryPasswords
SET Password = @.NewPassword, CreatedDateTime=GetDate()
WHERE CURRENT OF Password_Cursor
END
IF @.PasswordCount > @.PasswordHistory
BEGIN
DELETE FROM tblHistoryPasswords
WHERE CURRENT OF Password_Cursor
END
FETCH NEXT FROM Password_Cursor
INTO @.Password
END
END
CLOSE Password_Cursor
DEALLOCATE Password_Cursor
IF @.DuplicateFlag=1
BEGIN
--raise error
SET @.ErrorMessage = "IRIMS Admin DB: User password has to
be different from last " + CAST(@.PasswordHistory AS varchar
(2)) + " passwords."
RaisError (@.ErrorMessage,16,1)
RETURN 1
END
ELSE
IF @.PasswordCount < @.PasswordHistory OR @.PasswordHistory
= 0
INSERT INTO tblHistoryPasswords (UserRef,Password)
VALUES (@.UserID,@.NewPassword)
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi Jamie,
Thanks for your feedback. Providing definite problem and detailed
description of the problem helps us troubleshoot the issue more
efficiently. As I understand, the SQL Server version is SQL Server 2000 Sp1
and you have a merge replication with 2 machines. One is as publication and
distribution, the other is as pull subscription. Do you mean the error
occurs when you configure the subscription? In other words, you did not
finish setting up your subscription, or was it that applying a snapshot to
a subscriber failed with this error?
What is your type of your pull subscription, named subscription or
anonymous subscription? You can check it in the General tab in the property
dialog of the subscription.
What is the kind of snapshot, FTP or UNC? You can check it in the Snapshot
file location tab in the property dialog of the subscription.
Based on my research, this can occur if the publication includes a stored
procedure that references fields that do not exist in the underlying table.
Please check to see who the owner of the stored procedure is and who the
underlying tables tblsystemvariables and tblHistoryPasswords are. If they
are different, please specifying the owner of the underlying table in the
stored procedure. Please try to run the snapshot agent for the publication
by right-clicking it and then clicking Start on the shortcut menu.
Please let me know if this solves your problem, or if you would like
further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||work around...I am only merging the tables...the sp are
not neccessary on this database...merging the tables seem
to be working fine....
Quick question
when setting up the merge part it ask if you want to
do vertical or horizontal, is it best practices to select
both or just one of them..if one of them, which one?|||Hi Jamie,
Thanks for your feedback. I am glad to hear that this problem has been
resolved.
Regarding your further question, horizontal and vertical enable you to
create partitions of data to be published. The selection of them depends on
you requirements. By filtering the published data, you can:
1. Minimize the amount of data sent over the network.
2. Reduce the amount of storage space required at the Subscriber.
3. Customize publications and applications based on individual Subscriber
requirements.
4. Avoid or reduce conflicts because the different data partitions can be
sent to different Subscribers (no two Subscribers will be updating the same
data values).
Row and column filters can be used with snapshot, transactional, and merge
publications. Row filters use the WHERE clause of a SQL statement and
restrict the rows included in a publication based on specific criteria.
Column filters restrict the columns that are included in a publication.
For more information regarding this issue, please refer to the following
articles on SQL Server Books Online.
Topic:" Filtering Published Data"
Topic:" Filter Data"
Thank for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

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 in Snapshot publication

Hi,
I built a database replication between 2 SQL 2000 servers in our domain
(both with SP3). I used snapshot publication and it worked for a few days.
But one day the Distribution Agent task failed with the following,
Error message:
Line 95: Incorrect syntax near 'DROP TABLE '.
Error details:
Line 95: Incorrect syntax near 'DROP TABLE '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Line 102: Incorrect syntax near 'SELECT * INTO '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Please suggest
Thanks
Johnny H.
can you script out the schema of the articles you are replicating and post
them here along with your publication script?
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
"Johnny Hu" <Johnny Hu@.discussions.microsoft.com> wrote in message
news:A5490840-428B-4025-B3DD-80536F81DE5F@.microsoft.com...
> Hi,
> I built a database replication between 2 SQL 2000 servers in our domain
> (both with SP3). I used snapshot publication and it worked for a few
days.
> But one day the Distribution Agent task failed with the following,
> Error message:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> Error details:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Line 102: Incorrect syntax near 'SELECT * INTO '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Please suggest
> Thanks
> Johnny H.

Replication Error after Upgrade (7.0-2000)

Scenario: 3 servers - one Subscriber, one Distributor, one Publisher. Transactional replication; immediate updating subscribers disallowed. Each article in its own publication... independent agents.
Subscriber & Publisher = SQL 7.0
Distributor recently upgraded to 2000, SP3a.
Issue: new publications created at publisher fail to initialize snapshot agent properly. Error: "Another snapshot agent for the publication is running". Agent then fails after total retries (default is 10... does not matter what I set it to).
All existing publications work fine; all existing subscriptions work fine.
So what's the deal?
It appears that once you upgrade the Distributor(s), you have to upgrade the Publisher(s) immediately thereafter if you wish to create new publications. I have not seen that documented however... anyone know?
Thanks!
X
This is documented somewhat. Check out
http://msdn.microsoft.com/library/de...grade_45ir.asp
It doesn't say do this immediately though.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Scenario: 3 servers - one Subscriber, one Distributor, one Publisher.
Transactional replication; immediate updating subscribers disallowed. Each
article in its own publication... independent agents.
> Subscriber & Publisher = SQL 7.0
> Distributor recently upgraded to 2000, SP3a.
> Issue: new publications created at publisher fail to initialize snapshot
agent properly. Error: "Another snapshot agent for the publication is
running". Agent then fails after total retries (default is 10... does not
matter what I set it to).
> All existing publications work fine; all existing subscriptions work fine.
> So what's the deal?
> It appears that once you upgrade the Distributor(s), you have to upgrade
the Publisher(s) immediately thereafter if you wish to create new
publications. I have not seen that documented however... anyone know?
> Thanks!
> X
|||Thanks for the response Hilary.
I read that article; it doesn't specify that publishers MUST be upgraded to 2000 once the Distributor has been upgraded. That's the issue here: we have an upgraded Distributor, but we cannot upgrade the Publisher for some time. In the meantime, we need to
create new publications. We are unable to do so however, due to the error I outlined. It APPEARS that once you upgrade your Distributor, you have to upgrade your Publisher before you can create new publications. That doesn't seem right, but I have yet to
find evidence of its validity.
FWIW: one of the DBAs here reported the same error after applying SPs/hotfixes on the Distributor w/o applying same to Publisher.
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||FYI: the following URL shows the configuration I outlined as a 'valid replication topology' http://msdn.microsoft.com/library/de...limpl_4joy.asp
However, it doe not indicate that new publications created on the 7.0 Publisher w/ 2k Distributor and 7.0 Subscriber(s) will fail. On the contrary - it leads one to believe that the new publications will be in 70 compatability mode, as the replication top
ology defaults to the lowest level in a mixed environment.
Odd then, that I get this error. (btw, we have recycled the servers)
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||Let me try to repro this tonight.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> Thanks for the response Hilary.
> I read that article; it doesn't specify that publishers MUST be upgraded
to 2000 once the Distributor has been upgraded. That's the issue here: we
have an upgraded Distributor, but we cannot upgrade the Publisher for some
time. In the meantime, we need to create new publications. We are unable to
do so however, due to the error I outlined. It APPEARS that once you upgrade
your Distributor, you have to upgrade your Publisher before you can create
new publications. That doesn't seem right, but I have yet to find evidence
of its validity.
> FWIW: one of the DBAs here reported the same error after applying
SPs/hotfixes on the Distributor w/o applying same to Publisher.[vbcol=seagreen]
> "Hilary Cotter" wrote:
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Each[vbcol=seagreen]
snapshot[vbcol=seagreen]
not[vbcol=seagreen]
fine.[vbcol=seagreen]
upgrade[vbcol=seagreen]
|||Were you able to reproduce the error?
Many thanks!!
X
"Hilary Cotter" wrote:

> Let me try to repro this tonight.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> to 2000 once the Distributor has been upgraded. That's the issue here: we
> have an upgraded Distributor, but we cannot upgrade the Publisher for some
> time. In the meantime, we need to create new publications. We are unable to
> do so however, due to the error I outlined. It APPEARS that once you upgrade
> your Distributor, you have to upgrade your Publisher before you can create
> new publications. That doesn't seem right, but I have yet to find evidence
> of its validity.
> SPs/hotfixes on the Distributor w/o applying same to Publisher.
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> Each
> snapshot
> not
> fine.
> upgrade
>
>
|||No, I was unable to get the error. What error did you get, and what service
packs were you running?
Where you using an Administrative Link password?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:C359EA3E-0ED4-417C-B1A9-F4F015253BD0@.microsoft.com...[vbcol=seagreen]
> Were you able to reproduce the error?
> Many thanks!!
> X
>
> "Hilary Cotter" wrote:
upgraded[vbcol=seagreen]
we[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
upgrade[vbcol=seagreen]
create[vbcol=seagreen]
evidence[vbcol=seagreen]
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Publisher.[vbcol=seagreen]
disallowed.[vbcol=seagreen]
is[vbcol=seagreen]
does[vbcol=seagreen]
work[vbcol=seagreen]
know?[vbcol=seagreen]
sql

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 Error

I have been trying to setup SQL Server Replication between two SQL Server
2000 SP3 servers.
I have configured the Distributer and publisher on the same SQL Server. I
modified the Publisher Snapshot folder to a non default ie.
\\server\is\Replication\TLIS. Note \\server is a file server within our
network
The SQL Agent Service on both SQL Servers run under a domain account; with
admin rights on the SQL Servers and full control on the Publisher Snapshot
folder.
Questions/Errors:
When creating the subscription i get the following message; which is
probably normal?
SQL Server Enterprise Manager successfully created a pull subscription to
publication 'Merge_Replicate'.
The initial snapshot for publication 'Merge_Replicate' is not available.
This subscription will be initialized after the snapshot is available.
Errors:
I am receiving the following errors when replication executes. The database
objects I am trying to replicate are not replicating.
event log:
Unable to start a DCOM Server: {08B0B2D9-3FB3-11D3-A4DE-00C04F610189}. The
error: "The system cannot find the path specified. "
Happened while starting this command:
C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe -Embedding
Agent logs:
Executed as user: NFPOWER_DOMAIN\SQLboss. Replication-Replication Snapshot
Subsystem:
agent NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1 failed. The system
cannot find the path specified. NOTE: The step was retried the requested
number of times (10) without succeeding. The step failed. [SQLSTATE 42000]
(Error 14151). The step failed.
We are looking at using SQL Replication to support an application we are
currently working on. I would really appreciate your help in resolving this
issue. Thanks in Advance.
Hi Gary, can you try to see if you can manually start
C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe on the distributor machine?
-Raymond
"Gary Murphy" wrote:

> I have been trying to setup SQL Server Replication between two SQL Server
> 2000 SP3 servers.
> I have configured the Distributer and publisher on the same SQL Server. I
> modified the Publisher Snapshot folder to a non default ie.
> \\server\is\Replication\TLIS. Note \\server is a file server within our
> network
> The SQL Agent Service on both SQL Servers run under a domain account; with
> admin rights on the SQL Servers and full control on the Publisher Snapshot
> folder.
> Questions/Errors:
> When creating the subscription i get the following message; which is
> probably normal?
> SQL Server Enterprise Manager successfully created a pull subscription to
> publication 'Merge_Replicate'.
> The initial snapshot for publication 'Merge_Replicate' is not available.
> This subscription will be initialized after the snapshot is available.
> Errors:
> I am receiving the following errors when replication executes. The database
> objects I am trying to replicate are not replicating.
> event log:
> Unable to start a DCOM Server: {08B0B2D9-3FB3-11D3-A4DE-00C04F610189}. The
> error: "The system cannot find the path specified. "
> Happened while starting this command:
> C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe -Embedding
> Agent logs:
> Executed as user: NFPOWER_DOMAIN\SQLboss. Replication-Replication Snapshot
> Subsystem:
> agent NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1 failed. The system
> cannot find the path specified. NOTE: The step was retried the requested
> number of times (10) without succeeding. The step failed. [SQLSTATE 42000]
> (Error 14151). The step failed.
> We are looking at using SQL Replication to support an application we are
> currently working on. I would really appreciate your help in resolving this
> issue. Thanks in Advance.
>
|||HI Raymond,
I tried as you suggested. I logged into the server using the same domain
account that the SQL Agent runs under.
I am assuming you manually start snapshot.exe by just running the
executasble as I tried below:
C:\Program Files\Microsoft SQL Server\80\COM>snapshot.exe -Embedding
C:\Program Files\Microsoft SQL Server\80\COM>echo %errorlevel%
0
C:\Program Files\Microsoft SQL Server\80\COM>replmerg.exe -Embedding
C:\Program Files\Microsoft SQL Server\80\COM>echo %errorlevel%
0
When I setup replication two jobs are setup within sql server server. A
snapshot and merge job. Within each job there are three steps. The first
step of each job (Snapshot Agent startup message, Merge Agent startup
message) starts successfully.
The second step of each job (Run Agent) fails with "The system cannot find
the path specified" error.
1) Job 1 is NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1
steps :
- Snapshot Agent startup message:
sp_MSadd_snapshot_history @.perfmon_increment = 0, @.agent_id = 1,
@.runstatus = 1, @.comments = 'Starting agent.'
- Run Agent:
-Publisher [NPBOSSSQL4\NPBOSSSQL4] -PublisherDB [INTRANETD] -Distributor
[NPBOSSSQL4\NPBOSSSQL4] -Publication [Merge_Replicate] -ReplicationType 2
-DistributorSecurityMode 1
-Detect nonlogged agent shutdown:
sp_MSdetect_nonlogged_shutdown @.subsystem = 'Snapshot', @.agent_id = 1
Fails with error: Executed as user: NFPOWER_DOMAIN\SQLboss.
Replication-Replication Snapshot Subsystem: agent
NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1 failed. The system cannot
find the path specified. NOTE: The step was retried the requested number of
times (10) without succeeding. The step failed. [SQLSTATE 42000] (Error
14151). The step failed.
2) Job 2 is:
NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-NPBOSSSQL2\NPBOSSSQL2-2
steps:
- Merge Agent startup message:
sp_MSadd_merge_history @.perfmon_increment = 0, @.agent_id = 2, @.runstatus =
1, @.comments = 'Starting agent.'
- run agent:
-Publisher [NPBOSSSQL4\NPBOSSSQL4] -PublisherDB [INTRANETD] -Publication
[Merge_Replicate] -Subscriber [NPBOSSSQL2\NPBOSSSQL2] -SubscriberDB
[Northwind] -Distributor [NPBOSSSQL4\NPBOSSSQL4] -DistributorSecurityMode 1
- Detect nonlogged agent shutdown:
sp_MSdetect_nonlogged_shutdown @.subsystem = 'Merge', @.agent_id = 2
Fails with error: Executed as user: NFPOWER_DOMAIN\SQLboss.
Replication-agentclassname: agent
NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-NPBOSSSQL2\NPBOSSSQL2-2
failed. The system cannot find the path specified. NOTE: The step was
retried the requested number of times (10) without succeeding. The step
failed. [SQLSTATE 42000] (Error 14151). The step failed.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Gary, can you try to see if you can manually start
> C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe on the distributor machine?
> -Raymond
> "Gary Murphy" wrote:
|||Gary, I have the vague suspicion that the 8.3 path that you listed below is
bad. As such it is important that you try running:
C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe
driectly instead of navigating to the 80\com directory and then run the
executables. Once we have determined that the 8.3 path is indeed bad (or
not), we can then try something else accordingly.
-Raymond
"Gary Murphy" wrote:
[vbcol=seagreen]
> HI Raymond,
> I tried as you suggested. I logged into the server using the same domain
> account that the SQL Agent runs under.
> I am assuming you manually start snapshot.exe by just running the
> executasble as I tried below:
> C:\Program Files\Microsoft SQL Server\80\COM>snapshot.exe -Embedding
> C:\Program Files\Microsoft SQL Server\80\COM>echo %errorlevel%
> 0
> C:\Program Files\Microsoft SQL Server\80\COM>replmerg.exe -Embedding
> C:\Program Files\Microsoft SQL Server\80\COM>echo %errorlevel%
> 0
> When I setup replication two jobs are setup within sql server server. A
> snapshot and merge job. Within each job there are three steps. The first
> step of each job (Snapshot Agent startup message, Merge Agent startup
> message) starts successfully.
> The second step of each job (Run Agent) fails with "The system cannot find
> the path specified" error.
> 1) Job 1 is NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1
> steps :
> - Snapshot Agent startup message:
> sp_MSadd_snapshot_history @.perfmon_increment = 0, @.agent_id = 1,
> @.runstatus = 1, @.comments = 'Starting agent.'
> - Run Agent:
> -Publisher [NPBOSSSQL4\NPBOSSSQL4] -PublisherDB [INTRANETD] -Distributor
> [NPBOSSSQL4\NPBOSSSQL4] -Publication [Merge_Replicate] -ReplicationType 2
> -DistributorSecurityMode 1
> -Detect nonlogged agent shutdown:
> sp_MSdetect_nonlogged_shutdown @.subsystem = 'Snapshot', @.agent_id = 1
> Fails with error: Executed as user: NFPOWER_DOMAIN\SQLboss.
> Replication-Replication Snapshot Subsystem: agent
> NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-1 failed. The system cannot
> find the path specified. NOTE: The step was retried the requested number of
> times (10) without succeeding. The step failed. [SQLSTATE 42000] (Error
> 14151). The step failed.
> 2) Job 2 is:
> NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-NPBOSSSQL2\NPBOSSSQL2-2
> steps:
> - Merge Agent startup message:
> sp_MSadd_merge_history @.perfmon_increment = 0, @.agent_id = 2, @.runstatus =
> 1, @.comments = 'Starting agent.'
> - run agent:
> -Publisher [NPBOSSSQL4\NPBOSSSQL4] -PublisherDB [INTRANETD] -Publication
> [Merge_Replicate] -Subscriber [NPBOSSSQL2\NPBOSSSQL2] -SubscriberDB
> [Northwind] -Distributor [NPBOSSSQL4\NPBOSSSQL4] -DistributorSecurityMode 1
> - Detect nonlogged agent shutdown:
> sp_MSdetect_nonlogged_shutdown @.subsystem = 'Merge', @.agent_id = 2
> Fails with error: Executed as user: NFPOWER_DOMAIN\SQLboss.
> Replication-agentclassname: agent
> NPBOSSSQL4\NPBOSSSQL4-INTRANETD-Merge_Replicate-NPBOSSSQL2\NPBOSSSQL2-2
> failed. The system cannot find the path specified. NOTE: The step was
> retried the requested number of times (10) without succeeding. The step
> failed. [SQLSTATE 42000] (Error 14151). The step failed.
> "Raymond Mak [MSFT]" wrote:
|||Hi Raymond,
Your suspicion is correct the path is bad.
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.
C:\>C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe -Embedding
The system cannot find the path specified.
I changed to the path specified as you will see below it's the incorrect path.
C:\>cd C:\PROGRA~1\MICROS~3
C:\PROGRA~1\MICROS~3>dir
Volume in drive C has no label.
Volume Serial Number is E47F-3E92
Directory of C:\PROGRA~1\MICROS~3
2004/06/29 12:22a <DIR> .
2004/06/29 12:22a <DIR> ..
2004/06/29 12:22a <DIR> Bin
2004/06/29 12:22a <DIR> Data
2004/02/24 12:43p 40,158 DeIsL1.isu
2004/06/29 12:22a <DIR> Help
2004/06/29 12:22a <DIR> Help80
2000/08/05 11:22p 47,815 readme.txt
2004/06/29 12:22a <DIR> Samples
2002/10/20 03:13p 49,152 uninst.dll
2004/02/24 01:10p 182,606 uninst.isu
4 File(s) 319,731 bytes
7 Dir(s) 13,039,251,456 bytes free
I tried setting to other combinations: C:\PROGRA~1\MICROS~1,
C:\PROGRA~1\MICROS~2, C:\PROGRA~1\MICROS~4 and all these give me the wrong
folder as well.
How do I get the replication tools to point at the correct folders i.e
C:\Program Files\Microsoft SQL Server\80\COM.
I searched the registry for snapshot.exe and replmerg.exe and some entries
have the correct path while others don't. It's inconsistent within the
registry.
Thanks Raymond.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Gary, I have the vague suspicion that the 8.3 path that you listed below is
> bad. As such it is important that you try running:
> C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe
> driectly instead of navigating to the 80\com directory and then run the
> executables. Once we have determined that the 8.3 path is indeed bad (or
> not), we can then try something else accordingly.
> -Raymond
> "Gary Murphy" wrote:
|||Hi Gary,
You can repair the path of the replication COM servers by doing the following:
c:\Program Files\Microsoft SQL Server\80\Com>snapshot -RegServer
c:\Program Files\Microsoft SQL Server\80\Com>replmerg -RegServer
c:\Program Files\Microsoft SQL Server\80\Com>distrib -RegServer
c:\Program Files\Microsoft SQL Server\80\Com>logread -RegServer
c:\Program Files\Microsoft SQL Server\80\Com>qrdrsvc -RegServer
It seems kind of strange that the 8.3 path (btw, you can do a dir /x to find
out what the current 8.3 path is) has changed since you first installed SQL
Server. It would be great if you can let us know if you can recall anything
unusual that happened recently on your machine that would have caused such a
change. (accidental renaming of Program Files, Microsoft SQL Server in
explorer ?)
-Raymond
"Gary Murphy" wrote:
[vbcol=seagreen]
> Hi Raymond,
> Your suspicion is correct the path is bad.
> Microsoft Windows 2000 [Version 5.00.2195]
> (C) Copyright 1985-2000 Microsoft Corp.
> C:\>C:\PROGRA~1\MICROS~3\80\COM\snapshot.exe -Embedding
> The system cannot find the path specified.
> I changed to the path specified as you will see below it's the incorrect path.
> C:\>cd C:\PROGRA~1\MICROS~3
> C:\PROGRA~1\MICROS~3>dir
> Volume in drive C has no label.
> Volume Serial Number is E47F-3E92
> Directory of C:\PROGRA~1\MICROS~3
> 2004/06/29 12:22a <DIR> .
> 2004/06/29 12:22a <DIR> ..
> 2004/06/29 12:22a <DIR> Bin
> 2004/06/29 12:22a <DIR> Data
> 2004/02/24 12:43p 40,158 DeIsL1.isu
> 2004/06/29 12:22a <DIR> Help
> 2004/06/29 12:22a <DIR> Help80
> 2000/08/05 11:22p 47,815 readme.txt
> 2004/06/29 12:22a <DIR> Samples
> 2002/10/20 03:13p 49,152 uninst.dll
> 2004/02/24 01:10p 182,606 uninst.isu
> 4 File(s) 319,731 bytes
> 7 Dir(s) 13,039,251,456 bytes free
> I tried setting to other combinations: C:\PROGRA~1\MICROS~1,
> C:\PROGRA~1\MICROS~2, C:\PROGRA~1\MICROS~4 and all these give me the wrong
> folder as well.
> How do I get the replication tools to point at the correct folders i.e
> C:\Program Files\Microsoft SQL Server\80\COM.
> I searched the registry for snapshot.exe and replmerg.exe and some entries
> have the correct path while others don't. It's inconsistent within the
> registry.
> Thanks Raymond.
> "Raymond Mak [MSFT]" wrote:
|||Hi Raymond,
I'm not in the office today (Wednesday) so I will try your fixes on Thursday
and let you know the result. We have a number of SQL Server's that I can
check the 8.3 path on, just for curiosity.
As to why the path has changed I'm not sure. Our SQL Server's are
restricted to admin staff and DBA's so the chances of a folder getting
renamed is low. I am seeing the same type of error on the publisher and
subscriber SQL Servers (two separate servers). We have recently applied MS
updates related to security patches but no SQL Server patches. I will check
with the guys back at the office when I get back to work on Thursday to see
if they have any comments on this issue.
Thanks.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Gary,
> You can repair the path of the replication COM servers by doing the following:
> c:\Program Files\Microsoft SQL Server\80\Com>snapshot -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>replmerg -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>distrib -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>logread -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>qrdrsvc -RegServer
> It seems kind of strange that the 8.3 path (btw, you can do a dir /x to find
> out what the current 8.3 path is) has changed since you first installed SQL
> Server. It would be great if you can let us know if you can recall anything
> unusual that happened recently on your machine that would have caused such a
> change. (accidental renaming of Program Files, Microsoft SQL Server in
> explorer ?)
> -Raymond
> "Gary Murphy" wrote:
|||Hi Raymond,
I did the dir /x on the servers where I am having the problem. Here is the
result:
As you will see MS SQL Server has a 8.3 path called MI6841~1. Not sure how
it got this name.
I checked 5 other sql servers that we have and the 8.3 path is either
micros~3 or micros~3. I assume these are okay.
2002/01/31 08:08a <DIR> ACCESS~1 Accessories
2004/10/08 04:01p <DIR> COMMON~1 Common Files
2004/06/29 12:21a <DIR> Compaq
2002/01/31 11:40a <DIR> COMPLU~1 ComPlus Applications
2004/07/12 02:00p <DIR> COMPUT~1 ComputerAssociates
2004/06/29 12:21a <DIR> DATADI~1 DataDirect
2004/11/06 09:14a <DIR> INTERN~1 Internet Explorer
2004/06/29 12:22a <DIR> MICROS~3 Microsoft Analysis
Servi
es
2002/01/31 08:08a <DIR> MICROS~2 microsoft frontpage
2004/06/29 12:22a <DIR> MICROS~4 Microsoft Operations
Man
ger 2000
2002/01/31 08:09a <DIR> MICROS~1 Microsoft Script
Debugge
2004/06/29 12:22a <DIR> MI6841~1 Microsoft SQL Server
2004/04/24 11:03a <DIR> NETMEE~1 NetMeeting
2004/10/05 02:34p <DIR> Oracle
2004/11/06 11:33a <DIR> OUTLOO~1 Outlook Express
2004/06/29 12:22a <DIR> RESOUR~1 Resource Kit
2004/12/08 02:54p <DIR> Robocopy
2003/08/03 07:48a <DIR> WINDOW~3 Windows Media Player
2002/02/01 10:50a <DIR> WINDOW~1 Windows NT
0 File(s) 0 bytes
21 Dir(s) 13,038,665,728 bytes free
I ran the executables using the -RegServer command as you instructed and now
replication is working. Thanks alot.
The changes made by running the -RegServer command are permanent? I
wouldn't lose anything on a reboot?
When I was registering executable C:\Program Files\Microsoft SQL
Server\80\COM>qrdrsvc.exe -regserver another screen appeared:
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Server: NPBOSSSQL4, Database distribution : ODBC Error:SQL Server does not ex
or access denied.
Unable to connect to Local Distributor
Queue Reader aborting
The process finished. Use CTRL+C to close this window.
Replication seems to be working okay now; should I be concerned about "queue
reader aborting"?
I was talking to another DBA here at our office querying about the 8.3
issue. The two servers that I was having replication trouble with were
involved in a test disaster recovery scenerio in 2004. Basically a
production server restore (using tapes) was restored onto a development
server (server I am having replication issues with). After the disaster
restores and tests the development server was restored again back to its
original development state. So it's possible that the 8.3 path changed
during these restores.
Gary.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Gary,
> You can repair the path of the replication COM servers by doing the following:
> c:\Program Files\Microsoft SQL Server\80\Com>snapshot -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>replmerg -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>distrib -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>logread -RegServer
> c:\Program Files\Microsoft SQL Server\80\Com>qrdrsvc -RegServer
> It seems kind of strange that the 8.3 path (btw, you can do a dir /x to find
> out what the current 8.3 path is) has changed since you first installed SQL
> Server. It would be great if you can let us know if you can recall anything
> unusual that happened recently on your machine that would have caused such a
> change. (accidental renaming of Program Files, Microsoft SQL Server in
> explorer ?)
> -Raymond
> "Gary Murphy" wrote:
|||Hi Gary,
The output from qrdrsvc.exe doesn't look right to me although you shouldn't
be affected if you don't have any transactional queued updating subscribers.
The 8.3 filename for "Microsoft SQL Server" does look kind of strange and you
may want to run chkdsk to see if everything is in order. Have you tried
installing SQL2005 on the machine recently? That might sort of explain it but
I am not exactly sure how.
-Raymond
"Gary Murphy" wrote:
[vbcol=seagreen]
> Hi Raymond,
> I did the dir /x on the servers where I am having the problem. Here is the
> result:
> As you will see MS SQL Server has a 8.3 path called MI6841~1. Not sure how
> it got this name.
> I checked 5 other sql servers that we have and the 8.3 path is either
> micros~3 or micros~3. I assume these are okay.
> 2002/01/31 08:08a <DIR> ACCESS~1 Accessories
> 2004/10/08 04:01p <DIR> COMMON~1 Common Files
> 2004/06/29 12:21a <DIR> Compaq
> 2002/01/31 11:40a <DIR> COMPLU~1 ComPlus Applications
> 2004/07/12 02:00p <DIR> COMPUT~1 ComputerAssociates
> 2004/06/29 12:21a <DIR> DATADI~1 DataDirect
> 2004/11/06 09:14a <DIR> INTERN~1 Internet Explorer
> 2004/06/29 12:22a <DIR> MICROS~3 Microsoft Analysis
> Servi
> es
> 2002/01/31 08:08a <DIR> MICROS~2 microsoft frontpage
> 2004/06/29 12:22a <DIR> MICROS~4 Microsoft Operations
> Man
> ger 2000
> 2002/01/31 08:09a <DIR> MICROS~1 Microsoft Script
> Debugge
> 2004/06/29 12:22a <DIR> MI6841~1 Microsoft SQL Server
> 2004/04/24 11:03a <DIR> NETMEE~1 NetMeeting
> 2004/10/05 02:34p <DIR> Oracle
> 2004/11/06 11:33a <DIR> OUTLOO~1 Outlook Express
> 2004/06/29 12:22a <DIR> RESOUR~1 Resource Kit
> 2004/12/08 02:54p <DIR> Robocopy
> 2003/08/03 07:48a <DIR> WINDOW~3 Windows Media Player
> 2002/02/01 10:50a <DIR> WINDOW~1 Windows NT
> 0 File(s) 0 bytes
> 21 Dir(s) 13,038,665,728 bytes free
> I ran the executables using the -RegServer command as you instructed and now
> replication is working. Thanks alot.
> The changes made by running the -RegServer command are permanent? I
> wouldn't lose anything on a reboot?
> When I was registering executable C:\Program Files\Microsoft SQL
> Server\80\COM>qrdrsvc.exe -regserver another screen appeared:
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Server: NPBOSSSQL4, Database distribution : ODBC Error:SQL Server does not ex
> or access denied.
> Unable to connect to Local Distributor
> Queue Reader aborting
> The process finished. Use CTRL+C to close this window.
> Replication seems to be working okay now; should I be concerned about "queue
> reader aborting"?
> I was talking to another DBA here at our office querying about the 8.3
> issue. The two servers that I was having replication trouble with were
> involved in a test disaster recovery scenerio in 2004. Basically a
> production server restore (using tapes) was restored onto a development
> server (server I am having replication issues with). After the disaster
> restores and tests the development server was restored again back to its
> original development state. So it's possible that the 8.3 path changed
> during these restores.
> Gary.
> "Raymond Mak [MSFT]" wrote:

Replication Deadlocks

Hi All,

I need to get some assistance with some replication deadlocks im getting.

We have two servers, which push information between each other, which I have a hunch could be a cause.

Basically I keep getting the 'Agent Retry' Alert from SQL:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When doing some googleing, I found the Trace Flags and setup flags 1204 and 1205, but they dont give me any additional information.

I've looked in the error log, but all I get is this:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Does anyone have any suggestions on what I can try to find more information on the cause?

Cheers

This may be a genuine deadlock. Did it work when you restart the replication agent affected?|||If you're using SQL 2005, then use trace flag 1222, it will print out more detailed information to the errorlog. The other alternative is to capture a profiler trace using the Deadlock events.|||Hi, I've restarted and recreated the replication, but every now and again it comes back.

It's not a mega issue, but I want to resolve it.

We are running SQL 2K, sadly wont be going to 2K5 until at least SP1 to allow plenty of time to watch what's happenning with it.|||I'm doing that now, but I've already had a couple of occurances this morning which have not shown up.

Will check it over again. Thanks for the tip.|||Ok, then you can use 1204 trace flag instead. If you're restarting your server frequently, then you can just add it to the server startup trace flag section.

When you find the issue, and need help parsing it, cut/paste the output here and we can help you figure it out.
|||Thats the problem. I use Traceflag 1204, 1205, but it doesent give me any more information, than what I've already posted.

If i look through the log file, I can see the time and date where I run a DBCC TraceOn(1204, 1205), but then afterwards, when I get a replication retry/deadlock, I only get the message in the initial post...

|||

How are you enabling the trace flags? And are you enabling the trace flags on the correct machine? Try enabling them on both the publisher and subscriber machine.

You can enable them in one of two ways:

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

|||Thanks for your help so far...Much appreciated.

I was enabling them via Query Analyser. I've just re-entered your statement, so will see what I can find out on the next agent retry.

Thanks

Steve|||

Right,

I've ran the command as above, which I can see in the error log, but anything after that fails to show any more information.

This is all I get from the log:

2005-09-21 16:12:30.98 spid4 -

2005-09-21 16:16:07.60 spid188 Error: 14152, Severity: 10, State: 1

2005-09-21 16:16:07.60 spid188 Replication-Replication Distribution Subsystem: agent <SNIP>SQ01-EMS-<SNIP>SQ02\UK-183 scheduled for retry. Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

2005-09-21 16:17:56.21 spid4 -
2005-09-21 16:17:56.21 spid4 Starting deadlock search 41806

2005-09-21 16:17:56.21 spid4 Target Resource Owner:

Do the traceflags expire after a certain period of time or do the remove themselves when you close Query Analyser?

Thanks

|||

I'm not sure why the traceflag isn't working. Please use profiler instead then, use the Lock:Deadlock and Lock:Deadlock Chain events.

|||

Bringing this one back up...

I'm still having this issue. Profiler and TraceFlags give me nothing, so I've done some digging into the replications themselves.

I have a hunch that the table is being locked when data is being pushed to it, it cant because it's locked.

Is there any way I can check the type of locking happenning ?

|||Hi Steve,
I hope you enabled the trace flags with the options that Greg mentioned.

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

If you used option 2, you may need to restart your server. Also add -T 3605 if you used option 2.

And as a sidenote, would it be a case the replication agent is trying to select/insert rows while an external process (user query) is also trying to update the same table? then it could be understandable. Otherwise, without the deadlock graph, it will hard to understand what could be going on.

|||

Hi Mahesh,

I have indeed put those flags in the startup parameters section.

I have thought about what you suggested with the agent accessing whilt another process is working...

I've done a diag. of our replication: http://www.aoqz41.dsl.pipex.com/Replication.jpg

The DB Circled is the one causing problems, with the replication from 'EMS Claims Payments'. I'm wondering if this is a 'Real Deadlock' situation?

Steve

|||But after adding the trace flags to the startup paramters, did you restart the SQL server?

And looking at your diagram, I assume the line from EMS (EMS Claims Payments) is the replication agent writing data. I see only one more arrow into the UKResManClock db (circled one). Does this mean that this also writing data (through replication or user DML)? If so, that can be another thing to look at. Also would the same tables be updated by these two lines?

Also just FYI: Merge replication does not recommend a central subscriber topology. It has its problems.

Replication Deadlocks

Hi All,

I need to get some assistance with some replication deadlocks im getting.

We have two servers, which push information between each other, which I have a hunch could be a cause.

Basically I keep getting the 'Agent Retry' Alert from SQL:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When doing some googleing, I found the Trace Flags and setup flags 1204 and 1205, but they dont give me any additional information.

I've looked in the error log, but all I get is this:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Does anyone have any suggestions on what I can try to find more information on the cause?

Cheers

This may be a genuine deadlock. Did it work when you restart the replication agent affected?|||If you're using SQL 2005, then use trace flag 1222, it will print out more detailed information to the errorlog. The other alternative is to capture a profiler trace using the Deadlock events.|||Hi, I've restarted and recreated the replication, but every now and again it comes back.

It's not a mega issue, but I want to resolve it.

We are running SQL 2K, sadly wont be going to 2K5 until at least SP1 to allow plenty of time to watch what's happenning with it.|||I'm doing that now, but I've already had a couple of occurances this morning which have not shown up.

Will check it over again. Thanks for the tip.|||Ok, then you can use 1204 trace flag instead. If you're restarting your server frequently, then you can just add it to the server startup trace flag section.

When you find the issue, and need help parsing it, cut/paste the output here and we can help you figure it out.
|||Thats the problem. I use Traceflag 1204, 1205, but it doesent give me any more information, than what I've already posted.

If i look through the log file, I can see the time and date where I run a DBCC TraceOn(1204, 1205), but then afterwards, when I get a replication retry/deadlock, I only get the message in the initial post...

|||

How are you enabling the trace flags? And are you enabling the trace flags on the correct machine? Try enabling them on both the publisher and subscriber machine.

You can enable them in one of two ways:

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

|||Thanks for your help so far...Much appreciated.

I was enabling them via Query Analyser. I've just re-entered your statement, so will see what I can find out on the next agent retry.

Thanks

Steve|||

Right,

I've ran the command as above, which I can see in the error log, but anything after that fails to show any more information.

This is all I get from the log:

2005-09-21 16:12:30.98 spid4 -

2005-09-21 16:16:07.60 spid188 Error: 14152, Severity: 10, State: 1

2005-09-21 16:16:07.60 spid188 Replication-Replication Distribution Subsystem: agent <SNIP>SQ01-EMS-<SNIP>SQ02\UK-183 scheduled for retry. Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

2005-09-21 16:17:56.21 spid4 -
2005-09-21 16:17:56.21 spid4 Starting deadlock search 41806

2005-09-21 16:17:56.21 spid4 Target Resource Owner:

Do the traceflags expire after a certain period of time or do the remove themselves when you close Query Analyser?

Thanks

|||

I'm not sure why the traceflag isn't working. Please use profiler instead then, use the Lock:Deadlock and Lock:Deadlock Chain events.

|||

Bringing this one back up...

I'm still having this issue. Profiler and TraceFlags give me nothing, so I've done some digging into the replications themselves.

I have a hunch that the table is being locked when data is being pushed to it, it cant because it's locked.

Is there any way I can check the type of locking happenning ?

|||Hi Steve,
I hope you enabled the trace flags with the options that Greg mentioned.

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

If you used option 2, you may need to restart your server. Also add -T 3605 if you used option 2.

And as a sidenote, would it be a case the replication agent is trying to select/insert rows while an external process (user query) is also trying to update the same table? then it could be understandable. Otherwise, without the deadlock graph, it will hard to understand what could be going on.

|||

Hi Mahesh,

I have indeed put those flags in the startup parameters section.

I have thought about what you suggested with the agent accessing whilt another process is working...

I've done a diag. of our replication: http://www.aoqz41.dsl.pipex.com/Replication.jpg

The DB Circled is the one causing problems, with the replication from 'EMS Claims Payments'. I'm wondering if this is a 'Real Deadlock' situation?

Steve

|||But after adding the trace flags to the startup paramters, did you restart the SQL server?

And looking at your diagram, I assume the line from EMS (EMS Claims Payments) is the replication agent writing data. I see only one more arrow into the UKResManClock db (circled one). Does this mean that this also writing data (through replication or user DML)? If so, that can be another thing to look at. Also would the same tables be updated by these two lines?

Also just FYI: Merge replication does not recommend a central subscriber topology. It has its problems.

Replication Deadlock

Dear All,
I'm just configured transactional replication.
I have 2 DB Servers, OLTP Server and Reporting Server.
I uses transactional replication to replicate the data from OLTP Server
to Reporting Server.
I configured the replcation to use Push Subcription.
I set the schedule every 3 minutes bcos I need the report to be avaiable
as soon as possible.
Unfortunately, my Reporting Server got deadlock bcos of it.
Did I configure incorrectly?
Pls I need some suggestion for it.
Thanks
Robert Lie
Robert,
perhaps you could use the NOLOCK hint on the reporting server queries.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication conflict at deleting

Hello I have a question.
We have a merge replication between 4 servers and it's working fine but we
had a conflict while deleting a record in a master table wich has the
location and product as a key that says :
"The same row was updated at 'servername1.replication' and deleted at
'servername2.replication'. The resolver chose the update as the winner"
It looks like the same record was updated and deleted in a diferent site,
but first it's imposible because every site touches the products of their
own location, the programs that access the database only inserts, updates or
deletes the records of the location where the user logged belongs. Plus
everytime I delete or update or insert a record in this master table we
insert a transaction record for control.
We had the problem and I can see that the user never updated this master
file because I don't have a transaction for update and they deleted the
record but it never got deleted and the problem is that now I have my master
file with records that are not soppoused to be there anymore.
Is there any other reason why this error comes because as I said they never
updated the record and how can I set replication for the deleted as a winner
in every process.
Thanks in advance
Jennyfer
The conflict resolver will give you enough information to accurately identify
the record that is being updated/deleted.
Before you say it is impossible look at these details and compare them.
If the records match then you need to look at your log files and find out
who is deleting and then who is changing this record.
It sounds like replication has discovered an activity that you were not
aware of.
Jim.
"Jennyfer J Barco" wrote:

> Hello I have a question.
> We have a merge replication between 4 servers and it's working fine but we
> had a conflict while deleting a record in a master table wich has the
> location and product as a key that says :
> "The same row was updated at 'servername1.replication' and deleted at
> 'servername2.replication'. The resolver chose the update as the winner"
> It looks like the same record was updated and deleted in a diferent site,
> but first it's imposible because every site touches the products of their
> own location, the programs that access the database only inserts, updates or
> deletes the records of the location where the user logged belongs. Plus
> everytime I delete or update or insert a record in this master table we
> insert a transaction record for control.
> We had the problem and I can see that the user never updated this master
> file because I don't have a transaction for update and they deleted the
> record but it never got deleted and the problem is that now I have my master
> file with records that are not soppoused to be there anymore.
> Is there any other reason why this error comes because as I said they never
> updated the record and how can I set replication for the deleted as a winner
> in every process.
> Thanks in advance
> Jennyfer
>
>
|||Yes I indentify what is the record being deleted and "update" at the same
time and in my log files I have the transaction for delete but not for
update. But I know that replication discovered some how an activity for this
record.
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:4C1F2708-5F98-402C-B4D5-2950FB356CF4@.microsoft.com...
> The conflict resolver will give you enough information to accurately
identify[vbcol=seagreen]
> the record that is being updated/deleted.
> Before you say it is impossible look at these details and compare them.
> If the records match then you need to look at your log files and find out
> who is deleting and then who is changing this record.
> It sounds like replication has discovered an activity that you were not
> aware of.
> Jim.
>
> "Jennyfer J Barco" wrote:
we[vbcol=seagreen]
site,[vbcol=seagreen]
their[vbcol=seagreen]
updates or[vbcol=seagreen]
master[vbcol=seagreen]
never[vbcol=seagreen]
winner[vbcol=seagreen]

Replication between two servers without db_owner

Hello there
I have destination database which i'm not db_owner on it but guest
The user i'm using to access between two servers is:
1. Source is connected by linked server to destination
2. on the destination none of the servers roles are active for the user i'm
trying to access
3. the user has only access to one of my databases whith permittion of:
db_public, db_ddladmin
, db_datareader, db_datawriter
What else i need to use replication between two sides?
You'll need rights to create tables. There are two system tables which are
necessary and you should be able to precreate these using an account in the
ddl_admin role.
This is a push subscription right? What version of SQL Server is running on
the publisher and subscriber?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uu6vAScGGHA.3864@.tk2msftngp13.phx.gbl...
> Hello there
> I have destination database which i'm not db_owner on it but guest
> The user i'm using to access between two servers is:
> 1. Source is connected by linked server to destination
> 2. on the destination none of the servers roles are active for the user
> i'm trying to access
> 3. the user has only access to one of my databases whith permittion of:
> db_public, db_ddladmin
> , db_datareader, db_datawriter
> What else i need to use replication between two sides?
>
|||Thankes Hilary.
Now the replication succedded on the other side. In fact it gave me db_owner
to do that.
The problem now is that it create new tables what replicated with the
username owner that i used to access the subscriber, instead of entering the
data to my existing databses with dbo owners.
How can i cause the subscriber to transfare data to the original tables with
dbo owners?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e$6nsyfGGHA.1180@.TK2MSFTNGP09.phx.gbl...
> You'll need rights to create tables. There are two system tables which are
> necessary and you should be able to precreate these using an account in
> the ddl_admin role.
> This is a push subscription right? What version of SQL Server is running
> on the publisher and subscriber?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uu6vAScGGHA.3864@.tk2msftngp13.phx.gbl...
>
|||Right click on your publication and select properties, click on the articles
tab, and select the browse button to the right of each table, in the general
tab, specify destination table owner name to be dbo.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23xLlJkCHGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Thankes Hilary.
> Now the replication succedded on the other side. In fact it gave me
> db_owner to do that.
> The problem now is that it create new tables what replicated with the
> username owner that i used to access the subscriber, instead of entering
> the data to my existing databses with dbo owners.
> How can i cause the subscriber to transfare data to the original tables
> with dbo owners?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e$6nsyfGGHA.1180@.TK2MSFTNGP09.phx.gbl...
>
|||Thankes A lot hilary
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6j6N8EHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Right click on your publication and select properties, click on the
> articles tab, and select the browse button to the right of each table, in
> the general tab, specify destination table owner name to be dbo.
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23xLlJkCHGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Replication between SQL 7 and SQL 2000

Hello. I'm new to SQL servers. I'm curious to know if there are any known
issues for SQL replication between SQL 7 and SQL 2000.
I currently have SQL 7 running on NT 4 SP6a. This server is currently a
publisher.
I have another server running SQL 2000 on Windows 2000 SP4. It's a clean
install for this server. I would like to make this a backup publisher to the
SQL 7.
I'm curious to know if there would be any problems with replication between
these two servers. Thanks.
we do it all the time here. No problems.
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
"New to SQL" <New to SQL@.discussions.microsoft.com> wrote in message
news:E6778BD1-C545-488F-86E5-E80573A0DD42@.microsoft.com...
> Hello. I'm new to SQL servers. I'm curious to know if there are any known
> issues for SQL replication between SQL 7 and SQL 2000.
> I currently have SQL 7 running on NT 4 SP6a. This server is currently a
> publisher.
> I have another server running SQL 2000 on Windows 2000 SP4. It's a clean
> install for this server. I would like to make this a backup publisher to
the
> SQL 7.
> I'm curious to know if there would be any problems with replication
between
> these two servers. Thanks.

replication between SQL 2000 and 2005?

We currently have a SQL2000 replication in place and we're considering
moving one of the servers to SQL2005 x64.
is 2000/2005 replication interoperable?.
is the x64 2005 different in handling replication or is it interoperable
with 32bit versions?(be it 2005 x64 to 2005 32b or 2000 32b)
thanks in advance
Guillermo,
for the question about using different versions, please see:
http://msdn2.microsoft.com/en-us/library/ms143241(en-US,SQL.90).aspx.
For the other part, a MS developer recently pointed out that 64-bit and
32-bit are nearly interoperable, but as the Microsoft Jet engine is not
supported, Microsoft Jet push subscriptions for merge replication are not
supported. Unless the subscriber provides a 64-bit ODBC or OLE DB driver,
transactional or snapshot push subscriptions for ODBC or OLE DB subscribers
are not supported. Because of the unavailability of 64-bit Data
Transformation Services (DTS), transformable push subscriptions are not
supported.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||if transactional and push replication aren't supported, then what
replication methods do i havce left?
i'm not using jet replication, but push between remote servers across a VPN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORH3Utc9FHA.3208@.TK2MSFTNGP11.phx.gbl...
> Guillermo,
> for the question about using different versions, please see:
> http://msdn2.microsoft.com/en-us/library/ms143241(en-US,SQL.90).aspx.
> For the other part, a MS developer recently pointed out that 64-bit and
> 32-bit are nearly interoperable, but as the Microsoft Jet engine is not
> supported, Microsoft Jet push subscriptions for merge replication are not
> supported. Unless the subscriber provides a 64-bit ODBC or OLE DB driver,
> transactional or snapshot push subscriptions for ODBC or OLE DB
subscribers
> are not supported. Because of the unavailability of 64-bit Data
> Transformation Services (DTS), transformable push subscriptions are not
> supported.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Guillermo,
I have no way of testing this, but pull subscriptions and merge would seem
to be ok. If you give it a try please let us know how it works out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i've been told we're using merge subscription, so there would be no problem
with sql 2005 64 to sql2000 it seems
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FE0coo9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Guillermo,
> I have no way of testing this, but pull subscriptions and merge would seem
> to be ok. If you give it a try please let us know how it works out.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluation/features/replication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
sql

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evalua...replication.asp
Steve

Replication between never-connected SQL Servers

Is it possible to set-up replication between two servers that are never
directly connected?
We have a production server that is not connected to our network for
security reasons. I am doing testing on a local server and then want to
periodically apply the updates to the production server via CD. I haven't
been able to do this with replication as it seems to require a connection to
the subscription db for the initial snapshot.
At the moment, the only way I have been able to do this is by creating a
backup and recreating the db on the remote server.
Thanks!
James
James,
replication will require a connection between servers at some point. Where a
connection is not possible, you can use a derivative of log shipping or
database shipping as you are doing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very much Paul.
James
"Paul Ibison" wrote:

> James,
> replication will require a connection between servers at some point. Where a
> connection is not possible, you can use a derivative of log shipping or
> database shipping as you are doing.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Replication Between Different Versions of SQL Server

Hello,

We are trying to set up replication between different versions of SQL
Server (7 & 2000).

This is how the 2 servers are now setup:
DB1 - External web-server w/SQL Server 7
DB2 - Internal server w/SQL Server 2000

REPLICATION SETUP
This is how we need the replication to be set up. They are all
Transactional Replications.

DISTRIBUTION:
This is set up on DB2, which is running on 2000

WITH PUBLISHERS ON DB1, AND PULL SUBSCRIPTIONS ON DB2:
dbo.Employment_App & dbo.Employ_Jobs (together in one replication)
dbo.Feedback
NOTE: This set of replications work some of the time, but make DB1 &
DB2 very slow when running. A lot of times, the jobs say "Retrying
Job", but they usually will eventually work.

WITH PUBLISHERS AND PUSH SUBSCRIPTIONS ON DB2:
dbo.Jobs
dbo.Releases
dbo.Search
NOTE: This set of replications used to work when DB1 & DB2 both ran on
SQL Server 7 with 2000 Enterprise Manager's. But since we moved the
data to an internal server (DB2) that runs on 2000, it doesn't work at
all.

We've researched the problem, and found an article by MS called
"Replication Between Different Versions of SQL Server" at
http://msdn.microsoft.com/library/d...plimpl_4joy.asp
that states that this setup should be possible (on Combination 3 for
Transactional Replications).

But it's not working at all. Are there some steps that we need to
check to have replication setup between 2 servers running on different
versions of SQL Server (7 & 2000)? Any & all help & advice is
appreciated. Thanks.

KWilliamsJust to follow up on this - I'd be very interested to know if anyone has
successfully got a replication to work with 6.5 as the publisher /
distribution and MSDE (7 or 2000) as the subscriber?

Thanks

Chris Gadsby

"KWilliams" <webmaster@.douglas-county.com> wrote in message
news:e9cb71d3.0311050735.38e1b491@.posting.google.c om...
> Hello,
> We are trying to set up replication between different versions of SQL
> Server (7 & 2000).
> This is how the 2 servers are now setup:
> DB1 - External web-server w/SQL Server 7
> DB2 - Internal server w/SQL Server 2000
> REPLICATION SETUP
> This is how we need the replication to be set up. They are all
> Transactional Replications.
> DISTRIBUTION:
> This is set up on DB2, which is running on 2000
> WITH PUBLISHERS ON DB1, AND PULL SUBSCRIPTIONS ON DB2:
> dbo.Employment_App & dbo.Employ_Jobs (together in one replication)
> dbo.Feedback
> NOTE: This set of replications work some of the time, but make DB1 &
> DB2 very slow when running. A lot of times, the jobs say "Retrying
> Job", but they usually will eventually work.
> WITH PUBLISHERS AND PUSH SUBSCRIPTIONS ON DB2:
> dbo.Jobs
> dbo.Releases
> dbo.Search
> NOTE: This set of replications used to work when DB1 & DB2 both ran on
> SQL Server 7 with 2000 Enterprise Manager's. But since we moved the
> data to an internal server (DB2) that runs on 2000, it doesn't work at
> all.
> We've researched the problem, and found an article by MS called
> "Replication Between Different Versions of SQL Server" at
http://msdn.microsoft.com/library/d...plimpl_4joy.asp
> that states that this setup should be possible (on Combination 3 for
> Transactional Replications).
> But it's not working at all. Are there some steps that we need to
> check to have replication setup between 2 servers running on different
> versions of SQL Server (7 & 2000)? Any & all help & advice is
> appreciated. Thanks.
> KWilliams