Problem with snapshot replication

I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
You have to make sure the distribution agent is also running, and not
failing with errors.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"captainkt" <nothing@.fake.com> wrote in message
news:OLA7eR$TFHA.2940@.TK2MSFTNGP10.phx.gbl...
I have a snapshot publication, which does not seem to be moving the data. I
only move data for the previous day. The snapshot agent runs, and creates
the .bcp file. I check the MSsnapshot_history table, and the snapshot
completes successfully, and the commands are posted to the distribution
database. I run sp_browsereplcmds, and I can see the commands in there.
The data is not getting moved to my subscriber. I have no idea why. What
can I check to figure this out? I have dropped the subscription, and
re-subscribed, but it just doesn't work.
Please help!
sql

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
Steen
Problem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
----
--
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
SteenProblem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
----
--
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
SteenProblem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with replication of view which depend on other view in merge replication

Hi There,
I am trying to replicate the sql server 2000 db with merge and pull
subscription.
When i try to initialize the ini. snapshot, the error message is
generated saying
"the schema script "JobPriceLookupQueryNew.sch" could not be
propagated to the subscriber". This view depend on
JobPriceLookupQuery.
I try to read in this group and get one post suggesting to run
sp_refreshview for the view but i am getting error message for this
also.
exec sp_refreshview [@.JobPriceLookupQuery =] 'JobPriceLookupQuery'
error "incorrect syntax near 'JobPriceLookupQuery'
2nd try:
exec sp_refreshview JobPriceLookupQuery
Invalid object name 'JobPriceLookupQuery;
exec sp_refreshview [@.JobPriceLookupQuery =]
Invalid object name 'JobPriceLookupQuery
I have many view with depend on other view, and its not working now.
could i please get some information how can I solve this problem.
Thanks in advance.
Indra.
Indra,
exec sp_refreshview JobPriceLookupQuery should work, as long as you are in the correct database.
Running sp_depends will show you that the values in sysdepends are incorrect, and need updating.
What you need to do is (manually) work out the order of dependencies of views, and refresh them from the bottom up. Then you'll be able to let replication create the snapshot in the right order.
HTH,
Paul Ibison

Problem with replication job schedule

We have recently setup a snapshot replication on a SQL Server 2000 server.
It's an unusual replication, since it's replicating from one database to
another on the same server (because of the interaction between two 3rd party
applications).
My problem is that when I set the job schedule's "enabled" setting to "yes"
(through the snapshot agent properties under the "schedules" tab) the job
runs at the scheduled time (daily at 11:00 PM) and has a status of
successful, but the "enabled" setting changes to "no" and I have to reset it
to "yes" the next day.
Another orginization that has a similar setup has been using this method for
a few years without a problem, so I assume this should work.
I'm not seeing anything unusual in the logs. Is there anything else that I
can look at to narrow down the problem? Has anyone come across this type of
problem before?
Oh brother. I was about to respond back with:
It is set to "Recurring" with "Occurs every 1day(s), at 11:05:00 PM".
But when I clicked on the "Change" button to examine it more closely I saw
the duration was set to "End Date" of the current date, rather than "No End
Date". Someone else had setup the replication and I assumed it was set up
properly because of what I was seeing on the other screen. I should have
caught that. Thanks for the response. I might not have checked that more
closely for quite a while.
"Paul Ibison" wrote:

> David - is the job set on a recurring schedule. It sounds like it is set on
> a schedule to execue one time only.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Problem with replication

When I try to insert a new record in the subscriber, into a published table, I get a error, saying that it could not execute a stored procedure in the remote server SQLOLEDB. But I when I insert a new record in the publisher, it works correctly, transmitting the changes to the subscriber. What's wrong with it? Both systems are windows 2000 server, with sql server 2000. Thanks.Please give us an idea what type of replication it is?|||- Do you have latest fix installed on the subscriber?
- Does the insertion fire any triggers?|||It is a transactional replication. The error occurs when the subscriber is calling the remote stored procedure in the publisher, which updates the original table.

Problem with replication

Dear friends
I have following problem in replication kindly suggest
what can be the problem.
Last Command: create table #belong_agent_-2049795750
(tablenick int NOT NULL, rowguid uniqueidentifier NOT
NULL,generation int NULL, lineage varbinary(255) NULL,
colv varbinary(2048) NULL)
Error Code: Failed to enumerate changes in the filtered
articles.
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error
number: -2147200925)
----
Line 1: Incorrect syntax near '-'.
(Source: BPOGEM (Data source); Error number: 170)
----
does this help?
http://support.microsoft.com/?id=814916
"Niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:061a01c4b6c3$ca7b4250$a401280a@.phx.gbl...
> Dear friends
> I have following problem in replication kindly suggest
> what can be the problem.
> Last Command: create table #belong_agent_-2049795750
> (tablenick int NOT NULL, rowguid uniqueidentifier NOT
> NULL,generation int NULL, lineage varbinary(255) NULL,
> colv varbinary(2048) NULL)
> Error Code: Failed to enumerate changes in the filtered
> articles.
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error
> number: -2147200925)
> ----
> ----
> Line 1: Incorrect syntax near '-'.
> (Source: BPOGEM (Data source); Error number: 170)
> ----
> ----
>

Problem with replication

Dear Friends
I am getting the following problem.
Kindly help
The schema script '\\server2
\f$\sqldatafiles\MSSQL\ReplData\unc\server2_MAINSQ L_MAINSQ
L\20040425110134\ListOTH_8.sch' could not be propagated
to the subscriber.
(Source: Merge Replication Provider (Agent); Error
number: -2147201001)
----
General network error. Check your network documentation.
(Source: AJAY (Data source); Error number: 11)
----
Communication link failure
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
----
general network error means that your network link hiccupped. Rerun your
merge agent.
By any chance are you replicating over the internet?
"sharad" <niitmalad@.yahoo.co.in> wrote in message
news:3eaf01c42aa8$d497d890$a001280a@.phx.gbl...
> Dear Friends
> I am getting the following problem.
> Kindly help
> The schema script '\\server2
> \f$\sqldatafiles\MSSQL\ReplData\unc\server2_MAINSQ L_MAINSQ
> L\20040425110134\ListOTH_8.sch' could not be propagated
> to the subscriber.
> (Source: Merge Replication Provider (Agent); Error
> number: -2147201001)
> ----
> ----
> General network error. Check your network documentation.
> (Source: AJAY (Data source); Error number: 11)
> ----
> ----
> Communication link failure
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> ----
> ----

Problem with replication

Hi
Using SQL 7.0
I get this error :
[vbcol=seagreen]
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
Don
Try checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:

> Hi
> Using SQL 7.0
> I get this error :
>
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>

Problem with replication

Hi
Using SQL 7.0
I get this error :
>> Line 81: Incorrect syntax near '@.c1'.
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
DonTry checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:
> Hi
> Using SQL 7.0
> I get this error :
> >> Line 81: Incorrect syntax near '@.c1'.
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>

Problem with Replication

I keep getting an error when I try to set up a merge replication...
Cannot insert duplicate key row in object 'TRDFIL' with unique index
'ITRDFIL0'.
(Source: CMTSQL2 (Data source); Error number: 2601)
I can't seem to figure out how to prevent this from happening, any ideas on
what is not correct?
ThanksHow did you apply your snapshot?
Normally you get this error for one of two reasons.
1) You applied the snapshot manually, either by a restoring a backup, or a
data transfer process (DTS, bcp, etc). Your snapshot is out of sync, and you
are performaning an insert, update operation for which the data already
exists on the Subscriber.
2) Someone or some process has already entered data in the Subscriber, and
the replication process is trying to add the same row.
Use EM, expand your server, expand Replication Monitor, expand the
Publishers Folder, expand your publisher, right click on the publication,
select Validate Subscriptions.
After this process has run, double click on your distribution agent, select
session details to view the results of the data validation process.
"Atley" <atley_1@.homtmail.com> wrote in message
news:eCBH6bVCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> I keep getting an error when I try to set up a merge replication...
> Cannot insert duplicate key row in object 'TRDFIL' with unique index
> 'ITRDFIL0'.
> (Source: CMTSQL2 (Data source); Error number: 2601)
> I can't seem to figure out how to prevent this from happening, any ideas
on
> what is not correct?
> Thanks
>

Problem with replication

Hi
Using SQL 7.0
I get this error :
[vbcol=seagreen]
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
DonTry checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:

> Hi
> Using SQL 7.0
> I get this error :
>
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>

Problem with remote replication

I have a number of dialup users who are trying to replicate against a master database.The first few times worked fine.but now each user is getting the following message

"The process could not change generation history at the publisher"

Any help or pointers gratefully received

many thanks

David J.

1) How many days has it run smoothly and end up with that error?

2) What is the number of transaction run per day?

3) Is this transactional replication?

|||

The first replication was done over the wirless network, after that the replication was done via a dialup connection over the Internet.Most users got 2/3 replications befor encountering the error message

"The process could not change generation history as the Publisher"

Not sure what you mean - Each user might add 400/500 new transactions each day

We are using Merge Replication

|||

It could be a timeout issue or a network related issue (due to flakiness)

Have you encountered the problem repeatedly now?

|||What will be the solution for timeout or network issues?|||

If it is a Timeout issue, it can be dealt with increasing the timeout value.

If it is a network issue, you will have to ensure that your network is connectable and the servers can talk to each other.

You should monitor your servers and see when you start encountering these problems. Analyze if there are any other external factors that influence it and then take some actions based on that fact.

|||Thanks, do you mean increasing timeout in SQL Enterprise Managers properties?|||Kind of. You would increase the timeout values (QueryTimeout/LoginTimeout) values for the agent jobs. But again, this suggestion holds if it is a timeout related problem.

Problem with Publication Articles being deleted

I wonder if anyone can advise, I currently have a replication set to 5 offices. Which has been working fine for some 18 months+, then the replication dropped out. When I looked in the publisher it had lost all its articles and would not allow me to add back in. I deleted the publication and recreated it and then after creating a new snapshot starting rolling back out to the various locations, and one of the locations failed and again all the articles where lost. I deleted and rebuilt the database at that office and went through the same routine again, only for it to happen again, the issue being that now errors where produced!!!.

So at present I have the publication and all the subscriptions running apart from this one office.

Has anyone had this before, or any ideas. I have run a full virus scan just in case and it was fine.

Can you provide more information?

Are you using push transactional replication?

Where does the replication fail? What error messages are you getting?

When you say one of the location failed, does it mean other locations worked fine?

Gary

|||

Hi Gary,

I found the problem, they were two dns records for the server I was trying to replicate to, and one of the records pointed back to the publication server. Now just need to find why all of a sudden I have 2 DNS records.

Thanks

Tony

Problem with peer to peer replication across a VPN

We are experiencing some problems with peer to peer replication configuration across a VPN between a 32 bit and 64 bit SQL server 2005. We have been able to configure it but we notice that on successful completion of the configuration the 64 bit machine does not have a subscription for the 32 bit machine article. But both servers have articles created and only the 32 bit machine has a subscription. This is a bizarre experience because the configuration was successful.

We know the server windows account has administrative rights at both end and we can telnet each server successful and all the necessary ports are open.

Might feeling is that the firewall or VPN us acting up but I cannot prove it..

I need help or advice about how to resolve this…

I assume you configure the P2P using P2P wizard. When you go through the wizard, in the summary page, do you see replication indiciate it will create subscription from 64bit machine to 32 machine?

Peng