I'm pretty new to SQL and am having some porblems with a linked server.
I have a table on a SQL server which stores employee information.
I also have a view on a linked server which stores the same information.
What I would like to happen is, whenever the view changes on the linked server I want the information to be changed in the table on my server.
I've been trying to write a trigger to do this, but have had no luck so far.
This is the trigger I've written:
CREATE TRIGGER fnChangeData
SET XACT_ABORT ON
LAST_NAME_SRCH = i.LAST_NAME_SRCH,
FIRST_NAME_SEARCH = i.FIRST_NAME_SEARCH
FROM Employee t JOIN inserted i ON t.EMPLID = i.EMPLID
And the error I get:
Server: Msg 117, Level 15, State 1, Procedure fnChangeData, Line 5
The object name 'PSREP..SYSADM.' contains more than the maximum number of prefixes. The maximum is 2.
Is this because you can't run a trigger on a linked table? I've tried using openquery as well, but with no success.
You probably need to write your trigger on the other side, - against the view of your linked server. This means that you have to create a linked server on the other side as well. Also, avoid defaults and fully qualify object references (servername.databasename.owner.object)
You can create a stored procedure that selects the data from theview on the first server, inserts it on the second one in a temporary table and from that point on you can update the table using the temporary table. Depending on the time you want to allow between an update on the view and the necessary update on the table, you can create a job that will run on interval. It's quite a way around and probably not improving the performance of your server, but you can't fire a trigger from one server when data is changed on another.
Pumping the entire set of data from one server to another on a periodic basis maybe...how should I put it?.. You can probably slightly alter the previous reply and have a permanent table on your side. Then with your scheduled task do an INSERT with LEFT OUTER JOIN from the view to your table where the key_field in your table is null.