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
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.
What is a snapshot from replication point of view.
------------------------
I think, therefore I am - Rene Descartes
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
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
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
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
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
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!
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
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
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
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
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?
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?
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
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.
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 RelatedHiI am relatively new to databases. I would like to be able to run aquery that returns the t_no, b_no, status and cpu for the latestrecord for each unique combination of (t_no, b_no and cpu) before agiven point in time (say @snap_time). d_no is an autoincrementingprimary key.d_no t_no b_no status cpu update_time1 500 3 0 1 3:012 501 3 1 1 3:023 501 3 0 1 3:034 502 3 1 1 3:045 503 3 1 1 3:056 500 3 1 1 3:10In this example, if @snap_time = '3:15' the query would return the(t_no, b_no, status, cpu) for records 3, 4, 5, 6 because 1 issuperceded by 6 (which has the same t_no, b_no, cpu combination butoccurs later) and 2 is superceded by 3.If @snap_time = '3:03', the query would return records 1 and 3 becauserecords 4, 5 and 6 were created at a later update_time and record 3supercedes record 2.The query:SELECT MAX(update_time), t_no, b_no, cpu, statusFROM my_tableWHERE (time <= @snapTime)GROUP BY t_no, b_no, cpureturns an error:Column 'dbo.my_table.status' is invalid in the select list because itis not contained in either an aggregate function or the GROUP BYclause.And even if this did work, ideally I would like to suppress theupdate_time field from appearing.How could I do such a query and is it even possible without usingsomething more sophisticated than a select query?Many thanksJon
View 1 Replies View Relatedhello group,is it possible to do a storage snapshot of a running ms-sql databasewithout losing transactions?What tasks must be done before such a snapshot.thanks in advance,Bernhard
View 7 Replies View RelatedHi,
I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.
Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.
Thanks,
Prashant
Need some help, i have some database snapshots files provided from an external source. Need to be able to understand how i get them back into a database format if possible.
Files for example are table1.bcp, table2.bcp with als a file called scheme.sql which sets up these tables in sql but does not populate them. Nothing else was provided except a .vcd file which i dont know whtat its for.
any clues?
Dan
I get an error "Error: Subreport could not be shown." when I set subreport rendering from snapshot. When I render subreport with the most recent data everything works fine.
Does anybody know how to use snapshots for subreport? Thanks in advance.
Can anyone tell me where the data for a snapshot is kept at?
R
How can I, with TSQL script, know when a snapshot run last time?
Thank You.
Hi Everyone
Im a newbie on this but I have created a couple of reports and am running sql server 2005 and have downloaded all the updates. The problem i have is that when i view the report i have no History Tab and it is not listed in properties and cannot create snapshots even in sql man. studio. I have set the snapsots to unlimited in site settings. what am i doing wrong that is so obvious.
cheers
Barry
I had it all set up to take a snapshot of my OLAP as the first step in my package and restore from it on error...then I started to think.....isn't that what a transaction is supposed to do?
Does anyone have any experience of the pros and cons of either?
Thanks.
Hi,
Wanted to clarify some doubts about Report History and Snapshot.
1. What is the difference between the snapshot id and history id. Report Snapshots are stored in the report history.
When I call CreateReportHistorySnapshot method programmatically, the method returns a history Id. Is this id different from the snapshot id (report snapshot which is stored in the history). If the id's are different how can I get the snapshot id ?
2. The History id is a date-time stamp of the snapshot. This string is used as a unique identifier for the historical snapshot. It contains the time portion till seconds(not milliseconds). Hence if I generate the next snapshot in the same second it gets overridden.
Is there any way to create more snapshots in the same second ?
Is there any way to change the algorithm used to generate history ids ? eg : Can I use some unique id generator and get history id using this algorithm ?
Thanks in advance,
Hello,
Here's the idea: There is a central SQL Cluster Server and there are branches in different cities. I'll replicate databases from branch SQL Servers to the central SQL Server. Every city will have a database in the central SQL Server.
While i was creating publication for replication, i get the following errors from different (6-7) tables among 289 articles.
----------------------------------------------------
2007-04-20 12:00:45.18 [46%] The replication agent had encountered an exception.
2007-04-20 12:00:45.18 Source: Replication
2007-04-20 12:00:45.18 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-04-20 12:00:45.18 Exception Message: Incorrect syntax near the keyword 'percent'.
2007-04-20 12:00:45.18 Must declare the scalar variable "@metadata_type".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@p8".
2007-04-20 12:00:45.18 Failed to generate merge replication stored procedures for article 'mf_pro_processeditem_financial'.
2007-04-20 12:00:45.18 Message Code: 156
2007-04-20 12:00:45.18
----------------------------------------------------
These articles are belong to a software which is developed by another company.
Any ideas?
SQL Version : Enterprise 2005 with SP2
Replication type: Merge
Thanks,
Eko
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
------------------------------
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER B'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
Dear Colleagues,
I am using Windows Server 2003 RT, and SQL Server 2005.
When Replication is performed with SQL Server Mobile. All 3 taskes i.e. Publisher creation, Article Publish, Snapshot agen starting. All are ok.
However after replication is done when checking the Status of the Snapshot Agent it is not Running, and I cannot make it Run as well.
Please advice me as to what could possibily be wrong with it, what can I do.
Thankx.
Hi guys , I just want to know whethere anyone here faced this experience before. Let say I take a snapshot on a particular database (Test database as example). Then I open a SQL query and write a simple query ( use Test_snapshot; Select * from admin ). Obviously I am able to retrieve all the data from admin table in this snapshot database. However , if I snapshot again (assume i already wrote a store procedure which able to overwrite the snapshot) and I run that t-sql on that SQL query again , it displays this error message. 'Transport-level error ' But once I execute this again, then it displays the data again. Can someone explain to me please ? If this goes on, no point for me running a report services using snapshot feature. Thx for the assistance. Have a nice day.
Best Regards,
Hans
i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \serverfolder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '---' is not yet available". can you guide me what are causes of this problem and how may i solve it?
Thanks for help.
Regards,