In addition to my former blog post Create a Linked server, I will now show you what you can do with the linked server and how to run scheduled jobs between the databaseservers.

In our last post we created a link between a Oracle 10G database and MSSQL2008 server. Now that we established the connection we can schedule jobs in the mssql database to get the data synced with the oracle server.

You can view and query your oracle database in linked server you created under mssql, but you can also send jobs and data to the oracle server

Create a job step and insert a query to specify the data that needs to be copied. We want to insert new and modified organisation data to a financial database.

The code for my Scheduled job:

 DECLARE @dtLastRun DATETIME
 DECLARE @dtNow DATETIME
 SELECT @dtLastRun = CAST(EV000_ALPHANUM_VALUE AS DATETIME) FROM EV000_SYS_PARMS WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 SET @dtNow=getDate()
 BEGIN TRY
 INSERT INTO DECADE_INTERFACE..FinancialDB_RELATIES
 SELECT
 CASE ISNULL(EV870_KEYWORD_2, '') WHEN ''
 THEN EV870_ACCT_CODE
 ELSE EV870_KEYWORD_2 END AS RELA_ID,
 REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, EV870_ENTER_STAMP, 126), '-', ''), ':', ''), 'T', '') AS DATUM_INVOER,
 CASE
 WHEN EV870_AR_DESIG = '0' THEN 'C'
 WHEN EV870_AP_DESIG = '0' THEN 'D'
 ELSE 'B' END AS SOORT_RELA,
 EV870_ALPHA_SEARCH_KEY AS ZOEKNAAM,
 DECADE_LAND_CODE AS TAAL_ID,
 DECADE_LAND_CODE AS LAND_ID,
 0 AS IND_GEBLOKKEERD,
 LEFT(EV870_NAME, 40) AS NAAM_RGL_1,
 '' AS NAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 40) AS STRAATNAAM_RGL_1,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L2, '')
 ELSE ISNULL(EV878_ADDRESS_L2, '') END, 40) AS STRAATNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L3, '')
 ELSE ISNULL(EV878_ADDRESS_L3, '') END, 40) AS STRAATNAAM_RGL_3,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS PLAATSNAAM_RGL_1,
 '' AS PLAATSNAAM_RGL_2,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_ADDRESS_L1, '')
 ELSE ISNULL(EV878_ADDRESS_L1, '') END, 15) AS POSTBUSNR,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_POSTAL_CODE, '')
 ELSE ISNULL(EV878_POSTAL_CODE, '') END, 15) AS POSTBUS_POSTCODE,
 LEFT(CASE WHEN EV878_ORG_CODE IS NULL THEN ISNULL(EV870_CITY, '')
 ELSE ISNULL(EV878_CITY, '') END, 40) AS POSTBUS_PLAATSNAAM_RGL_1,
 '' AS POSTBUS_PLAATSNAAM_RGL_2,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'MAIN'), ISNULL(EV870_MAIN_PHONE, '')), 15) AS TELEFOONNR,
 LEFT(ISNULL((SELECT TOP 1 CC810_COMM_CODE FROM CC810_COMM_DIR
 WHERE CC810_ORG_CODE = EV870_ACCT_CODE
 AND CC810_ACCT_CODE = EV870_ACCT_CODE
 AND CC810_COMM_TYPE = 'FAX'), ISNULL(EV870_MAIN_FAX, '')), 15) AS FAXNR,
 '' AS CONTACTPERSOON,
 0 AS IND_FOUT
 FROM EV870_ACCT_MASTER
 LEFT OUTER JOIN EV878_ACCT_ADDRESSES
 ON EV878_ORG_CODE = EV870_ORG_CODE
 AND EV878_REF_CODE = EV870_ACCT_CODE
 AND EV878_ADDR_TYPE = 'F'
 LEFT OUTER JOIN U_D_VERTAALTABEL_LANDEN
 ON UNGERBOECK_LAND_CODE = EV870_COUNTRY
 WHERE EV870_CLASS = 'O'
 AND (EV870_AR_DESIG != '0'
 OR EV870_AP_DESIG != '0')
 AND (EV870_ENTER_STAMP >= @dtLastRun
 OR EV870_UPD_STAMP >= @dtLastRun)
 UPDATE EV000_SYS_PARMS
 SET EV000_ALPHANUM_VALUE = CAST(@dtNow AS VARCHAR)
 WHERE EV000_APPLICATION='AA' AND EV000_CODE='ZZZ'
 END TRY
 BEGIN CATCH
 PRINT 'ERROR!'
 END CATCH

We run this job every 30 minutes daily and our job is done, that was easy!

We can write as much jobs and add multiple linked servers as you like.