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!