none
sp_addscriptexec is not working with FTP Snapshot delivery

    Question

  • Hi,

    We have a SQL Server Merge Replication topology. Both Publisher a Subscribers are using SQL Server 2012.

    As I have seen in http://msdn.microsoft.com/en-us/library/ms174360(v=sql.120).aspx

    "Using sp_addscriptexec to post a script file for publications using FTP for snapshot delivery is only supported for Microsoft SQL Server Subscribers."

    So we should be OK...

    The problem is that the Merge Agent is looking for the Script in the local PC instead of download it form the FTP Server.

    Here is the Code at the publisher:

    exec sp_addscriptexec 'IPoint_Pub','C:\IPoint_Files\ReplicationScripts\User\reIndex.sql'

    This is how the subscription was created:

    use IPoint
    exec sp_addmergepullsubscription @publisher = @Central_Server_Name, @publication = N'IPoint_Pub', @publisher_db = @MGR_DB, @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @sync_type = N'Automatic'
    
    exec sp_addmergepullsubscription_agent 
     @publisher = @Central_Server_Name,
     @publisher_db = @MGR_DB,
     @publication = N'IPoint_Pub', 
     @distributor = @Central_Server_Name, 
     @distributor_security_mode = 0, @distributor_login = 'XXX', @distributor_password = 'XXX', 
     @enabled_for_syncmgr = N'True', 
     @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 4, @frequency_subday_interval = 3, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20100527, @active_end_date = 99991231,
     @alt_snapshot_folder = N'', @working_directory = N'', 
     @use_ftp = N'True',
     @job_login = null, @job_password = null, 
     @publisher_security_mode = 0, @publisher_login = 'XXX',  @publisher_password = 'XXX',
     @use_interactive_resolver = N'False', @dynamic_snapshot_location = null, @use_web_sync = 0,
     @hostname =@SUCURSAL_ID
    GO

    And the log of the REPLMERG.EX

    2014-03-29 13:01:49.009 Microsoft SQL Server Merge Agent 11.0.2100.60
    2014-03-29 13:01:49.009 Copyright (c) 2008 Microsoft Corporation
    2014-03-29 13:01:49.009 
    2014-03-29 13:01:49.009 The timestamps prepended to the output lines are expressed in terms of UTC time.
    2014-03-29 13:01:49.009 User-specified agent parameter values:
    			-Publication Netul_Pub
    			-Publisher SRV01
    			-Subscriber NETUL-01\SQLEXPRESS
    			-Distributor SRV01
    			-PublisherDB IPoint_Netul
    			-SubscriberDB IPoint
    			-SubscriptionType 1
    			-ParallelUploadDownload 1
    			-DistributorLogin sucursal
    			-DistributorPassword **********
    			-DistributorSecurityMode 0
    			-PublisherLogin sucursal
    			-PublisherPassword **********
    			-PublisherSecurityMode 0
    			-SubscriberSecurityMode 1
    			-OutputVerboseLevel 1
    			-Validate 0
    2014-03-29 13:01:49.019 Connecting to Subscriber 'NETUL-01\SQLEXPRESS'
    2014-03-29 13:01:50.654 Connecting to Distributor 'SRV01'
    2014-03-29 13:01:51.599 Initializing
    2014-03-29 13:01:51.604 Validating publisher
    2014-03-29 13:01:51.614 Connecting to Publisher 'SRV01'
    2014-03-29 13:01:51.769 Retrieving publication information
    2014-03-29 13:01:51.774 Retrieving subscription information.
    2014-03-29 13:01:52.294 Connecting to Subscriber 'NETUL-01\SQLEXPRESS'
    2014-03-29 13:01:52.304 Connecting to Distributor 'SRV01'
    2014-03-29 13:01:52.414 Initializing
    2014-03-29 13:01:52.429 Validating publisher
    2014-03-29 13:01:52.439 Connecting to Publisher 'SRV01'
    2014-03-29 13:01:52.604 Retrieving publication information
    2014-03-29 13:01:52.619 Retrieving subscription information.
    2014-03-29 13:01:53.274 [29%] [0 sec remaining] Snapshot files will be downloaded via ftp
    2014-03-29 13:01:53.284 [29%] [0 sec remaining] Snapshot will be applied from a compressed cabinet file
    2014-03-29 13:01:53.294 [29%] [0 sec remaining] Connecting to ftp site 'SRV01.real2b.com'
    2014-03-29 13:01:55.019 [33%] [2 sec remaining] Extracting snapshot file 'Documento_Formc3f5d2f9_190.sch' from cabinet file
    2014-03-29 13:01:55.064 [33%] [2 sec remaining] Extracted file 'Documento_Formc3f5d2f9_190.sch'
    2014-03-29 13:01:57.299 [33%] [2 sec remaining] Applied script 'Documento_Formc3f5d2f9_190.sch'
    2014-03-29 13:01:57.304 [33%] [2 sec remaining] Preparing table 'Documento_Formulario_Otros' for merge replication
    2014-03-29 13:02:02.574 [51%] [8 sec remaining] Extracting snapshot file 'sysmergesubsetfilters_Documento_Formulario_Otros90.bcp' from cabinet file
    2014-03-29 13:02:02.594 [51%] [8 sec remaining] Extracted file 'sysmergesubsetfilters_Documento_Formulario_Otros90.bcp'
    2014-03-29 13:02:02.604 [55%] [7 sec remaining] Bulk copying data into table 'sysmergesubsetfilters'
    2014-03-29 13:02:02.609 [55%] [7 sec remaining] Bulk copied data into table 'sysmergesubsetfilters' (0 rows)
    2014-03-29 13:02:02.619 [55%] [7 sec remaining] Extracting snapshot file 'Documento_Formc3f5d2f9_190.dri' from cabinet file
    2014-03-29 13:02:02.624 [55%] [7 sec remaining] Extracted file 'Documento_Formc3f5d2f9_190.dri'
    2014-03-29 13:02:02.864 [55%] [7 sec remaining] Applied script 'Documento_Formc3f5d2f9_190.dri'
    2014-03-29 13:02:02.874 [59%] [6 sec remaining] Extracting snapshot file 'Documento_Formc3f5d2f9_190.trg' from cabinet file
    2014-03-29 13:02:02.884 [59%] [6 sec remaining] Extracted file 'Documento_Formc3f5d2f9_190.trg'
    2014-03-29 13:02:02.889 [62%] [5 sec remaining] Applied script 'Documento_Formc3f5d2f9_190.trg'
    2014-03-29 13:02:02.899 [62%] [5 sec remaining] Extracting snapshot file 'Documento_Formc3f5d2f9_190.prc' from cabinet file
    2014-03-29 13:02:02.919 [62%] [5 sec remaining] Extracted file 'Documento_Formc3f5d2f9_190.prc'
    2014-03-29 13:02:03.819 [62%] [5 sec remaining] Applied script 'Documento_Formc3f5d2f9_190.prc'
    2014-03-29 13:02:09.334 [74%] [5 sec remaining] Launching sqlcmd to apply the script 'Create_Categorias_Tables.sql'
    2014-03-29 13:02:09.974 [74%] [5 sec remaining] Applied script 'Create_Categorias_Tables.sql'
    2014-03-29 13:02:09.979 [77%] [4 sec remaining] Launching sqlcmd to apply the script 'Create_Categorias_Tables.sql'
    2014-03-29 13:02:10.144 [77%] [4 sec remaining] Applied script 'Create_Categorias_Tables.sql'
    2014-03-29 13:02:10.174 [81%] [3 sec remaining] Launching sqlcmd to apply the script 'reIndex.sql'
    2014-03-29 13:02:10.189 [81%] [3 sec remaining] Last 115 characters in 'sqlcmd' output buffer: Sqlcmd: 'C:\inetpub\ftproot\ReplData\ftp\SRV01_IPOINT_NETUL_NETUL_PUB\UserScripts\reIndex.sql': Invalid filename.
    
    2014-03-29 13:02:10.194 [81%] [3 sec remaining] Failed to apply the script 'reIndex.sql' using the 'sqlcmd' utility.
    2014-03-29 13:02:10.204 The schema script 'reIndex.sql' could not be propagated to the subscriber.
    2014-03-29 13:02:10.254 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147201001
    Message: The schema script 'reIndex.sql' could not be propagated to the subscriber.
    2014-03-29 13:02:10.259 [100%] The process was successfully stopped.
    2014-03-29 13:02:10.299 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147200963
    Message: The process was successfully stopped.

    As you can see, the FTP snapshot is working fine 

    "Extracted Documento_Formc3f5d2f9_190.trg"

    But the it is looking for a file that only exists in the server.

    'C:\inetpub\ftproot\ReplData\ftp\SRV01_IPOINT_NETUL_NETUL_PUB\UserScripts\reIndex.sql'

    Do you have any idea?



    Best Regards, Daniel.



    • Edited by dKaco Wednesday, April 02, 2014 4:11 PM
    Wednesday, April 02, 2014 4:07 PM

All replies

  • Hi Daniel,

    Can you script out your publication and post it here please?


    Brandon Williams (blog | linkedin)

    Wednesday, April 02, 2014 5:47 PM
  • Hi Brandon, thanks for your quick answer!

    Here is the script without the articles.

    Plesase let me know if you need more info.

    exec sp_addmergepublication @publication = N'NETUL_Pub', @description = N'Publicación del Servidor - IPoint', @sync_mode = N'native', @retention = 45, @allow_push = N'false', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'true', @snapshot_in_defaultfolder = N'true', @alt_snapshot_folder = N'C:\inetpub\ftproot\ReplData', @pre_snapshot_script = N'C:\IPoint_Files\ReplicationScripts\PreSnapshotScript.sql', @post_snapshot_script = N'C:\IPoint_Files\ReplicationScripts\PostSnapshotScript.sql', @compress_snapshot = N'true', @ftp_address = N'XXXX.xxxx.com', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = XXXX', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'true', @conflict_retention = 20, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @validate_subscriber_info = N'HOST_NAME()', @max_concurrent_merge = 9, @max_concurrent_dynamic_snapshots = 2, @use_partition_groups = N'true', @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'true', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 1 GO

    -- Adding the merge subscriptions

    exec sp_addmergesubscription @publication = N'NETUL_Pub', @subscriber = N'NETUL-01\SQLEXPRESS', @subscriber_db = N'IPoint', @subscription_type = N'Pull', @sync_type = N'Automatic', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'XXX', @use_interactive_resolver = N'False'



    Best Regards, Daniel.

    Wednesday, April 02, 2014 6:00 PM
  • Hi Daniel,

    With pull subscriptions you must specify a shared directory as a UNC path, such as \\ftpserver\home\snapshots.

    Try making the ReplData folder a shared directory as a UNC path, giving the Snapshot Agent write permissions and the Merge Agent read permissions.  Then specify a value of false for @snapshot_in_defaultfolder and a value of \\ftpserver\Repldata for the @alt_snapshot_folder parameter.  Then generate a new snapshot.

    After the snapshot is generated, try executing sp_addscriptexec again.  Be sure to specify a value of 1 for the @skiperror parameter which instructs the agent to skip errors.


    Brandon Williams (blog | linkedin)

    Wednesday, April 02, 2014 6:59 PM
  • Also I would like to see the script you are passing to sp_addscriptexec.

    You have to be careful with these scripts as they can cause sqlcmd to choke, which is what your error message looks like.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, April 02, 2014 7:07 PM
  • Hi Hilary, thanks for your answer.

    The cmd line to add the script is:

    exec sp_addscriptexec 'IPoint_Pub','C:\IPoint_Files\ReplicationScripts\User\reIndex.sql'

    I think it has nothing to do with the content of the script file, however here is the file content:

    DBCC DBREINDEX ('MSmerge_contents')
    DBCC DBREINDEX ('MSmerge_genhistory')
    DBCC DBREINDEX ('MSmerge_tombstone')
    Exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
    Exec sp_msForEachTable @COMMAND1= 'update statistics ? with fullscan; '

    The problem is that the Merge Agent is looking for the Script in the local PC instead of download it form the FTP Server.

    You can see the error: Invalid FileName and I solved it by now copying that file to the local PC (subscriber)



    Best Regards, Daniel.

    Thursday, April 03, 2014 12:35 PM
  • Hi again Brandon,

    I understand what you are saying and I know it would solve the problem, but it's now our case.<o:p></o:p>

    We have other installations with Merge Replication using a shared path (UNC) to download the initial snapshot and every user script is downloaded also from de shared location fine.<o:p></o:p>

    In this case, we have no direct access to a shared folder in server. The full replication process is done by internet without VPN.<o:p></o:p>

    I think the “Snapshot folder” it's well configured as the snapshot it’s being downloaded OK from the FPT Server.<o:p></o:p>

    My question is: Why the merge agent connects to the FPT server for download de Snapshot but it does not do the same to download the user script??<o:p></o:p>

    May be it’s a bug in the Merge Agent or some parameter I am not passing?<o:p></o:p>

    This is the merge agent invocation:<o:p></o:p>

    REPLMERG.EXE -Publication %Publication%  -Publisher %Publisher%  -Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB %PublicationDB%  -SubscriberDB %SubscriptionDB% -SubscriptionType 1  -ParallelUploadDownload 1  -DistributorLogin %user% -DistributorPassword %password% -DistributorSecurityMode  0 -PublisherLogin %user% -PublisherPassword %password% -PublisherSecurityMode  0  -SubscriberLogin %user% -SubscriberPassword %password%  -SubscriberSecurityMode  1 -OutputVerboseLevel 1 -Validate 0


    Best Regards, Daniel.

    Thursday, April 03, 2014 1:35 PM
  • Hi Brandon, do you have any clue?

    Best Regards, Daniel.

    Monday, April 07, 2014 11:45 PM