Create a Project Database
This procedure details how to create a project database 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 installed, then executed by the database administrator.
In CONNECT Edition, the script can be found in the Promis.e program folder:
C:\Program Files\Bentley\Promise CONNECT Edition\Promise\Electrical\SqlScripts\
- 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.
-
Remove the following line which 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.
- Replace the instances of "$ShortProductName$" with the name of the database to be created.
-
The "$ShortProductName$" value in the last line of the excerpt
must be changed to
'promise' regardless of the name of
database, and it is case-sensitive.
-
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
- 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
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;