Transaction Isolated SnapShot Fix

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Transaction Isolated SnapShot Fix

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-20 : 02:14:19

Hi All,

Good Morning.

I have created a Trigger on a particular Table that after insert it will create a copy to another table which is located to a remote server.

The command goes like this.


Create Trigger RemoteInsert on Test_Table After Insert
as

Begin

Insert Into OpenQuery(RemoteServer1,
'Select Col1, Col2, Col3 from Server..Remote_Table')
Select
NewCol1,
NewCol2,
NewCol3
from inserted

End

When I insert the data via C# Desktop Application, I receive and error that
"Error Saving Record: Remote Access is not Supported Transaction Isolated level 'SNAPSHOT'.

I have tried to Turn On the "READ_COMMITTED_SNAPSHOT" but it doesnt work.

As much as possible I want to use the trigger so that I can retrieve the data in real time, but if there are no other way I think I will have to use a SQL Job to solve the problem.

Kindly advise if there are some ways to fix the Isolation Level Snapshot problem

Thank you

Kristen
Test

22859 Posts

Posted - 2015-04-20 : 03:01:04

I think you need to go the other way and turn Snapshot OFF on the Remote. (I think that Remote Server can not manage snapshots for you because it can't know what transactions are still running on your server to know when to release snapshot versions at its end)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-04-20 : 07:02:17

You might want to look at transactional replication:

https://msdn.microsoft.com/en-us/library/ms151176.aspx

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-21 : 05:19:42

Hello,

Thank you, I will check this one

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2015-04-21 : 14:05:09

Also look at service brokers - as you have it now if the remote server is down it will cause issues. Using a service broker, the inserts would queue up - and when the remote server is back up would be applied.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-21 : 14:19:49

I would highly recommend that you heed the advice given. Do not do this in a trigger. It participates in the transaction.

I would use transactional replication instead. Latency would be minimal (under a second) usually. But you'll need to monitor latency and make changes as necessary. Things that can impact latency are IO, CPU, memory (publisher, subscriber and distributor), large transactions on the table (such as index rebuilds), etc.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources