For versions prior to promis.e V8i SELECTseries 5 (08.11.10.xx), please see Creating and Updating the promis.e Project Database thru V8i SS3
Promis.e has used a database to store project related information for the last several versions. Starting with promis.e 2007 and continuing with Promis.e V8i and Bentley Substation V8i, the project database is a SQL database instead of an Access database, and a project database file is no longer created in each project folder. Instead, one project database now stores information for several projects.
The term the software will be used in this article to mean the Promis.e and Bentley Substation software.
SQL Server Express can be installed from the main Promis.e/Substation installation dialog (see Figure 1) or from a download from Microsoft (see Installing SQL Server). When a local BENTLEYECAD instance of SQL Server is detected when Promis.e/Substation is first run after installation, the software will attempt to create a project database. This article describes alternate ways of creating and updating the project database.
Note: Each version of the software has its own corresponding database schema and "version" number property. When creating or updating the project database with any of the methods in this article, be sure the source material will create or update the database with the correct version number. For example, using a script from the program folder of the software version x will not necessarily create/update the database to a version compatible with the software version y. If the database is created/updated from different media than the version of the software being used, a "Database version does not match the application" message will display when trying to connect to the database in the software. If the media is older, the database schema will then have to be updated. If the media is newer, the software will need to be updated.
If you were last using a version of promis.e 2007 older than v3.0.x, the project database cannot be updated to be compatible with Promis.e V8i. Backup files of the projects must be made in the older software using Backup Project. The resulting .PRJ backup files can then be restored into Promis.e V8i connected to a new Promis.e V8i-compatible project database.
The "Executing a Script" methods are recommended when the project database is hosted by SQL Server running on a different machine than the workstation.
Create the Database Method 1: Project Database Utility
Create the Database Method 2: Executing a Script
Update the Database Method 1: Project Database Utility
Update the Database Method 2: Executing a Script
The methods described in this article assume that a compatible SQL Server is already installed and running.
A version of SQL Server is included with the Promis.e installation and recent versions of Bentley Substation. Beginning with promis.e V8i SELECTseries 5, SQL Server will not be installed during Promis.e installation but must be installed separately from the main installation screen:
Figure 1
Installing SQL Server from the Promis.e/Substation installation file or CD will install SQL Server Express with an instance name of "BENTLEYECAD". The installation wizard must be run on the machine on which SQL Server is to be installed. If it is run on the workstation, it will be installed on the workstation and not a different machine such as a network server.
SQL Server can also be downloaded from Microsoft and installed independently of the software. See Installing SQL Server for more information.
The methods below will not install SQL Server or create or rename an instance. Instances are created by (re)installing SQL Server Express and specifying a new instance name. If creating the database on an existing SQL Server Express that was not installed via the Promis.e/Substation installation files, the instance name may be different, such as the default instance of "SQLEXPRESS". Non-Express versions of SQL Server may not use instances.
After a new project database is created with any of the methods below, the Project Database area of the software's Setup dialog must match the server name and database name in order to connect to the new database. When using Method 1, this is done automatically. Multiple setup configurations can be created for easily switching between different project databases and other settings.
To create databases on a machine other than the workstation such as a network server, the Execute a Script method typically works better.
Included with the software is a Project Database Utility that can be used to create the project database. The utility runs the SqlServerProject.sql script and allows various settings to be adjusted without requiring the script be manually modified as in Method 2.
Note: This method only creates a project database. SQL Server must already be installed and running.
To open Project Database Utility in CONNECT Edition
To open Project Database Utility inV8i
The Project Database Utility dialog will appear. Select the Create Project Database tab.
The server name format is MACHINENAME\INSTANCENAME. If creating a database on the local workstation, "(local)" can be substituted for the machine name. For non-Express versions of SQL Server there will be no backslash and no instance name.
If the server name or instance name entered for "Server Name" is incorrect (does not exist) an error message like the following will be displayed:
Please check SQL Server connection setting.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
The SQL Server Configuration Manager can be used to determine the names of existing SQL Server instances. On the machine where the SQL Server that is to host the database is installed, run this program by selecting Start > All Programs > Microsoft SQL Server 2008 R2* > Configuration Tools > SQL Server Configuration Manager. Then click on Services in the left pane of the SQL Server Configuration Manager and note the instance name(s) listed in the right pane. For example a service called "SQL Server (BENTLEYECAD)" indicates that there is an instance named BENTLEYECAD installed.
* name depends on SQL version
A login with administrator privileges must be used when attempting to create a database, whether Windows or SQL Server authentication is selected.
Windows Authentication - When this option is enabled the utility will try to connect to the SQL Server using the credentials of the Windows user account in which the utiltiy is currently running. If the current Windows account was used to install SQL Server, then Windows Authentication is likely to work. Otherwise, the current Windows account would have had to have been added as a SQL Server Administrator on the Database Engine Configuration step of SQL Server installation or afterwards with SQL Server management software.
Server Authentication - To use this option the SA password must be known. With this option, a more general SQL login can be created for the users with the Username and Password fields on the Project Database Utility dialog.
This password is specified during SQL Server installation and is not required when using Windows Authentication.
As configured in the screen shot of the Project Database Utility dialog above, the utility would create a database named "promise" attached to the BENTLEYECAD instance of the local SQL Express Server. If a "promise" database already exists on the server, use a different name such as "promise1". The standard name for a project database for Bentley Substation is "Substation". See Rules for Regular Identifiers regarding acceptable database names.
SQL Server Management Studio (including Express) can be used to verify the names of existing databases. The figure below shows a database named "promise" in the Object Explorer window of SQL Server Management Studio Express.
On the Project Database Utility dialog, the path specified in the Datafile Path field is where the database file and transaction log will be created. Once a valid server name is entered into the Server Name field, click the Detect button (formerly the Query button) to populate the Datafile Path field with the correct path. Alternatively, the [...] button can be used to browse for the desired folder.
A typical path for SQL Server 2014 with a BENTLEYECAD instance is
C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\DATA
A typical path for SQL Server 2008 R2 with a BENTLEYECAD instance is
C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA
See also: Finding the SQL Data folder
Specifying the path to the Promis.e program folder or the promis-e Data folder or the corresponding Substation folders for the Datafile Path field is not recommended since it would result in the loss of the database should the folder be deleted as part of a reinstallation process.
To create a SQL login (as opposed to using a Windows login) that the software can use to connect to the database, enter the desired name in this field. For example, Promis.e and Bentley Substation have historically used "ecaduser" for the SQL Login name. The SQL login can be used by multiple users.
This field is only available when the Server Authentication option is enabled.
Specify a password to be used with the login/username that will be created if using Server Authentication.
Enter the same password as entered into the Password field.
After configuring the parameters on the Create Project Database tab, click the Test Connection button. If the utility determines the parameters on the dialog are acceptable, a confirmation message is displayed in the message window and the Execute Script button will become available. Otherwise, see the message display area for reasons why the test failed.
Click the Execute Script button to create the database. A separate command window will open as the database is created. When this window closes itself, click the Close button on the Project Database Utility to return to CONNECT Edition's New Project dialog or V8i's Setup dialog, which will be populated with the newly created database name and other associated settings.
The project database can also be created by modifying and executing the SqlServerProject.sql script using SQL Server Management Studio, sqlcmd, or the osql utility. For an Oracle database the script is OracleProject.sql. When the database is to be created on a machine other than the workstation, the script can be can be copied to the server machine from the workstation that has Promis.e/Bentley Substation installed, then executed by the database administrator.
For this example, the script will be executed using SQL Server Management Studio, which is normally installed when SQL Server 2008 R2 is installed via the Promis.e or Bentley Substation package, unless the Management Tools feature was declined during installation. SQL Server Management Studio will need to be downloaded from Microsoft and installed separately when SQL Server 2014 is provided with the Promis.e/Bentley Substation package.
In V8i the script can be found in the Promis.e program folder and in the Substation program folder on the workstation that has Promis.e/Bentley Substation installed. In CONNECT Edition, the script can be found in the "Promis.e program folder \ Electrical \ SqlScripts" and in the "Substation program folder \ Electrical \ SqlScripts" on the workstation that has Promis.e/Bentley Substation installed. If the file cannot be found, perform a search. Make a backup copy of the script before modifying it.
Navigate to the script file in Windows Explorer and double click it. On machines where SQL Server Management Studio is installed, double clicking on a .sql file will open it in SQL Server Management Studio.
When the Connect To Database window appears, enter the name of the server machine and instance where the database to be updated resides in the format MACHINENAME\INSTANCENAME. Consulting the Project Database tab of the software's Setup dialog may help determine what should be entered here. Select the appropriate Authentication method, then click Connect.Refer to the script excerpt shown in the Script Excerpt section below. The excerpt shows how the script would look for creating a database named "promise1" with paths typical of a SQL Server 2008 R2 Express installation with a BENTLEYECAD instance.
Remove the following line, which is highlighted in red in the script excerpt below. This is related to preventing performance degradation.
ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ONAlternatively, change ON to OFF for that line. The line will already be set to OFF in recent versions.
Replace the instances of "$ShortProductName$" with the name of the database to be created. See Rules for Regular Identifiers regarding acceptable database names. The text highlighted yellow shows where the database name must be inserted. DO NOT USE FIND AND REPLACE to replace all instances of this text throughout the script or you will introduce an error by replacing too many instances of this text. Only replace the ones indicated.
The "$ShortProductName$" value in the last line of the excerpt must be changed to promise for the Promis.e software and Substation for the Bentley Substation software, regardless of the name of database, and it is case-sensitive. The correct value for Promis.e is shown highlighted in magenta in the excerpt below.
Modify the paths for the database and transaction log files to reflect the true path to be used.
A typical path for SQL Server 2014 is
A typical path for SQL Server 2008 R2 is
A typical path for SQL Server 2005 is
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
The paths are highlighted in cyan in the script excerpt below but the correct paths may be different than shown. The Finding the SQL Data folder solution may be helpful.
Note: Executing the script will not create any folders in the specified path. All folders in the path must exist at the time the script is executed.
After making the necessary changes, click the Execute button.
After successfully executing the script in SQL Server Management Studio, the Messages pane should display many lines of "(1 row(s) affected)" with no errors.
The below example is for Promis.e, but if you are using Bentley Substation then substitute the highlighted "promise" entries with "Substation."
use masterGOCREATE DATABASE promise1ON (NAME = N'promise1',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\promise1.mdf',SIZE = 200,FILEGROWTH = 40%) LOG ON (NAME = N'promise1_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\promise1_log.ldf',SIZE = 80,MAXSIZE = 200,FILEGROWTH = 20%)GOALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ON GOALTER DATABASE promise1 SET RECOVERY SIMPLEGOuse promise1GOALTER DATABASE promise1 SET AUTO_CLOSE OFFGOSET QUOTED_IDENTIFIER ONGO--********************************************************************--if exists (select * from master..sysdatabases WHERE name=N'promise1')beginCREATE TABLE Project ( id int IDENTITY (1, 1) NOT NULL, name nvarchar(255) NOT NULL, anum nvarchar(255) NULL, templatename nvarchar(255) NULL, username nvarchar(50) NULL, date_create datetime NOT NULL, version nvarchar(255) NULL, prjpath nvarchar(255) NULL, guid nvarchar(50) NULL, ModifyTime datetime NULL, ConnectionBuildTime datetime NULL, isLock int NULL DEFAULT 0, refid int NULL, CONSTRAINT PK_Project PRIMARY KEY (id) );EXEC sp_addextendedproperty 'Version', '8.0.69', 'user', dbo, 'table', project;EXEC sp_addextendedproperty 'ShortProductName', 'promise', 'user', dbo, 'table', project;
Important: Do not attempt to update a database created for Bentley Substation V8i to be compatible with Bentley Substation CONNECT Edition or CONNECT Edition Update 1. Project migration for these versions requires the projects to be backed up in V8i and restored into CONNECT Edition / Update 1. The Project Database Utility should not allow a V8i database to be updated to CONNECT.
To update a database attached to SQL Server running on a machine other than the workstation, the Execute a Script method typically works better.
CONNECT Edition
V8i
Set the Server name and Database fields and the other parameters to the correct values for the project database. The information should usually be made to match what is seen on the New/Modify Project > Database (CONNECT Edition) dialog or Setup (V8i) dialog when connected to the database; it may not be automatically configured correctly. Correct the settings if necessary.
Click the Upgrade Database button.
If the server name or instance name entered for Server Name is incorrect (does not exist) a message such as the following will appear in the message area:
Upgrade Error: A network related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
If the database specified in the Database field does not exist, a message such as the following will appear in the message area:
Upgrade Error: Cannot open database "database" requested by the login. The login failed.
If the incorrect Login name or Password were entered, a message such as the following will appear in the message area:
Upgrade Error: Login failed for user 'login name specified'. Please check MS-SQLServer Login info.
If all the settings are valid when the Upgrade Database button is clicked, a prompt will appear to optionally backup the database. Click Yes or No. Consider electing to backup the database if current backups of the projects have not been made.
An "Are you sure you want to upgrade the database?" prompt will be displayed. Click Yes to accept.
A separate command window will open as the database is updated. When this window closes itself, click the Close button on the Project Database Utility.
Important: Do not update a database created for Bentley Substation V8i to be compatible with Bentley Substation CONNECT Edition or CONNECT Edition Update 1. Project migration for these versions requires the projects to be backed up in V8i and restored into CONNECT Edition / Update 1.
Execute the SQL_UpdateDB.sql script using SQL Server Management Studio, or sqlcmd or the osql utility. For an Oracle database, the update script is ORA_UpdateDB.sql. When the database to be updated is attached to SQL Server running on a machine other than the workstation, the script can be can be copied to the server machine from the workstation that has Promis.e/Bentley Substation installed, then executed by the database administrator.
When using this method to update a project database shared between multiple users, be sure the version of the Promis.e or Substation software installed on the workstation being used matches the desired database version.
The script can be found in the Promis.e program folder and in the Substation program folder on the workstation that has Promis.e/Bentley Substation installed. If the file cannot be found, perform a search.
The script can be can be copied to the server machine and executed by the database administrator. For this example, the script will be executed using SQL Server Management Studio, which is normally installed when SQL Server 2008 is installed via the Promis.e or Bentley Substation package, unless the Management Tools feature was declined during installation. SQL Server Management Studio will need to be downloaded from Microsoft and installed separately when SQL Server 2014 is provided with the Promis.e/Bentley Substation package.
When the Connect To Database window appears, enter the name of the server machine and instance where the database to be updated resides in the format MACHINENAME\INSTANCENAME. Consulting the Project Database tab of the software's Setup dialog may help determine what should be entered here. Select the appropriate Authentication method, then click Connect.
When the script opens, find the following line:
use [$ShortProductName$]
Change $ShortProductName$ to the name of the database to be updated. For example, to update a database named "promise1", the line should be changed to the following:
use [promise1]
When the script opens, find the following line (this line may not exist for scripts from before SS8 so those users should skip this step and go to step 5):
ALTER DATABASE [$ShortProductName$] SET AUTO_SHRINK OFF GO
ALTER DATABASE [promise1] SET AUTO_SHRINK OFF GO
Find the following line:
EXEC sp_addextendedproperty 'ShortProductName', '$ShortProductName$', 'user', dbo, 'table', project;
For the Promis.e software, change $ShortProductName$ in that line to promise regardless of the name of the database being updated:
EXEC sp_addextendedproperty 'ShortProductName', 'promise', 'user', dbo, 'table', project;
For Bentley Substation, change $ShortProductName$ in that line to Substation regardless of the name of the database being updated:
EXEC sp_addextendedproperty 'ShortProductName', 'Substation', 'user', dbo, 'table', project;
Click the Execute button to execute the script.
Disable Auto Shrink to avoid performance degradation
Installing SQL Server
Install SQL Server Management Studio
Finding the SQL Data folder
Rules for Regular Identifiers
Creating an SQL Server Login
Project Database Version - promis.e
Project Database Version - Bentley Substation