January 29, 2014 at 3:05 am
Please go through the below query...
CREATE TRIGGER TRG_UpdateSyncAcc
ON dbo.AccountSample
AFTER update AS
BEGIN
INSERT INTO AccountSample_Backup
SELECT * FROM INSERTED
END
CREATE TRIGGER TRG_InsertSyncAcc
ON dbo.AccountSample
AFTER Insert AS
BEGIN
INSERT INTO AccountSample_Backup
SELECT * FROM INSERTED
END
I need to track all the data in the AccountSample table. But AccountSample table is in some other server. I dont have much access on those tables. Is there any other way to achieve above by using linked servers ?
January 29, 2014 at 3:10 am
Trigger on AccountSample, Change data capture, Change tracking or SQL Audit. All require you to have fairly high permissions on the table, database or server. If you don't have them, ask your DBA to do the implementation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2014 at 3:31 am
This is other party table.. we do have only select permission ( even my DBA also).
Is there any other way to perform this?
January 29, 2014 at 3:58 am
I suppose you could write some job that fetched the entire contents of the table on a regular basis and compared with previous versions. Horribly inefficient though.
Someone's got to have admin-level access to that database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2014 at 9:52 am
Try Red Gate's or ApexSQL's Data Compare tools. This is right up their alley, and both can be automated I believe. You should be OK with read-only permission on the primary table this way. As Gail mentions, this will be inefficient as the table size grows, especially if the secondary database is across a slow network link. If everything is local on Gb+ network, should be fine even with GBs of data. Note you need some form of uniqueness on the table for this check to be efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply