How manually syncronize Databases for Transactional replication.
-
we're using windows 2003 latest SP
SQL 2000 SP4, 8 cpu, 4 Gs of memory,
network card 1 Gigabit, etc
1. Create replication, but not the subscription.
2. Shut down all replication agents, disable snapshot agent.
3. Put database in the single user mode.
4. Dump a backup.
5. Create a new subscription.
While you are setting up the subscription, look for the Distribution Agent Schedule screen. Change the task to run only once. (This prevents the Distribution Agent from running until after you restore the database [and the transaction log backup] to the subscriber.)
6. Remove single user mode. Because subscription is set up all changes are forwarded to the distribution db.
7. Restore database at the subscriber.
8. Run
sp_scriptpublicationcustomprocs at the publisher. This procedure generates text for the stored procedures
that are required at the subscriber. Apply generated script at the subscriber.
9. Start distribution agent, change schedule to run continuously.
Ok, let me tell you something about # 8.
sp_scriptpublicationcustomprocs
, oh yes it perfectly worked for Pubs db......that was the end of it.
It has generated the script which didn't even passed the 'Parse Query' test, this is an example:
Server: Msg 103, Level 15, State 7, Procedure sp_MSins_thing_transaction, Line 4
The identifier that starts with 'Act
)
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @' is too long. Maximum length is 128.
Server: Msg 105, Level 15, State 1, Procedure sp_MSins_ecn_transaction, Line 4
Unclosed quotation mark before the character string 'Act
)
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @C'.
Server: Msg 170, Level 15, State 1, Procedure sp_MSins_thing_transaction, Line 4
Line 4: Incorrect syntax near 'Act
)
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @'.
Server: Msg 156, Level 15, State 1, Procedure sp_MSins_ECN_TradeMods, Line 2
Incorrect syntax near the keyword 'AS'.
Server: Msg 103, Level 15, State 1, Procedure sp_MSins_ECN_TradeMods, Line 4
The identifier that starts with 'addedD
)
values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19' is too long. Maximum length is 128.
Server: Msg 105, Level 15, State 1, Procedure sp_MSins_ECN_TradeMods, Line 4
Unclosed quotation mark before the character string 'addedD
)
I think that is because the output has only 4000 characters limit. I believe i have red it somewhere.
Ok i said to myself, let's use the Enterprise manager to generate a script for sys repl stored proc for insert update and delete.
Applied it to subscriber, wow successfully.
It did it or so i was thinking. After starting the replication it.......failed with... you all know this error message
"Procedure or function sp_MSins_ecn_subscriber has too many arguments specified.
(Source: BACKUPSQLSRV1 (Data source); Error number: 8144)"
What happened is, if for example table has 10 tables, the script has generated arguments only for 6.....
It was a case for every single table we have in our database, so i have to go through every replication's stored proc
and add those arguments manually. Very strange though, that when i was doing my research on the internet, no body
was mentioning the same fix i was forced to figure out on my own
.People were saying about re-applying the SP4 or maybe two systems have a different sql2000 versions (service packs)
and so on and so forth. I am wondering is it A Microsoft bug or just a limitation and would it has a limit for the output?
I hope this post will help somebody, because for me , considering size of our database, it took 12 hours to take care of the replication.
Peopls pls talk to me, what you think about all this.
-
- Fellas, i couldn't believe that no one had the similar problems......
- come on , talk to me...
-
Ok!
I have done this and i'm always getting the error:
invalid object name 'jobs'
When distribuion agent tries to deliver transanctions!!
WHY??
thanks
claudia
Regards,Cláudia Rego
www.footballbesttips.com
-
Slava, i am having the same problem while i try to setup a transactional replication. Wondering if you were able find a solution. Please share
Cheers
Shas3
-
Figured out the problem. The script is not completely copied since the result is displaying in less character....Choose Tool-->Option-->Results-->max char set-->8612
Cheers
Shas3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply