Problem Accessing Snapshot Files Over FTP
May 24, 2004
:mad:
Hi folks, i am trying an anonymous pull merge replication over VPN.
Publisher and FTP server are the same machine(VPNSERVER).
In the Snapshot configuration in the enterprise manager i have following parameters:
Generate snapshot in following location \VPNSERVERD$FTP
FTP Server Name :VPNSERVER Port:21
Client path to this folder from ftp root: /ftp/ :confused: may be wrong here.
login: repl
Ftp Server configuration is:
FTP port no: 21
Repl user has administrative rights
When i run snapshot agent; SNAPSHOT.CAB is created in the following folder is created:
\vpnserverd$ftpftpVPNSERVER_repl_repl2004052 4164453
When i run the the merge agent at subscriber:
following error i recieve:
The schema script '' could not be propagated to the subscriber.
The process could not retrieve file 'VPNSERVER_repl_repl20040524164453snapshot.cab' from the FTP site 'VPNSERVER'.
200 Type set to I.
200 PORT Command successful.
550 /d:/ftp/VPNSERVER_repl_repl/20040524164453/snapshot.cab: No such file.
if i change the parameter "client path to this folder" i get error that the snapshot files are obsolete, run the snapshot again.
I guess the problem is with the exact location of snapshot file, because when i run the agent i can see the user REPL in the Ftp-monitor and there is no problem with accessing SQL at the publisher.
Any idea of what i a missing?
Thanx in advance.
View 8 Replies
ADVERTISEMENT
Jun 1, 2007
I recently had a problem with one of my publications that I haven't been able to figure out. Hopefully someone can help me with it.
I got this error last week: You must rerun snapshot because current snapshot files are obsolete. I assume this means the subscription needed to be re-initialized. Is this correct?
If so, I don't understand why. The publication was not changed. The subscription did not expire. The subscription was not marked for reinitialization.
Here are the specifics of my installation:
Merge publication with 1 pull subscription.
Subscriptions set to expire after 14 days.
Merge agent runs every day, every 5 minutes between 12:00:00A & 11:59:59P.
Snapshot agent was run once when publication was created and then disabled.
Publisher SQL ver: 2000 sp3
Publisher Win ver: 2003 sp1
Subscriber SQL ver: 2000 sp3
Subscriber Win ver: 2003 sp1
The subscription was successfully replicating every 5 minutes. The data for 12:35 shows the first problem. Here's the agent history at the time:
runstatus
start_time
comments
error_id
1
5/23/07 12:25
Initializing
0
3
5/23/07 12:25
Connecting to Publisher 'AASTAUSSQL01'
0
3
5/23/07 12:25
Uploaded 4 data changes (3 inserts, 1 updates, 0 deletes, 0 conflicts).
0
3
5/23/07 12:25
Downloaded 7 data changes (2 inserts, 5 updates, 0 deletes, 0 conflicts).
0
2
5/23/07 12:25
Merged 11 data changes (5 inserts, 6 updates, 0 deletes, 0 resolved conflicts).
0
1
5/23/07 12:30
Initializing
0
3
5/23/07 12:30
Connecting to Publisher 'AASTAUSSQL01'
0
3
5/23/07 12:30
Uploaded 4 data changes (3 inserts, 1 updates, 0 deletes, 0 conflicts).
0
3
5/23/07 12:30
Downloaded 20 data changes (14 inserts, 6 updates, 0 deletes, 0 conflicts).
0
2
5/23/07 12:30
Merged 24 data changes (17 inserts, 7 updates, 0 deletes, 0 resolved conflicts).
0
1
5/23/07 12:35
Initializing
0
3
5/23/07 12:35
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:35
You must rerun snapshot because current snapshot files are obsolete.
2
1
5/23/07 12:40
Initializing
0
3
5/23/07 12:40
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:40
You must rerun snapshot because current snapshot files are obsolete.
3
1
5/23/07 12:45
Initializing
0
3
5/23/07 12:45
Connecting to Publisher 'AASTAUSSQL01'
0
2
5/23/07 12:45
You must rerun snapshot because current snapshot files are obsolete.
4
Any help with this would be greatly appreciated.
Thanks!
View 11 Replies
View Related
Jan 16, 2007
My Snapshot Agent for SQL Server 2005 sometimes does not fully complete. I am expecting around 2073 files in the snapshot folder but only get 492 (this number does change). I have immediate_sync set to true, so I should get a full snapshot every time. It seems to quit after all data has been BCP'ed out and before the schema generation starts. I do get the "The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. " message in the MSSnapshot_history table.
I am guessing that there is some sort of blocking that is preventing the snapshot agent from continuing and then it times out and dies. But that is a total guess as I have not been able to observe the behavior, only the outcome.
Any suggestions to what the cause is or how I can fix it?
Thanks, Amy
View 4 Replies
View Related
Sep 15, 2015
We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:
MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp
With other tables, I'll get just one LARGE snapshot file, named:
MyOtherTable_4.bcp
In the latter case, the file can be very large (most recent is 38GB).
In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'
My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.
View 0 Replies
View Related
Jan 7, 2008
can sql server 2005 access files on a shred folder (which sql 2000 was not able)?
thnaks in advance
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
View 3 Replies
View Related
Oct 22, 2015
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
View 0 Replies
View Related
Jul 11, 2006
Hi
When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed.
When I open the images table in VWD and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table.
Any ideas?
Thanks
View 2 Replies
View Related
Jun 20, 2007
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View 1 Replies
View Related
Aug 7, 2015
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. no effect; the user makes their own login id but that does nt matter either.
View 8 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Mar 13, 2008
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
View 3 Replies
View Related
Apr 24, 2008
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 16 Replies
View Related
Feb 24, 2015
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
View 6 Replies
View Related
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
View 20 Replies
View Related
Jul 23, 2005
I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you
View 4 Replies
View Related
Jun 25, 2015
I have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?
View 6 Replies
View Related
Apr 17, 2007
What is a snapshot from replication point of view.
------------------------
I think, therefore I am - Rene Descartes
View 6 Replies
View Related
Aug 28, 2006
Dear friends
I am a new fellow to replication.What is the problem I am getting is I am not able to create snapShot.Whenever I start Snapshot Agent it is going for half an hour and it will stuck in the case of one view saying like that 'failed to process bulk copy of data from dbo.syncobj_03x666*'.If I am publishing tables only then also I am getting same error.This view created by system.In my case publiher only working as a distributor.I created a SQL username which have access in subscriber & Publisher.Preferably I will be happy if someone suggest what and all are the primary criterias we have to keep in mind while doing Replication
Thanks in Advance
Filson
View 5 Replies
View Related
Apr 9, 2002
I get the following error message in the job history for
synchronization between two SQL 7 machines across the
network.
The process could not read file 'acct.sch' due to OS error
1231.
Is there something to fix this? It has been running fine
for almost 2 years and suddenly quit. There is a firewall
on both sides, if that helps.
TIA,
Cami
View 3 Replies
View Related
May 7, 2002
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
View 1 Replies
View Related
Aug 27, 2001
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
View 1 Replies
View Related
Dec 29, 2000
Hi,
I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?
Thanks
TT
View 1 Replies
View Related
Dec 28, 2000
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
View 1 Replies
View Related
Jan 11, 2001
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
View 1 Replies
View Related
Jan 11, 2001
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
View 1 Replies
View Related
Oct 17, 2002
Hi everybody,
We have merge replication between two servers with Sql Server 2000 Service Pack 2, and the Merge agent display the follow message :
The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.
But i have changes at the suscriber, How can I do to Merge the last changes if the merge agentes is stopped.
I appreciate any help.
thanks
View 1 Replies
View Related
Jan 13, 2003
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu
View 3 Replies
View Related
Apr 13, 2006
Suppose i want to replicate data from server A to server B
I am using snaphot replication.I did the snapshot replication for the first time
and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
View 2 Replies
View Related
May 16, 2008
We have a training server and I've had a request that after each training session, we have the ability to quickly roll the training server back to its previous state so that the next group of people can be trained with the same examples.
Doing a restore requires that the maintenance (defrag, warm cache, etc..) also runs after that before the server performs fast enough.
So I was thinking of snapshots as an alternative. When you roll back to a snapshot, does that invalidate what's in the cache or have any adverse effect on query plans, stats, or indexes?
View 1 Replies
View Related
Jun 5, 2008
Hello everyone
I am learning about snapshots at the moment and i dont know how to incorporate the filegroup within the sytax
at the moment i use
create snapshot snapshotName
on (
Name='Filename_data',
Filename=@'osFIlePath')
as snapshot of DatabaseName
i get an error for filegroup, can someone tell me what im doing wrong please.
Regards
Rob Dineen
View 13 Replies
View Related
Jan 23, 2008
hello everyone
i am going through the self kit book to get my MCTS and i am stuck on
one of the practices. Creating a snapshot
I typed in
create database snapshottest
on
(
Name= 'Adventureworks_data',
Filename = 'C:program filesMicrosoft SQL servermssql.1mssqldatasnapshottest.ds')
as snapshot of adventureworks
i get the error message
msg 5127 level 16 state 1 line 1
all files must be specified for database snapshot creation
missing file "AdventureworksFT_data".
i have not stated AdventureworksFT_data
i stated Adventureworks_data
why is it asking for AdventureworksFT_data
can someone please tell me if the syntax is wrong or is there something missing
Regards
Rob
View 7 Replies
View Related
Jan 31, 2008
Hi...
Why is it that I encounter an error during execution of database snapshot i SQL Server 2005 Standard Edition. It says that database snapshot is not supported under standard versin. Is this true?
Thanks.
View 2 Replies
View Related
Jul 23, 2005
Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.
View 1 Replies
View Related
