RE: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis
From: Mad Matt (MadMatt_at_discussions.microsoft.com)
Date: 10/25/04
- Next message: Phil: "New articles will not replicate to subscribers"
- Previous message: Stephen Dybing [MSFT]: "SQL Server Chat: SQL Server 2005 Database Mirroring"
- In reply to: Paul Ibison: "RE: Use replication + DTS - Referred to Hilary Cotter or Paul Ibison"
- Next in thread: Paul Ibison: "Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis"
- Reply: Paul Ibison: "Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 25 Oct 2004 13:59:05 -0700
Paul,
Have gotten the servers to link and started MS DTC. I've been playing around
with triggers and have been able to copy data from one table to another
inside of the same database. However, when I try to create my distributed
query I cannot get it correct and am at the end of my patience rope. Below is
an test query I have been playing with for orders table in database 1.
CREATE TRIGGER copy_Order ON [dbo].[orders]
FOR INSERT
AS insert into openQuery(database2, 'INSERT INTO orders(idOrder,
orderDateEntered) VALUES (5555, ' '12/20/1977' ')')
I hard coded some values just to try and get it to work and then go from
there but no luck. It says my syntax is wrong. Can you tell me what is wrong
with the syntax I have been all over google looking for a similar example
with no luck. Also, if possible can you create a statement to copy an
inserted record from database 1 into database 2? Can I use a SELECT INTO and
if so what is the syntax? Any help would be greatly appreciated. Have been
playing with this for a few days now.
Table 1: name(orders) : Database 1
idOrder int 4
orderDate nvarchar 20
idCustomer int 4
details ntext 16
total float 8
address nvarchar 70
zip nvarchar 10
stateCode nvarchar 4
state nvarchar 50
city nvarchar 50
countryCode nvarchar 4
obs nvarchar 255
taxAmount float 8
shipmentDetails nvarchar 50
paymentDetails nvarchar 50
discountDetails nvarchar 50
nroRan int 4 1
orderStatus tinyint 1
viewed int 4 1
digitalemailtext ntext 16
idAffiliate int 4 1
idCustomerType int 4
browserIp nvarchar 30 1
transactionResults nvarchar 50
shipmentTracking ntext 16
vatNumber nvarchar 15 1
idAddress int 4 1
Table 2: name(orders) : Database 2
idOrder int 4
orderMasterNumber varchar 255
orderDateEntered datetime 8
orderPONum varchar 255
invoiceDate datetime 8
comments ntext 16
idClient int 4
idOrderStatus int 4
boxesShipped int 4
dateShipped datetime 8
trackingNumber varchar 255
clientComment ntext 16
idOrderBackOrder int 4
orderShippingCost money 8
idAddress bigint 8
shippingType int 4
orderPODate datetime 8
pickedBy int 4
enteredBy int 4
terms nvarchar 1000
"Paul Ibison" wrote:
> Matt,
> the distributed transaction could potentially slow down
> your application as it is a 2PC (2 phase commit)
> situation and the record is only committed when both
> parties have been able to commit. This will depend on
> bandwidth etc between the 2 servers.
> BOL is an abbreviation for Books On Line - the MS
> newsgroups have caused me to become so lazy :)
> HTH,
> Paul Ibison (SQL Server MVP)
>
> >> (recommended sql server 2000 replication book:
> >> http://www.nwsu.com/0974973602p.html)
>
>
- Next message: Phil: "New articles will not replicate to subscribers"
- Previous message: Stephen Dybing [MSFT]: "SQL Server Chat: SQL Server 2005 Database Mirroring"
- In reply to: Paul Ibison: "RE: Use replication + DTS - Referred to Hilary Cotter or Paul Ibison"
- Next in thread: Paul Ibison: "Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis"
- Reply: Paul Ibison: "Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
