Hi to all, I have a nice problem to solve:
We have one SQL Server 2000 Cluster (A) that holds
fresh data coming from web and a SQL Server 7.0 (B) that
holds internal data into the intranet.
(A) sees (B) as linked server but (B) does not see
(A) as linked server (it seems a problem of SQL Server 7.0
with SQL Server 2000 Cluster).
Now we want that in "real time" some data that changes on
(A) (i.e. a new order) also changes on (B) (the new
order appears also in (B)).
We found 2 solutions:
1) Build Triggers on (A) tables that perfom action on (B)
but this seems very difficult because we found problems
while dealing with INSERT instruction into a Linked Table
when into this Table there is an autoincrement primary key.
In addiction if something changes on (B) , (A) raises an
Inconsistent Metadata Error, that we have to fix everytime
with a stored procedure that updates "syscolumns" on (B).
2) Build Triggers on (A) that simply call stored procedures on (B)
that perform all the work, but this is impossible because
(B) does not see (A) so it's impossible to retrive data from (A).
Please let me know your opinion!
Thanks A Lot!
PS: We found also some problems while calling stored procedures
that perform actions on linked tables and we solved with
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON