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