• Problem: Some Environments Do Not Have xp_cmdshell
• Solution: Use DBAmpAZ.exe
  • Example: Using the SOAP Option
  • Example: Using the Command With No Options
  • Example: Using an External ID and SOAP Headers
• Setting Up a Job Step

Problem: Some Environments Do Not Have xp_cmdshell

In some SQL Server environments, the use of xp_cmdshell may be restricted. This may be due to security concerns.

Solution: Use DBAmpAZ.exe

You can use a CmdEXEC feature of the SQL job step to run the underlying table loader program directly (i.e. instead of using the SF_TableLoader stored procedure). The name of the exe is DBAmpAZ.exe, and it is located in C:\"Program Files"\CData\"CData DBAmp"\bin\.

The DBAmpAZ.exe program accepts the following parameters:

• Operation—must be Insert, Update, Upsert, Delete, HardDelete, Undelete, or ConvertLead. This is similar to the first parameter of SF_TableLoader.

• Input Table—name of the local SQL table containing the data.

• SQL Server Name—name of the SQL instance to connect to.

• SQL Database Name—name of the database to connect to. Enclose in double quotes if the name contains a blank.

• Link Server Name—name of the DBAmp link server.

• Options—various options, including soap, bulkapi, bulkapi2, batchsize(), ignorefailures, soapheaders(), externalid(), serial, parallel, and assignmentruleid().

For more information on these options, see SF_TableLoader.

Example: Using the SOAP Option

Here is an example of a complete command using the soap option:

   "C:\Program Files\CData\CData DBAmp\bin\DBAmpAZ.exe" 
   UPDATE Account_Load BUDDY "Salesforcebackups" SALESFORCE "soap"

Although the command appears on multiple lines in this document, you must enter it as a single line in the job step. Also, double quotes are required when values contain blanks.

Example: Using the Command With No Options

Here is an example of a complete command using no options:

   "C:\Program Files\CData\CData DBAmp\bin\DBAmpAZ.exe" UPDATE Account_Load BUDDY "Salesforce backups" SALESFORCE ""

The double quote (“”) at the end is mandatory even when no options are specified.

Example: Using an External ID and SOAP Headers

Here is an example of a complete command using an external ID and soap headers:

    "C:\Program Files\CData\CData DBAmp\bin\DBAmpAZ.exe" upsert Account_Load BUDDY "Salesforcebackups" SALESFORCE 
    "soapheaders(EmailHeader,triggerAutoResponseEmail,true),externalid(XID__c)" 

Setting Up a Job Step

When setting up a job step to call the program directly, you must change the Type of the job step to Operating System (CmdExec). Then enter your complete command in the Command text box. The command must be on a single line.

DBAmpAZ.exe returns 0 for a successful completion and -1if any rows failed. Ensure that the Process exit code of a successful command is 0 (zero). A -1 is returned when some of the rows succeeded and some failed. Use the Errorcolumn of the Result table to determine the failed rows. Rows that succeeded do not need to be resubmitted.

Below is a screen shot of a sample job step calling the DBAmpAZ.exe.

Your command may be different depending on the location of the install directory.