August 4, 2009 at 9:21 am
Hello, everyone.
I am an SQL Server DBA and I have recently assigned the task to create a Replication infrastructure between several SQL Servers that support Pharmaceutical software.
Basically I want to sync table content between two tables that are equal in name but not in column order. Otherwise, every field on the two tables is identical, has the same constraints, etc..
I can create the initial snapshot of the table, but when I apply the Snapshot on the remote Subscriber SQL complains about "Field Size too Large".
This problem only goes away when I delete the remote table and let SQL recreate it with the article's "DROP" option...
I have even tried advanced customization like creating the Articles manually with sp_addarticle and using the @status = 24 option and another one I need, @fire_triggers_on_snapshot = N'true'.
Can anyone help out? I just dont understand how something so simple as respecting column names can be so dificult for the BCP utility...
I have tried the BCP command that SQL Replication Monitor gives me and no luck: syntax error. This is incredible 🙁
Thanks in advance,
Ivo Pereira
IT Consultant
Portugal
August 6, 2009 at 12:51 pm
Here is what you can do. Set up the replication with no Snapshot. That means no data will be transferred. Here are things you can try to make it work.
1. Import the data from the publisher to another temporary table in the subscriber .
2. Insert into your Subscriber target table by writing a Insert into (column list) Select column list from temp table.
3. Run the sproc sp_scriptpublicationCustomProcs to generate the replication script and modify that with the right order in your insert script.
This option would probably work.
-Roy
August 7, 2009 at 1:47 am
I have tried the BCP command that SQL Replication Monitor gives me and no luck: syntax error. This is incredible
Can you post the BCP command and the error message for us to look at please?
August 7, 2009 at 10:55 am
Hello, everyone.
I just found out today that my application's database table triggers are ENCRYPTED and so SQL Server cannot replicate them... Forgive the newbie lack of experience but I usually say: "knowledge comes from banging your head in real life, not in books" 🙂
So, having said that, I still have the column order problem. The suggestion to use SP_SCRIPTPUBLICATIONCUSTOMPROCS is indeed very good and useful. I am trying to squeeze some time to develop all customized SPs for the initial sync... in the meantime for new Databases I can easily use a T-SQL Script to create Publication, add the articles, etc..
One problem I found is that since I drop the remote tables and SQL cannot recreate my encrypted triggers, using @FIRE_TRIGGERS_ON_SNAPSHOT has no effect since they don´t exist on the remote tables... So I end up synchronizing more tables that inicially planed (the tables that the triggers should automatically fill in for me 🙁 )
But I still have another pain in the butt: because of the above problem I cannot sync any data without dropping remote tables to recreate them.. This is more than inconceivable because our software is a 24-Hr Pharmaceutical aplication.. 🙁 It just can´t stop for me to drop tables or export data...
Anyway, I found that BCP is still useless so posting the command will not help me. I tried removing the row filtering expressions, etc, no luck: just blank output and "Zero rows copied". Forgive my anger: WHAT A PIECE OF JUNK SOFTWARE...
Is there any way I can just replicate the table schemas, initially? Even droping the tables and recreating them without row data would be faster than doing what I do now... (replicating whole tables). This would at least attenuate the problem.
Any ideas, anyone?
Cheers,
Ivo Pereira
IT Consultant
Portugal
August 7, 2009 at 11:33 am
Cant you just take a back up and restore?
-Roy
August 9, 2009 at 4:01 am
Hello again.
For some cases I can, indeed, take a backup of the database, restore it and configure a new Publication without Snapshot creation. I have, in the meantime, tested this sucessfully but I cannot use this method for all databases.
The reason for this is related to the business model of my clients in the Pharmaceutical area (24-hr open Pharmacies (I can´t really find the correct English term 🙂 )
They all have a local SQL Server to store their data, run their applications, etc. On another central server, I have other older-version databases that only run parts of the software suite (Accounting, Salaries, etc) that need to be fed with new Billing data dinamically.
It is not by my will that these databases are different from the ones in the 24hr Pharmacies, it's a design consideration that surpasses me 🙁 In these databases the Accountants access the server remotely with Terminal Services to insert and calculate financial data.
However, they started working with these databases before I started the replication project.. So they already have much content and hence I cannot (or do not have the time to; i know its possible...) drop those tables to rebuild them.
Cheers,
Ivo Pereira
IT Consultant
Portugal
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply