September 3, 2002 at 3:16 am
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!
B.J.
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
trick.
September 3, 2002 at 3:24 am
I had similar problems calling remote SP's from 2000 to 2000 where the far end requested data from the local server and it got into a loop...
The answer I came up with was to pass all of the data needed at the far end as parameters to the procedure call - it's a long call statement, but it worked for us and it also means that from a performance point of view, that the data is passed across once, then a result is sent back, so less chatter.
HTH,
Simon
September 3, 2002 at 6:15 am
Ok Stored procedures...
It's heavy, but it seems the only solution..
Some tips to write triggers/stored procedures in a o.o. way (reuse code for different tables, etc..)?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply