asked on

Cannot drop or truncate tables in replication

Hi,

We have a full load job running every night which drops and recreates tables
and load data in the olap database..and an incremental job which runs
every hour during day..
We require to implement transactional replication to this database on
another server for reporting purpose, however when replicated..
the full load job fails since the tables are used in replication and cannot be dropped.
Tried truncating instead of dropping tables, however even that does not seem to work
Can we use sp_droparticle and sp_addarticle before dropping and after creating tables
for full load job to work.
Please advise a workaround to this problem?

Thanks

virtuadept

Couple of possibilities. If the tables don't have that many rows per hour (few thousand) you can just use DELETEs and let them rep over. If its over say 5000 but under 50,000, maybe put some deletes in a loop so it will only rep over a few thousand deletes at a time in each delete. The exact number of "too many" will depend on your network speeds.

We had a similar problem before in an environment where we were running a BCP in and truncate ever 5 minutes for about 20K rows per 5 min and that was killing replication, so we changed our process to use a different database for staging and write the end results to the perm, replicated database. The staging database was not replicated. So we'd do our trunc and BCP on a database lets call it STAGE and the script or procedure did its thing and put the perm results (or you might could use a view) to the perm database that DID get replication. In our situation, we ALSO needed this data on both the "hot" and the "warm-standby" databases, so we actually had our scripts doing it on both sides to the non-rep database on each server.

SP_2018 .

ASKER

Well..the incremental load is not a problem since it does not drop tables,
the only issue is with full load where it requires to drop and recreate hem.

Please could you give an example for a table which needs to be dropped and recreate it
If we have staging table where there is no replication and perform the drop and recreate
operation
How do we propogate those changes to perm database which is getting replicated.
The view which you suggest would also truncate and repopulate the data which is
not supported in replication.?

ASKER CERTIFIED SOLUTION

virtuadept

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial