SQL Command

EDIS Online User Guide – SQL Command

The SQL Command stored procedure allows you to issue SQL commands to external servers via a server ID listed in table SSISDB.EDIS.lkup_server_id. This stored procedure is great for dropping/creating tables on other servers, indexing and aggregating data, and preparing data sets on other servers for a data transfer. Below are a couple examples:

Procedure: SSISDB.EDIS.usp_run_sql_cmd

Parameters

Name Description Default Value
@srvr_nm Server ID to issue the command against None; Required
@sql_cmd SQL Command to execute against the server ID None; Required

Remarks
1. SQL Commands can allow multiple commands in a single transaction. Separate each command with a semi-colon. All commands will be ran in the order that they are listed.

Examples

Example 1: Creating a Table on a Teradata Server

EXEC SSISDB.EDIS.usp_run_sql_cmd

 @srvr_nm = 'TRD_P1'

,@sql_cmd =

'

CREATE TABLE PRD1_SLS.SLS_WKLY_SNSH

AS

(

SELECT FSCL_WK, RGN_ID, SUM(SLS_AMT) AS SLS_AMT

FROM PR1_SLS.SLS_HDR

WHERE FSCL_WK BETWEEN 1 AND 16

AND FSCL_YR = 2012

GROUP BY 1, 2

) WITH DATA

PRIMARY INDEX (FSCL_WK, RGN_ID)

'

 

Example 2: Dropping a Table on Another SQL Server if it Exists

EXEC SSISDB.EDIS.usp_run_sql_cmd

 @srvr_nm = 'AVT_WORKS_2014'

,@sql_cmd =

'

IF OBJECT_ID(''Adventureworks2014.Test.sls'') is not null drop table

Adventureworks2014.Test.sls

'