Converting to an SQL Parts Database


	Applies To

	Product(s):	promis.e, Bentley Substation
	Version(s):	through 08.11.12.xx
	Environment:	N/A
	Area:	Platform Support
	Subarea:	Database Platform Support
	Original Author:	Matt_P, Bentley Technical Support Group

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” tool included with SQL Server 2008. 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” tool 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 the “Import and Export Data” tool would otherwise choose.

Download the script file 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.

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

If execution of the script fails because the paths are not valid (do not exist), correct the paths (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

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.

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 the Next button.

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 the Next button.

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 the Next button.

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 the Next button.

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 the Next button.

Run Package

On the "Run Package" step, enable the “Run immediately” option and click the Next button.

Complete the Wizard

On the "Complete the Wizard" step, click the Finish button.

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

Click the Close button.

This process can be repeated to transfer part number records from another Access database, such as a downloaded manufacturer parts database.

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.