	Product(s):	Promis.e, Bentley Substation
	Version(s):	08.11.10.xx - 10.01.00.23
	Environment:	N/A
	Area:	Database Platform Support
	Subarea:	N/A

For versions prior to promis.e V8i SELECTseries 5 (08.11.10.xx), please see [[5724|Creating and Updating the promis.e Project Database thru V8i SS3]]

Background

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

SQL Server must already be installed

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.

Creating a Project Database

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.

Create the Database Method 1: Project Database Utility

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

Create a new WorkSet if necessary.
On the wokstation, select the WorkSet in Project Manager and click the button for New Project.
On the New Project dialog, select the Database tab.
Click New Database.

To open Project Database Utility inV8i

On the workstation, open the software's [[Setup]] dialog and click the Create New Database button.
Click Yes to the "Are you sure you want to create a new blank project database?" prompt.

The Project Database Utility dialog will appear. Select the Create Project Database tab.

Server Name

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

Authentication

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 [[Installing SQL Server|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.

SA password

This password is specified during [[Installing SQL Server|SQL Server installation]] and is not required when using Windows Authentication.

Database Name

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.

Datafile Path

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 [[5365|program folder]] or the [[5367|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.

Username

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.

Password

Specify a password to be used with the login/username that will be created if using Server Authentication.

Confirm Password

Enter the same password as entered into the Password field.

Test Connection button

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.

Execute Script button

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.

Create the Database Method 2: Executing a Script

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. [[Install SQL Server Management Studio|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.

Note: This method only creates a project database. SQL Server must already be installed and running.

In V8i the script can be found in the [[5365|Promis.e program folder]] and in the [[7613|Substation program folder]] on the workstation that has Promis.e/Bentley Substation installed. In CONNECT Edition, the script can be found in the "[[5365|Promis.e program folder]] \ Electrical \ SqlScripts" and in the "[[7613|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 [[Disable Auto Shrink to avoid performance degradation|performance degradation]].

ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ON

Alternatively, 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

C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\DATA

A typical path for SQL Server 2008 R2 is

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA

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 [[6619|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.

Script Excerpt

The below example is for Promis.e, but if you are using Bentley Substation then substitute the highlighted "promise" entries with "Substation."

use master
GO

CREATE DATABASE promise1
ON (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%)
GO

ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK ON
GO

ALTER DATABASE promise1 SET RECOVERY SIMPLE
GO

use promise1
GO

ALTER DATABASE promise1 SET AUTO_CLOSE OFF
GO

SET QUOTED_IDENTIFIER ON
GO

--********************************************************************--
if exists (select * from master..sysdatabases WHERE name=N'promise1')
begin
CREATE 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;

Updating a Project Database

Update the Database Method 1: Project Database Utility

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 [[Backup Project|backed up]] in V8i and [[Restore Project|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

When connecting to an outdated project database by opening an existing project or creating a new project after upgrading the software, the software will prompt to update the database. If accepted, the Project Database Utility will open to the "Upgrade Project Database" tab.

V8i

When the Project Database tab of the software's [[Setup]] dialog is configured to connect to a project database that has a schema for an older version, the software will prompt to update the database. If accepted, the Project Database Utility will open to the "Upgrade Project Database" tab. Alternatively, it is possible to click the Create New Database button on the Setup dialog then select the Upgrade Project Database tab.

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 [[Backup Project|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.

Update the Database Method 2: Executing a Script

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 [[Backup Project|backed up]] in V8i and [[Restore Project|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 [[5365|Promis.e program folder]] and in the [[7613|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. [[Install SQL Server Management Studio|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.

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.
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

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:

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.

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.

Creating and Updating the Project Database

Background

SQL Server must already be installed

Creating a Project Database

Create the Database Method 1: Project Database Utility

Server Name

Authentication

SA password

Database Name

Datafile Path

Username

Password

Confirm Password

Test Connection button

Execute Script button

Create the Database Method 2: Executing a Script

Script Excerpt

Updating a Project Database

Update the Database Method 1: Project Database Utility

Update the Database Method 2: Executing a Script

See Also