Error in Transacitonal Replication - SQL Server 2005

Hi,

I'm deploying tran repln between 2 SQL Server 2005 databases and when viewing the 'Distributor to Subscription History' see a error message:

Incorrect syntax near the keyword 'where'. (Source: MSSQLServer, Error number: 156)

On the subscriber, I don't see the snapshot being generated....Please advice.

There are a couple of known issues related to the generation of reconciliation procedures for concurrent snapshot that will lead to the kind of error that you are seeing. These issues can be classified by the following scenarios:

1) You are publishing a table with only timestamp as the primary key column, and the article is configured to replicate timestamp as timestamp on the subscriber (default)

2) You are publishing a table with a primary key consisting only of computed columns

3) You are publishing a table where all non-primary key columns are not updateable (timestamp, computed, or identity)

To find out if any of the above actually applies to your case, you need to find out whether the distribution agent was hitting the error while delivering a .sch script to the subscriber from the agent history (just find the message before the error). Once you have identified the .sch script that was causing the failure, you should open the script in notepad and see if the update statement in the sp_MSins*_msrepl_css procedure was generated incorrectly. If you are indeed hitting one of the known issues mentioned above, you should see that either the update column list or the where clause is empty.

The solution (or workaround) will then depend on which one of the stated scenarios matches your own. If 1) is your case, then you need to replicate the timestamp column as a binary(8) column by disabling the 0x08 schema option via sp_changearticle and then regenerate/reapply the snapshot. Both 2) and 3) can be resolved by disabling concurrent snapshot through sp_changepublication <YourPublicationName>, 'sync_method', 'native', 1, 1 and then regenerating/re-applying the snapshot. Notice that by disabling concurrent snapshot, shared table locks will be taken out for an extended period of time while the snapshot is being generated so you may want to take a less intrusive approach of "fixing" the schema scripts instead. If 2) is your scenario, you can supply a where clause on the primary key parameters. On the other hand, if 3) matches your case, you can simply replace the faulty update statement with a return statement. You can post the problematic procedure definition here if you are not exactly sure how you should modify it.

If you are running the enterprise edition of SQL2005 on your publisher, you can also use the unofficial sync_method of 'database snapshot' to resolve case 2) or 3) without locking out your published tables for an extended period of time while the snapshot is being generated.

Hope that helps,

-Raymond

|||

I am having a similar problem. I know that I fall into the 3rd category. Basically, I want to replicate an identity to the subscriber, keeping the same value as what is in the publisher. I also have a situation where only the publisher can make changes to this table, not the subscriber. Is there any way I can alter the system stored procedures that are used to create the insert stored procedure so that it adds my ID field to the "set" area of the auto generated stored procedure?

Eric

|||

This should be fixed in the latest SP2 CTP and I would encourage you to try that out.

-Raymond

|||

Ray

I was looking through the notes for the SP2 CTP November release and I didn't see anything that mentioned that the problems with Replication were addressed in this Service Pack.

Eric

|||

Eric, if you can post the (sanitized) schema of the table that you are having trouble with, perhaps I can quickly run through it on our current SP2 build and see if your particular issue is resolved.

-Raymond

|||

Raymond,

Try the following (its not the exact table that I'm using, but the concept is the same)

State char(2) PK

County varchar(100) PK

CountyID int identity

Thanks,

Eric

|||

Yep, this is fixed in SP2, you can pick up the CTP here:

http://www.microsoft.com/sql/ctp.mspx

-Raymond

|||Thanks!

Error in Transacitonal Replication - SQL Server 2005

Hi,

I'm deploying tran repln between 2 SQL Server 2005 databases and when viewing the 'Distributor to Subscription History' see a error message:

Incorrect syntax near the keyword 'where'. (Source: MSSQLServer, Error number: 156)

On the subscriber, I don't see the snapshot being generated....Please advice.

There are a couple of known issues related to the generation of reconciliation procedures for concurrent snapshot that will lead to the kind of error that you are seeing. These issues can be classified by the following scenarios:

1) You are publishing a table with only timestamp as the primary key column, and the article is configured to replicate timestamp as timestamp on the subscriber (default)

2) You are publishing a table with a primary key consisting only of computed columns

3) You are publishing a table where all non-primary key columns are not updateable (timestamp, computed, or identity)

To find out if any of the above actually applies to your case, you need to find out whether the distribution agent was hitting the error while delivering a .sch script to the subscriber from the agent history (just find the message before the error). Once you have identified the .sch script that was causing the failure, you should open the script in notepad and see if the update statement in the sp_MSins*_msrepl_css procedure was generated incorrectly. If you are indeed hitting one of the known issues mentioned above, you should see that either the update column list or the where clause is empty.

The solution (or workaround) will then depend on which one of the stated scenarios matches your own. If 1) is your case, then you need to replicate the timestamp column as a binary(8) column by disabling the 0x08 schema option via sp_changearticle and then regenerate/reapply the snapshot. Both 2) and 3) can be resolved by disabling concurrent snapshot through sp_changepublication <YourPublicationName>, 'sync_method', 'native', 1, 1 and then regenerating/re-applying the snapshot. Notice that by disabling concurrent snapshot, shared table locks will be taken out for an extended period of time while the snapshot is being generated so you may want to take a less intrusive approach of "fixing" the schema scripts instead. If 2) is your scenario, you can supply a where clause on the primary key parameters. On the other hand, if 3) matches your case, you can simply replace the faulty update statement with a return statement. You can post the problematic procedure definition here if you are not exactly sure how you should modify it.

If you are running the enterprise edition of SQL2005 on your publisher, you can also use the unofficial sync_method of 'database snapshot' to resolve case 2) or 3) without locking out your published tables for an extended period of time while the snapshot is being generated.

Hope that helps,

-Raymond

|||

I am having a similar problem. I know that I fall into the 3rd category. Basically, I want to replicate an identity to the subscriber, keeping the same value as what is in the publisher. I also have a situation where only the publisher can make changes to this table, not the subscriber. Is there any way I can alter the system stored procedures that are used to create the insert stored procedure so that it adds my ID field to the "set" area of the auto generated stored procedure?

Eric

|||

This should be fixed in the latest SP2 CTP and I would encourage you to try that out.

-Raymond

|||

Ray

I was looking through the notes for the SP2 CTP November release and I didn't see anything that mentioned that the problems with Replication were addressed in this Service Pack.

Eric

|||

Eric, if you can post the (sanitized) schema of the table that you are having trouble with, perhaps I can quickly run through it on our current SP2 build and see if your particular issue is resolved.

-Raymond

|||

Raymond,

Try the following (its not the exact table that I'm using, but the concept is the same)

State char(2) PK

County varchar(100) PK

CountyID int identity

Thanks,

Eric

|||

Yep, this is fixed in SP2, you can pick up the CTP here:

http://www.microsoft.com/sql/ctp.mspx

-Raymond

|||Thanks!

Error in Snapshot replication

Hi,
I've a little problem in my sanpshot replication.
I triy to replicate a db in a new server, everythig works fine expect one
stored procedure.
This store procedure, when replicated, generate the error number: 207 -
"Invalid column name 'SELECT c.CliID AS ID_Client, '."
This is a simple sp that create dinamically a select command and execute it
(with the exec command) at the end of the sp.
If I try to copy manually this sp to the replicated server no error occurred
and the sp works fine.
Any suggestion?
Thanks
Hi Ramon,
I am guessing that double-quotes (") instead of single-quotes (') are used
for delimiting string-literals in the stored procedure that you are trying to
replicate and unfortunately this is not supported at all in SQL2000 or
earlier versions. I have posted the following two years back in an attempt to
explain a bit about how this came to be broken although I can imagine that
you will find little comfort in my explanation:
http://groups.google.com/group/micro... e6aaa0ff1c69
While I am glad to say that this particular scenario (using " as string
delimiters in text objects) is handled properly in SQL2005 (only need SQL2005
distributor\snapshot agent), here are a couple of workarounds that you can
implement if you need to stay on SQL2000 or earlier:
1) Use sp_addscriptexec (SQL2000) to implement replication of the stored
procedure definition yourself
2) Alter your stored procedures at your publisher so that single-quotes
instead of double-quotes are used for delimiting string-lterals.
HTH
-Raymond
"Ramon" wrote:

> Hi,
> I've a little problem in my sanpshot replication.
> I triy to replicate a db in a new server, everythig works fine expect one
> stored procedure.
> This store procedure, when replicated, generate the error number: 207 -
> "Invalid column name 'SELECT c.CliID AS ID_Client, '."
> This is a simple sp that create dinamically a select command and execute it
> (with the exec command) at the end of the sp.
> If I try to copy manually this sp to the replicated server no error occurred
> and the sp works fine.
> Any suggestion?
> Thanks
|||I changed the double-quotas with single-quotas (only one sp was
using double-quotaas) and the entire replication works fine.
Thnak you for the suggestion.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Ramon,
> I am guessing that double-quotes (") instead of single-quotes (') are used
> for delimiting string-literals in the stored procedure that you are trying to
> replicate and unfortunately this is not supported at all in SQL2000 or
> earlier versions. I have posted the following two years back in an attempt to
> explain a bit about how this came to be broken although I can imagine that
> you will find little comfort in my explanation:
> http://groups.google.com/group/micro... e6aaa0ff1c69
> While I am glad to say that this particular scenario (using " as string
> delimiters in text objects) is handled properly in SQL2005 (only need SQL2005
> distributor\snapshot agent), here are a couple of workarounds that you can
> implement if you need to stay on SQL2000 or earlier:
> 1) Use sp_addscriptexec (SQL2000) to implement replication of the stored
> procedure definition yourself
> 2) Alter your stored procedures at your publisher so that single-quotes
> instead of double-quotes are used for delimiting string-lterals.
> HTH
> -Raymond
> "Ramon" wrote:

Error in setting up SQL 2000 replication.

underprocessableThis seems to be a warning telling you that you could run into a problem if
your subscribers are less than SQL 2000. From what it says if your
subscribers are SQL 7 you will need to reinitialize, if they are SQL2000 you
are ok. Does this new table have a timestamp col on it?
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
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:drtu8d$2mil$1@.sxnews1.qg.com...
> Hi all,
> Ran into this odd error and I'd like to get some feed back.
> First things first, both the publisher and subscriber are SQL2000 with
> SP3A. The inital replication was setup using a batch file that one of our
> DBAs wrote. The other day I disovered that a table in the database was not
> being published. There where no errors generated during the setup phase.
> When I tried to use the Wizard to add the table to the database
> publication, this window popped up:
>
> I was told by one of the developers that this application orginally used
> Access and then was migrated to SQL 7.0. We've since upgrade to SQL 2000.
> As far as I know, replication was never used in SQL7.0. It's only be used
> in the past couple of years and just very recently for this database in
> question.
> Any thoughts about this? Is there some embedded meta data that in a system
> table that still has hooks to pre SQL 2000?
> TIA,
> Joe D.
>
|||"Joe D" <jkdriscoll@.qg.com> wrote in message
news:drtu8d$2mil$1@.sxnews1.qg.com...
> Hi all,
> Ran into this odd error and I'd like to get some feed back.
> First things first, both the publisher and subscriber are SQL2000 with
> SP3A. The inital replication was setup using a batch file that one of our
> DBAs wrote. The other day I disovered that a table in the database was not
> being published. There where no errors generated during the setup phase.
> When I tried to use the Wizard to add the table to the database
> publication, this window popped up:
>
> I was told by one of the developers that this application orginally used
> Access and then was migrated to SQL 7.0. We've since upgrade to SQL 2000.
> As far as I know, replication was never used in SQL7.0. It's only be used
> in the past couple of years and just very recently for this database in
> question.
> Any thoughts about this? Is there some embedded meta data that in a system
> table that still has hooks to pre SQL 2000?
> TIA,
> Joe D.
>
|||Joe,
this standard warning will occur if you add a table to a publication created
in SQL Server 2000 - ie the fact that you were using Access and SQL Server
7.0 is a red herring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
Thanks for the reply. To answer your question, no - there is no
timestamp col in the table.
Joe
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eCGagfGKGHA.3944@.tk2msftngp13.phx.gbl...
> This seems to be a warning telling you that you could run into a problem
> if your subscribers are less than SQL 2000. From what it says if your
> subscribers are SQL 7 you will need to reinitialize, if they are SQL2000
> you are ok. Does this new table have a timestamp col on it?
> --
> 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
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:drtu8d$2mil$1@.sxnews1.qg.com...
>
|||Paul,
Thanks for the information. That's one rabbit trail I won't have to go
down, trying to figure why this table got missed in the replication setup
process.
Joe
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uuEclIKKGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Joe,
> this standard warning will occur if you add a table to a publication
> created in SQL Server 2000 - ie the fact that you were using Access and
> SQL Server 7.0 is a red herring.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Just in case anyone cares, I've figured out why this one table wasn't being
added to the publication during replication setup. Seems our custom code
filters out any tables that start with sys. The table in question starts
with SYS. It is a user table and not a system table.
As Paul pointed out, the pop message I got was just a 'Red Herring'.
Joe D.
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:drtu8d$2mil$1@.sxnews1.qg.com...
> Hi all,
> Ran into this odd error and I'd like to get some feed back.
> First things first, both the publisher and subscriber are SQL2000 with
> SP3A. The inital replication was setup using a batch file that one of our
> DBAs wrote. The other day I disovered that a table in the database was not
> being published. There where no errors generated during the setup phase.
> When I tried to use the Wizard to add the table to the database
> publication, this window popped up:
>
> I was told by one of the developers that this application orginally used
> Access and then was migrated to SQL 7.0. We've since upgrade to SQL 2000.
> As far as I know, replication was never used in SQL7.0. It's only be used
> in the past couple of years and just very recently for this database in
> question.
> Any thoughts about this? Is there some embedded meta data that in a system
> table that still has hooks to pre SQL 2000?
> TIA,
> Joe D.
>

Error in Replication how to continue

I have transactional Replication from A to B.

My distribution agent failed becos of a primary key violation error in a table.[i have not included the skip error option].

Then the replication stopped.
The transactions on other tables are also not replicated to B.

IF an error is encountered in one of the tables will the replication stop entirely??.Is there no way for the replication to continue for the rest of the tables..
Pls clarifyI don't think you can skip this error. To use Replication you have to have a primary key in the table you want to replicate.|||The transactions are processed in a serialized fashion, meaning that if one failed nothing else goes through.

Error In Merge Replication while server syncs

At first we received error like

"The merge process could not retrieve identity range resource for table..."

then after solving this error we again started syncing of servers manually then we got some other error like

"the process could not enumerate changes to the subscriber".

We tried our best to solve this problem but some error appears each time like

"The process could not deliver the snapshot to the Subscriber."

"The schema script 'sync -t"Design" -o"dbo" -d"\\ECOSYSTEM\D$\FTP\MSSQL\EcoSystem\ftp\ECOSYSTEM_MLEcosystem_MLEcosystem\20060619204802\Design_1337.bcp" -hORDER( [intDesignId] ASC)' could not be propagated to the subscriber"

again identity erroe emerges and agin we do the same process.

How to solve these problem?

These look like transitory network issues. Can you set QueryTimeout to something larger and see if this solves the problem.

-- Hilary Cotter

Error in Merge replication "The schema script..."

I'm trying to set up a merge replication. Publication is created successfully and snapshot also, but when I create pull subscription on subscriber server and merge agent starts, after some time I get an error message of this type:

The schema script '\\ANIL\REPLDATA\unc\ANIL_BEJK_BEJK\20070625142735\dl_HF_vMSCene_3836.sch' could not be propagated to the subscriber.

It seams there is a problem with certain Views and SPs, because tables are successfully created, and some Views and SPs also.

I tried to exclude problematic articles, but every time another one pops up. Up until now, I excluded 7 articles from publication (1 Stored Procedure and 6 Views) but I still get errors.

I gave up because I can't exclude half of the Views and SPs just to make it work.

Is there something that can be done to solve this problem?

Thanks!
Does your SQL Server agent account have rights to read the share \\anil\repldata? Ensure that this account also has rights to list files and folders on the physical drive underlying \\anil\repldata.

If you are using a push subscription it should be the SQL Server agent account on the publisher. If you are using a pull subscription is should be the SQL Server agent account on the subscriber.|||

Hi Hilary

Sharing permisions were set to Read/Write to "Everyone", and security permisions also Read/Write to "Everyone", but it didn't work until I added an account on subscriber computer (which is not in domain) with same username and password as one that exists on publisher (which is in domain). I set SQL Server Agent to use that account and everything started to work fine.

I'm connecting to publisher using the VPN, and despite the fact that those two accounts are not from the same domain, everything is working fine. This is a little bit confusing. It looks like domain name doesn't play any role when logging in to shared folder.

Thanks for the help.

Anil

Error in Merge replication "The schema script..."

I'm trying to set up a merge replication. Publication is created successfully and snapshot also, but when I create pull subscription on subscriber server and merge agent starts, after some time I get an error message of this type:

The schema script '\\ANIL\REPLDATA\unc\ANIL_BEJK_BEJK\20070625142735\dl_HF_vMSCene_3836.sch' could not be propagated to the subscriber.

It seams there is a problem with certain Views and SPs, because tables are successfully created, and some Views and SPs also.

I tried to exclude problematic articles, but every time another one pops up. Up until now, I excluded 7 articles from publication (1 Stored Procedure and 6 Views) but I still get errors.

I gave up because I can't exclude half of the Views and SPs just to make it work.

Is there something that can be done to solve this problem?

Thanks!
Does your SQL Server agent account have rights to read the share \\anil\repldata? Ensure that this account also has rights to list files and folders on the physical drive underlying \\anil\repldata.

If you are using a push subscription it should be the SQL Server agent account on the publisher. If you are using a pull subscription is should be the SQL Server agent account on the subscriber.|||

Hi Hilary

Sharing permisions were set to Read/Write to "Everyone", and security permisions also Read/Write to "Everyone", but it didn't work until I added an account on subscriber computer (which is not in domain) with same username and password as one that exists on publisher (which is in domain). I set SQL Server Agent to use that account and everything started to work fine.

I'm connecting to publisher using the VPN, and despite the fact that those two accounts are not from the same domain, everything is working fine. This is a little bit confusing. It looks like domain name doesn't play any role when logging in to shared folder.

Thanks for the help.

Anil

Error in Merge Replication

Hi,

I have made two subscription in Merge Replication .

One Subscription is syncing data properly while other on is giving error

'The process could not enumerate changes at the 'Publisher'. error -2147200999

MS SQL Server Service Pack 4 is installed.

Now how can resolve it.

Thanks

Sanjay Tiwari

Is this an immediate error, or does it happen after a long time? If the latter, then increase the querytimeout parameter for the merge agent.

Error in Merge Agent Replication

Dear All,
while connecting the merge agent after updates in the
subscriber , the following error occured
'the process could not enumerate changes at the subscriber'
Any reason why this error appears?
Waiting for ur feedback.
Thanks in advance
Dalia,
Could you enable logging on the merge agent (-output
c:\somefile.log -outputverboselevel 3) and we can see if
there is any more info to help.
Regards
Paul Ibison
|||does the error message complain about any missing stored procedures?
90% or more of merge replication errors can be clear by simply restart your merge agent. Can you try this to see if it clears the error message.

Error in Inintial Replication

I'm Replicating from one SQL200 db to another on our LAN. The target db is
blank, but on some of the tables, it fails with:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: STAN (Data source); Error number: 4819)
I've verified that the collation and sort order are the same. Any ideas?
Jeff
Jeff,
Make sure the publisher and subscriber have the same collations - this
is normally due to differing collations.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jeff Dillon wrote:
> I'm Replicating from one SQL200 db to another on our LAN. The target db is
> blank, but on some of the tables, it fails with:
> Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
> (Source: STAN (Data source); Error number: 4819)
> I've verified that the collation and sort order are the same. Any ideas?
> Jeff
>
|||As I stated, they do have the same collation. Even using an empty database
as the subscriber causes this. So far, it appears the tables that are
failing have text columns.
I did a Google search on this, and the common reply is to check collation.
In neither of the 2 cases I found was collation the issue. It was never
resolved. Appears to be a bug.
I run sp_helpsort in each database, and it returns the same results. Is
there somewhere else I need to check?
Jeff
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Jeff,
> Make sure the publisher and subscriber have the same collations - this
> is normally due to differing collations.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jeff Dillon wrote:
is[vbcol=seagreen]
sorted.[vbcol=seagreen]
|||I found the problem. I had a wide clustered index (by wide I mean several
fields, including varchar).
I changed it to non-clustered, and it works. Bug? maybe...
Jeff
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:eEnFD0WpEHA.2612@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> As I stated, they do have the same collation. Even using an empty database
> as the subscriber causes this. So far, it appears the tables that are
> failing have text columns.
> I did a Google search on this, and the common reply is to check collation.
> In neither of the 2 cases I found was collation the issue. It was never
> resolved. Appears to be a bug.
> I run sp_helpsort in each database, and it returns the same results. Is
> there somewhere else I need to check?
> Jeff
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...
db[vbcol=seagreen]
> is
> sorted.
ideas?
>

Error in Inintial Replication

I'm Replicating from one SQL200 db to another on our LAN. The target db is
blank, but on some of the tables, it fails with:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: STAN (Data source); Error number: 4819)
I've verified that the collation and sort order are the same. Any ideas?
JeffJeff,
Make sure the publisher and subscriber have the same collations - this
is normally due to differing collations.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jeff Dillon wrote:
> I'm Replicating from one SQL200 db to another on our LAN. The target db is
> blank, but on some of the tables, it fails with:
> Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
> (Source: STAN (Data source); Error number: 4819)
> I've verified that the collation and sort order are the same. Any ideas?
> Jeff
>|||As I stated, they do have the same collation. Even using an empty database
as the subscriber causes this. So far, it appears the tables that are
failing have text columns.
I did a Google search on this, and the common reply is to check collation.
In neither of the 2 cases I found was collation the issue. It was never
resolved. Appears to be a bug.
I run sp_helpsort in each database, and it returns the same results. Is
there somewhere else I need to check?
Jeff
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...
> Jeff,
> Make sure the publisher and subscriber have the same collations - this
> is normally due to differing collations.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jeff Dillon wrote:
> > I'm Replicating from one SQL200 db to another on our LAN. The target db
is
> > blank, but on some of the tables, it fails with:
> >
> > Could not bulk insert. Bulk data stream was incorrectly specified as
sorted.
> > (Source: STAN (Data source); Error number: 4819)
> >
> > I've verified that the collation and sort order are the same. Any ideas?
> >
> > Jeff
> >
> >|||I found the problem. I had a wide clustered index (by wide I mean several
fields, including varchar).
I changed it to non-clustered, and it works. Bug? maybe...
Jeff
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:eEnFD0WpEHA.2612@.TK2MSFTNGP15.phx.gbl...
> As I stated, they do have the same collation. Even using an empty database
> as the subscriber causes this. So far, it appears the tables that are
> failing have text columns.
> I did a Google search on this, and the common reply is to check collation.
> In neither of the 2 cases I found was collation the issue. It was never
> resolved. Appears to be a bug.
> I run sp_helpsort in each database, and it returns the same results. Is
> there somewhere else I need to check?
> Jeff
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > Jeff,
> >
> > Make sure the publisher and subscriber have the same collations - this
> > is normally due to differing collations.
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Jeff Dillon wrote:
> > > I'm Replicating from one SQL200 db to another on our LAN. The target
db
> is
> > > blank, but on some of the tables, it fails with:
> > >
> > > Could not bulk insert. Bulk data stream was incorrectly specified as
> sorted.
> > > (Source: STAN (Data source); Error number: 4819)
> > >
> > > I've verified that the collation and sort order are the same. Any
ideas?
> > >
> > > Jeff
> > >
> > >
>

Error in first run of the snapshot agent (Error: 170)

Hi All
I am getting the following error from the snapshot agent, while first
setting up transactional queued replication.
"Line 1: Incorrect syntax near '000'.
(Source: distribution (Data source); Error number: 170)
"
I have read the KB articles saying I should install SP3 and the problem will
go away. I have installed SP3 on both publisher/distributor & single test
subscriber, on specially prepared clean installations of SQL2000.
Here is the end of the detailed logfile produced by the repl agent:
"Inserted constraint creation command for article 'EventBookingstatus' into
the distribution database.
*** [Publication:'I-junction_Support-Central_Live'] Total snapshot
generation time excluding publication setup: 439271 (ms) ***
SourceTypeId = 5
SourceName = distribution
ErrorCode = 170
ErrorText = Line 1: Incorrect syntax near '000'.
Line 1: Incorrect syntax near '000'.
Disconnecting from Publisher 'IJUNC-LUDD'
"
What should I try next?
I am happy to provide the sql to create the db & the replication if it helps.
Many thanks
Vaughn
Hi Vaughn,
I have the vague suspicion that you are hitting the issue described in
KB834453 (just search on microsoft.com) which is fixed as a QFE after sp3.
If you can include more of the output from the snapshot agent or a trace of
the failing command then I can tell with greater certainty. In any case, I
think it is worthwhile for you to contact Microsoft support to try out the
fix.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Vaughn Cleminson" <Vaughn Cleminson@.discussions.microsoft.com> wrote in
message news:EC3169EF-6EC5-4BF1-B43C-C51403DAA06C@.microsoft.com...
> Hi All
> I am getting the following error from the snapshot agent, while first
> setting up transactional queued replication.
> "Line 1: Incorrect syntax near '000'.
> (Source: distribution (Data source); Error number: 170)
> "
> I have read the KB articles saying I should install SP3 and the problem
will
> go away. I have installed SP3 on both publisher/distributor & single test
> subscriber, on specially prepared clean installations of SQL2000.
> Here is the end of the detailed logfile produced by the repl agent:
> "Inserted constraint creation command for article 'EventBookingstatus'
into
> the distribution database.
> *** [Publication:'I-junction_Support-Central_Live'] Total snapshot
> generation time excluding publication setup: 439271 (ms) ***
> SourceTypeId = 5
> SourceName = distribution
> ErrorCode = 170
> ErrorText = Line 1: Incorrect syntax near '000'.
> Line 1: Incorrect syntax near '000'.
> Disconnecting from Publisher 'IJUNC-LUDD'
> "
> What should I try next?
> I am happy to provide the sql to create the db & the replication if it
helps.
> Many thanks
> Vaughn
|||Hi
Thanks for the reply.
I'll check the db for reserved words and silly characters.
How to I trace the failing command, please?
The full snapshot logfile is too long to post.
Is there a specific bit that will help?
Vaughn
|||Hi Riaymond.
The KB you suggest says:
SYMPTOMS
When the Snapshot Agent runs after you make schema changes to the underlying
tables of a publication, the Snapshot Agent may fail.
This is the first time I am running the agent, after creating the
publication. I haven't changed the tables since making the publication and
setting up the areticles.
Does the KB still apply?
Vaughn
|||Hi Vaughn,
You can definitely see the error even if this is the first time you run the
snapshot agent. Making schema\publication changes simply gives the random
flavors described in the KB. To tack down the problematic command, you
should start a SQL Profiler trace with the statement starting\batch
startng\RPC starting events enabled before running the snapshot agent. The
problematic command should starts with sp_MSadd_repl_error if my guess is
correct but you may need to back track a few entries in the profiler trace
before you can find it.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Vaughn Cleminson" <VaughnCleminson@.discussions.microsoft.com> wrote in
message news:D8229CB1-A616-4ABB-BF09-787A280C413C@.microsoft.com...
> Hi Riaymond.
> The KB you suggest says:
> SYMPTOMS
> When the Snapshot Agent runs after you make schema changes to the
underlying
> tables of a publication, the Snapshot Agent may fail.
> This is the first time I am running the agent, after creating the
> publication. I haven't changed the tables since making the publication and
> setting up the areticles.
> Does the KB still apply?
> Vaughn
|||Hi Raymond.
Man, you clearly know your stuff.
Here is the error line from profiler, and the error reported:
What do you suggest I try next?
line:
exec sp_MSadd_repl_command @.publisher_id = 0, @.publisher_db =
N'I-junction_Support-Central_Live', @.type = -2147483639, @.article_id = 63,
@.xact_id = 0x000000000000, @.xact_seqno = 0x00001c490000012802b900000001,
@.command_id = 1111, @.partial_command = 0, @.command =
0x6900660020002800400040006d006900630072006f007300 6f0066007400760065007200730069006f006e0020003e003d 00200030007800300038003000300030003200430030002900 200062006500670069006e0020006500780065006300200073 0070005f00610064006400730079006e006300740072006900 67006
70065007200730020004e00270045006d00610069006c00520 075006c006500450078006500630075007400610062006c006 50027002c0020004e0027006e0075006c006c0027002c00200 04e00270049004a0055004e0043002d004c005500440044002 7002c0020004e00270049002d006a0075006e0063007400690 06f00
6e005f0053007500700070006f00720074002d00430065006e 007400720061006c005f004c0069007600650027002c002000 4e00270049002d006a0075006e006300740069006f006e005f 0053007500700070006f00720074002d00430065006e007400 720061006c005f004c0069007600650027002c0020004e0027 00730
070005f004d005300730079006e0063005f0069006e0073005 f0045006d00610069006c00520075006c00650045007800650 0630075007400610062006c0065005f00310027002c0020004 e002700730070005f004d005300730079006e0063005f00750 0700064005f0045006d00610069006c00520075006c0065004 50078
006500630075007400610062006c0065005f00310027002c00 20004e002700730070005f004d005300730079006e0063005f 00640065006c005f0045006d00610069006c00520075006c00 6500450078006500630075007400610062006c0065005f0031 0027002c0020004e002700730070005f004d00530063006600 74005
f0049002d006a0075006e006300740069006f006e005f00530 07500700070006f00720074002d00430065006e00740072006 1006c005f004c006900760065005f0045006d00610069006c0 0520075006c006500450078006500630075007400610062006 c00650027002c0020004e002700640062006f0027002c00200 04e00
27005000720069006d006100720079004b006500790027002c 0020004e0027006e0075006c006c0027002c0020004e002700 6e0075006c006c0027002c00200030007800300031002c0020 0031002c0031002c004e00270049004a0055004e0043002d00 4c0055004400440027002c0020003200200065006e00640020 00
e006?????6?6
error:
exec sp_MSadd_snapshot_history @.agent_id = 5, @.runstatus = 6, @.comments =
N'Line 1: Incorrect syntax near ''e006''.', @.delivered_transactions = 1,
@.delivered_commands = 0, @.log_error = 1,
|||Hi Vaughn,
The only thing to do now is to contact Microsoft Product\Customer Support
with the information below and ask for the QFE fix described in KB834453;
there should be no charge for this. Or, you can try to work around the
problem by using either a longer (or shorter) article name for article id#63
(just drop and recreate the article).
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Vaughn Cleminson" <VaughnCleminson@.discussions.microsoft.com> wrote in
message news:549EC277-0623-4DC5-992F-8780FA613ADF@.microsoft.com...
> Hi Raymond.
> Man, you clearly know your stuff.
> Here is the error line from profiler, and the error reported:
> What do you suggest I try next?
> line:
> exec sp_MSadd_repl_command @.publisher_id = 0, @.publisher_db =
> N'I-junction_Support-Central_Live', @.type = -2147483639, @.article_id = 63,
> @.xact_id = 0x000000000000, @.xact_seqno = 0x00001c490000012802b900000001,
> @.command_id = 1111, @.partial_command = 0, @.command =
>
0x6900660020002800400040006d006900630072006f007300 6f006600740076006500720073
0069006f006e0020003e003d00200030007800300038003000 30003000320043003000290020
0062006500670069006e002000650078006500630020007300 70005f00610064006400730079
006e0063007400720069006700670065007200730020004e00 270045006d00610069006c0052
0075006c006500450078006500630075007400610062006c00 650027002c0020004e0027006e
0075006c006c0027002c0020004e00270049004a0055004e00 43002d004c0055004400440027
002c0020004e00270049002d006a0075006e00630074006900 6f006e005f0053007500700070
006f00720074002d00430065006e007400720061006c005f00 4c0069007600650027002c0020
004e00270049002d006a0075006e006300740069006f006e00 5f0053007500700070006f0072
0074002d00430065006e007400720061006c005f004c006900 7600650027002c0020004e0027
00730070005f004d005300730079006e0063005f0069006e00 73005f0045006d00610069006c
00520075006c00650045007800650063007500740061006200 6c0065005f00310027002c0020
004e002700730070005f004d005300730079006e0063005f00 7500700064005f0045006d0061
0069006c00520075006c006500450078006500630075007400 610062006c0065005f00310027
002c0020004e002700730070005f004d005300730079006e00 63005f00640065006c005f0045
006d00610069006c00520075006c0065004500780065006300 75007400610062006c0065005f
00310027002c0020004e002700730070005f004d0053006300 660074005f0049002d006a0075
006e006300740069006f006e005f0053007500700070006f00 720074002d00430065006e0074
00720061006c005f004c006900760065005f0045006d006100 69006c00520075006c00650045
0078006500630075007400610062006c00650027002c002000 4e002700640062006f0027002c
0020004e0027005000720069006d006100720079004b006500 790027002c0020004e0027006e
0075006c006c0027002c0020004e0027006e0075006c006c00 27002c00200030007800300031
002c00200031002c0031002c004e00270049004a0055004e00 43002d004c0055004400440027
002c0020003200200065006e0064002000
> e006?Y6Y6
> error:
> exec sp_MSadd_snapshot_history @.agent_id = 5, @.runstatus = 6, @.comments =
> N'Line 1: Incorrect syntax near ''e006''.', @.delivered_transactions = 1,
> @.delivered_commands = 0, @.log_error = 1,

Error in Distribution Clean Up Agent

Server 1 SQL 2000 (SP4) Distributor, Publisher
Server 2 SQL 2000 (SP4) Subscriber
Transactional Replication set up between Server 1 and 2.
All the agents are running successfully (Log Reader, Snapshot, Other
Miscellenaous agent...except the Distribution Clean Up Agent
error
Executed as user: COOP\sqladmin. Could not remove directory
'\\ALXCONTGC\ReplData\unc\ALXCONTGC_sales_Pub_Alxo ffc_Anualtrans\20061119100406\'.
Check the security context of xp_cmdshell and close other processes
that may be accessing the directory. [SQLSTATE 42000] (Error 20015)
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent
distribution@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only scheduled for retry. Could not clean up
the distribution transaction tables. [SQLSTATE 01000] (Message 14152).
The step failed.
?
can you make sure xp_cmdshell is enabled? Do this:
exec master.dbo.sp_configure 'xp_cmdshell'
If run value is 0, do this
exec master.dbo.sp_configure 'xp_cmdshell',1
reconfigure with override
Then start up your distribution clean up task.
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
"noda" <hend_g@.hotmail.com> wrote in message
news:1164102353.485024.276880@.j44g2000cwa.googlegr oups.com...
Server 1 SQL 2000 (SP4) Distributor, Publisher
Server 2 SQL 2000 (SP4) Subscriber
Transactional Replication set up between Server 1 and 2.
All the agents are running successfully (Log Reader, Snapshot, Other
Miscellenaous agent...except the Distribution Clean Up Agent
error
Executed as user: COOP\sqladmin. Could not remove directory
'\\ALXCONTGC\ReplData\unc\ALXCONTGC_sales_Pub_Alxo ffc_Anualtrans\20061119100406\'.
Check the security context of xp_cmdshell and close other processes
that may be accessing the directory. [SQLSTATE 42000] (Error 20015)
Replication-@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only check rowcou: agent
distribution@.rowcount_only parameter must be the value 0,1, or 2. 0=7.0
compatible checksum. 1=only scheduled for retry. Could not clean up
the distribution transaction tables. [SQLSTATE 01000] (Message 14152).
The step failed.
?
|||Is your sql server agent running as an account that is in the sysadmin role?
If not it'll try to use the proxy account that might not have been
configured.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Error in : MSmerge_tombstone

I when i am try to update data in one table having replication, it gives me the error msg below;

cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

why this happens? pls. advise how i can get rid of this message.

I discovered that the table MSmerge_tombstone on subscriber is having triggers. I removed this trigger and the problem doenot reapper. Is it advisable to do that? Pls. advise.

Thanks,

I do not think removing triggers is good idea, MSmerge_tombstone table is used by replication process to track the rows that have been deleted on the subscriber
|||Could you pls. advise how can i get rid of this error?

Error icon on replication monitor and sub items

Hi,

Have two servers, one production server and one backup server. Both are Windows 2003 Server having SQL Server 2000 installed.

I have created replication on these servers using a wizard. I then generated SQL scripts to delete and create the replication. These scripts work well, but to test to remove the replication on both servers I used sp_removedbreplication 'DBNAME' on both servers. Not all were removed and on Replication Monitor and sub items plus Log Reader Agent there were added an icon, a red 'X' as in error. The replication seems to work.

Does anyone have any idea on why this happened and what I can do about it. It is not very nice to have errors in replication on a customers servers.

Grateful for answers
Best,
/M

Please refrain from creating multiple posts for the same question. Here's cut/paste from my reponse to your other post:

The cleanest way and supported way is to drop the distributor and recreate it from scratch. However you can also look at the following tables in distribution database and delete any rows that reference your deleted publications (but take a backup of the distribution database first, and note that manually deleting these tables is unsupported):

MSpublications

MSsubscriptions

MSarticles