Invalid object name after synchronize manually replication
-
I'm felling so stupid and so unlucky!!
I don't understand why... i followed the article for synchronize subscriptions without applying the initial snapshot http://support.microsoft.com/kb/320499/EN-US/
Everything OK, but in the last step when i try to apply changes to subscriber with Distribution Agent i get error!!!
"Invalid object name 'Region'.
(Source: 192.168.195.10 (Data source); Error number: 208)"
The article is the table Region of Northwind DB.
Please help me...
Thanks in advance,
Claudia
Regards,Cláudia Rego
www.footballbesttips.com
-
Any tips??
Regards,Cláudia Rego
www.footballbesttips.com
-
Claudia
Sounds like you've got a table called Regions in the published database but not in the subscriber. Check the list of articles in the publication, and make sure that all objects in that list exist in the subscriber.
John
-
John,
I'm sure that exists Region table in the subscriber and both table are equal (the same data and the same design).
But when i start distribution agent appens the following (in agent history):
1.Initializing
2.Error executing a batch of commands. Retrying individual commands.
3.Invalid object name 'Region'
And in error details i see the following:
Invalid object name 'Region'.
(Source: 192.168.195.10 (Data source); Error number: 208)
Last command:
{CALL sp_MSins_Region (7, N'Lx ')}
Transaction sequence number and command ID of last execution batch are 0x0000001D0000012E000700000000 and 1.
Please help me...
This never appen to you when using manual syncro?
Thanks
Claudia
Regards,Cláudia Rego
www.footballbesttips.com
-
Claudia
Is 192.168.195.10 the publisher or the subscriber? Is it possible that you have a case-sensitivity issue here (maybe one of the tables is called Region and the other REGION)? You say you have the same data in both tables... you must be doing something right! What happens if you add another row to the Regions table and then try again?
How did you apply the initial schema to the subscriber if you didn't use snapshot replication?
John
-
John
- 192.168.195.10 is the subscriber
- both tables have the name "Region"
- i have the same data in both tables after the manual syncro/
- i apply the initial snapshot manually following article http://support.microsoft.com/kb/320499/EN-US/ and making a full backup on the publisher. Then i paste the bckup file on the subscriber and recovery db
- when i add another row in Regions table at the publisher, appens the error "Invalid object name 'Region'"
Thanks
Claudia
Regards,Cláudia Rego
www.footballbesttips.com
-
please give me a light...
Regards,Cláudia Rego
www.footballbesttips.com
-
Claudia
Is it the LogReader Agent or the Distribution Agent that gives you the error?
John
-
John as i said before:
when i start distribution agent appens the following (in agent history):
1.Initializing
2.Error executing a batch of commands. Retrying individual commands.
3.Invalid object name 'Region'
Everything ok with log reader
Thanks
Cláudia
Regards,Cláudia Rego
www.footballbesttips.com
-
OK, so the problem is occurring in transferring data from the distribution database to the subscription database (Northwind) on 192.168.195.10. I would guess that there's a problem with the sp_MSins_Region stored procedure in that database. Does it exist? Has anybody changed it?
Does the distribution agent keep running after this error occurs? I'm guessing it does, because of what you said in your fourth post in this thread. If that's the case, does this error only occur if you try to insert a row into Region? What about if you update or delete a row in that table?
John
-
If its not a big table, you could drop it from subscription, truncate the table from subscriber, and add it back to the subscription. then start the snapshot agent. It will attempt to push all the records to the subscriber and hopefully it would work.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
John,
Is it possible that my problem is the result of a bad restore of the DB at the subcriber??
I started again from the beggining, following http://support.microsoft.com/kb/320499/EN-US/
and i have several questions...
1-should i have created the publication before starting the process described in the article?
2-If i'm using transaction log backup (instead full backup) i only need to make the transaction log backup, rigth?
And then put this file on the subscriber, rigth?
And then, restore BD from this file, rigth??
3-Should i drop Northwind DB in the subscriber?
4-How i make restore DB from transaction log backup??
Thanks a lot
Claudia (in a hot day at Portugal)
Regards,Cláudia Rego
www.footballbesttips.com
-
Claudia
1. I don't know, but I don't think it would hurt if you did. Then you can follow all the way through the article in one go.
2. Not sure what you're saying, but take a full backup of Northwind and use that to restore from on the subscriber. Northwind isn't a big database (unless you've added a lot of your own data to it).
3. No. Just restore over it.
4. You need a full backup to do a restore, even if you then apply transaction log backups afterwards. So if I were you I would just take a full backup and restore from that, as I said.
Did you check for the existence of those replication stored procedures? The more I think about it, the more I think that their absence was your problem.
John (on a cooler day in the north of England)
-
Hello!
John i just solved!!!!
I belive that the problem was when restoring DB at the subscriber.
I was succed using the complete backup of DB. I still haven't opportunity of testing the same process but using the backup of transaction log.
Thanks foryour support
Cláudia
Regards,Cláudia Rego
www.footballbesttips.com
-
Hi All
I am having a very similar problem. I have a very large database that is being replicated using one way transactional replication. I replicate this database to two totally different machines. Creating the snapshot to the first server worked perfectly. Similarly to the second server, except for a single table where I get an error on the distribution agent as follows:
Invalid object name 'tblname'.
{CALL sp_MSins_tblName (24743806, 26, NULL, 945, NULL, 117977, '', 45, 'N', 1.00, 66.80, 0.00, 13.36, 0.00, 0.00, 'N', NULL, 7210, NULL, 1, 4769, NULL, '820175', 1744879, NULL, 457, NULL, 2007-06-14 00:00:00.000, 2007-06-07 00:00:00.000, NULL, 163, 285212673, NULL, 'P', '12345443/blablabla/00JJ - 8133/1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 66.80, NULL, NULL, NULL, 1, 262145, 9, 0, 0, 0, 0, 1, 0, NULL, NULL, '', NULL, 2007-06-14 14:23:52.610, NULL, NULL, NULL, 50,
Transaction sequence number and command ID of last execution batch are 0x0010F0620000027B002900000000 and 3.
This table contains a calculated field, and the snapshot and synch agents filter by a specific date, the filter looks as follows:SELECT <published_columns> FROM <<TABLE>> WHERE
<<TABLE>>.date_start>='1 june 2007'
The replication cammand I use is as follows:
exec sp_addarticle @publication = N'DATABASE_NAME', @article = N'tblName', @source_owner = N'dbo', @source_object = N'tblName', @destination_table = N'tblName', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_tblName', @del_cmd = N'CALL sp_MSdel_tblName', @upd_cmd = N'MCALL sp_MSupd_tblName', @filter = null, @sync_object = null, @auto_identity_range = N'false', @filter_clause = N'tblName.date_start>=''1 june 2007'''
GO
-- Adding the article filter
EXEC sp_articlefilter @publication = N'DATABASE_NAME', @article = N'tblName', @filter_name = N'dbo.FLTR_tblName_1__391', @filter_clause = N'tblName.date_start>=''1 june 2007'''
GO
-- Adding the article synchronization object
EXEC sp_articleview @publication = N'DATABASE_NAME', @article = N'tblName', @view_name = N'SYNC_DATABASE_NAME_tblName', @filter_clause = N'tblName.date_start>=''1 june 2007'''
GO
EXEC sp_refreshsubscriptions @publication = N'DATABASE_NAME'
GO
The interesting thing here is that the tblName is never created on the subscriber database. Bear in mind this has worked for approximately 400 tables in the same publication subscritption combination! The other server works perfectly with exactly the same settings and the same distribution database!
I have tried
- creating the table manually, this causes only the most recent records to synch, the snap shot does not propergate.
- Dropping the table from the subscriber (manually and via Enterprise manager)
- propergating all fields except the calculation field
It is critical I get this working!!
Please help!!!!
Regards
James
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply