.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Logical HOST_NAME within Post Snapshot Script for Dynamic Merge Snapshot

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

I have a Merge Publication that is dynamically filtered based on HOST_NAME(). All subscriptions are push subscriptions (i.e all agents run in a common distributor)

In the definition of any Subscriber I can specify  the value of HOST_NAME() for the pupose of dynamic filtering. But HOST_NAME() in the Post Snapshot script appears to be the actual HostName of the Distributor (not the HostName of the Subscriber for whom that dynamic snapshot is intended).

How can I retrieve either the HOST_NAME for the subscriber as defined by the subscription, or the actual HOST_NAME() of the intended subscriber (either will do).

The processing performed by the Post Snapshot Script is conditional on which dynamic snapshot has just been invoked




View Complete Post

More Related Resource Links

A dynamic snapshot is required because the publication XXX contains both dynamic filters and logical


Hi, I really need help as this is working on production environment and I have no idea what's happening.

We have a SQL Server 2008 Merge Publication (Merge Pull Replication)

All subscribers are running SQL Server Express 2008.

The replication has been working fine for 4 month, yesterday we noticed that one column of a Table were out of the publication article. BTW, it was replicated originally... we don’t know how it disappeared…

So we executed:

exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Tipo_Empleado', @column = N'Row_Version', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

After that, ALL subscriptions failed to sync returning the error:

A dynamic snapshot is required because the publication 'IPoint_Pub' contains both dynamic filters and logical record relationships.

Obviously I did executed the jobs for creating the main snapshot (static) and also all the dynamic snapshots after checking the main snapshot was successfully generated.

In some subscribers, dropping the su

Merge Replication: How to give read access on snapshot share to a sql account

Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

Merge Replication, Push Subscription : The snapshot takes centuries to apply

Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

Merge Replication Compressed Snapshot

Hi, How do I go about compressing my snapshot files? I am unable to find a tutorial explaining how to do it.

Initializing a Merge Subscription Without a Snapshot - doesn't operate correctly



I do the backup from the publisher, next I restore it at the subscriber using *pure* database.

next I attempt to create subscription using this db. of course, I use

SubscriptionSyncType.None for SyncType propery of the subscription.


1. nevertheless the merge agent downloads whole snapshot!

both data (bcp files) and the schema.

2. I get the *strange* error. below thelines from the log:

Applying the snapshot to the Subscriber

A dynamic snapshot will be applied from 'C:\DOCUME~1\...\LOCALS~1\Temp\DB$MAIN_DB1_Main_testReplFromBackup\'

Preparing table '__UserSyncOptions' for merge replication

Applied script '__UserSyncOptions_2.cft'

{call sp_MSsetconflicttable (N'__UserSyncOptions', N'MSmerge_conflict_Main___UserSyncOptions', N'DB\MAIN', N'DB1', N'Main')}

{call sp_MSsetconflicttable (N'__UserSyncOptions', N'MSmerge_conflict_Main___UserSyncOptions', N'DB\MAIN', N'DB1', N'Main')}

Incorrect syntax near 'Id'.

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When trou

SQL Server 2008 merge replication snapshot hangs on filtered articles


I have a publication on SQL Server 2008 Standard Edition using merge replication.  When I attempt to generate the initial snapshot, the snapshot agent appears to hang on the step "Setting up the publication for filtered articles."  I get a long (over 4 hour) series of messages: "The process is running and is waiting for a response from the server."  I know something is happening server-side, as SQL Server and the snapshot agent use a lot of memory and max out one core's processing capacity.

This has me confused as the publication is not doing any filtering.

Even more confusing:  I backed up the database and restored it onto my development-test system.  I created the snapshot there, and it took under 10 minutes every time.

Any suggestions for investigating and resolving this?

dynamic snapshot error



   I am getting the following error when merge agent runns.

·          The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

·          The partitioned snapshot available for this Subscriber is outdated. To initialize this Subscriber, you must generate a new partitioned snapshot or enable the Merge Agent to automatically de

SQL 2008. Merge replication. Snapshot agent. Access Denied

Windows Server 2008 Standard x64 SP1, SQL Server 2008 Enterprise Edition x64 SP1
Snapshot agent has read-write permissions to ReplData folder but cannot access local snapshot folder. How to resolve this error?

Error messages:
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ReplData\unc\ServerName_DatabaseName_PublicationName\DateTime\' is denied.
Stack:    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
   at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
   at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
   at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: mscorlib, Error number: 0)
Get help: http

When is a new snapshot requied in merge replication?


Have I correctly understood that a snapshot is only required in a merge replication (pull) when the subscription is first created or reinitialized?

And that the publisher is re-generating those snapshots on some schedule, only to speed up to process if some subscribers need to be reinitialized?

I'm asking this because I intend to download the snapshot via ftp manually (inorder to use passive FTP-SSL, merge replication itself only supports active FTP). Since the MergeSubscriptionAgent.IsSnapshotRequired() method takes ages to return a result (more than a minute ove local LAN), I'd like to skip that part and simply go ahead and download the snapshot when the subscription has not been created or is marked for reinitialization.

I need answers to the questions above, so that if I go down this route -> a couple of weeks in production I wouldn't run into trouble, because SQL requires a new snapshot to say validate data or something...

Applying Push Merge Replication snapshot manually


Here is the complete scenario :- SBS 2003 running Sql Server 2005 Standard, Merge Replication and Push Subscription. Connection Via VPN. Subscribers are SQL SERVER 2005 express..

Sometimes due to some structural changes, snapshot is to be re-applied. This is rare but is possible in our case.. On some subscribers snapshot applying goes on for hours and days with no sucess.

Are there any step by step instructions on how we can reintialize or reapply the snapshot on those subscribers manually i.e. maybe by copying the replication folder manually to subscriber and applying manually..

Pls help..

IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

Snapshot while initializing a MERGE is not appled to the subscriber




2x SQL2008R2 SP2. (Box 'A' andf 'B')

'A' has a database 'C' which is to be replicated onto 'B'. 'C' has only 8 Articles (4 tables, 4 views).

I set up a Merge-Replication (Publisher and distributor is 'A'), subscriber is 'B'. Snaphot is to be transferred via FTP.

When creating a new pull on 'B', i want to use a new database (selected "new database', 'D'). 'D' should then be initialized with the snapshot which has been generated while creating the publication.

Unfortunately, the snapshot will not be applied to 'D'. The agent just starts to replicate the database. But since the new (empty) database 'D' is missing some tables, i receive an errormessage, that the view cannot be inserted due to missing components.

This is correct since the snapshot was not applied to 'D'. If i prepare 'D' manually to include the tables and views, the replication works fine, data is being copied between 'A' and 'B' and vise-versa.

But setting up a SNAPSHOT-publication for database 'C' on box 'A', then creating a snapshot-pull-subscription for this on box 'B' into 'D' , the snapshot will be applied  as expected.

I used the same permissions (SQL-Agent, etc.) for both publications/subscriptions.

Ans hints for troubleshooting ?

Thanks in advance !


SQL Server 2008 Merge Replication - Snapshot File Location Is Incorrect


I've searched far and wide, and so far have not been able to find any reference to this issue.

There is a merge replication setup on a SQL 2008 server, setup to replicate to SQL 2008 Express edition. We have similar setups in SQL 2005 AND SQL 2008 without issues. The setup is that the current merge replication snapshot file location is \\<ServerName>\Repldata\UNC\<SERVER_PUBLICATION>\20101229112828 which represents the file location of the latest snapshot which was just generated due to an obsolete snapshot.

The issue comes in when attempting to replicate against the server. The replication says that the schema script could not be propogated to the subscriber. Under normal circumstances I would say it's due to security issues with being able to read to the above folder, however this is not the case. The additional error that comes up is stating that it could not read the file along with an OS error 3 (The system cannot find the path specified).

What is unique about this, is that the subscriber is trying to download the snapshot from an old and now non-existent snapshot folder. Here is an copy of the error, noting the file location of "20091121130613" which no longer exists and is from an old snapshot.


How to take a snapshot pic from a (.mp4/.3gp) video file ?


Hello there,

I need some help with video embedding in ASP.NET. So I am developing a litle custom blog and I need users to upload Quick Time videos (.mp4,.3gp) but I also need when upload completes to take a snapshot from the uploaded video which should be used as a link to a page that plays the video, in other words I need to take a frame from this video file... Do you have any idea how this is possible??

Thanks in advance

Initialize snapshot fro alternate location - multiple .bcp files for an article

I have to replicate over 50GB of data over a slow network.  I did not use the option to initialize snapshot from database backup because the replication articles contain row filters.  If I do that, I'll have to run a lot of scripts to remove the data and other unnecessary database objects on the subscriber. Instead, I created a workaround.  On the publisher, I first create the actual push subscription to the target subscriber on the publication.  This subscription is set not to initialize from snapshot.  I then created a second push subscription on the same publication, but the subscriber is a random database on the publisher server.  This second subscription is a dummy subscription set to initialize from snapshot - the purpose is to generate the necessary snapshot files.  I then reinitialized all subscriptions and generate the new snapshot files. On the subscriber, an empty database is created with the same tables as the publisher database.  I created an identical publication on this empty database, and a dummy push subscription on the target subscriber.  The subscription is reinitialized, and the snapshot files on the empty database is created.  These dummy snapshot files are then overwritten with the actual snapshot files created on the publisher, and then I synchronize the the dummy subscriptions with the actual sn

Help! "You must rerun snapshot because current snapshot files are obsolete" every 90 minutes!

I'm having difficulty with SQL Server replication getting machines in the field to replicate correctly.   Here are the specifics of my installation:   100 remote tablet PCs using web sync to update. 1/2 running Vista, 1/2 XP (all latest updates applied) SQL Server Express 2005SP2 w/ cumulative update 6.   Server: Win2003 (all latest updates) SQL Server 2005SP2 CU6   There is 1 database. For each tablet I create a separate publication (PUB001, PUB002, PUB003, etc.) with filtered articles containing data for the tablet. Each publication has a single subscription -- the tablet to which the subscription is assigned. Subscriptions are set to expire after 14 days. Snapshot agent was run when subscription was created, and is set to run automatically every 14 days.     This is a "test" system which is scheduled to roll out into production in 2 weeks.   For the past month I have been running 6 tablets A-OK with no problems (had 6 separate publications/subscriptions on the server/tablets).   I use a stored procedure to create the publications and subscriptions. This stored procedure hasn't changed in months.   Yesterday, we added another 6 tablets w/ separate publications on the server, using the same aforementioned stored procedure.     The tablet application is set to auto-sync over the internet every 15 minutes.   Starting yesterday afternoon, at random, tablets would st

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

How to let a non admin business user run a replication snapshot job?

According to the msdn library, I would have to give the non admin user (User1) SQLAgentUserRole privileges. But it also says that it has to be a local job and not a multi server job. Is the snapshot agent job a local or multi server job? Considering the snapshot agent is a local job and i make USER1 the owner of that job. He should be able to run the job rite? I need to give USER1 the ability to run sp_start_job to start the snapshot agent ONLY! (and not any other jobs). So what are the steps to do this? I also read that the only way to achieve this is by creating a proxy account? Is there an article somewhere that best describes this?  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend