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
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!
Hi,
I am in need of some help with this error - I am seeing on my PDA units. Here is my scenario:
I have 5 PDA units that use merge replication with SQL 2005. These PDA units replicate fine. But when I try to sync a 6th or 7th PDA unit, I see the following error:
Source : Microsoft SQL Server 2005 Mobile Edition - The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized. -2147198698
I know that if I regenerate a new snapshot, I will be able to sync fine. But here are my concerns and questions:
What happens to the first 5 PDAs when I regenerate the a new snapshot.
Will they be able to sync without losing the data?
Does generating a new snapshot, relinitialize all the subscriptions?
Am I missing the whole picture with generating a new snapshot?
Thanks,
P
Hello,
I need to find all of the SQL Server objects, (tables, procs, functions, etc.) that my application is NOT using, so they can be removed from the system. Does anyone know how to accomplish this?
Thank you,
Dave
:mad: It seems that the SQLmaint is going to obsolete in SQL 2005, what happen to my "sophisticated" SQLmaint scripts!!!! Any help or comment?
View 8 Replies View RelatedWhen I find that a column should no longer be used because of design changes. ( Moved column to other table to correct design normalization etc.)
I added a check to make sure the column must be NULL.
The following list the obsolete columns
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE IS_NULLABLE = 'YES' AND cc.CHECK_CLAUSE = '([' + col.COLUMN_NAME + '] IS NULL)'
ORDER BY col.TABLE_NAME, col.COLUMN_NAME
In the past columns could not be set to null.
This script list those columns that I need to add null checks to.
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME, cc.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.COLUMNS col
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE cc.CHECK_CLAUSE LIKE '([[]' + col.COLUMN_NAME + '] =%' AND
NOT cc.CHECK_CLAUSE LIKE '% OR %' AND
NOT cc.CHECK_CLAUSE LIKE '% AND %' AND
NOT cc.CHECK_CLAUSE LIKE '% IS %' AND
NOT cc.CHECK_CLAUSE LIKE '% IN %'
ORDER BY col.TABLE_NAME, col.COLUMN_NAME
Tim S
Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.
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 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 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
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.