ss_EnableDatabase.sql #

Provided alongside the SQLSalesInstaller.exe, the “enabling” script ss_EnableDatabase.sql will fully deploy the required SQL Server components (stored procedures and functions).

ss_EnableDatabase.sql is installed to the exe install location – typically to the default C:\Program Files\SQLSales, hence: C:\Program Files\SQLSales\ss_EnableDatabase.sql

Open and compile to your chosen database, to “enable” that database for use with SQL Sales.

Note, SQL-Sales requires that xp_cmdshell is enabled on your SQL Server. If it is not, the enabling script will identify that and provide a suggested script to run, to alter your system configuration settings.

The following are deployed to your target (schema) and database by running the enabling script:

Object	Type	Purpose
ss_Admin	Stored Procedure	Primarily to control the Daemon from your SSMS client
ss_Delta	Stored Procedure	Delta Replication of Salesforce data
ss_DeltaAll	Stored Procedure	Delta Replication of Salesforce data (all objects)
ss_Handler	Stored Procedure	Generic process used by most stored procedure calls to interact with the Handler application
ss_Loader	Stored Procedure	Allows bulk loads to Salesforce via the SOAP and Bulk APIs (Version 1 & 2)
ss_LoaderChecks	Stored Procedure	Supports ss_Loader
ss_Logger	Stored Procedure	Supports Logging
ss_MetaField	Stored Procedure	Returns Field metadata
ss_MetaObject	Stored Procedure	Returns Object metadata
ss_MetaPick	Stored Procedure	Returns Picklist metadata
ss_ObjectLog	Stored Procedure	Supports Logging
ss_PullChecks	Stored Procedure	Supports ss_Replica & ss_Delta
ss_Replica	Stored Procedure	Full Replication of Salesforce data
ss_ReplicaAll	Stored Procedure	Full Replication of Salesforce data (all objects)
ss_UserInfo	Stored Procedure	Miscellaneous user information for a given Environment
ss_18	Function	Helper function to generate the full 18 character case insensitive Salesforce Id from a 15 character case sensitive Id.
ss_AutoReplica	Table	Controls if a given Object is automatically fully replicated when calling ss_DeltaAll
ss_BulkAPILog	Table	Log table used by ss_Loader and the Bulk API
ss_Log	Table	Log table for all SQL Sales Delta and Full replications for a given Salesforce object
ss_Log_Working	Table	Finer logging detail to ss_Log
ss_ObjectExclusion	Table	Control which objects are excluded from ss_ReplicaAll and ss_DeltaAll
ss_ObjectInclusion	Table	Control which objects are included from ss_ReplicaAll and ss_DeltaAll
ss_SysField	Table	Output from ss_MetaField
ss_SysObject	Table	Output from ss_MetaObject
ss_SysPicklist	Table	Output from ss_MetaPicklist
ss_System	Table	Contains the deployed SQL Sales Version
ss_LoaderDefinition	Function	Supports ss_Loader
ss_SchemaDefinition	Function	Supports ss_Replica & ss_Delta

Full Schema isolation support #

SQL Sales can run in separate schema on the same databases, which can be helpful for certain use cases or where for example you can only operate in one database and need to have different sandboxes replicated to different schemas, even if necessary having Production replication in their own “prod” schema (although generally speaking as best practise we would advise to physically separate a Production replication to its own dedicated database and ideally to its own SQL Server), that said we recognise some customers have to operate within very restricted SQL environments, which is why full schema isolation can be a real benefit.

Setup #

When enabling a given database, simply change the highlighted code block, specifying your required schema (unless the default dbo schema is required in which case do nothing). Now execute the script.

Working with schemas #

As with standard SQL Server, you do not need to specify the dbo schema when calling stored procedures or selecting from tables, if that is the schema you specified on your given installation (which is the usual default), hence using ss_Replica as an example:

exec ss_Replica 'DEMO', 'Account'
select * from Account

you can, if you prefer also run as

exec dbo.ss_Replica 'DEMO', 'Account'
select * from dbo.Account

Let’s say you have enabled with schema “uat” (for a User Acceptance Testing sandbox) and a second schema called “dev”(for a Development sandbox), you can run the below:

exec uat.ss_Replica 'UAT_SBOX', 'Account'
exec dev.ss_Replica 'DEV_SBOX', 'Account'

and thereby create physically separate replicas in tables:

uat.Account
dev.Account

select * from uat.Account
select * from dev.Account

#