Replication Error...

I have a SQL server which has the databases installed with transactional replciation enabled. I got an error which is mentioned below in my distribution agent history :-

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.
(Source: FLORA\INSTANCE1 (Data source); Error number: 2627)
--------------------------

Can anyone let me know the reason for the above mentioned error?

Thanks,
Santhosh.C.Your subscriber is not in sync with your publisher. If the database is not too big or you can shut down the app, go ahead and reinitialize the subscription to sync the replication.

replication error while copy data

Hi,
I have a transactional replication between SQL Server and Oracle. We already have the schema exist in both databases. So the the snapshut I check the 'If the table exists, leave it unchanged' box. But when the data start to load for the intial snapshut, I
got an error saying' The process encountered invalid column data in bcp file ...' for an existing table. How can me trace down which column has error? What is the data type for the Rowguid in my subscriber ( I do not have a rowguid column setup in the su
bscriber)?
Thanks
Hong,
was this error encountered when creating the snapshot file or when
distributing it to Oracle?
You can get some more (hopefully useful) info by logging:
http://support.microsoft.com/?id=312292
HTH,
Paul Ibison
|||It is encountered when distributing it to Oracle.
Hong Wang
"Paul Ibison" wrote:

> Hong,
> was this error encountered when creating the snapshot file or when
> distributing it to Oracle?
> You can get some more (hopefully useful) info by logging:
> http://support.microsoft.com/?id=312292
> HTH,
> Paul Ibison
>
>
|||OK, then try the logging I mentioned but I had hoped that it was a snapshot
agent error, as this is almost certainly an Oracle issue - mapping of
datatypes etc. Anyway, let's see if the logging returns anything.
Regards,
Paul Ibison
sql

Replication Error - SharePoint 2007 DB

I have several SharePoint 2007 SQL 2005 databases that are replicating fine.

However, I have the WSS_Content _db that errors every time as shown below.

This only happens on the table shown, not on any of the other tables.

I am looking for confimation that this is a glitch withing SQL 2005 and there is nothing I can do about it pending a service release.

Anyone else run into this?

---

Error messages:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Specialized.StringCollection ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[])
Message: Script failed for Table 'dbo.NameValuePair_Hindi_CI_AS'.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)

Can you exlain your scenario in more detail? Are you using transactional replication? Also, are you using Hindi as table name? What collation are you using?

Gary

|||

The 'Hindi' table is part of the database that was automatically installed by SharePoint, along with numerous others.

Wish I could include a photo.

Some of the other tables are Greek, Hebrew, Hindi, Hungarian, Japanese....etc

They start with Albanian and end with Vietnamese.

The backup works great until it hits the 'Hindi' table and then crashes.

If this wasn't a production environment I would delete the table and see what happened.

Thanks.

Replication down Log Shrinking

Hi,

Is there any relation between replication and the transaction log, we have set up transactional replication between our databases, and I had to clear the log as its size was huge, I also reduced the temp db size. After that, the replication is down, with an error, the subscriptions are invalid, reconfigure the Topology.

Is this related to the shrinking of the logs ?

These two things (shrinking the log, and the expiration of subscription) are not related.|||Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?|||

. Transaction log and Transactional replication very much related.

Transaction Log Space

For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances.

Ref :

http://msdn2.microsoft.com/en-us/library/aa237475(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Madhu

|||

see the first thing i want to mention here is you should never ever trucate the TL of a replicated DB. Each subscription has a lif which is by default (336 hrs ) if i remember correctly, which can be changed in the property of of a publication. If you restart server the replication will not effect. it will re-start from where it left.

Madhu

|||

Why do you need to reduce the size of tempdb?

DO you have any free disk space issues?

I wouldn't suggest to shrink the TEMPDB and keep up the log size with regular transactionlog backups, as you have more information about Trans.log & Trans.replication relation.

Rick305652 wrote:

Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?

Replication doesnt replicate

Ok, I'm knee-deep in replication-hell.

I'm trying to set up snapshot replication between two SQL Server 2000 databases over the internet. Both servers run Windows 2000 server edition and SQL server 2000. The idea is to send certain local information, stored in database tables to a database from which the information is published on a website.

I've set up the distribution database (with the original name: distribution) and connected a publisher to it (let's call that one "source_database") using the sp_adddistpublisher stored procedure. No problems there...
Next step, I added a publication, the tables in source_database, and a subscriber, the website database. I've opened up port 1433 on both servers, but still it won't send anything from the source_database to the website database...

It gives no errors, but just doesn't do anything...
Now, I've looked for possible problems and I think these problems might be able to cause problems:
- The initial snapshot is not reaching the subscriber
- I've made a push subscription which may has to become a pull subscription
- The RPC's aren't available, because both servers are highly secured
- The wrong serverpack is installed (this one is a wild guess)

Does anybody have any idea on how I can get my distribution working or solve any of the problems above, cause I'm running out of idea's...
(P.S. I'm a bit of a noobie to replication, I've set it up in a test environment before and it just worked perfectly, but the real thing isn't)You may have already done it, but just to make sure that you have define the publisher and the subscriber in the source server, right? Does your source server know the network id of the target server? Simply try to register the target server's name on EM of the source to see.|||Service pack will get you bad. I experimented with 2 servers running 194 and 818 builds, and was getting nothing, no matter what I tried. And when you apply SP3 you'd notice that there are scripts that directly affect replication (repl*.sql)|||I solved the problem...
It did turn out to be the service pack.
Sp3a was installed by a sys-admin, but not correctly.
After re-installing Sp3a everything just started working.

Man, I'm glad that one's solved, I had like 5 minutes left till insanity... :cool:

Anyhow, thanx for the help...|||Man, I'm glad that one's solved, I had like 5 minutes left till insanity... :cool: Oh, so you aren't really a dba yet? Actually, once I gave up on the sanity part, the rest seemed a lot easier!

-PatP

Replication does not seem to work for rows inserted by a trigger.

Hi all,
Maybe I'm being a bit thick, but here goes.
I have merge replication set up between TableA on different databases
(say X and Y). X and Y are on different servers on different physical
machines.
If I run (in Query Analyser) an insert statement that inserts onto
X.TableA, all is well. The row appears on Y.TableA. Ditto for update
and delete.
If, instead, I place the exact same insert statement as the body of a
trigger on X.TableB and execute a SQL statement on X that causes the
trigger to fire - then the row(s) is(are) inserted onto X.TableA but
never arrive at Y.TableA.
This worked fine when both databases were on the same server.
What am I missing?
Kirsty
Do they show up in the conflict viewer? If not, can you post the schema for
the tables and triggers here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<duff@.netcomuk.co.uk> wrote in message
news:1117029413.775980.20700@.g43g2000cwa.googlegro ups.com...
> Hi all,
> Maybe I'm being a bit thick, but here goes.
> I have merge replication set up between TableA on different databases
> (say X and Y). X and Y are on different servers on different physical
> machines.
> If I run (in Query Analyser) an insert statement that inserts onto
> X.TableA, all is well. The row appears on Y.TableA. Ditto for update
> and delete.
> If, instead, I place the exact same insert statement as the body of a
> trigger on X.TableB and execute a SQL statement on X that causes the
> trigger to fire - then the row(s) is(are) inserted onto X.TableA but
> never arrive at Y.TableA.
> This worked fine when both databases were on the same server.
> What am I missing?
> Kirsty
>
|||Hilary,
thanks for your response.
There are no conflicts.
SQL below (LONG: sorry, the tables do have a fair few fields).
I have also tried with a different subscriber on a different server (we
have different servers in different countries). Same problem.
Additionally, and MOST strangely, if I then UPDATE (in query analyser)
one of the rows sitting in the table on server X, the row then turns up
on server Y.
Kirsty
/************************************/
--'tableA'
CREATE TABLE [_ST_SageToTess] (
[Order_no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Order_line_no] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CountryCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CountrySiteCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Cust_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Cust_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cust_Address] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_County] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Cust_Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cust_Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Cust_VAT_Num] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_Cur_Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Cust_VAT_Code] [smallint] NULL ,
[Cust_Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Part_Num] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Part_Desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod_Num] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Prod_Desc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Site_Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Address] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_County] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_Main_Contact] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site_Phone1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Site_Cust_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Site_Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Ser_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Cont_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Employ_Num] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF___ST_SageT__rowgu__51628081] DEFAULT (newid()),
CONSTRAINT [PK_ST_SageToTess] PRIMARY KEY CLUSTERED
(
[Order_no],
[Order_line_no]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
--'TableB'
if not exists (select * from dbo.sysobjects where id =
object_id(N'[scheme].[opheadm]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
BEGIN
CREATE TABLE [opheadm] (
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__order_n__65062755] DEFAULT (' '),
[alpha] [char] (8) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__alpha__65FA4B8E] DEFAULT (' '),
[customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__custome__66EE6FC7] DEFAULT (' '),
[address1] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__67E29400] DEFAULT (' '),
[address2] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__68D6B839] DEFAULT (' '),
[address3] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__69CADC72] DEFAULT (' '),
[address4] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__6ABF00AB] DEFAULT (' '),
[address5] [char] (32) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__address__6BB324E4] DEFAULT (' '),
[invoice_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__6CA7491D] DEFAULT (' '),
[statement_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__stateme__6D9B6D56] DEFAULT (' '),
[group_customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__group_c__6E8F918F] DEFAULT (' '),
[date_entered] [datetime] NULL CONSTRAINT
[DF__opheadm__date_en__6F83B5C8] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_received] [datetime] NULL CONSTRAINT
[DF__opheadm__date_re__7077DA01] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_required] [datetime] NULL CONSTRAINT
[DF__opheadm__date_re__716BFE3A] DEFAULT
(convert(datetime,'31/12/1899',103)),
[date_despatched] [datetime] NULL CONSTRAINT
[DF__opheadm__date_de__72602273] DEFAULT
(convert(datetime,'31/12/1899',103)),
[invoice_due_date] [datetime] NULL CONSTRAINT
[DF__opheadm__invoice__735446AC] DEFAULT
(convert(datetime,'31/12/1899',103)),
[customer_order_no] [char] (20) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__74486AE5] DEFAULT (' '),
[territory] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__territo__753C8F1E] DEFAULT (' '),
[class] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__class__7630B357] DEFAULT (' '),
[region] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__region__7724D790] DEFAULT (' '),
[cust_disc_code] [char] (4) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__cust_di__7818FBC9] DEFAULT (' '),
[customer_vat_code] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__790D2002] DEFAULT (' '),
[credit_category] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__credit___7A01443B] DEFAULT (' '),
[status] [char] (1) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__status__7AF56874] DEFAULT (' '),
[hold_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__hold_in__7BE98CAD] DEFAULT (' '),
[ack_sent_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__ack_sen__7CDDB0E6] DEFAULT (' '),
[invoice_date] [datetime] NULL CONSTRAINT
[DF__opheadm__invoice__7DD1D51F] DEFAULT
(convert(datetime,'31/12/1899',103)),
[invoice_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__7EC5F958] DEFAULT (' '),
[indicators1] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__indicat__7FBA1D91] DEFAULT (' '),
[indicators2] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__indicat__00AE41CA] DEFAULT (' '),
[price_list] [char] (3) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__price_l__01A26603] DEFAULT (' '),
[order_discount] [float] NOT NULL CONSTRAINT
[DF__opheadm__order_d__02968A3C] DEFAULT (0),
[credit_limit] [float] NOT NULL CONSTRAINT
[DF__opheadm__credit___038AAE75] DEFAULT (0),
[settlement_discoun] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__047ED2AE] DEFAULT (0),
[hdcomm_unused] [float] NOT NULL CONSTRAINT
[DF__opheadm__hdcomm___0572F6E7] DEFAULT (0),
[spare1] [float] NOT NULL CONSTRAINT [DF__opheadm__spare1__06671B20]
DEFAULT (0),
[spare2] [float] NOT NULL CONSTRAINT [DF__opheadm__spare2__075B3F59]
DEFAULT (0),
[settlement_days] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__084F6392] DEFAULT (0),
[labels] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__labels__094387CB] DEFAULT (' '),
[warehouse] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__warehou__0A37AC04] DEFAULT (' '),
[wo_order_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__wo_orde__0B2BD03D] DEFAULT (' '),
[pick_list] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__pick_li__0C1FF476] DEFAULT (' '),
[vehicle_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__vehicle__0D1418AF] DEFAULT (' '),
[spare_field] [char] (18) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__spare_f__0E083CE8] DEFAULT (' '),
[consolidation_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__consoli__0EFC6121] DEFAULT (' '),
[transaction_anals1] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__0FF0855A] DEFAULT (' '),
[transaction_anals2] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__10E4A993] DEFAULT (' '),
[transaction_anals3] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__transac__11D8CDCC] DEFAULT (' '),
[back_to_back_ind] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__back_to__12CCF205] DEFAULT ('N'),
[vat_method] [char] (1) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__vat_met__13C1163E] DEFAULT (' '),
[po_allocated_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__po_allo__14B53A77] DEFAULT ('N'),
[quote_proforma] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__quote_p__15A95EB0] DEFAULT (' '),
[pricing_date] [datetime] NULL CONSTRAINT
[DF__opheadm__pricing__169D82E9] DEFAULT
(convert(datetime,'31/12/1899',103)),
[consol_invoice_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__consol___1791A722] DEFAULT (' '),
[comment_only_flag] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__comment__1885CB5B] DEFAULT ('N'),
[direct_debit_inv] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__direct___1979EF94] DEFAULT (' '),
[invoice_required] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__invoice__1A6E13CD] DEFAULT ('Y'),
[vat_type] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__vat_typ__1B623806] DEFAULT (' '),
[lang] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__lang__1C565C3F] DEFAULT (' '),
[delivery_method] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__deliver__1D4A8078] DEFAULT (' '),
[carrier_code] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__carrier__1E3EA4B1] DEFAULT (' '),
[payment_method] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__1F32C8EA] DEFAULT (' '),
[customer_class] [char] (8) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__custome__2026ED23] DEFAULT (' '),
[sales_type] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sales_t__211B115C] DEFAULT (' '),
[lower_value] [float] NOT NULL CONSTRAINT
[DF__opheadm__lower_v__220F3595] DEFAULT (0),
[payment_date_1] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__230359CE] DEFAULT (' '),
[payment_date_2] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__23F77E07] DEFAULT (' '),
[payment_date_3] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__24EBA240] DEFAULT (' '),
[court_charges] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__court_c__25DFC679] DEFAULT (' '),
[shipno] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__shipno__26D3EAB2] DEFAULT (' '),
[date_promised] [datetime] NULL CONSTRAINT
[DF__opheadm__date_pr__27C80EEB] DEFAULT
(convert(datetime,'31/12/1899',103)),
[from_indicator] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__from_in__28BC3324] DEFAULT (' '),
[from_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__from_re__29B0575D] DEFAULT (' '),
[sl_batch] [char] (6) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_batc__2AA47B96] DEFAULT (' '),
[sl_period] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_peri__2B989FCF] DEFAULT (' '),
[sl_year] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__sl_year__2C8CC408] DEFAULT (' '),
[payer] [char] (10) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__payer__2D80E841] DEFAULT (' '),
[responsibility] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__respons__2E750C7A] DEFAULT (' '),
[schedule_number] [char] (15) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__schedul__2F6930B3] DEFAULT (' '),
[to_reference] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__to_refe__305D54EC] DEFAULT (' '),
[payment_terms_desc] [char] (20) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__payment__31517925] DEFAULT (' '),
[shipping_note_ind] [char] (1) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__32459D5E] DEFAULT ('Y'),
[delivery_reason] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__deliver__3339C197] DEFAULT (' '),
[shipper_code1] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__342DE5D0] DEFAULT (' '),
[shipper_code2] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__35220A09] DEFAULT (' '),
[shipper_code3] [char] (10) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shipper__36162E42] DEFAULT (' '),
[shipping_text] [char] (60) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__370A527B] DEFAULT (' '),
[appearance] [char] (60) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__appeara__37FE76B4] DEFAULT (' '),
[total_packages] [int] NOT NULL CONSTRAINT
[DF__opheadm__total_p__38F29AED] DEFAULT (0),
[total_ship_weight] [float] NOT NULL CONSTRAINT
[DF__opheadm__total_s__39E6BF26] DEFAULT (0),
[shipping_site] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__3ADAE35F] DEFAULT (' '),
[sett_category] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__sett_ca__3BCF0798] DEFAULT (' '),
[settlement_days2] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3CC32BD1] DEFAULT (0),
[settlement_days3] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3DB7500A] DEFAULT (0),
[settlement_days4] [int] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3EAB7443] DEFAULT (0),
[settlement_disc2] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__3F9F987C] DEFAULT (0),
[settlement_disc3] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__4093BCB5] DEFAULT (0),
[settlement_disc4] [float] NOT NULL CONSTRAINT
[DF__opheadm__settlem__4187E0EE] DEFAULT (0),
[short_name] [char] (3) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__short_n__427C0527] DEFAULT (' '),
[shipping_dept] [char] (2) COLLATE Latin1_General_BIN NOT NULL
CONSTRAINT [DF__opheadm__shippin__43702960] DEFAULT (' '),
[effective_date] [datetime] NULL CONSTRAINT
[DF__opheadm__effecti__44644D99] DEFAULT
(convert(datetime,'31/12/1899',103)),
[period] [char] (2) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__period__455871D2] DEFAULT (' '),
[slyear] [char] (4) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT
[DF__opheadm__slyear__464C960B] DEFAULT (' ')
) ON [PRIMARY]
END
GO
--trigger (for testing replication)
CREATE TRIGGER scheme.ST_upd_opheadm ON [scheme].[opheadm]
FOR UPDATE
AS
BEGIN
insert into _ST_SageToTess (Order_no,
Order_line_no,CountryCode,CountrySiteCode, Cust_Num, Part_Num,
Prod_Num,
Site_Num, Ser_Num, Cont_Num, Employ_Num)
values
(1,1,'UK','XX','ABC','12345','12345A','gggg','kkk' ,'jkjkgjg','999')
end
GO
|||Sorry, the opheadm table is actually scheme.opheadm. As you might
guess, this one's not mine, but part of our Sage database.
Kirsty

Replication Dilemma - Need a Solution/Ideas

Hi all,
Here's a situation I have been challenged with....
I will have up to 6 remote databases and I want them all to update a
single master database in a company with multiple production plants.
The remote databases will just have active work in a plant and only the
work that is in that particular plant. I would like the master
database to have all information from the plants. Ideally, the remote
plants will update the master database once an hour (consisting of new
inserts of orders coming into the plant and updated records as work
moves through the plant). Once work ships from a plant, I want to purge
it off the remote plant database, yet keep it on the master.
It seems with replication, if I delete records at a plant, it will
delete on the master, which is not what I want. The master is
basically the database customer service will use and needs active
status on work in the plants and would contain a couple of years of
information. The remote plant databases have just the data they need
and our optimized. A remote database has 20 tables, with multiple
tables with 1 to 20 million records actively inserted/udated each day.
The schema of the master is identical to the plants and all keys at
each plant are guaranteed unique.
Any ideas would be greatly appreciated, as I am currently contemplating
using bcp in an automated fashion and anticipating a nightmare of
keeping referential integrity.
Regards
What I think you need to do is the following.
1) create a filter on your publications which filter by plant id. Hopefully
you have a column to do this with. Then when you create you publication and
get to the specify articles dialog click on the browse button to the right
of your tables. Select the snapshot tab, and select the option to delete the
data which matches the row filter.
2) In the table article properties dialog, click on the commands tab and
replace the delete command with the word NONE - this will prevent deletes
from being replicated
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<davidkrainess@.yahoo.com> wrote in message
news:1131135473.722037.278030@.g44g2000cwa.googlegr oups.com...
> Hi all,
> Here's a situation I have been challenged with....
>
> I will have up to 6 remote databases and I want them all to update a
> single master database in a company with multiple production plants.
> The remote databases will just have active work in a plant and only the
> work that is in that particular plant. I would like the master
> database to have all information from the plants. Ideally, the remote
> plants will update the master database once an hour (consisting of new
> inserts of orders coming into the plant and updated records as work
> moves through the plant). Once work ships from a plant, I want to purge
> it off the remote plant database, yet keep it on the master.
> It seems with replication, if I delete records at a plant, it will
> delete on the master, which is not what I want. The master is
> basically the database customer service will use and needs active
> status on work in the plants and would contain a couple of years of
> information. The remote plant databases have just the data they need
> and our optimized. A remote database has 20 tables, with multiple
> tables with 1 to 20 million records actively inserted/udated each day.
> The schema of the master is identical to the plants and all keys at
> each plant are guaranteed unique.
> Any ideas would be greatly appreciated, as I am currently contemplating
> using bcp in an automated fashion and anticipating a nightmare of
> keeping referential integrity.
> Regards
>
|||Thanks...
My initial thoughts were a bcp dump, zipping the data, sending it over
the WAN, and unzipping then loading it with update/insert SPs. bcp is
incredibly fast, but this strategy requires a lot of management
applications written to ensure the data gets to the master and there
might be loss of some referential integrity as each bcp command would
dump a table at a different time interval (that would be fixed in the
next hourly update). I am intrigued by the replication strategy
because of it's ease of use.
Assuming I have the distributon on the master, what is the performance
impact on the plant databases, assuming a publishing every hour. I am
concerned that the overhead of a replication scheme is vastly greater
than a bcp dump.
What is going on with the replication and how do people set this up. I
guess I was thinking of each plant's database dumping it's repl data on
a remote share over a WAN on the distribution server. I was thinking
the Master would reside on the same server as the distribution (why not
take advantage of loading the data off of a local disk). bw, I would
rather take slowness on the master than in a production facility.
Also...what's the overhead of the replication dump, is it similar to a
log? Given a WAN is down, would sql recover and recopy the replication
data when the WAN came backup?
Also, is this information in any of the replication books out there
(I.E. enterprise strategies for moving data) with pros and cons of
different methods.

Replication design

Hi,
I have the following scenario, and need to configure
replication for this. Pls advice.
We have 3 SQL server databases, at 3 locations on SQL
server 2000 servers. They are connected over WAN by 2
MBPS link (triangle). I have to configure replication on
this. Database has around 200 tables, and size of
database would be around 200-500 MB each. Changes would
be minor on daily basis.
I have these servers at following locations.
Loc1 (HeadOffice)
Loc2 (branch office)
Loc3 (branch office)
Data has to replicate from Loc2 and Loc3 to Loc1 at short
intervals during the day.
Somewhere in midnight (off peak hours), data has to
replicate from Loc1 to Loc2 and Loc3 (for changes made at
Loc1 to be reflected in Loc2 and Loc3).
These database are modified only during the day, and
number of users at each location will not exceed 30.
I have following queries
- Can I configure Transactional replication for Loc2 ->
Loc1 and Loc3 -> Loc1 to replicate at short intervals.
And additionally Snapshot to happen at midnight from
Loc1 -> Loc2 and Loc3.
Or do I require Merge replication for this scenario. (or
any other solution you think would suit this requirement)
Pls suggest.
Thanx,
Shrikant,
the easiest scenario would be mege replication. Snapshot will copy over the
whole tables and unless the tables are small or all rows are always updated,
this is not recommended, and you'd have to set up replication each time
after the snapshot - possible but not so nice. Also, if the data is
partitioned then great, but if not then merge will take care of that with
the conflict resolvers. The only thing is that you won't be able to easily
prevent the download of data from Loc1 until the evening, as these changes
will propagate during the frequent synchronizations. If this is not at all
desirable, than you might have to consider a more radical solution - you
could have Loc1 as the publisher but use unidirectional merge with the
subscribers and reinitialize each evening.
HTH,
Paul Ibison
|||Hi Paul,
Thanx for your reply.
But would not be using the bi-directional transactional
replication solve this ?
Loc1 acts as Publisher with Loc2 and Loc3 as subscribers.
And then Loc2 and Loc3 as Publishers, and Loc1 as
Subscriber ?
regds,
Shrikant

>--Original Message--
>Shrikant,
>the easiest scenario would be mege replication. Snapshot
will copy over the
>whole tables and unless the tables are small or all rows
are always updated,
>this is not recommended, and you'd have to set up
replication each time
>after the snapshot - possible but not so nice. Also, if
the data is
>partitioned then great, but if not then merge will take
care of that with
>the conflict resolvers. The only thing is that you won't
be able to easily
>prevent the download of data from Loc1 until the
evening, as these changes
>will propagate during the frequent synchronizations. If
this is not at all
>desirable, than you might have to consider a more
radical solution - you
>could have Loc1 as the publisher but use unidirectional
merge with the
>subscribers and reinitialize each evening.
>HTH,
>Paul Ibison
>
>.
>
|||Shrikant,
this would be an option. If you expect conflicts then it can become terribly complicated, and the initial setup is not straightforward, but you're right - it is in some ways a simpler solution. To You might want to have a look at the most recent issue of
SQL Server magazine if you're thinking of going down this route - there is a nice article on setting it up.
Regards,
Paul Ibison

Replication Datawarehousing

Can anyone give me a clue as to do DataWarehousing using Replication
Publisher/Subscriber?
I have 4 Production Database's that I want to replicate to one
database acting as a DataWarehouse.
For the 4 production Databases I created a publication for each
database.
I then created a subscription for each publication on the machine that
will have the DataWarehouse database.
I am using "Transactional Publication".
When I set up all the subscriptions my DataWarehouse is only holding
the data of the last publication I set up.
I think this is because the Publication SnapShot over writes all the
other data.
Is there any way around this?
Am I doing this all wrong?
This is called a central subscriber.
In the articles section of the replication wizard there is a tab called
snapshot. In this section select the option to keep the existing table
intact.
Ideally you would have a filter on each publication so if you have to send
the snapshot down again, you would use the delete where the data meets the
row filter option and only the data from a particular publisher would be
deleted.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1175106220.671931.201830@.e65g2000hsc.googlegr oups.com...
> Can anyone give me a clue as to do DataWarehousing using Replication
> Publisher/Subscriber?
> I have 4 Production Database's that I want to replicate to one
> database acting as a DataWarehouse.
> For the 4 production Databases I created a publication for each
> database.
> I then created a subscription for each publication on the machine that
> will have the DataWarehouse database.
> I am using "Transactional Publication".
> When I set up all the subscriptions my DataWarehouse is only holding
> the data of the last publication I set up.
> I think this is because the Publication SnapShot over writes all the
> other data.
> Is there any way around this?
> Am I doing this all wrong?
>
|||On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Please take a look at this article:http://www.sql-server-performance.com/pi_multiple_publishers_replicat...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hilary
When you say the "Replication Wizard" do you mean the Publication
Wizard? When I set up the publication and do the Articles Section
where you choose the Tables I don't get a SnapShot Tab. Not Using
Enterprise. Using Standard.
Mike
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
I'm using "Truncate all data in the existing object" for all Article
Property
"Action if name is in use" and when I reinitialize the subscription it
still overwrites the data so I only get the data from one publisher
|||On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 2:27 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Hilary
> When you say the "Replication Wizard" do you mean the Publication
> Wizard? When I set up the publication and do the Articles Section
> where you choose the Tables I don't get a SnapShot Tab. Not Using
> Enterprise. Using Standard.
> Mike
If I choose "Keep existing object unchanged" I get the data from both
Publications into the Central Subscriber.
Now if I reinitialize the publications will it double up the data?
|||On Mar 29, 10:06 am, "jughead" <mike.aar...@.gmail.com> wrote:
> On Mar 28, 4:37 pm, "jughead" <mike.aar...@.gmail.com> wrote:
>
>
> If I choose "Keep existing object unchanged" I get the data from both
> Publications into the Central Subscriber.
> Now if I reinitialize the publications will it double up the data?
Jughead;
I believe that Hilary's quote comes into play here:
"Ideally you would have a filter on each publication so if you have to
send
the snapshot down again, you would use the delete where the data meets
the
row filter option and only the data from a particular publisher would
be
deleted. "
Joseph

Replication between SQL 2000 & SQL 2005

We currently replicate two databases. We would like to installed SQL 2005 o
n
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thankssql

Replication between SQL 2000 & SQL 2005

We currently replicate two databases. We would like to installed SQL 2005 on
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
--
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thanks

replication between same server

Hi,
I am setting up a DataCenter database on SERVER1. my data is residing in
the different databases on SERVER1 as well. I am intending to do a
replication for each of the databases to DataCenter but i believe replication
cannot be done on the same server.
However, i would like the abilities of Replication to update DataCenter
whenever there's modification and new records automatically from the various
databases. How can i acheive this as there's no unique field in the tables of
these databases to allow me ensure that i am able to copy over all the new
records as well as modified records by using queries.
furthermore , all these databases are also replicated databases from
elsewhere , if i create a insert/modify triggers in these databases will it
work ? i.e during replication a record is inserted and will this be
considered a real insertion that will trigger off the triggers ?
Appreciate any advice
tks & rdgs
Hi
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
> Hi,
> I am setting up a DataCenter database on SERVER1. my data is residing in
> the different databases on SERVER1 as well. I am intending to do a
> replication for each of the databases to DataCenter but i believe
> replication
> cannot be done on the same server.
> However, i would like the abilities of Replication to update DataCenter
> whenever there's modification and new records automatically from the
> various
> databases. How can i acheive this as there's no unique field in the tables
> of
> these databases to allow me ensure that i am able to copy over all the new
> records as well as modified records by using queries.
> furthermore , all these databases are also replicated databases from
> elsewhere , if i create a insert/modify triggers in these databases will
> it
> work ? i.e during replication a record is inserted and will this be
> considered a real insertion that will trigger off the triggers ?
> Appreciate any advice
> tks & rdgs
|||Hi ,
Actually it's not a matter of me not able to set up replication but i
don't think Replication can occur on the same server between 2 different
databases as in my case.
So i am wondering if w/o having to write any queries (as the structure does
not allow me to uniquely identify whether a record is a new or modified one)
, can i have something that as automatic as replication in detecting changes
(such as a modified or new record)
I am thinking of using triggers actually ...
Appreciate any advice
tks & rdgs
"Uri Dimant" wrote:

> Hi
> http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
> Replica (Step by step)
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
>
>

replication between same server

Hi,
I am setting up a DataCenter database on SERVER1. my data is residing in
the different databases on SERVER1 as well. I am intending to do a
replication for each of the databases to DataCenter but i believe replicatio
n
cannot be done on the same server.
However, i would like the abilities of Replication to update DataCenter
whenever there's modification and new records automatically from the various
databases. How can i acheive this as there's no unique field in the tables o
f
these databases to allow me ensure that i am able to copy over all the new
records as well as modified records by using queries.
furthermore , all these databases are also replicated databases from
elsewhere , if i create a insert/modify triggers in these databases will it
work ? i.e during replication a record is inserted and will this be
considered a real insertion that will trigger off the triggers ?
Appreciate any advice
tks & rdgsHi
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
> Hi,
> I am setting up a DataCenter database on SERVER1. my data is residing in
> the different databases on SERVER1 as well. I am intending to do a
> replication for each of the databases to DataCenter but i believe
> replication
> cannot be done on the same server.
> However, i would like the abilities of Replication to update DataCenter
> whenever there's modification and new records automatically from the
> various
> databases. How can i acheive this as there's no unique field in the tables
> of
> these databases to allow me ensure that i am able to copy over all the new
> records as well as modified records by using queries.
> furthermore , all these databases are also replicated databases from
> elsewhere , if i create a insert/modify triggers in these databases will
> it
> work ? i.e during replication a record is inserted and will this be
> considered a real insertion that will trigger off the triggers ?
> Appreciate any advice
> tks & rdgs|||Hi ,
Actually it's not a matter of me not able to set up replication but i
don't think Replication can occur on the same server between 2 different
databases as in my case.
So i am wondering if w/o having to write any queries (as the structure does
not allow me to uniquely identify whether a record is a new or modified one)
, can i have something that as automatic as replication in detecting changes
(such as a modified or new record)
I am thinking of using triggers actually ...
Appreciate any advice
tks & rdgs
"Uri Dimant" wrote:

> Hi
> http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
> Replica (Step by step)
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
>
>

replication between same server

Hi,
I am setting up a DataCenter database on SERVER1. my data is residing in
the different databases on SERVER1 as well. I am intending to do a
replication for each of the databases to DataCenter but i believe replication
cannot be done on the same server.
However, i would like the abilities of Replication to update DataCenter
whenever there's modification and new records automatically from the various
databases. How can i acheive this as there's no unique field in the tables of
these databases to allow me ensure that i am able to copy over all the new
records as well as modified records by using queries.
furthermore , all these databases are also replicated databases from
elsewhere , if i create a insert/modify triggers in these databases will it
work ? i.e during replication a record is inserted and will this be
considered a real insertion that will trigger off the triggers ?
Appreciate any advice
tks & rdgsHi
http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
Replica (Step by step)
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
> Hi,
> I am setting up a DataCenter database on SERVER1. my data is residing in
> the different databases on SERVER1 as well. I am intending to do a
> replication for each of the databases to DataCenter but i believe
> replication
> cannot be done on the same server.
> However, i would like the abilities of Replication to update DataCenter
> whenever there's modification and new records automatically from the
> various
> databases. How can i acheive this as there's no unique field in the tables
> of
> these databases to allow me ensure that i am able to copy over all the new
> records as well as modified records by using queries.
> furthermore , all these databases are also replicated databases from
> elsewhere , if i create a insert/modify triggers in these databases will
> it
> work ? i.e during replication a record is inserted and will this be
> considered a real insertion that will trigger off the triggers ?
> Appreciate any advice
> tks & rdgs|||Hi ,
Actually it's not a matter of me not able to set up replication but i
don't think Replication can occur on the same server between 2 different
databases as in my case.
So i am wondering if w/o having to write any queries (as the structure does
not allow me to uniquely identify whether a record is a new or modified one)
, can i have something that as automatic as replication in detecting changes
(such as a modified or new record)
I am thinking of using triggers actually ...
Appreciate any advice
tks & rdgs
"Uri Dimant" wrote:
> Hi
> http://www.mssqlcity.com/Articles/Replic/Replic.htm --Setting All
> Replica (Step by step)
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:83E7984A-50AC-4FA2-9541-EB2558EB7905@.microsoft.com...
> > Hi,
> >
> > I am setting up a DataCenter database on SERVER1. my data is residing in
> > the different databases on SERVER1 as well. I am intending to do a
> > replication for each of the databases to DataCenter but i believe
> > replication
> > cannot be done on the same server.
> >
> > However, i would like the abilities of Replication to update DataCenter
> > whenever there's modification and new records automatically from the
> > various
> > databases. How can i acheive this as there's no unique field in the tables
> > of
> > these databases to allow me ensure that i am able to copy over all the new
> > records as well as modified records by using queries.
> >
> > furthermore , all these databases are also replicated databases from
> > elsewhere , if i create a insert/modify triggers in these databases will
> > it
> > work ? i.e during replication a record is inserted and will this be
> > considered a real insertion that will trigger off the triggers ?
> >
> > Appreciate any advice
> >
> > tks & rdgs
>
>

Replication between 2 or more SQL Express databases

Problem description
There is one central SQL Express database. And one (or more) SQL Express
database(s) on remote locations. This approach is mandatory due to some
application features later-on.
The local database(s) on the remote location(s) will act as a sort of backup
in case there is no network connection. Meaning that the data is stored
locally at all times. And moved to the central database once the network
connection is on.
Updates to the local database(s) should trigger a process where all data is
moved to the central database if there is a network connection. So for now,
it’s a one way stream: remote to central.
This approach gives us the option to delay the moves - if needed. For
example, the daily data is kept local and moved to the central site over
night. Which is a good thing to have if network resources are limited during
office hours.
Solution
To make this happen, I was thinking allong the lines of using triggered
stored procedures.
Where a local database update triggers a stored procedure that checks the
network connection and performs the move to the central database.
If there is more than one record in the remote database, all records are
moved to the central database. Also, an entry should be added to a log-file.
If the network connection is not there for whatever reason, an entry should
be written in a log-file stating that there was no connection to the central
database.
Options
Another way of solving this is using RMO-based publications and subscribers.
Where the database on the remote locations are publishers. And the central
database are subscribers. However, according to Microsoft:
- SQL Express can only act as a subscriber.
- Data is copied. And not moved.
According to the SQL-BOL, the first limitation doesn’t exist when using
programmatic, transactional replication.
However, the second needs to be solved as well. And in the examples of
SQL-BOL, this is not mentioned. At least not that I’m aware of.
There are some advantages using RMO. For example deploying a new version of
the application. Or deploying an updated database schema. Please let me know
if this is proven to be really usefull. If so, this would be the preferred
method – provided that the limitations mentioned earlier are solved. And if
using this method, I would prefer stored procedures or Visual Basic for
programming this.
Help wanted
Since I’m a newbie on these replication matters, I would like some help with
this.
Who is willing to help with examples and suggestions?
Sofar, I’ve been able to:
- Install SQL Express with network connections enabled
- Running the Management Studio and create the data model I would like to use
- Add data manually and via an ODBC connection (using a system wide DSN).
- Run SQL select commands against the added data.
So the infrastructure is up and running….
firing triggers over the network is not scalable, as there is significant
latency involved for each trigger firing. For a batch operation it is
painful. Your transactions will also hang for up to 20 seconds if the
connection is down.
You might want to look at SSIS (Integration Services) or perhaps even bcp
for what you are trying to do.
You might also want to upgrade from Express to Standard so you can do
transactional replication which will do exactly what you are trying to do.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:474B4797-1811-4ADC-8576-0137A4FCBAF9@.microsoft.com...
> Problem description
> --
> There is one central SQL Express database. And one (or more) SQL Express
> database(s) on remote locations. This approach is mandatory due to some
> application features later-on.
> The local database(s) on the remote location(s) will act as a sort of
> backup
> in case there is no network connection. Meaning that the data is stored
> locally at all times. And moved to the central database once the network
> connection is on.
> Updates to the local database(s) should trigger a process where all data
> is
> moved to the central database if there is a network connection. So for
> now,
> it's a one way stream: remote to central.
> This approach gives us the option to delay the moves - if needed. For
> example, the daily data is kept local and moved to the central site over
> night. Which is a good thing to have if network resources are limited
> during
> office hours.
> Solution
> --
> To make this happen, I was thinking allong the lines of using triggered
> stored procedures.
> Where a local database update triggers a stored procedure that checks the
> network connection and performs the move to the central database.
> If there is more than one record in the remote database, all records are
> moved to the central database. Also, an entry should be added to a
> log-file.
> If the network connection is not there for whatever reason, an entry
> should
> be written in a log-file stating that there was no connection to the
> central
> database.
> Options
> Another way of solving this is using RMO-based publications and
> subscribers.
> Where the database on the remote locations are publishers. And the central
> database are subscribers. However, according to Microsoft:
> - SQL Express can only act as a subscriber.
> - Data is copied. And not moved.
> According to the SQL-BOL, the first limitation doesn't exist when using
> programmatic, transactional replication.
> However, the second needs to be solved as well. And in the examples of
> SQL-BOL, this is not mentioned. At least not that I'm aware of.
> There are some advantages using RMO. For example deploying a new version
> of
> the application. Or deploying an updated database schema. Please let me
> know
> if this is proven to be really usefull. If so, this would be the preferred
> method - provided that the limitations mentioned earlier are solved. And
> if
> using this method, I would prefer stored procedures or Visual Basic for
> programming this.
> Help wanted
> --
> Since I'm a newbie on these replication matters, I would like some help
> with
> this.
> Who is willing to help with examples and suggestions?
> Sofar, I've been able to:
> - Install SQL Express with network connections enabled
> - Running the Management Studio and create the data model I would like to
> use
> - Add data manually and via an ODBC connection (using a system wide DSN).
> - Run SQL select commands against the added data.
> So the infrastructure is up and running..
>
|||Hi Hilary,
Thanks for your support and feedback. I really do appreciate this!!!

> firing triggers over the network is not scalable, as there is significant
> latency involved for each trigger firing. For a batch operation it is
> painful. Your transactions will also hang for up to 20 seconds if the
> connection is down.
Mmm - sounds like we have a different understanding of the term trigger.
I would like to use triggers on the remote databases - not the central one.
As a result, triggers are not travelling across the network.

> You might want to look at SSIS (Integration Services) or perhaps even bcp
> for what you are trying to do.
I looked at SSIS. That would require
I'm not familiar with BCP. But it looks like a bulk COPY tool.
How would this help in MOVING the data?

> You might also want to upgrade from Express to Standard so you can do
> transactional replication which will do exactly what you are trying to do.
Do you mean replace Express with standaard on the remote side?
On the central side? Or both?
Keep in mind that SQL Express on the remote sites needs to run on
Workstations.
Ans therefor requires a light footprint.
Will
|||Answers inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:E6201678-BFD9-4290-ABAC-1D7A5F6E1962@.microsoft.com...
> Hi Hilary,
> Thanks for your support and feedback. I really do appreciate this!!!
>
> Mmm - sounds like we have a different understanding of the term trigger.
> I would like to use triggers on the remote databases - not the central
> one.
> As a result, triggers are not travelling across the network.
>
> I looked at SSIS. That would require
> I'm not familiar with BCP. But it looks like a bulk COPY tool.
> How would this help in MOVING the data?
>
it doesn't move data, it copies it row by row. I know of no technology
which actually moves that, all copy it and you have to delete it yourself on
the source.

> Do you mean replace Express with standaard on the remote side?
> On the central side? Or both?
> Keep in mind that SQL Express on the remote sites needs to run on
> Workstations.
> Ans therefor requires a light footprint.
In retrospect you will probably need a code solution. Put a timestamp on
your tables which you can use to determine which rows have changed since the
last time you "moved" data. Anything greater than the timestamp the last
time you polled must be moved.
You will probably be able to use any version of SQL Server for this.
> --
> Will
>

Replication Automation...

My Scenarion is like this.

I have configured replication with a publication database, a distributor database and a few subscriber databases.
The replication model is snapshot + transactional replciation.

Now i want to check using system stored procedures as to whether the replication is configured and running properly.

What are the system stored procedures which can be used and how do use them??

Basically i wanted to automate the check of replciation. How can this be done?

--Sandu.Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.|||Originally posted by pushistik
Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.

Is there any other tables or system stored procedures which i can check to see the replciation details?

--Sandu.sql

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and Log shipping

Sql server 2005 heavily hit database
Plans
1)Create a replicated databases to let users with query only access
2)Log shipping to allow a 4hour lag time with Disaster recovery just in
case
users gaff..
Your opinion
1)Transactional replication
2)Log shipping with a stagger of 4 hours
Not done this and wondering are there things to watch out for?
Are there better ways to go about this?
Is replication and log shipping in 2005 configured as a single setup?
Again your time and ideas are appreciated as alwaysI would create database snapshots on the local server if you have a large
tempdb and lots of memory. Otherwise I would use replication.
With log shipping your exposure to data loss if in excess of 4 hours. When
you apply the log you will have to kick your users off the system as you
apply the log to the database. With replication you can put user indexes on
the replicated subscriber database to offer better querying performance.
With log shipping the database is read only - you can't create indexes
there.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159285642.485892.323850@.b28g2000cwb.googlegroups.com...
> Sql server 2005 heavily hit database
> Plans
> 1)Create a replicated databases to let users with query only access
> 2)Log shipping to allow a 4hour lag time with Disaster recovery just in
> case
> users gaff..
> Your opinion
> 1)Transactional replication
> 2)Log shipping with a stagger of 4 hours
> Not done this and wondering are there things to watch out for?
> Are there better ways to go about this?
> Is replication and log shipping in 2005 configured as a single setup?
> Again your time and ideas are appreciated as always
>|||Massa,
I did an article a while back that will help you decide between the 2
options: http://www.replicationanswers.com/Standby.asp.
As you're using SQL Server 2005, I'd also add a couple more options for you
to consider looking at:
(a) Mirroring and database snapshots
(b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
variant on the standard Transactional Replication
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Folks for your time and great ideas ...............
Paul Ibison wrote:
> Massa,
> I did an article a while back that will help you decide between the 2
> options: http://www.replicationanswers.com/Standby.asp.
> As you're using SQL Server 2005, I'd also add a couple more options for you
> to consider looking at:
> (a) Mirroring and database snapshots
> (b) READ_COMMITTED_SNAPSHOT option to transactional replication as a
> variant on the standard Transactional Replication
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .