Creating Pull Subscription using T-SQL

Hello,

i am trying to create a pull subscription to my publication using T-SQL. The pull subcription is created successfully but the problem is that data is not merged correctly. I mean when i run the agent ( despite of having data in subscription / publication ) the data is not merged and i get "No Data Needed To Be Merged" message.

i run following sp in this sequence:

At Subscrber:

1 - sp_addmergepullsubscription
2 - sp_addmergepullsubscription_agent

At Publisher:

1 - sp_addmergesubscription

Again at subscriber :

Transaction for adding merge agent job.

Regards,

You must have subsetfiltering enabled, are you using hostname() or suser_sname()? It's common error where the agent is connecting with some value that doesn't match any of the rows. So check what value agent is connecting with, and check what rows have the matching filter value.|||Dear Gregg,

i have not enabled anything. it is a simple replication without Filtering.|||OK, just to be clear, you've generated the snapshot and applied it to the subscriber, correct? Now you've made more changes (at publisher or subscriber), like inserts, updates or deletes, run merge agent, but merge agent says no changes need to be sync'd, correct?|||Yes, Exactly.

I applied snapshot, and then after make some changes but it says "No Data Needed to be Merged".|||what version and build of sql server are you using?|||

i am using MSDE 2000 With SP4.

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

|||

If you're positive you have no filtering, and you're positive you're making changes to the correct tables, then it's not clear to me what the problem is without looking at your machine/subscription/publication.

Only suggestion I can offer is to try and create a new publication and/or new subscription, but using the UI Wizards as opposed to TSQL scripts. I know MSDE doesn't come with Enterprise Manager, but if you find a machine with it installed, you can connect to the necessary machines to do so.

|||

Greg,

i have tried this with EM and it works fine but my requirement is that i need to create script for same operation so that it be included in my setup program and be deployed. EM works fine but it does not fulfill my requirements which is a script file to be run at time of setup.

Looking forward to your kind reply.

Regards,

|||If it works fine via EM, then after you create the subscription via the wizard, script out the subscription and compare it to what you originally had.|||i did the same. the script that i am running is based on script generated from Wizard. the question is that why is it not running with OSQL utility ?!

Regards,|||I'm not sure what you're asking, but the wizard will generate TSQL statements that you can execute any way you want - via osql, isqlw, vb app, etc. Just make sure to execute the statements at the proper machine, and in the proper database.|||

Dear Greg,

I am doing same as what you are suggesting. That Script is so clear with proper guidance. i just run that on proper machine and Database but the only proper is that Merge Agent does not behave properly. The rest is fine.

Regards,

|||How are you invoking the merge agent?|||Greg,

Althougt i have made a schedule for merge agent to be run after creating Test Enviroppment i run it by right clicking on Merge Agent and START SYNCHRONZIE. but in both cases it does not work properly.

Regards,

Creating publication hangs server

What are the actions performed by SQL Server when it creates a new publication or alters a previously created publication?

The reason I ask is because when I perform the above actions on a specific production database the server completely hangs. The tables are not large, maybe 5000 rows. I cannot delete the subscriptions either as this hangs the server too. Using any of the supplied stored procedures also has the same effect.

As this is a production environment I really don't want to do this too often.

Thanks

GavinMy dear friend. I have same problem on a clustered production system. Try this for start. When Creating publication, choose only one or two small tables at a time to create articles. Start snap shot and generate and apply changes to replicated server. Once ok. Start the same thing for other tables. If this works then u are good to go...


Originally posted by gmb72
What are the actions performed by SQL Server when it creates a new publication or alters a previously created publication?

The reason I ask is because when I perform the above actions on a specific production database the server completely hangs. The tables are not large, maybe 5000 rows. I cannot delete the subscriptions either as this hangs the server too. Using any of the supplied stored procedures also has the same effect.

As this is a production environment I really don't want to do this too often.

Thanks

Gavin

Creating full-text indexes on a table in a publication

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

By default, full-text indexes are not replicated. You can enable it when you setup a Publication through Publication Wizard UI in the "Articles" page; set the article property to "true" for "Copy full text indexes".

If you setup your publication through stored procedure, make sure when you call sp_addarticle, @.schema_option includes 0x1000000.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0483a157-e403-4fdb-b943-23c1b487bef0.htm

Regards,

Gary

|||Gary,

I do have that schema option enabled in my publication, but it only seems to replicate full text indexes on first initialization of that publication, not if you add a full text index and catalog after the publication is up and running.

-mike
|||

Hi, Mike,

The behavior you're observing is by design. Schema change after initial sync has its own rule, and adding an index is considered a schema change to the publication, and it will not be replicated per DDL replication rule, however, you can explicitly add script to be run at subscriber to create the index (for example, use sp_addscriptexec).

Excerpt from SQL 2005 Books Online:

Making Schema Changes on Publication Databases:

Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

Thanks,

Zhiqiang Feng

|||

Mike,

The error you are getting from the distribution agent is indeed caused by the "CREATE FULLTEXT CATALOG" statement.

sp_addscriptexec would have been the best approach for what you want to achieve, however as it stated in the error message, "CREATE FULLTEXT CATALAG" cannot run within a transaction. In this case, your script is applied by distribution agent, and there is always a transaction created by the agent for the purpose of rolling back in case of an error is encountered (and this is by design).

So unfortunately, the sp_addscriptexec would not work for you in this case. The only work around I can think of are the ones you already know: apply scripts directly or reinitialize the publication.

Regards,

Gary

|||Odd. I just created a script to create a full-text catalog and create a full-text index on a table and applied it to a merge publication using sp_addscriptexec and it worked fine. Any reason why this should work on a merge publication but not a transactional?

Thanks for the help guys.
|||

Cut the long story short.

For transactional replication, ACID properties are preserved and transactions are taken place in the same order in publisher tables and subscriber tables. Since it's important all changes are executed sucessfully on subscriber, it needs to be wrap in a transaction, so roll back is possilbe in case of unexpected failure.

For merge replication, since conflicts may occur, and it can be detected and resolved (manually or by preset conflict resolver). There is no need to roll back all the changes in the committed batch of commands, only the failed ones are marked for conflict resolution by user. Therefore it's okay not to wrap it within a transaction.

Regards,
Gary

|||I have a similar question. I have a one-way transactional replication from A to B. I have replicated the initial full text index from A to B. But A will be contantly updated with new data, and its full text index will be incrementally updated. I would like to have only the updates to the full text index replicated to B as they occur or on a schedule.

Is there a way to do this? Thanks
|||

I don't think the full text index updates will be replicated. The full text engine for SQL Server (MSFTESQL) is responsible for updating and building the full text catalog on each server. So if you insert couple new records at the publisher, those new records got replicated to the subscriber, then the full text engine on the subscriber will take care of updating the full-text database on subscriber database. Replication shouldn't give you additional work load because of the full-text index on the table.

Regards,
Gary

|||Gary,

Thanks for the reply. We want to avoid running the full text index update on the subscriber because the subscriber needs to respond to the web server. When we run the full text index updating on the subscriber, the server becomes very busy for over several hours and response to the web server becomes very slow.

We got the following suggestion from a different forum:
Use "Change Tracking" and "Update Index in Background" on the Push Subscribers

Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking.

Will this work?

Paul
|||

Paul,

I think this is more of a full-text index question than replication question, but here is my two cents :-)

The full text index should use the most resources (time-wise) on your subscriber when the initial snapshot is applied, at this time, the full-text index engine is doing a full population of the initial dataset. Depending on the size of the initial dataset, it is not uncommon to see a full population takes several hours. But after the initial population, all the subsequent updates to your subscriber should be a reasonable light weight process to your full-text index engine (this is my personal opinion, don't quote me on that). Having say that, it really depend on what kind of data you have and how much data you are replicating to your subscribers.

"Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking."

I think the suggestion would save you time when you apply the snapshot, because the distribution agent (which responsible for replicating data from publisher to subscriber) is not running at the same time as the full-text index engine when it's trying to populate the full-text index catalog as data was pouring in. But after the initial snapshot, all the subsequent index updates are still done by the full-text index engine on the subscriber.

So, back to your question, I "think" it'll work if you are using snapshot replication where you are always apply a new data snapshot to subscriber.

As for transactional replication, it would save you time when the initial snapshot is applied, and after that, the distribution agent will replicate the changes to subscriber and you really just want the full-text index engine to do incremental update on subscriber tables instead of a full population.

Hope that helps,

GaryC

Creating full-text indexes on a table in a publication

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

By default, full-text indexes are not replicated. You can enable it when you setup a Publication through Publication Wizard UI in the "Articles" page; set the article property to "true" for "Copy full text indexes".

If you setup your publication through stored procedure, make sure when you call sp_addarticle, @.schema_option includes 0x1000000.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0483a157-e403-4fdb-b943-23c1b487bef0.htm

Regards,

Gary

|||Gary,

I do have that schema option enabled in my publication, but it only seems to replicate full text indexes on first initialization of that publication, not if you add a full text index and catalog after the publication is up and running.

-mike|||

Hi, Mike,

The behavior you're observing is by design. Schema change after initial sync has its own rule, and adding an index is considered a schema change to the publication, and it will not be replicated per DDL replication rule, however, you can explicitly add script to be run at subscriber to create the index (for example, use sp_addscriptexec).

Excerpt from SQL 2005 Books Online:

Making Schema Changes on Publication Databases:

Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

Thanks,

Zhiqiang Feng

|||

Mike,

The error you are getting from the distribution agent is indeed caused by the "CREATE FULLTEXT CATALOG" statement.

sp_addscriptexec would have been the best approach for what you want to achieve, however as it stated in the error message, "CREATE FULLTEXT CATALAG" cannot run within a transaction. In this case, your script is applied by distribution agent, and there is always a transaction created by the agent for the purpose of rolling back in case of an error is encountered (and this is by design).

So unfortunately, the sp_addscriptexec would not work for you in this case. The only work around I can think of are the ones you already know: apply scripts directly or reinitialize the publication.

Regards,

Gary

|||Odd. I just created a script to create a full-text catalog and create a full-text index on a table and applied it to a merge publication using sp_addscriptexec and it worked fine. Any reason why this should work on a merge publication but not a transactional?

Thanks for the help guys.|||

Cut the long story short.

For transactional replication, ACID properties are preserved and transactions are taken place in the same order in publisher tables and subscriber tables. Since it's important all changes are executed sucessfully on subscriber, it needs to be wrap in a transaction, so roll back is possilbe in case of unexpected failure.

For merge replication, since conflicts may occur, and it can be detected and resolved (manually or by preset conflict resolver). There is no need to roll back all the changes in the committed batch of commands, only the failed ones are marked for conflict resolution by user. Therefore it's okay not to wrap it within a transaction.

Regards,
Gary

|||I have a similar question. I have a one-way transactional replication from A to B. I have replicated the initial full text index from A to B. But A will be contantly updated with new data, and its full text index will be incrementally updated. I would like to have only the updates to the full text index replicated to B as they occur or on a schedule.

Is there a way to do this? Thanks|||

I don't think the full text index updates will be replicated. The full text engine for SQL Server (MSFTESQL) is responsible for updating and building the full text catalog on each server. So if you insert couple new records at the publisher, those new records got replicated to the subscriber, then the full text engine on the subscriber will take care of updating the full-text database on subscriber database. Replication shouldn't give you additional work load because of the full-text index on the table.

Regards,
Gary

|||Gary,

Thanks for the reply. We want to avoid running the full text index update on the subscriber because the subscriber needs to respond to the web server. When we run the full text index updating on the subscriber, the server becomes very busy for over several hours and response to the web server becomes very slow.

We got the following suggestion from a different forum:
Use "Change Tracking" and "Update Index in Background" on the Push Subscribers

Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking.

Will this work?

Paul|||

Paul,

I think this is more of a full-text index question than replication question, but here is my two cents :-)

The full text index should use the most resources (time-wise) on your subscriber when the initial snapshot is applied, at this time, the full-text index engine is doing a full population of the initial dataset. Depending on the size of the initial dataset, it is not uncommon to see a full population takes several hours. But after the initial population, all the subsequent updates to your subscriber should be a reasonable light weight process to your full-text index engine (this is my personal opinion, don't quote me on that). Having say that, it really depend on what kind of data you have and how much data you are replicating to your subscribers.

"Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking."

I think the suggestion would save you time when you apply the snapshot, because the distribution agent (which responsible for replicating data from publisher to subscriber) is not running at the same time as the full-text index engine when it's trying to populate the full-text index catalog as data was pouring in. But after the initial snapshot, all the subsequent index updates are still done by the full-text index engine on the subscriber.

So, back to your question, I "think" it'll work if you are using snapshot replication where you are always apply a new data snapshot to subscriber.

As for transactional replication, it would save you time when the initial snapshot is applied, and after that, the distribution agent will replicate the changes to subscriber and you really just want the full-text index engine to do incremental update on subscriber tables instead of a full population.

Hope that helps,

GaryC

Creating custom merge profile

You can also in the Create Publication Wizard, select
Yes, minimize the amount of data on the Optimize
Synchronization page (@.keep_partition_changes = true),
and consider if column-level tracking is appropriate for
the business needs.
The timeout parameterss are usually adjusted only when
timeouts are an issue, and won't optimize your process.
You might want to look at this article to get more info
on Optimization:
http://www.microsoft.com/technet/pro.../sql/2000/main
tain/mergperf.mspx
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
thanks a lot.
Janez