On Center Home | MyOnCenter Portal | Start an On-Screen Takeoff Trial | Request a Quick Bid Demo | Contact Us | SALES: 1-866-627-6246
Learn more about Searching
Looking for help for one of ConstructConnect's Other products? Login

Table of Contents

OST - 20.04 Creating a Microsoft SQL Database

Views: 92 Last Updated: 07/24/2023 01:55 pm 0 Rating/ Voters
Be sure to rate this article 5 Stars if you find it helpful!

Microsoft SQL Server is a robust server application that provides a multi-user environment with enhanced security, performance, and stability. If you need to share your database or store it on a network, you must use SQL-type databases. SQL is not provided by On Center Software, it is a separate product available from Microsoft.

See CLS - Using SQL with Classic Products for important information related to using Microsoft SQL with Classic products.

See the previous article for instructions on setting up an Access-type (default) database if you are not sharing your database or storing it in a network location. Microsoft Access® databases are the default database-type used by On-Screen Takeoff and require no additional software to create or use. 

This document details how to setup and create an SQL database. The steps are basic and easy to follow, however On Center Software, Inc. recommends that only IT professionals with knowledge of the server environment and with appropriate expertise in server configuration and administration perform these actions. In some cases, a server may have been configured in a way that would require additional steps not detailed in this process.

We provide these instructions as a resource for our customers, but you are solely responsible for implementation, maintenance, troubleshooting, and configuration of SQL Servers and databases. On Center Software, Inc. does not provide technical support for SQL server setups, assist in the administration, maintenance, configuration, or troubleshooting of an SQL server performance, network connectivity, or any other issues directly related to SQL Server. For assistance with managing your SQL Server installation, please contact the vendor from whom you purchased SQL Server or contact Microsoft directly.

This document assumes your server is running a supported version of Microsoft SQL Server™ that meets (preferably exceeds) the system requirements for SQL Server™. If SQL is not currently installed, we recommend that a certified IT professional assist with SQL Server installation and configuration.

Before proceeding, please the Related article for up-to-date information on using Microsoft SQL with On Center's products. On Center Software only tests and certifies certain versions of SQL Server for each version of the software - what worked in the past may no longer be supported.

Notes about SQL Databases
  • The SQL database is created from within On-Screen Takeoff - not from the SQL Management Console.
  • There are several editions of SQL Server available from Microsoft - you need to determine the edition (Free, Full, Enterprise, etc.) that meets your particular needs. On Center recommends you contact a qualified IT Professional to discuss your needs based on the number of users, your network environment, how you bid jobs, and other software that may be running on your server.
  • The versions of SQL Server with which On Center's products can be used are listed in Using SQL Server to Share Databases in Classic Products
  • There are two ways to authenticate to (log into, attach to...) a SQL Server:
    • Windows Authentication passes the user's Windows logon and password to the SQL Server to make it easier for the users to use the SQL Server database (this is the recommended authentication method)
    • SQL Server Authentication requires the SQL Admin create SQL Logins for all users and manage these user IDs and passwords separate via SQL Server Management Studio. On Center provides no assistance with setting up or maintaining user names and passwords. Each user should have a unique SQL sign on whether Windows or SQL Authentication is used.
  • Once a new SQL database is created you can synchronize it with an existing Access-type database and copy Bids to it (see below for more information). Your database will be ready to use in minutes!
Only users with appropriate permission (SQL Server Administrators and dbo's) should attempt to create or upgrade SQL databases. Attempting to modify an SQL database if you do not have sufficient SQL rights may render the database unusable.

Creating a New SQL Database

Step 1 - Install On-Screen Takeoff (on Your Workstation)

If you haven't already, you need to install On-Screen Takeoff on your computer.

You do not need to install On-Screen Takeoff on the server where SQL is installed, although for future database upgrades and routine maintenance, it's not a bad idea.

On-Screen Takeoff is not a Client-Server application - the program cannot be used in this manner and must be installed on each user's workstation.

Step 2 - Open Your Current Microsoft Access Database

Open the existing Access (*.mdb) database to which you will be synchronizing your new SQL database (to bring over your Master information such as Styles/Sets, Condition Types, Job Statuses, Employees, etc.).

It's a good idea to run a Compact and Repair on the Access database before continuing, see Database Maintenance - Compact and Repair for details.

It is recommended that you synchronize only one database to any other database. Synchronizing multiple databases into a single database can cause duplicated entries.

Step 3 – Create the SQL Database

Ensure your product is licensed before proceeding.

In On-Screen Takeoff click File > New > Database

Select "Microsoft SQL Server Database", the Database Properties (SQL Server) dialog opens

Type the name of the SQL Server in the SQL Server field (On Center Software is unable to provide this information - contact the SQL or Network Administrator for assistance). It is important that all users enter this Server Name exactly the same if you are using OST and QB interactively (On Center recommends entering the Server name in all lower-case letters, for consistency).

When using a "Named Instance" type in the name of the server followed by the instance, for example:  "sqlserver\instance"

Select appropriate server authentication under Connect using (if you are using SQL Authentication, type in the Login Name and Password provided by your SQL Server Administrator). Again, On Center does not know this information, you will have to check with your SQL Administrator.

Enter a Name for the new Database in the Database field

Database names must not contain special characters such as the following symbols: \ / : * ? ’ ; < >;  only standard alpha-numeric English characters should be used. (Anything that is not an English letter or number should be avoided when naming databases or projects.)

The "Requires login" option is only available after the database is created. To enable program-level security (require a password to open the database in OST), follow the steps in this article to create the database, then add appropriate estimators to the database,  and then come back to Database Properties and check the box for this feature. Securing your database is covered later in Related Articles.

In the Measure mode field, select either Imperial or Metric as the system of measurement to use. All UOM boxes in the application are based on this setting. Do not mix Imperial and Metric Bids within the same database, especially if you are using OST and QB interactively. (In On-Screen Takeoff, you can store Imperial and Metric Projects/Bids in the same database, but we recommend against doing so - it is better to create separate Imperial and Metric database.)

Click OK

The database is created and listed on the Bids Tab (any existing, open Microsoft Access (*.mdb) databases will be visible also).

There are no Auto-Backup or Auto-Compress options for SQL databases because backing up must be performed on the SQL Server, using software specifically designed for those tasks (Microsoft SQL Server Management Studio). Your SQL Administrator is responsible for backing-up and "Shrinking" all SQL databases on a regular basis in accordance with good business practices. Regularly running the Compact and Repair routine from On-Screen Takeoff, described in the Maintenance article that follows, is required to keep your database in tip-top shape.

Step 4 – Synchronize SQL Database With Access Database (optional)

See Database Maintenance - Synchronizing Databases for information on "synchronizing" databases. 

Synchronizing transfers Master Database Tables information from one database to another, but your Bids and database preferences are not copied during sync'ing - you will do that in Step 5...

Step 5 – Copy Bids from Access To SQL Database (optional)

To copy Bids from Access database to the SQL database, both databases must be visible on the Bids Tab (both databases must be open...).

Select the Bids you want to copy over, and then just drag-n-drop them from your old database to your new database. See Copying and Pasting Bids from One Database to Another for details.

Verify the Bids are correct and match the originals, then go back and delete the originals (if you want) to avoid any confusion.

If you are copying interactive Bids, disconnect the Bids first, copy both Bids into their respective SQL databases, then re-connect. See the Quick Bid User Guide for instructions on disconnecting and reconnecting interactive Bids.

Step 6 – Archive Your Microsoft Access Databases

Move the Access (*.mdb) database to a folder, CD, or Flash Drive that is not shared nor visible to your end user(s) (you can also move them to a "ZIP" archive). Consider this an archive of previous work which should be accessed only for review of 'old' information. You want to prevent your users from using these old databases by accident.

If you leave the Access (*.mdb) database visible, you risk your end users accidentally opening the outdated Microsoft Access database and creating Bids in the wrong database or modifying archival Bids. Also, leaving the Access databases available increases the risks of 'cross-connecting' more than one OST database to a single QB database and vice versa.

Each user must type the name of the server exactly the same when they "Find"/Open an SQL database, case matters. We recommend using all lower-case letters, just to be consistent. When users type the server name differently, they will cause connection issues for interactive bids.

previous article arrow Creating a Microsoft Access Database Database Maintenance - Upgrading Databases next article arrow

Product documentation (user guides) describes functionality in the latest version of each major release and may not match the functionality in the version you are using. Please check the Product Information and Downloads pages by clicking one of the product buttons above.

Something Wrong with this Article? Let us Know! Copyright 2013 - On Center Software, Inc. by ConstructConnect - All Rights Reserved.