Copying data across multple servers SQL

Question

I am trying to have a job on each server use the following stored procedure. The problem is, when I run this job on the local server, it works fine, when I run the same job on a remote server, I get an error about the "Merge" statement not being able to run against a remote database. We are running SQL 2008 R2.

MERGE INTO dbo._demo_sp_exec_stats STAT
USING 
(SELECT  d.object_id, d.database_id, 
  OBJECT_NAME(object_id, database_id) AS proc_name, 
  d.last_execution_time
  FROM sys.dm_exec_procedure_stats AS d
  WHERE d.database_id = DB_ID('_DemoDB')  ) AS SRC
ON STAT.object_id = SRC.object_id
WHEN MATCHED 
 AND STAT.last_execution_time <> SRC.last_execution_time THEN
UPDATE SET
    last_execution_time = SRC.last_execution_time
WHEN NOT MATCHED THEN
INSERT  (object_id,
 database_id,
 proc_name,
 last_execution_time)
VALUES (SRC.object_id, SRC.database_id, 
 SRC.proc_name, SRC.last_execution_time) ;

You canno call MERGE on remote server. May be this question will be helpfull to you http://stackoverflow.com/questions/15236961/sql-merge-to-remote-linked-server-table — MikkaRin, Sep 15 '14 at 14:30

score 2 · Accepted Answer · answered Sep 15 '14 at 14:46

If you re-write your code so that you You are pulling the information from the remote server and inserting it into a local server you will be able to bypass the limits of the merge statement. Change the from to include the fully qualified name of the linked server. This way instead of running the statement on each server, it runs the job on the central server and pulls the data from remote servers and inserts the data locally.

Copying data across multple servers SQL

1 Answers1