Skip to main content
Installation

stored procedure setup

Updated this week

Installing SQL-Sales from the provided “SQLSalesInstaller.exe” is straight-forward with minimal inputs required to get going on working with Salesforce data directly in your SQL Server.

Start here

Licence Agreement

By default the location of the SQL-Sales config will be to root drive where your Windows OS is installed (typically C:\)

Customise this as required (usually to a more appropriate folder on the SQL Server such as C:\SQLSales\Working)

Example of a customised configuration path

Generally, accept the default installation location, or customise as required

Installation running…

You’re all set, now Open from your SQL Server Start Menu

Select “SQL-Sales Config” to open the SQL Sales Environment Definition tool.

In the guide, the Environment “DEMO” has been created. Select your Environment name and click “Open”.

Daemon Status

The SQL-Sales Daemon is started automatically the first time it is called (by running one of the stored procedures - see Database enabling article). It can be controlled from a user’s client SSMS by running:

exec ss_Admin 'STOP'

against any SQL-Sales enabled database.

In normal use, the stored procedure will ensure the Daemon is kept running , however the Start/Stop buttons allow for quick setup smoke testing on the installation SQL Server, removing the need to run a stored procedure.

Housekeeping Schedule

File>>Housekeeping

This feature instructs SQL Sales to perform auto housekeeping tasks at a set time either Daily or Weekly. It is recommended (but not mandatory) that you setup a schedule, for example 3am daily. Pause an existing schedule, if required, by unchecking “Active”.

Attribute

Notes

Frequency

Monday-Sunday picklist (if a daily schedule is required and not a set day per week, either don’t select a day or pick the empty day at the top of the list).

Time

Choose an HH:MM minute in the range 00:00 to 23:59

Daily

Checkbox (if daily is required, do not select a day in Frequency, else you’ll encounter the validation message shown above)

Active

Checkbox

Environment Setup

Opening a given Environment from the definition window produces the second main window below where you configure a specific Environment, refer to the attributes reference that follows for detailed guidance, although at a minimum, to get started immediately, you’ll need to enter:

  • Sandbox (checkbox, hence tick/not ticked)

  • Licence Key provided to you (whether full/paid or trial)

  • Username

  • Password

  • Security Token

  • Working Path

Attribute

Notes

Environment

An Environment defines each unique connection to a Salesforce instance, whether that is a Sandbox or a Production Org, all connection work is done against this Name

Sandbox

Click this checkbox if your given Environment is a sandbox. Note, there are no licence restrictions imposed on accessing a sandbox, regardless of the Salesforce Org they belong to (i.e. even Orgs different to the one(s) you have licenced have no cost implication).

Username

The Salesforce User used in the connection. This will be a Production username for your Production instance, or if you're defining a sandbox Environment, then a sandbox username.

Password

The Password of your username (text security obscured)

Security Token

The Security Token of your username (text security obscured)

Working Path

SQL Sales for the BULK Connection Method makes temporary use of a working directory on a drive available to the SQL Server. No data is held at rest and you are advised to check there is suitable spare capacity on the drive which hosts this directory.

Chunksize

Defaulted to 100000. SQL Sales will chunk internal data processing to this setting, separate to any api batchsize you may have specified for a given replication or data operation. Note, for certain heavier data operations, the specified Chunksize may be automatically lowered to achieve optimal data transfer between Salesforce and SQL Server.

Boolean fields as bit

Salesforce will by default return boolean fields as TRUE or FALSE, which SQL Server handles in a varchar(5) datatype. Working in SQL Server it is often preferable to work with a bit datatype.

BulkAPI: allow null updates

By default when using the loading to Salesforce with the BULKAPI, you are not permitted to update a field with an null, this setting overrides that

DropSSId

When loading to Salesforce, via a SQL Server table, SQL Sales will define a primary key

Instance

This is your Salesforce instance url, click on "Gather Instance & urn" to populate for your given username. By default this will return your current Salesforce api version for your end point. This will always be the latest api version you are set to, however this can be customised to a specific older api version if that is relevant to your particular requirements. Customise by altering the api version number in the Instance string.

urn

This is your urn, which by default will return the urn for your Salesforce end point as defined by your username. Customise this if necessary for your particular requirements, typically you would expect to have one of these two:

urn:enterprise.soap.sforce.com

urn:partner.soap.sforce.com

Generally, just accept the defaut returned for you

Load ContentVersion as data, not from file

When loading files to Salesforce, typically this is achieved by providing the path to a file located on a drive accessible by SQL Sales, in a UNC location. Alternatively, by checking this option, you can provide the file as a field in a SQL Server table (see detail in the ss_Loader stored procedure).

Include base64 fields with replica/delta

By default, when replicating (via ss_Replica & ss_Delta), Salesforce fields which contain Content (base64) data are not included as they are binary data-heavy. If you require this data, check this box.

Connection Method (BULK/ODBC)

BULK (for the bulk text based method)

ODBC (for ODBC)

Typically BULK is quicker but you decide what works best for your situation.

Logging

Default is 0-OFF. The log file is created in the same location as the setup “Configuration Path Selection”. Generally, logging is not necessary and certainly 1-DEBUG should only be used when diagnosing issues with SQL-Sales support

0-OFF

1-DEBUG

2-INFO

3-WARNING

4-ERROR

5-CRITICAL

ODBC: SQL Server

Applicable only for the ODBC method: specify your SQL Server, this will be the server on which you have installed SQL Sales.

ODBC: Use trusted connection

Applicable only for the ODBC method: If applicable to your setup and how you have installed SQL Sales (i.e. how you are running SQL Sales). Check this if Windows trusted connection is applicable to your use case.

ODBC: SQL Login

Applicable if connecting via a SQL Server login (i.e. not trusted connection).

ODBC: SQL Password

Applicable if connecting via a SQL Server login (i.e. not trusted connection). (text security obscured).

ODBC: Driver

List of available drivers available on your specified SQL Server.

Integration Username

The API or integration Username specified in the [sub] (subject) claim of the JWT token (when using OAuth 2.0 to authenticate and connect to Salesforce).

Custom Connected App

Each configured Environment in each installation of SQL-Sales will require a custom connected app setting up, enter the name here, for the given Environment (when using OAuth 2.0 to authenticate and connect to Salesforce). Note, SQL-Sales will only accept alphanumeric, space and underscore characters for the ConnectedApplication.Name.

Consumer Key

Consumer key, copied from the above custom connected app to authenticate and connect to Salesforce).

Expiration (days)

Enter a numeric value between 1 and 365. This inputs to the certificate validity period which you'll create, for passing to the custom connected app when setting it up to authenticate and connect to Salesforce).

"Create Certificate" button

This will generate and pass a new digital certificate to your clipboard, for you to save as a .pem file for uploading to your custom connected app. The certificate is not held at rest within SQL-Sales.

"Drop Certificate" button

Instantly revoke an existing certificate.

Test Salesforce

Validate your credentials and licence

Gather Instance & urn

Click on this button to see your Instance and urn. Edit as needed (usually you should have no need to do this, but it can be helpful for more involved use cases. The example in the earlier “Environment” window is from our Demo developer Org.

Save | Save & Close

Save will save your settings, whereas Save & close will Save and close the Environment Configuration window, returning you to the first window, showing your list of Environments you have setup.

Base64

By default the following Objects and their Base64 field are included in what SQL Sales will action with the "Include base64 fields with replica/delta" option. Should you have a custom or additional requirement beyond this default state, clicking on "Base64" will open the window below where you can enter the field you wish to include in your replication.

Included by Default

  • Attachment (Body)

  • ContentVersion (VersionData)

  • EmailCapture (RawMessage)

  • MailmergeTemplate (Body)

  • Scontrol (Binary)

  • ContentNote (Content)

  • Document (Body)

  • EventLogFile (LogFile)

  • MobileApplicationDetail (ApplicationBinary)

  • StaticResource (Body)

ODBC

Either connect using the trusted connection method or via direct SQL credentials (SQL Login and password). Select the driver available to you based on what is actually installed on your SQL Server (list shown below is purely illustrative).

Auto Close

The Configuration tool will auto close 12 hours after first opening or the last open, add, test or save event, whichever is greater.

Did this answer your question?