A problem in retrieving files in pull replication via FTP. SQL 2008

Question

Hi all,

I have a problem setting up a replication via FTP.  Here is the process and error log.

2012-08-03 04:07:51.068 Ftp get file from Publication_name\20120802140039\Table1dcabd60c_323.pre to C:\Users\fritz.DEV\AppData\Local\Temp\bfc3b01ec7044bd0ad618e3f901e71e4\Table1dcabd60c_323.pre.
2012-08-03 04:17:55.730 OLE DB Subscriber 'FRITZ02': sp_MSreleasesnapshotdeliverysessionlock
2012-08-03 04:18:28.016 Agent message code 20033. The process could not retrieve file 'Publication_name/20120802140039/Table1dcabd60c_323.pre' from the FTP site 'U87'.
2012-08-03 04:18:28.671 ErrorId = 19, SourceTypeId = 2
ErrorCode = '20033' ErrorText = 'The process could not retrieve file

I have been through other peoples topics similar to the problem, and already double checked ftp sub folder names, the server 'u87' can be connected from the client computer 'fritz02' via command prompt FTP client, and I could even get the file, Table1dcabd60c_323.pre in there.

Two computers are connected via VPN and there is a firewall in between and I already asked the admin to open ports like 21 and 1433 from the client to the server so that I can access to the FTP server and the SQL server on the server from the client.
I use my domain account for distributor agent that have an owner access to the client PC SQL server. 'sa' for connecting the publication server, and enabled SQL account login at the server SQL.

First thing I don't understand is that above log does not give me detailed information of the problem although I set up the job log option to outputverboselevel = 2 or 3.

Could someone help me by telling me what is going wrong here, please?

Answer 1

I suspect it is either permissions related error or the value specified for Path from the FTP root folder on the Publication Properties -> FTP Snapshot page is incorrect.  Replication appends 'ftp' to the snapshot folder path when using FTP for snapshot delivery and I do not see that in the folder path for your error log.

Try following the troubleshooting steps in this post and see if that helps:  http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/03/merge-agent-fails-with-the-merge-failed-to-retrieve-the-snapshot-schema-script-merge-replication-with-web-synchronization-using-ftp-over-internet.aspx

---

Brandon Williams (blog | linkedin)

Answer 2

Hi Brandon,

Thank you very much for replying to my question.

Sorry that I did not include that ftp path part clearly but I already tried that and the link you gave me.  Path form the FTP root folder sets to "\ftp" and snapshots are saved to FTP home folder = D:\File.Server\FTP_server.  If you look at below log, you can see the path should be correct.  However, the replication still does not work.

Any ideas?

<The part showing the FTP path>

2012-08-06 11:57:38.342 Connecting to ftp site 'U87'
2012-08-06 11:57:40.175 OLE DB Subscriber 'FRITZ02': sp_MSissnapshotitemapplied @snapshot_session_token = N'D:\File.Server\FTP_server\ftp\Publication_name\20120805140029\', @snapshot_progress_token = N'D:\File.Server\FTP_server\ftp\Publication_name\20120805140029\Table1dcabd60c_323.pre'
2012-08-06 11:57:40.175 Last transaction timestamp: 0x0004358b000000b4000a00000008
Transaction seqno: 0x0004358b000000b4000a00000008
Command Id:  12
Partial: 0
Type: 60
Command: D:\File.Server\FTP_server\ftp\Publication_name\20120805140029\Table1dcabd60c_323.pre

Answer 3

Are these pull subscriptions?

Please try using a UNC network share such as \\<computername>\home\snapshot rather than a local path for the snapshot folder.  Then configure the FTP server root to be \\<computername>\home and set Path from the FTP root folder to be \snapshots\ftp.  Also, verify the default snapshot location for the publication's Distributor is set to \\ftpserver\home\snapshot, then generate a snapshot and sync.

If you still get an error and are using Merge, try using -OutputVerboseLevel 4 agent logging to see if there are any additional error message/codes.  Please post the log to SkyDrive and share the link back here.

---

Brandon Williams (blog | linkedin)

Answer 4

Hi Brandon,

Thank you for a reply again.

Yes, it is a pull subscription.

I changed the 'Put files in the following folder' text box in the Publication properties > Snapshot to a UNC share which is \\u87\File.Server\FTP_server.  I verified it can be connected from Windows explorer.  This is ftp home drive so that I set up 'Path from the FTP root folder' to \ftp.
I changed -OutputVerboseLevel to 4 at the subscription job.

It still failed so that I put the log called replication.txt on the SkyDrive.
Could you take a look, please?

Best regards,

Answer 5

Please share the SkyDrive file and post the link to it back here.

http://windows.microsoft.com/en-US/skydrive/change-access-permissions-faq

---

Brandon Williams (blog | linkedin)

Answer 6

Can you see the log file now?

https://skydrive.live.com/redir?resid=48192852F912BC43!108&authkey=!AD8115ZFi58B2RI