I am using SQL Server 2014. I have created the following T-SQL query which I uploaded to my local SQL server to run as a job process on a daily basis at a specific time. However, I noticed that it failed to run. If I run it manually in SSMS, it runs correctly.
What is preventing the query to run as an automated process? Is it a syntax issue?
USE MyDatabase
GO
DELETE FROM ExchangeRate -- STEP 1
;WITH MAINQUERY_CTE AS ( --STEP 2
SELECT *
FROM (
SELECT *
FROM [178.25.0.20].HMS_ARL.dbo.ExchangeRate
) q
)
INSERT INTO ExchangeRate --STEP 3
SELECT *
FROM MAINQUERY_CTE
Basically, the function of the query is to copy a table named ExchangeRate from the live server and paste its content in a table of the same name (which already exists on my local server).
Error Log shows the following message:
Description: Executing the query "USE MyDatabase DELETE FROM ExchangeRate..." failed with the following error: "Access to the remote server is denied because no login-mapping exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:59:30 AM Finished: 10:59:30 AM Elapsed: 0.422 seconds. The package execution failed. NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.
May be you have to create Linked Server in your local server to the Remote server?