February 18, 2009 at 4:02 am
Hi all,
I am pulling data from 2000 into 2005. The tables are called the same in 2005 but are in a different schema. However in setting up replication it seems to have now created its own set of tables within the dbo schema!
How do I get it to replicate to my selection of tables instead? Simpel as changing the Replication scripts? or something less time consuming?
Thanks 🙂
February 18, 2009 at 8:33 am
You need to ensure that on the "destination object owner" property of your table articles you specify the "right" schema.
By the way you have to re-create your replication settings.
* Noel
February 18, 2009 at 9:11 am
The wizard should let you pick the schema to move things into.
February 20, 2009 at 4:36 am
Thats brilliant (both answers). Thanks!
Another side question - When you are trying to get "continue on data consistency errors" on from 2000 to 2005 - how does this work? I have =-Skiperrors turned on the subscription agent on 2005 and it DOES skip BUT it throws up as an error stating "Skipped 50 transactions". So is it an error or not?!? Very annoying.
February 20, 2009 at 8:50 am
Shark Energy (2/20/2009)
Thats brilliant (both answers). Thanks!Another side question - When you are trying to get "continue on data consistency errors" on from 2000 to 2005 - how does this work? I have =-Skiperrors turned on the subscription agent on 2005 and it DOES skip BUT it throws up as an error stating "Skipped 50 transactions". So is it an error or not?!? Very annoying.
It is dangerous to run with SkipErrors "ON".
What you are seeing is a "Warning" (stern one by the way)
You should syncronize manually your subscriber and your publisher and then turn SkipErrors "OFF"
If the subscriber was changed by any process you get those errors.
* Noel
February 20, 2009 at 8:58 am
Yes but why fail? On SQL 2000 it runs as successful but with errors skipped. Skipping errors is not really dangerous when you are expecting them and on a temporary basis.
I'm taking data from 2 servers into 1 SQL2K5 server. If I copy the data out BCP then the data has changed by the time I load it onto the destination. I turn replication on and it has missing data.
If I bcp the data with replication switched on then it will continue but may have errors needing skipping.
If I use the snapshot/sync functionality the server 1 will overwrite the data from server 2 and so forth.
Any ideas?
February 20, 2009 at 9:20 am
Shark Energy (2/20/2009)
Yes but why fail? On SQL 2000 it runs as successful but with errors skipped. Skipping errors is not really dangerous when you are expecting them and on a temporary basis.I'm taking data from 2 servers into 1 SQL2K5 server. If I copy the data out BCP then the data has changed by the time I load it onto the destination. I turn replication on and it has missing data.
If I bcp the data with replication switched on then it will continue but may have errors needing skipping.
If I use the snapshot/sync functionality the server 1 will overwrite the data from server 2 and so forth.
Any ideas?
If it "fails" you have to ensure that your distrib agents are using the correct Profile. They wont fail because of data consistency.
If the data of your TWO Primary Servers is overlapping PKs it can be a mess!
- How can you effectively tell that all data on your primaries are actually in the replica?
- If There is PK overlap and different values on other columns how can you tell which is the "correct" one, Primary Server A or Primary Server B?
* Noel
February 20, 2009 at 9:27 am
No the primary key ranges are different on each. Its 2 different sources of data within 1 table structure at the destination end.
If I turn the distribution agent on SQL 2000 to Continue on errors it makes no difference, I'm pulling from SQL 2005, and there is no option to switch profiles on the 2005 agent from what I can see.
February 20, 2009 at 10:09 am
No the primary key ranges are different on each. Its 2 different sources of data within 1 table structure at the destination end.
If the PKs are different you should *not* have data consistency errors and replication should work seamlessly
If I turn the distribution agent on SQL 2000 to Continue on errors it makes no difference, I'm pulling from SQL 2005, and there is no option to switch profiles on the 2005 agent from what I can see.
Use SQL 2005 UI Tools ( aka sqlmonitor ) Right Click on the Subscription and Select Agent Profile!
* Noel
February 20, 2009 at 3:43 pm
no no think of it like this.....
Option A
I put the subscription in place. Log reader starts building up transactions.
I bulk copy the data from Servers A and B and put onto C.
Repl Distribution then kicks in and has errors because some of the data I bulked copied had already been picked up by the log reader.
Option B
I bulk copy the data into Server C.
I then put subscriptions in place.
Some data is now missing and out of sync as transactions were entered in the period between copying out the data and setting up the subscription.
Make sense?
Server A has primary keys and unique data.
Server B has primary keys and unique data, but we have given it a different range of ID data than on Server A.
Both servers data end up on Server C.
February 23, 2009 at 6:19 am
Method I have gone for (If anyone else can think of something better be great!)
Set up Pull subscription From Server A to C with snapshot to start it off.
BCP data out of Server B and into Server C.
Set up Pull subscription from Server B to C with NO initialization.
Used a SQL Comparison tool to add anything to Server C that doesn't exist or has been changed on Server B.
Should all be sync'd then I reckon. I bet I still get some "Row already exists" errors though due to records entered on B whilst runnin the comparison tool etc.
Really suprised nobody has come across these issues.
February 23, 2009 at 9:22 am
Shark Energy (2/23/2009)
Method I have gone for (If anyone else can think of something better be great!)Set up Pull subscription From Server A to C with snapshot to start it off.
BCP data out of Server B and into Server C.
Set up Pull subscription from Server B to C with NO initialization.
Used a SQL Comparison tool to add anything to Server C that doesn't exist or has been changed on Server B.
Should all be sync'd then I reckon. I bet I still get some "Row already exists" errors though due to records entered on B whilst runnin the comparison tool etc.
Really suprised nobody has come across these issues.
This is How I would do it.
You setup replication from server A to C start snapshot normally ... wait until it synchronizes.
Then you do the same thing for Server B to C BUT on the property "Action if name is in use" of each article set it to "keep existing object unchanged"
You could use your method for higher speed but at some point you have to make sure that all rows on primary match those on the replica!
Once again You "CAN" change the profile. In my opinion you should do it temporarily though.
* Noel
February 23, 2009 at 9:38 am
Does that property mean that it will keep the object intact and only ADD the data from B, and NOT remove the already populated data?
Also is that normal behaviour when in "Continue on errors" mode for the agent to report as a failure (with the error message reading "skipped x records".?
Cheers
February 23, 2009 at 11:18 am
Does that property mean that it will keep the object intact and only ADD the data from B, and NOT remove the already populated data?
FROM BOL
sp_addarticle:
[ @pre_creation_cmd =] 'pre_creation_cmd'
Specifies what the system should do if it detects an existing object of the same name at the subscriber when applying the snapshot for this article. pre_creation_cmd is nvarchar(10), and can be one of the following values.
Value Description
none --> Does not use a command.
delete ---> Deletes data from the destination table before applying the snapshot. When the article is horizontally filtered, only data in columns specified by the filter clause is deleted. Not supported for Oracle Publishers when a horizontal filter is defined.
drop (default)
Drops the destination table.
truncate
Truncates the destination table. Is not valid for ODBC or OLE DB Subscribers.
So by definition "none" is is supposed NOT to touch the table!
Also is that normal behaviour when in "Continue on errors" mode for the agent to report as a failure (with the error message reading "skipped x records".?
It should show skipped errors as a message but it should *not* be reported as "Failure" at all! The agent must stay alive and kicking without marking its status as error!
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply