May 23, 2008 at 2:56 am
Hi!
Goal:
Server A has sql statements that should execute when a row is inserted
in a spesific table on server B.
Situation:
Server B is a linked server of Server A and all is well, Server A can manipulate
and read all tables of Server B no problem.Server B cannot access Server A (security/political
reasons).
Problem:
I tried using an 'instead of'-trigger in a Server A view which selects the wanted
table from linked server B.But because data is inserted through the original table,
and not the view, it doesn't fire.
I'd rather not create a polling job programmatically, because the interval
would have to be 2-3secs.(possible, but highly unrecommendable 😛 )
I think I'm missing something here, there should be a lazy way to
do this - triggering a schema table which holds linked server stats or
something like that.
Any help would be appreciated!
May 23, 2008 at 12:28 pm
dataville28 (5/23/2008)
Hi!Goal:
Server A has sql statements that should execute when a row is inserted
in a spesific table on server B.
Situation:
Server B is a linked server of Server A and all is well, Server A can manipulate
and read all tables of Server B no problem.Server B cannot access Server A (security/political
reasons).
Problem:
I tried using an 'instead of'-trigger in a Server A view which selects the wanted
table from linked server B.But because data is inserted through the original table,
and not the view, it doesn't fire.
I'd rather not create a polling job programmatically, because the interval
would have to be 2-3secs.(possible, but highly unrecommendable 😛 )
Any help would be appreciated!
An event on B must trigger action on A, but B can't see A, so probably your only option is a polling job.
You should have a FIFO queue table on B: on B's table, trigger copy modified data to queue, which is processed and emptied by A's polling job.
May 25, 2008 at 6:19 am
Ok, thanks.
What would you suggest, I thought of
a way to accomplish the polling job but I'm not sure:
Server A:
A job running every minute that calls a stored
procedure which executes a loop with delay 30-60 times
(the delay being 2-1 sec respectively).The loop just
inserts the new data from server B queue into a table on A
which has the trigger.
I could get a job running every second with a little
elbowing, but I suppose the procedure would be
a little 'lighter'?
May 25, 2008 at 12:08 pm
If the polling job retains the connection, it doesn't matter much.
The simplest and most efficient would be delete with output:
delete from linkedserver.queue output deleted.*
The statement should not be any more complicated than this.
However, I never tried this, so if the statement is executed on server A, it's better to put it into stored proc on B.
See this for more info:
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/3041/
May 26, 2008 at 7:27 am
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply