Creating and Updating the promis.e Project Database thru V8i SS3 [TN]


	Applies To

	Product(s):	promis•e V8i & promis•e 2007
	Version(s):	3.0.x through 08.11.08.xx
	Environment:	N/A
	Area:	N/A
	Subarea:	N/A
	Original Author:	Bentley Technical Support Group

Overview

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

SQL Server 2005 Express will be installed and the project database created on the local machine if that option is selected during promis•e installation. Installing product updates in the form of service packs will update the project database structure of the database promis•e is connected to at the time of the update.

However, in some cases it is necessary to create or update the project database by other means. These cases include:

SQL Server Express and the project database need to be installed on a network server machine (where the promis•e application itself will not be installed)
The project database needs to be created on an existing network SQL Server
The project database needs to be an Oracle database
The project database that is attached to a network SQL Server needs to be updated
SQL Server Express and the project database need to be installed locally on a workstation because the option was declined during promis•e installation, or there were problems with SQL Server installation
Installation of a promis•e service pack updated the active project database but alternate project databases require updating

Each version of promis•e has its own corresponding database structure, or "version". When creating or updating the project database with any of the methods in this TechNote, be sure the source material will create/update to the correct version of the database. For example, using a script from the program folder of promis•e version x will not create/update the database to a version compatible with promis•e version y. If the database is created from different media than the version of promis•e being used, a "Database version does not match the application" message will display when trying to connect to the database in promis•e. If the media is older, the database will then have to be updated. If the media is newer, promis•e 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 Project Manager > File > Backup. The backup (.PRJ) files can then be restored into promis•e V8i.

Creating a promis•e Project Database

Method 1 below installs SQL Server Express and the BENTLEYECAD instance (for promis•e V8i) or the ECTECAD instance (for promis•e 2007). Running the SQL script in Methods 2 and 3 will not create these instances. So if creating the database on an existing SQL Server Express that was not installed during promis•e installation or by installing the Data Server (Method 1), the database would be associated with a different instance, such as the default SQLEXPRESS instance. Instances are created by (re)installing SQL Server Express and specifying a new instance.Non-Express versions of SQL Server do not use instances.

After a new project database is created with any of the methods below, the Project Database area of the promis•e Setup dialog must be adjusted to match the server name and database name in order to connect to the new database. Multiple setup configurations can be created for easily switching between different project databases and other settings.

Method 1: Install the Data Server

Installing the data server from the promis•e installation file or CD will install SQL Server Express and create a project database named "promise". Click the SQL Server Express Setup button on the promis•e installation dialog to install the data server.

See the SQL Express Server Setup section in the promis•e Installation Guide for details.

http://docs.bentley.com/product.php?prod=223

Method 2: Use the Front-end Program

A front-end program that will execute the script to create the project database is included in the program folder when promis•e is installed on a workstation. The front-end program runs the SqlServerProject.sql script and allows various settings to be adjusted without requiring the script be manually modified as in Method 3. The program is intended to be run on the promis•e workstation. The script itself does not need to be modified; changes are handled by the front-end.

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

For promis•e V8i

Select Start > Run and then click the Browse button. Browse to and run

C:\Program Files\Bentley\promis-e\DBTools.exe

The Promis-e Database Tool dialog will appear. Select the Create Promis-e Database tab.

Set the Server Name and Database Name fields to the desired values for the project database.

The server name format is <computer name>\<instance name>. If creating a database on the local machine, "(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 dialog shown above will create a database named "Promise" in 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".

NOTE: According to the Microsoft MSDN Library, "Database names must be unique within a server and conform to the rules for identifiers." Rules for identifiers state that the name must begin with a letter which can be followed by other alphabetic and numeric characters. Some special characters can be used, such as an underscore ( _ ). Also, limit the database name to 123 characters so that the transaction log file name is less than 128 characters. If these rules are not followed, the database will not be created.

If the server name or instance name entered for "Server Name" in the front-end dialog is incorrect (does not exist) an error message like the following will occur:

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. Run this program by selecting Start > All Programs > Microsoft SQL Server 2005 > 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.

SQL Server Management Studio Express, available in the promis•e installation file or CD, 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 Promis-e Database Tool dialog, the Datafile Path is where the database file and transaction log will be created. A typical path for SQL Server 2005 is

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Using the path to the promis•e program folder or the promis-e Data folder is not recommended since it would result in the loss of the database should those folders be deleted as part of a reinstallation process.

Click the OK button and then the RunScript button.

A DOS window will open with scrolling characters as the database is created. When this window closes, click theCancel button to close the Promis-e Database Tool program.

For promis•e 2007

The process for promis•e 2007 is very similar to the process for promis•e V8i (see above). However, the path to and name of the front end program is:

C:\Program Files\ECT\promis-e\CreateDB.exe

For promis•e 2007, the instance of the SQL Server Express would not be BENTLEYECAD. It may be ECTECAD, SQLEXPRESS, or some other name.

The Datafile Path field will default to the folder containing CreateDB.exe. It is recommended that this be changed so the data files are not located in the promis-e program folder. This will prevent loss of the database should the promis.e folder be deleted during a reinstallation. Set the Datafile Path field to the typical SQL Server data path, such as:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Method 3: Run the Script

The project database can also be created by executing the SqlServerProject.sql script using SQL Server Management Studio, or sqlcmd or the osql utility. For an Oracle database the script is OracleProject.sql. The script can be can be copied to the server machine and executed by the database administrator or executed on the workstation with administrative login credentials for the SQL Server. SQL Server Management Studio Express is included in the promis•e installation file or CD.

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

The script can be found in the following location on a promis•e workstation:

For promis•e V8i:

C:\Program Files\Bentley\promis-e\

For promis•e 2007:

C:\Program Files\ECT\promis-e\

Before running the script, make a backup copy of it since it may need to be modified.

Modify the paths for the database and transaction log files to reflect the true path to be used. The paths are highlighted in cyan in the script excerpt below. Note that the script will create the database (.mdf) and transaction log (_log.ldf) files but will not create any folders in the path. All folders in the path must exist at the time the script is executed.

Modify the name of the database from "Promise" to an unused database name if a Promise database already exists on the server. The database name is used in the script in the locations highlighted in yellow in the excerpt below. The excerpt shows how the script would look for creating a database named "Promise1" instead of "Promise1".

NOTE: According to the Microsoft MSDN Library, "Database names must be unique within a server and conform to the rules for identifiers." Rules for identifiers state that the name must begin with a letter which can be followed by other alphabetic and numeric characters. Some special characters can be used, such as an underscore ( _ ). Also, limit the database name to 123 characters so that the transaction log file name is less than 128 characters. If these rules are not followed, the database will not be created and an "Incorrect syntax near" (followed by the illegal database name) message will appear in the Messages area.

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.

Updating the promis•e Project Database

Method 1: Use the Data Server

This method can be used to update the project database attached to a SQL Express Server.
See the Upgrading the SQL Express Server section promis•e Installation Guide for details.

http://docs.bentley.com/product.php?prod=223

Method 2: Use the Front-end Program.

A front-end program that will execute the script to update the project database is included when promis•e is installed on a workstation. When using this method to update a project database shared between multiple users, be sure the version of promis•e installed on the workstation being used matches the desired database version. The update script itself does not need to be modified; changes are handled by the front-end.

For promis•e V8i

Select Start > Run and then click the Browse button. Browse to and run

C:\Program Files\Bentley\promis-e\DBTools.exe

The Promis-e Database Tool dialog will appear. Select the Update Promis-e Database tab.

Set the server name and database name to the correct values for the promis•e database. The information should match what you see on the promis•e Setup dialog when connected to the correct database. Then click the Updatebutton. A DOS window will open with numbers and other characters scrolling by as the database is updated. When this window closes, click the Cancel button to close the Promis-e Database Tool program.

If the server name or instance name entered for "Server Name" in the front-end dialog is incorrect (does not exist) an error message like the following will occur:

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)

For promis•e 2007

The process for promis•e 2007 is very similar to the process for promis•e V8i (see above). The path to and name of the front end program, however is:

C:\Program Files\ECT\promis-e\UpdateDB.exe

For promis•e 2007, the instance of the SQL Server Express would not be BENTLEYECAD. It may be ECTECAD, SQLEXPRESS, or some other name.

Method 3: Run the Update Script

Execute the SQL_UpdateDB.sql script using SQL Server Management Studio, or sqlcmd or the osql utility. SQL Server Management Studio Express is included in the promis•e installation file or CD. For an Oracle database, the update script is ORA_UpdateDB.sql.

When using this method to update a project database shared between multiple users, be sure the version of promis•e installed on the workstation being used matches the desired database version. Installing service packs to update promis•e also updates the script.

The script can be found in the following location on a promis•e workstation:

For promis•e V8i:

C:\Program Files\Bentley\promis-e\

For promis•e 2007:

C:\Program Files\ECT\promis-e\

Before running the script, make a backup copy of it then modify the second line, "use [promise]", to reflect the true database name if it is different than "promise". Then 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.