Installing LocalDB for PDXpert client-side ODBC applications

Last update 2024-03-15

The PDXpert client can maintain a "live view" of your part and supplier data for use by ODBC-compatible applications. While PDXpert's default SQLite database works well in most cases, you can install the Microsoft SQL Server LocalDB to improve ODBC capabilities.

Topic contents

• PDXpert PLM client-side database
• Installing and enabling the LocalDB database and tools
• Administrator only: Working with the LocalDB database

PDXpert PLM client-side database

The PDXpert client maintains a client-side database that contains part, supplier and file records. You can use this local data, via an Open Database Connectivity ("ODBC") connection, for applications such as CAD part lookup.

For most ODBC applications, you can use PDXpert's SQLite database for local data caching. SQLite has a small footprint, requires only an ODBC driver installation, and has no user account restrictions.

However, you may prefer to use Microsoft SQL Server Express LocalDB on the client workstation because:

• LocalDB queries and tools are fully compatible with SQL Server
• Views can be developed on the local client using SQL Server Management Studio (see note about developing queries on the server)
• Some applications (e.g., Altium Designer) lock the single-session SQLite database file, blocking periodic updates from PDXpert. LocalDB is a multi-session database, and prevents CAD file locks.

LocalDB is a reduced-footprint free edition of the SQL Server database engine, and is compatible with SQL Server language and tools.

ODBC client applications connect to LocalDB using the SQL Server Native Client for Windows, which contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one dynamic link library.

Installing and enabling the LocalDB database and tools§

This procedure installs SQL Server LocalDB, and the SQL Server ODBC Driver or Native Client. After this procedure is complete, PDXpert will copy ("mirror") selected data from the server's SQL Server database into the LocalDB database.

If you're a PDXpert administrator, you can install the SQL Server Management Studio ("SSMS"). Administrators use SSMS to develop new members of the Views collection.

Installing Microsoft LocalDB and ODBC driver§

The PDXpert client can't use any SQL Server edition other than LocalDB.

Checking for an existing SQL Server LocalDB instance

If another application has installed a SQL Server LocalDB instance, you don't need to install a new instance.

To check your system, find the SqlLocalDB.exe tool on your system (user help SqlLocalDB Utility). If you don't have this tool, there's a good chance you don't have SQL Server LocalDB installed. Open a Windows command prompt in the folder that has SqlLocalDB.exe, and run this to discover the installed instances:

The PDXpert client uses the MSSQLLocalDB (SQL Server 2014 or later) instance or, for older installs, the v11.0 (SQL Server 2012) instance. If both instances are installed and you want to force using the MSSQLLocalDB instance, then you must uninstall the v11.0 instance.

Even if you have a LocalDB instance, you may need to install the Microsoft ODBC driver as described below.

Downloading SQL Server LocalDB and ODBC driver§

• Required — Use SQL Server 2016 LocalDB or later if your system supports it, otherwise use SQL Server 2014 LocalDB.

  Shared LocalDB instances fail in early versions of SQL Server 2017 and 2019. Microsoft fixed this in later SQL Server cumulative updates.

  Install one of the following:

  • Windows 64-bit: SQL Server Express downloader tool. Requires PDXpert 11.2.31103.3 or later, on recent 64-bit Windows.

    ► When asked Which package would you like to download?, select the LocalDB option to download the Windows 64-bit (x64) SqlLocalDB.msi installer.

  • Windows 32-bit: SQL Server 2014 with Service Pack 2 Express LocalDB installer. Requires PDXpert 9.3.27875.554 or later, on Windows 7 or later.

    ► Select the correct SQL 2014 installer for your computer: Windows 64-bit x64\SqlLocalDB.msi or Windows 32-bit x86\SqlLocalDB.msi.

• Required — Use the most recent version of Microsoft ODBC Driver for SQL Server, unless your application requires SQL Server Native Client.

  Install one of the following:

  • Preferred: Microsoft ODBC Driver for SQL Server installer.

    ► Select the correct installer for your computer: Windows 64-bit x64\msodbcsql.msi or Windows 32-bit x86\msodbcsql.msi.

  • Not preferred: SQL Server Native Client 11.0 is contained in the SQL Server 2012 SP2 Feature Pack.

    ► Select the correct installer for your computer: Windows 64-bit x64\Sqlncli.msi or Windows 32-bit x86\Sqlncli.msi.

Installing the LocalDB database and ODBC driver§

You must have Windows administrator permissions to install these components.

After you save the files to your client computer:

1. If your system doesn't have SQL Server LocalDB, install the SqlLocalDB.msi using Microsoft's installation instructions.

2. If your system doesn't have Microsoft ODBC Driver for SQL Server (or SQL Server Native Client), install it using Microsoft's installation instructions.

Setting up an ODBC connection§

To make an ODBC connection to the PDXpert LocalDB database, search Windows for ODBC Data Sources – use 32-bit only if the connected application is 32-bit.

1. Select the appropriate user, system or file DSN list.

2. Click Add... to open the Data Link Properties window.

3. Select the most recent version of ODBC Driver XX for SQL Server (preferred) or SQL Server Native Client 11.0 (not preferred).

   

4. If prompted for connection details, use:

   Server instance	(localdb)\MsSqlLocalDb
   Log on mode	Use Windows NT Integrated security (SPN is empty)
   Database	PDXpertLocalDb

5. If available, use the Test Connection button to verify your settings.

Enabling the LocalDB database within PDXpert§

PDXpert will automatically copy your data from the PDXpert server to the LocalDB database after you enable local views in PDXpert.

1. Open the PDXpert client application.
2. On the Tools menu, select User Settings... (on older releases: Preferences...).
3. Unlock the window (F2 key), mark Enable local views and then choose Using SQL Server LocalDB.
4. Save the setting (F2 key).

Administrator only: Working with the LocalDB database§

Installing and connecting SQL Server Management Studio§

Install the optional SQL Server Management Studio (SSMS) for developing database views and checking query results. Most users do not need to install SSMS.

• Download the SQL Server Management Studio application and follow Microsoft's installation instructions.

• Using SSMS with SQL Server Express LocalDB requires special procedures and Windows Run as administrator permissions. See the Using SSMS with SQL Server LocalDB install guide topic.

Testing PDXpert software's local database§

Use these instructions to connect SQL Server Management Studio to the PDXpert local database.

The PDXpert local database contains:

• Public views (with a …View suffix)
• User-defined views (with a My… prefix)
• Private tables  (labeled _Private1, _Private2, …). The meaning and use of these tables may change from one release to the next, and their contents should not be used in queries.

Right-click the PDXpertLocalDb node and select New Query from the context menu.

This test query selects all parts with three of their sources:

Notes:

1. For simplicity, develop your query on the client database. If you develop a query on the server database using a view within the viewer scheme, remove the explicit reference to viewer before saving the query in the Views collection. Views within the server's viewer scheme appear with the client's dbo scheme. If you need to extract similar data from both client and server, create a separate view tailored for each.

Adding a user-defined view to the PDXpert Views collection§

The LocalDB replaces PDXpert's default SQLite database. LocalDB uses the Microsoft SQL Server language. Any queries developed for SQLite should be reviewed to ensure compatibility with LocalDB.

After creating and testing your SQL query on the LocalDB, you can save it in a Views collection member. For details, see the PDXpert help topic Adding a new collection member.

If you make changes within the PDXpert collections, your queries will need to be reviewed and possibly revised. For instance, a SQL statement will fail if you remove or rename a custom attribute, or rename a part type. Make changes to your PDXpert collections cautiously to minimize reworking your user-defined views.

Your SQL queries must only extract data using SELECT statements. Never attempt to update or delete data within the PDXpert database.

Deleting the PDXpertLocalDB database§

To avoid problems with your SQL Server LocalDB instance, perform these steps before deleting the %UserProfile%\PDXpertLocalDb.mdf and .ldf files.

To delete the PDXpertLocalDb database:

1. In the PDXpert client application, select Tools menu | User Settings... (or Preferences...) command.

2. When the window opens, choose Using SQLite database and clear the Enable local views checkbox.

3. Exit the PDXpert client. Exit/stop all other ODBC applications that are connected to the SQL Server Express LocalDB instance.

4. Detach the database from SQL Server Express LocalDB using one of these methods:

   • Use SQL Server Management Studio, OR

   • Use the SQLCMD utility. If it's not installed, download and install SQLCMD.exe.

     The {sqlcmdpath} depends on how your SQLCMD utility was installed:
     • SQL Server Express or higher: C:\Program Files\Microsoft SQL Server\110\Tools\Binn
     • SQL Server Command Line Utilities: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

     Open a Windows command prompt, and enter these commands:

     When you first run sqlcmd, there may be a delay opening the server connection. If you see an error, enter the command a second time.

     In the following, if your database uses SQL Server 2012 LocalDB, then use (localdb)\v11.0 instead of (localdb)\MsSqlLocalDb.

     Microsoft Windows [Version 10.0.14393]

     (c) 2016 Microsoft Corporation. All rights reserved.

      

     C:\{anypath}>cd "{sqlcmdpath}"

      

     {sqlcmdpath}>sqlcmd -S (localdb)\MsSqlLocalDb

      

     1> USE master;

     2> ALTER DATABASE PDXpertLocalDb

     3> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

     4> GO

     Changed database context to 'master'.

     Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

     Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

     1> sp_detach_db 'PDXpertLocalDb', 'true';

     2> GO

     1> QUIT

     Exit the command prompt.

After you've detached the database from (localdb)\MsSqlLocalDb, you can delete the %UserProfile%\PDXpertLocalDb.mdf and %UserProfile%\PDXpertLocalDb_log.ldf files. To create a new SQL Server PDXpertLocalDb database, open the PDXpert cient and enable the LocalDB database.

SQL Server and Microsoft are trademarks or registered trademarks of Microsoft Corp.