	Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.140
	Environment:	N/A
	Area:	Database Platform Support
	Subarea:	N/A

Background

With very large parts databases, some performance gains can often be achieved by moving from a parts database in Microsoft Access format (.mdb) to a SQL parts database.

While is possible to configure the software to use a SQL parts database, there is no built-in feature of the software to perform the conversion. However, since Access and SQL Server are both Microsoft products, there are utilities available from Microsoft and other vendors to do this.

Recent versions of Access have the capability of moving data to a table in a new or existing SQL database. Access 97 and later versions include an Upsizing Wizard that has the capability of moving data to a table in a new or existing SQL database. Please see http://support.microsoft.com/kb/237980 for more information about this method.

Since some users may not have Microsoft Access, this document describes converting the parts database to SQL using the Import and Export Data application included with SQL Server 2008 - SQL Server 2014. SQL Server 2008 R2 Express is included as an optional installation with recent versions of Promis.e and Bentley Substation.

It would be possible to transfer the data to a table in an existing SQL database such as the project database, but it might be better to transfer the data to a database separate from the project database so that if the project database is lost, or updated, or a new one is created, the parts database is not affected. A separate parts database can also be backed up separately for distribution or safeguarding. This document describes moving the data to a new, separate database.

After moving the data to a SQL database it is necessary to create a new Parts Database Path on the [[Setup]] in order to use the SQL database as the parts database. This document also describes this process.

For simplicity, this document describes the creation and configuration of a SQL parts database on SQL Server 2008 R2 Express that is installed locally on the workstation. The procedure can be adapted to network installations and other versions of SQL software.

Steps to Accomplish

Create a New Database and Table

Import the Data

Create Parts Database Path

Create a New Database and Table

This step is not absolutely necessary since the Import and Export Data application used later in this procedure can create databases and tables, but creating a database with the desired schema eliminates the need to clean up the data types and sizes that Import and Export Data would otherwise choose.

Download the script here

Double click the downloaded file to open it in SQL Server Management Studio (SSMS). SSMS can also be started by selecting Start > All Programs > SQL Server 2008 R2* > SQL Server Management Studio.

* name depends on version of SQL Server installed

On the Connect To Server dialog, specify “(local)\BENTLEYECAD” for the Server Name and select Windows Authentication. This assumes the user currently logged into Windows is an administrator of the SQL Server, which would typically be the case if the user installed SQL Server. However, it may be necessary to use SQL Server Authentication and the “sa” login.

Click the Connect button. After successfully connected to the SQL Server, the script contained in the downloaded .sql file should be displayed.

Make sure no part of the script is selected (highlighted) and click the Execute button or right click the script and select Execute.

The Messages pane should read “Commands completed successfully” if the script executed successfully.

Note: The two paths in the CREATE DATABASE section (highlighted in yellow below) are the typical paths for SQL Server 2008 R2:

CREATE DATABASE PartsDB
ON (NAME = N'PartsDB',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\PartsDB.mdf',SIZE = 200,FILEGROWTH = 40%)
LOG ON (NAME = N'PartsDB_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BENTLEYECAD\MSSQL\DATA\PartsDB_log.ldf',SIZE = 80,MAXSIZE = 200,FILEGROWTH = 20%)
GO

A typical path for SQL Server 2014 is:

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

If execution of the script fails because the paths are not valid (do not exist), correct the paths ([[6619|Finding the SQL Data folder]] may help), and execute the script again.

Import the Data

If there is no data to import, skip to the Create Parts Database Path section.

Select Start > All Programs > Microsoft SQL Server 2008 R2* > Import and Export Data

* name depends on version of SQL Server installed

Click Next to proceed past the "Welcome" step.

Choose a Data Source

On the Choose a Data Source step, select “Microsoft Access” for the Data Source. If there is more than one "Microsoft Access" listed, try "Microsoft Access (Microsoft Jet Database Engine)" first. If the import fails, try another "Microsoft Access" item in the list.

Click the Browse button and select the .mdb parts database file from which the data is to be imported.

The User name and Password fields can typically be left blank.

Click Next.

Choose a Destination

On the Choose a Destination step, select “SQL Server Native Client 10.0” for the Destination.

Enter “(local)\BENTLEYECAD” for the Server Name.

Use the Authentication method that was successful in the Create a New Database and Table section.

Select “PartsDB” for the Database.

Click Next.

Specify Table Copy or Query

On the Specify Table Copy or Query step, select the Copy data from one or more tables or views option.

Click Next.

Select Source Tables and Views

On the Select Source Tables and Views step, place a check mark in the box for the ‘parts_database’ Source. This should automatically map the parts_database table in the source .mdb database to the parts_database table in the destination SQL database.

Click Next.

Review Data Type Mapping

It is normal to see warning symbols on the Review Data Type Mapping step for the Additional_Part, User, and Memo fields. The warnings are occurring because these fields use the Memo data type in the source database and an nvarchar(4000) data type in the destination database, which would result in loss of data if the fields contained data longer than 4000 characters, which would be unusual.

Take no action on this step and click Next.

Run Package

On the Run Package step, enable the Run immediately option and click Next.

Complete the Wizard

On the Complete the Wizard step, click Finish.

The final step will indicate if the import was successful, and if so, how many records (rows) were imported.

Click Close.

This process can be repeated to transfer part number records from additional databases, such as downloaded manufacturer parts databases.

Create Parts Database Path

On the [[Setup]] dialog in Promis.e or Bentley Substation, select the System Paths tab.

Click the New button near the Parts Database Path field.

On the resulting Add Parts Database dialog, type “SQL parts database” into the Parts Database Name field. This is merely a label for the parts database and does not need to match the actual name of the parts database.

Select the Use Connection String option.

Click the Build button near the Use Connection String field.

On the Build dialog, set Database Type to “SQL Server”.

Enter “(local)\BENTLEYECAD” for the Server name and “PartsDB” for the Database to match what was used in the Create a New Database and Table and Import the Data sections.

The script used in the Create a New Database and Table section also created an “ecaduser” SQL login with a password of Ecad1pass, so these credentials can be specified here. Windows Authentication may also work.

Click OK.

On the Add Parts Database dialog, select the parts_database table for the Table field.

Click Next.

Click the Default Mapping button on the next step. Map any custom fields as necessary.