Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki Creating and Updating the Project Database
    • Sign In
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities sisNET - English
    • +OpenUtilities Substation
    • Promis.e Paths
    • -Promis.e Wiki
      • +CONNECT Edition - Promis.e
      • About Promis.e
      • +Administration Features - Promis.e
      • +APIs_VBA - Promis.e
      • +Automation Features - Promis.e
      • +Component Features - Promis.e
      • +Component Management - Promis.e
      • +CONNECT Services - Promis.e
      • +Data Manager - Promis.e
      • -Database Platform Support - Promis.e
        • Backing Up Promis.e Data Automatically
        • Converting to an SQL Parts Database
        • -Creating and Updating the Project Database
          • Creating and Updating the promis.e Project Database thru V8i SS3 [TN]
        • Desired SQL server Not Listed in the available Servers List
        • Disable Auto Shrink to avoid performance degradation
        • Error - Could not find SQL Server instance named BENTLEYECAD
        • Error - Could not find stored procedure
        • Error - Database version does not match the application
        • Error - Index was outside the bounds of the array (Microsoft.SqlServer.smo)
        • Error - Invalid database name - on Project Database Utility
        • Error - Invalid object name
        • Error - Login failed for Ecaduser: Reason: The Password of the account Expired
        • Error - No version resource could be located in database
        • Error - OraOLEDB.Oracle provider is not registered
        • Error - T-SQL execution command line utility stopped working
        • Error - The database is for product... you cannot use it
        • Error at Startup - Error 0 No connection could be made because the target machine actively refused it.
        • Error at Startup - Error 40 Could Not Open a Connection
        • Error at startup - The Type initializer for ECT.ECAD.API.SystemSetup threw an exception
        • Error at Startup - Unrecognized database format, catalog.mdb
        • Error deleting project - transaction log full
        • Error messages encountered due to case sensitive collation of SQL Server
        • Error on backup - Invalid database object reference
        • Error on Project Database Utility - Cannot find the file specified
        • Error running SQL scripts - Incorrect syntax
        • Exception thrown when creating a project due to SQL permissions
        • Exception thrown when opening a page, transaction log is full
        • Finding the SQL Data folder
        • Installing SQL Server
        • Minimum SQL user permissions
        • Project Database Version - Promis.e
        • Rebuild indexes in the SQL project database
        • Rules for Regular Identifiers
        • SQL Server FAQ
        • Troubleshooting Project Database Connection Problems
      • +Display - Promis.e
      • +Drawing Management - Promis.e
      • +Engineering Design Considerations - Promis.e
      • Error - Object reference...General
      • +Find_Replace - Promis.e
      • Finding the Tutorial
      • +Installation_Configuration - Promis.e
      • +Interoperability - Promis.e
      • +Licensing - Promis.e
      • +Maintenance Features - Promis.e
      • +Migration - Promis.e
      • +OS Support - Promis.e
      • +Panel Layout - Promis.e
      • +Performance - Promis.e
      • +PowerPlatform Support - Promis.e
      • +Print_Publish Features - Promis.e
      • +ProjectWise Integration - Promis.e
      • Promis.e Help file
      • +Reports_Output Features - Promis.e
      • +Template Management - Promis.e
      • +Text Tools - Promis.e
      • Training FAQs
      • +Wire Numbering - Promis.e
      • +Wiring Features - Promis.e
      • +Project Management - Promis.e
      • Promis.e Title
      • Working from home with Promise.e
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

     
     Questions about this article, topic, or product? Click here. 

    Creating and Updating the Project Database

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

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

    To open Project Database Utility inV8i

    1. On the workstation, open the software's Setup dialog and click the Create New Database button.
    2. 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 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 "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 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.

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

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

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

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

    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

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

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

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

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

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

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

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

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

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

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

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

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

    See Also

    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

      Original Author: Matt_P
    • CONNECT Edition
    • upgrade
    • v8i
    • Database Platform Support
    • promis.e
    • Administration
    • SQL Server
    • how to
    • Bentley Substation
    • en
    • Share
    • History
    • More
    • Cancel
    • Jordan Stierly Created by Jordan Stierly
    • When: Thu, Jun 3 2010 10:13 AM
    • Scott Walker Last revision by Bentley Colleague Scott Walker
    • When: Tue, Nov 6 2018 10:21 AM
    • Revisions: 40
    • Comments: 2
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies