I'm starting a new thread to keep things clean and simple. This is a transactional republishing issue.
In my simplest scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. (And C will need to transactionally replicate to D, but ignore that for now). All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C distribution doesn't work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I've gotten everything working except this. Who out there has done this and what is the secret?
We have not had any problems doing this in SQL 2000 with 36 replicated tables. In an earlier test, I had SQL 2000 Replication running on this box too, but I completely removed SQL 2000 from this box (except for my Virtual PC which is not running). I don't want to convert transactional to merge. I am running merge replication on 13 tables at the same time as the transactional replication, but am replicating different tables in each. I have no desire to increase the number of merge replicated tables.
Thanks for any advice, including any information about why this is an issue with 2005 and wasn't in 2000.
Paul
And fyi -
I followed all the rules specified here:
http://msdn2.microsoft.com/en-us/library/ms152553(SQL.90).aspx
They did not help.
|||Hi PDav,
How do you create DatabaseC? Is it created from scratch (use CREATE DATABASE) or restored from other database? Have you ever used it for any other replication purpose? As I said in another thread, replication thinks the table is published so it issues this error. So I want to know why replication think the table is published?
Could you try this query on DatabaseC and let me know the result?
select name, category from sysobjects where name = 'MyReplTable'
Thanks,
-Peng
|||Peng,
Sure, but at what point-in-time? Without implementing any replication? Without implementing replication from BtoC? With replication in both?
Oh and yes, all databases are copied from backups.
|||PDav,
With replication in both and without implenting any replication. This is just to know if this is introduced when configuring tran republishing.
So DatabaseC is copied from backups. From which backups? 80 server or other database? And do you use UI to do the backup or T-SQL?
Peng
|||-- So DatabaseC is copied from backups. From which backups? 80 server or other database? And do you use UI to do the backup or T-SQL?
The backups for the 3 databases were created by me using script from SQL 2000 (sp4) DBs and restored with script.
Databases came from replication working SQL 2000 environment.
Example BackkupScript follows:
BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH INIT,STATS
--Executed query: SELECT name, category from sysobjects where name = 'MyReplTable'
-- With merge replication implemented successfully AtoB and BtoC (using entirely different tables than tran replication)
-- With tran replication implemented successfull AtoB only. BtoC distribution fails with error ("Cannot drop the table...")
-- Run at database A
name category
MyReplTable 32
-- Run at B
name category
MyReplTable 32
-- Run at C
name category
MyReplTable 0
--After removing all replication from all levels
-- Run at database A
name category
MyReplTable 0
-- Run at B
name category
MyReplTable 0
-- Run at C
name category
MyReplTable 0
|||
Sorry, PDav. Looks like sysobjects table doesn't give us enough information to do troubleshooting. Could you use the following query again:
select * from sys.objects where name = 'MyReplTable'
Also, can you post a sample of your restore database command?
Thanks,
|||Example Scripts follows:
BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH INIT,STATS
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\MyDirectory\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\MyDirectory\data\MyDatabase.mdf',
MOVE 'MyDatabaseLog' TO 'C:\MyDirectory\log\MyDatabaseLog.ldf',
REPLACE,STATS
--Executed query: select * from sys.objects where name = 'MyReplTable'
-- With tran replication implemented successfull AtoB only. BtoC distribution fails with error ("Cannot drop the table...")
-- Run at database A
(to make readable you can use Regular expressions to replace tabs with newlines)
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 1419152101 NULL 1 0 U USER_TABLE 2003-10-23 17:38:30.417 2006-03-20 14:49:19.340 0 1 0
-- Run at B
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 981356498 NULL 1 0 U USER_TABLE 2006-03-20 14:49:38.560 2006-03-20 14:52:25.333 0 1 0
-- Run at C
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 468118100 NULL 1 0 U USER_TABLE 2006-02-16 17:27:13.717 2006-03-01 10:07:52.900 0 0 0
--After removing all replication from all levels
-- Run at database A
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 1419152101 NULL 1 0 U USER_TABLE 2003-10-23 17:38:30.417 2006-03-20 15:51:22.943 0 0 0
-- Run at B
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 981356498 NULL 1 0 U USER_TABLE 2006-03-20 14:49:38.560 2006-03-20 15:51:21.990 0 0 0
-- Run at C
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
MyReplTable 468118100 NULL 1 0 U USER_TABLE 2006-02-16 17:27:13.717 2006-03-01 10:07:52.900 0 0 0
From the result, looks like the error message is not caused by table is published by tran replication. The only other possiblity I can think of is server thinks the table is published by merge replication.
I know you have merge replication, but publish different table. Just want to make sure, could you run the following query at DatabaseC?
select * from sys.tables where name='MyReplTable'
Thanks,
|||Wow. You are correct! Here are results of 3 of the 36 tables. All 36 have the same results. So why do they have 1 for "is_merged_published" and how can I fix that? We don't include them in any merge publications, anywhere. They are only transactionally replicated.
Results for 3 tables (I removed table name column)
object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row
982215941 NULL 1 0 U USER_TABLE 2006-02-16 17:27:40.137 2006-03-01 10:08:02.540 0 0 0 0 NULL 17 0 1 0 0 1 0 0 0 0
978739931 NULL 1 0 U USER_TABLE 2006-02-16 17:27:43.043 2006-03-01 10:08:02.353 0 0 0 0 NULL 7 0 1 0 0 1 0 0 0 0
944643800 NULL 1 0 U USER_TABLE 2006-02-16 17:27:22.137 2006-03-01 10:08:02.180 0 0 0 0 NULL 6 0 1 0 0 1 0 0 0 0
|||Great. The bad news is I am not sure how to fix it. I will see if I can get someone who knows how to do it. The apparent solution is to drop DatabaseC and recreate it from scratch though.
I am also curious how this "is_merged_published" flag is introduced. But the answer the following questions may be helpful (also to the people who knows who to fix it).
1) You have merge replication. Is it on DatabaseA/B/C but publish different tables, or it is just on some totally different DBs?
2) Have you create any merge publications on the database (in SQL2000) from which DatabaseC is restored?
Thanks,
|||Answers:
1) You have merge replication. Is it on DatabaseA/B/C but publish different tables, or it is just on some totally different DBs?
It is on the same databases (A,B,C) but totally different tables. I define and start my merge publications first. Then I define and start my transactional publications. At the moment, I put in a 3 minute "waitfor" between the AtoB and the BtoC publications in both merge and trans, to ensure that the jobs are properly setup, in sequence, to allow for republishing.
2) Have you create any merge publications on the database (in SQL2000) from which DatabaseC is restored?
I may have taken the backups from "actively" replicating SQL 2000 databases in all cases (however no actual replication would have been occuring because it was from my test server, used only by me). It is also possible that I removed replication first, can't remember. In any case, the source databases are setup exactly as I have described here, same db names, same table names, same publication names, same replication job names.
|||I'm going to restore Database C from a backup that has no replication. Then I'll run the command to show if tables are marked for merge replication. Then I'll run my script. Then I'll check to see if they are marked for merge replication.
Peng, thanks very much for your help. This gives me a way to proceed. I'll update this thread with my results.
|||PDav,
Could you send me your build script? Maybe I can try and see if I can repro it on my own server? If you don't want to post it on this thread, you can send it to my email (psongbox@.hotmail.com)
Thanks,
No comments:
Post a Comment