ComCap has been tested with Microsoft SQL Server version 2005, 2008, 2008 R2, 2012, 2016 and 2019, both standard and express editions. There are no differences between set-up and data capture for these various versions. While SQL Server may be installed on the same PC as ComCap, for important applications a dedicated server is better.
Sample Database Tables
The database table to which ComCap data will be written must be created before ComCap can be configured, and the database must be running.
ComCap includes Sample Database SQL scripts to create empty tables and stored procedures, for Microsoft SQL Server these are in the two files newdb-mssql.sql and storedproc-mssql.sql. One of the Microsoft tools may be used to run the scripts into SQL server, such as SQL Server Management Studio or Universal SQL Editor is recommended. Then open the newdb-mssql.sql file and change the FILENAME statements with disk paths for the database data and log files to those for the SQL server, then Run all statements into SQL. Assuming there are no errors, open storedproc-mssql.sql and Run that in to add the stored procedures. Beware that running in newdb-mssql.sql more than once will delete any data already captured by first dropping the old tables, before it creates fresh empty tables.
Setting Up a Database Connection
On the Capture Settings, Database tab, choose Database Type as ADO, then click the Specify Database button displays the MDAC Data Link Properties dialog, showing a list of installed data providers:
From the provider list, select Microsoft OLE DB Provider for SQL Server, and click Next>>:.
On the Connection tab, select or enter a SQL server name (it may be available in the drop down box, but may need a UNC path or IP address entered), then specify authentication to logon to the database, and finally the actual database to be used. Click Test Connection to make sure SQL is working, then OK.
ComCap will then open the database and the details will be displayed to confirm it’s all working OK. ComCap can either insert data directly into a table, or pass data to a stored procedure that may manipulate the data and insert it into one or more tables. When you choose 'How to Add Records', a list of either SQL Tables or Stored Procedures will appear, from which one should be chosen. The list of stored procedures will include lots beginning sp_ but these should be ignored. How the data is chosen for the database is specified on the Data Format tab. Other settings on this tab are detailed at Database tab.