0

I have one db say DB1, on adding one column in one table (DB1.t1) on DB1, triggers and create replica of same row in table by same name (DB2.t1) in another DB2. Using the identity of inserted row I need to add some rows in another table (DB2.t2) in DB2.

Problem is, I need to insert in DB1.t1 and DB2.t2 in one transaction. When try to do that, DB1.t1 inserts fine, but since it is not committed, DB2.t1 does not contain the row, so DB2.t2 could not insert new rows (missing foreign key), still when data from application is sent properly.

One option is too commit DB1 transaction, and then do DB2 operation, but there are other functionality related. Please suggest best way to achieve it.

hungryMind
  • 6,931
  • 4
  • 29
  • 45

2 Answers2

3

Well, Id columns are not meaningful "keys", and they will trip you up all over the place. They are supposed to be meaningless, physical identifiers, and you have attached meaning to them. When you demand that the same Ids exist in another database.

Have you resolved the FK violation ? Most probably you have a totally different Id for the DB1 row, in DB2; and certainly different parent Ids.

You need to consistently not specify an Id value, and let the server fill it in, or always specify an Id value, in both databases.

The second problem is that you are not thniking transactionally. Multi-db transactions are no problem at all. So forget the Id column, what it contains, and use the real keys for the table, in both Dbs. The ids will be different, but who cares (that means release the need to attach meaning to the meaningless identifier).

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
0

Do it all in one transaction in a stored proc. Look up how to to do transactions in Books online. Make sure to put in the rollback if it errors (Use try catch blocks). ALso get the identity from table1 using the OUTPUT clause or Scope_identity().

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I am doing the same way you asked. I am calling one SP on DB1 and from the SP, second DB2 sp is called. Both are under one transaction, I can get the ID from DB1 but since same ID is not on DB2, it always throws exception and so rolled back. – hungryMind Jan 31 '11 at 18:00