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 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 Data-Driven Subscription

Hi, I'm trying to finish the Report Service 2005 Tutorial but I can't. When I create a Data-Driven Subscription, it is trigered and the job is finished succesfully, but in status of my subscription show me "Done: 3 processed of 3 total; 3 errors" and don't send any e-mail.

How can I see this errors?

Thanks.

You need to look in the ReportServerService_<timestamp>.log file. The error is in there.

error in create subscription wizard

When creating a subscription, if there is an error detected at the end - it creates the subscription but might not create the sql agent job, and it doesn't give you a chance to correct the error.

e.g. in the Agent Process account I forgot to prefix the username with the domain

At the end of the wizard it told me of the error, created the subscription but didn't let me go back to correct the error (and it didn't create the sql agent job). In the end it was easier to delete the subscription and create it again.

Not the end of the world but hopefully the developers will fix this in time.

thanks
Bruce

Hi, Bruce,

I was able to reproduce the issue you described with the exception that in my case the subscription was not created when the security account is specified without domain prefix in new subscription wizard.

I've entered a bug in our bug database to track this issue.

Thanks for reporting this problem.

Zhiqiang|||I'm following the steps of sample SQL Server Mobile Tutorials..
error on my replication subscription wizard :
"Initialiazing SQL Server Reconciler has failed
HRESULT 0x80045003 (29045)

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

error while waiting synchronizing Data in New Subcription Wizard!!
anyone can help?how to solve this problem?

error in create subscription wizard

When creating a subscription, if there is an error detected at the end - it creates the subscription but might not create the sql agent job, and it doesn't give you a chance to correct the error.

e.g. in the Agent Process account I forgot to prefix the username with the domain

At the end of the wizard it told me of the error, created the subscription but didn't let me go back to correct the error (and it didn't create the sql agent job). In the end it was easier to delete the subscription and create it again.

Not the end of the world but hopefully the developers will fix this in time.

thanks
Bruce

Hi, Bruce,

I was able to reproduce the issue you described with the exception that in my case the subscription was not created when the security account is specified without domain prefix in new subscription wizard.

I've entered a bug in our bug database to track this issue.

Thanks for reporting this problem.

Zhiqiang|||I'm following the steps of sample SQL Server Mobile Tutorials..
error on my replication subscription wizard :
"Initialiazing SQL Server Reconciler has failed
HRESULT 0x80045003 (29045)

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

error while waiting synchronizing Data in New Subcription Wizard!!
anyone can help?how to solve this problem?

Error impersonating user when subscribing a report.

Hello,
I am wondering why I am receiving an "Error impersonating user" error
when I am creating a subscription that schedules, executes, and renders
a report to a file share that I have Modify, Read & Execute, Read, and
Write permissions to through a user group that I am a part of.
When I enter my credentials used to access this file share, I receive
this error. When a person who has administrative privileges to this
directory enters his credentials, the subscription is successful.
I had the person who has administrative privileges enter my NT ID
directly in the security list to see if it is physically looking for my
ID when the subscription executes.
We really don't want to do this going forward when we will implement
this product in our production server environment in September.
Is there any way to make this work without adding everyone's NT ID with
Modify, Read & Execute, Read, and Write permissions to the security
list of this folder? Any help would greatly appreciated.
Thank you.When you say 'administrative user' what do you mean? A user who has
read/write access on the file share?
Do you or a group you are a member of have read/write access on teh file
share? If not, you'll need to supply credentials with sufficient permission
to write to the fileshare.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1109000217.745016.226520@.g14g2000cwa.googlegroups.com...
> Hello,
> I am wondering why I am receiving an "Error impersonating user" error
> when I am creating a subscription that schedules, executes, and renders
> a report to a file share that I have Modify, Read & Execute, Read, and
> Write permissions to through a user group that I am a part of.
> When I enter my credentials used to access this file share, I receive
> this error. When a person who has administrative privileges to this
> directory enters his credentials, the subscription is successful.
> I had the person who has administrative privileges enter my NT ID
> directly in the security list to see if it is physically looking for my
> ID when the subscription executes.
> We really don't want to do this going forward when we will implement
> this product in our production server environment in September.
> Is there any way to make this work without adding everyone's NT ID with
> Modify, Read & Execute, Read, and Write permissions to the security
> list of this folder? Any help would greatly appreciated.
> Thank you.
>