	Product(s):	Promise CONNECT
	Version(s):	10.00.XX.XX to 10.03.XX.xx
	Environment:	N/A
	Area:	Installation_Configuration
	Subarea:	N/A

Background

Promis.e uses database to store project related information. Promis.e stores all the Projects details in a SQL Database. And one Project Database can store details for several Projects.

While installing Substation CONNECT Edition by default a SQL Server Express is installed with SQL Server (local)\BENTLEYECAD and after first run Substation will try to create Project Database substation.

Each version of the Promis.e has its own corresponding database schema and "version" number property. When creating or updating the project database using Script, 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.

Note: 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

For Creating Database, you need to have Compatible SQL Server installed and running. SQL Express Server can also be downloaded from Microsoft and installed independently of the software. See Installing SQL Server for more information. And the Compatibility Chart – Promis.e CONNECT

Note: For Creating / Updating Database the user needs to have sysadmin in the SQL Login user under Server Roles. You can follow the below Wiki Link for adding Minimum SQL User Permission.

Create the Database Method 1: Project Database Utility

1.For opening Project Database Utility go to File>> Configure Database >> New Database.

2. Next in the Project Database Utility dialog select the Server >> Authentication Method for logging into the SQL Database >> Specify Database Name >> Select Detect >> Select Test Connection >> select Execute Script.

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.

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 running SQL Server Configuration Manager application. 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.

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

SA password

This password is specified during 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 "substation" database already exists on the server, use a different name such as "promise1". The standard name for a project database for Bentley Substation is "Promise". 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 "substation" 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 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

Specifying the path to the Promis.e program folder or the promis-e Data folder or the corresponding Promise 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 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. When the database is to be created on a machine other than the workstation, the script can be copied to the server machine from the workstation that has 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 2014 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 Bentley Substation package.

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

In CONNECT Edition, the script SqlServerProject.sql can be found in the Promis.e CONNECT Program folder \ Electrical \ SqlScripts" and in the ". If the file cannot be found, perform a search. Make a backup copy of the script before modifying it.

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

2. 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 Configure Database tab which may help determine what should be entered here. Select the appropriate Authentication method, then click Connect.

3. 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 "substation1" with paths typical of a SQL Server 2014 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 ON

Alternatively, change ON to OFF for that line. The line will already be set to OFF in recent versions.

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

5. The "$ShortProductName$" value in the last line of the excerpt must be changed to 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.

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

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.

7. After making the necessary changes, click the Execute button.

Script Excerpt

use master

CREATE DATABASE promise

ON (NAME = N' promise ',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ promise.mdf',SIZE = 200,FILEGROWTH = 40%)

LOG ON (NAME = N' promise _log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ promise _log.ldf',SIZE = 80,MAXSIZE = 2048,FILEGROWTH = 20%)

ALTER DATABASE promise SET AUTO_SHRINK OFF

ALTER DATABASE promise SET RECOVERY SIMPLE

use promise

ALTER DATABASE promise SET AUTO_CLOSE OFF

SET QUOTED_IDENTIFIER ON

--********************************************************************--

if exists (select * from master..sysdatabases WHERE name=N' promise ')

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(1000) NULL,

guid nvarchar(50) NULL,

ModifyTime datetime NULL,

ConnectionBuildTime datetime NULL,

isLock int NULL DEFAULT 0,

refid int NULL,

LockUserName nvarchar(50) NULL,

CONSTRAINT PK_Project PRIMARY KEY (id)

);

EXEC sp_addextendedproperty 'Version', '10.0.11', 'user', dbo, 'table', project;

EXEC sp_addextendedproperty 'ShortProductName', ' promise ', 'user', dbo, 'table', project;

Update the Database Method 1: Project Database Utility

To update a database attached to SQL Server running on a machine other than the workstation, the Execute a Script method typically works better.

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.

1. For opening Update Project Database utility go to File >> Configure Database >> New Database >> Update Project database.

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.

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

3. An "Are you sure you want to upgrade the database?" prompt will be displayed. Click Yes to accept.

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

Execute the SQL_UpdateDB.sql script using SQL Server Management Studio, or sqlcmd or the osql utility. When the database to be updated is attached to SQL Server running on a machine other than the workstation, the script 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 Substation software installed on the workstation being used matches the desired database version.

In CONNECT Edition, the script SQL_UpdateDB.sql can be found in the "Promis.e CONNECT Program folder \ Electrical \ SqlScripts" and in the ". If the file cannot be found, perform a search. Make a backup copy of the script before modifying it.

2. 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 Configure Database tab which may help determine what should be entered here. Select the appropriate Authentication method, then click Connect.

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

4. Next find the below line and:

ALTER DATABASE $ShortProductName$ SET AUTO_SHRINK OFF

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 substation SET AUTO_SHRINK OFF

5.Find the following line:

EXEC sp_addextendedproperty 'ShortProductName', '$ShortProductName$', 'user', dbo, 'table', project;

For Bentley Substation, 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;

6.Click the Execute button to execute the script.

Creating and Updating Project Database - Promise CE

Background

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

See Also