Querying the SOLIDWORKS PDM Database SOLIDWORKS PDM - SolidPractices

 

Best Practice Information

Domain and Role/Product

<a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="SQL server" data-type="internal" contenteditable="false">SQL server</a><a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="SOLIDWORKS PDM" data-type="internal" contenteditable="false">SOLIDWORKS PDM</a><a class="s6m-tag-link sm-link" data-predicate="ds6w:what" data-tag-value="Query" data-type="internal" contenteditable="false">Query</a><strong> </strong>

 

What's Covered? - Jump to each section using the Contents List in the top right!

  • Introduction
  • Best Practices for Writing SQL Queries
  • Querying the SOLIDWORKS PDM Database
  • Special Methodologies
     

Revision History

Rev #DateDescription
1.0Oct 2019Revised for use by customers and reset as document version 1.0
2.0Nov 2021New sub-chapter and updated content for SOLIDWORKS PDM 2022
2.1Jun 2025Updated content for SOLIDWORKS PDM 2025
3.0Oct 2026Document formatted for 3DSwym
<strong>Note</strong>

All SolidPractices are written as guidelines. You are recommended to use these documents only after properly evaluating your requirements. Distribution of this document is limited to Dassault Systèmes SolidWorks employees, VARs and customers that are on active subscription. This document may not be posted on blogs or any internal or external forums without prior written authorization from Dassault Systèmes SolidWorks Corporation.

This document was updated using version SOLIDWORKS 2025 SP3. If you have questions or need assistance in understanding the content, please get in touch with your designated partner.

1) Preface

This SolidPractice document describes best practices and methods to query a SOLIDWORKS PDM database for generating reports and troubleshooting issues that relate to the database. The document also presents SOLIDWORKS PDM database structure and content that is relevant for writing queries and describes the functional role of some of the many database objects in a vault database. As result of this policy, the SolidPractice documents only database tables and columns that have a key role for writing standard reports.

Your Feedback Requested

We would like to hear your feedback and also suggestions for new topics. After reviewing this document, please take a few minutes to fill out a brief survey. Your feedback will help us create the content that directly addresses your challenges.

2) Introduction

The SOLIDWORKS PDM Report Generator (Report Generator) makes it possible to expand existing search options by running custom reports based on structured query language (SQL) queries. For example, Engineers can use Report Generator to show custom document details. Managers can use Report Generator to show project statistics, and administrators can use Report Generator to show general vault settings and maintenance reports, such as the cumulative file size growth in the vault or an overview of all notifications configured in workflows.

The second benefit of knowing how to query an SOLIDWORKS PDM database is for troubleshooting problems in a more efficient way. For example, when troubleshooting errors in the application log, it is possible to retrieve additional information from the database or SQL Server to help isolate a problem and understand why the error occurred.

Another possible use of querying the SOLIDWORKS PDM database is when developing custom applications. However, be aware of license restrictions for SOLIDWORKS PDM Professional. For example, if the application is running on a stand-alone system, ensure that there is an available client access license (CAL) to prevent breaking the Microsoft® SQL Server® license agreement. These license restrictions do not apply to SOLIDWORKS PDM Standard.

Be aware also that custom queries or applications that you create might not work with newer or older versions of the SOLIDWORKS PDM database. This is because database objects and the tables they rely on may have changed or been replaced as part of new functionality or structural changes in the database. Be sure to consider this when developing queries or stand-alone applications.

Notes:

  • This document is for users who have a good basic understanding about how to query SQL Server or other database products that are based on the SQL standard.
  • It is a best practice to treat the SQL queries in this document as code examples only. Do not use these queries in production environments.
  • All of the SQL query examples were tested with SOLIDWORKS PDM Professional. However, the query examples should also work with SOLIDWORKS PDM Standard.
  • With few exceptions, all queries from this document were run against the same demonstration database. All of the queries and the demonstration database used to generate the results are available for download in the Knowledge Base article ID QA00000116234.

3) Best Practices for Writing SQL Queries

The following recommendations apply mainly when writing queries for SOLIDWORKS PDM reports. These recommendations are not mandatory when troubleshooting the vault database.

a) General recommendations

i) Adopt good coding practices

  • Use a style to improve code readability and maintainability. For example, the following two queries return the same result. However, Query 3-2 is much easier to understand because it uses aliases for simplification and additional comments.

Query 3-1

select Documents.Filename, Revisions.RevNr from Documents

Inner Join Revisions on Documents.DocumentID = Revisions.DocumentID and Documents.LatestRevisionNo = Revisions.RevNr where Documents.Deleted = 0

 

Query 3-2

select D.Filename as 'File Name', R.RevNr as 'Latest Version' from Documents D

Inner Join Revisions R on D.DocumentID = R.DocumentID and

D.LatestRevisionNo = R.RevNr -- we retrieve only latest version

 where D.Deleted = 0 -- we remove deleted files

 

  • Avoid using legacy join syntax to prevent accidental creation of cross-joins. Starting with ANSI SQL-92, the syntax for joins has the JOIN keyword and the ON clause (such as, FROM TB1 INNER JOIN TB2 ON TB1.COL1 = TB2.COL1). The previous method from ANSI SQL-89 used a comma between the table names, and all predicates are in the WHERE clause (such as, FROM TB1, TB2 WHERE TB1.COL1 = TB2.COL1).
  • Terminate statements with a semicolon. This is a requirement for statements that precede common table expressions (CTEs). Current and future versions of SQL Server enforce this style for all statements.

    Note: For a complete list of database engine features that are deprecated in future versions of SQL Server, see the Microsoft topic at https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-version-15?view=sql-server-ver15

  • Avoid using the wildcard (*) character in “select” statements. A query such as Select * from Documents does not take advantage of the indexed columns and therefore returns unnecessary columns.
  • Use correct casing. Take into consideration that databases are either case sensitive or case insensitive. Always refer to table names and columns with the exact case.
  • Write standard code whenever possible. This is a general recommendation, which does not actually apply for SOLIDWORKS PDM reports. The main benefit is that your query will be more portable and readable between platforms. For example, you can use CAST instead of the T-SQL Convert function, Coalesce instead of the T-SQL ISNULL function, or the “<>” operator instead of “!=”. There are also many exceptions when using T-SQL functions instead of the standard code because they provide additional functionalities. For example, the Convert function allows use of the date and time style.

     

ii) Logical operations and parenthesis role

You must beware of the logic and mathematical operators involved in queries. For example, Query 3-3 returns version 3 for DocumentID = 301 and all versions of DocumentID = 409. However, Query 3-4 returns only version 3 of the two DocumentIDs.

Query 3-3

select * from Revisions where RevNr = 3 and DocumentID = 301 or DocumentID = 409

 

 

Example of Query 3-3 results

Query 3-4

select * from Revisions Where RevNr = 3 and (DocumentID = 301 or DocumentID = 409)

 

 

Example of Query 3-4 results

iii) Use search arguments

One of the key elements during query optimization is use of the correct predicate in the query filters. Another aspect is how to achieve the best usage of indexes (for instance, have an index seek instead of table scan), but this depends heavily on the query predicate form. If a search predicate can use the index ordering, then it is named “Search Argument”, also known as “SARG”. A search predicate that fails to use the index is known as a “non-SARGable” expression, which can significantly affect the query performance. The following examples demonstrate non-SARG cases. Some of the examples show how to solve these cases, however there is not always a solution.

 

-- Inequality

where Revisions.DocumentID <> 100

or

where Revisions.DocumentID != 70

-- Functions

where SUBSTRING(Users.Username, 1, 1) = 'B'

-- can be made SARGable if we write:

where Users.Username LIKE 'B%'

-- Computed columns

where (Documents.LatestRevisionNo - VariableValue.RevisionNo) > 2

-- Implicit Conversions

select * from DocumentConfiguration where ConfigurationName = 123 /* ConfigurationName column is nvarchar(255) and in this case it will be converted to INT and cannot use an index*/

-- “Like” arguments

where Filename LIKE '%Doc' or Filename is LIKE '%Doc%'

-- but if write as shown below it will become SARGable:

where Filename LIKE 'Doc%'

 

iv) NULLs and the three-valued logic

When handling comparisons, the result can be true or false. However, a third case is also possible when the compared value is “NULL”. The software uses a NULL when there is no data available. NULL values are common during join operations or when a table has columns that accept NULLs. Query 3-5 returns no results because it filters for rows that do not have a numeric value. However, the ValueInt variable accepts only integers or NULLs. If you take into consideration the NULLs as shown in Query 3-6, the query returns all rows that have ValueInt set to NULL. It may be useful to consider NULLs when retrieving metadata that has empty values.

 

Query 3-5

SELECT * from VariableValue

where VariableID = 51 -- in this case 51 is the VariableID for Revision

and ValueInt not like '[0-9]%' -- without numeric values

 

Query 3-6

SELECT * from VariableValue

where VariableID = 51 -- in this case 51 is the VariableID for Revision

and (ValueInt not like '[0-9]%' -- without numeric values

OR ValueInt is null )

 

v) Avoid rules of thumb

It is not accurate to say that certain methods are better than others. For example, statements such as “using joins and avoiding subqueries is more efficient” or “using common table expressions for recursive queries is faster compared with while-loop method” may not always apply. In practice, a specific method is faster in some cases, but using the same method against another database can be much slower. Therefore, it is often misleading to adopt “rules of thumb”. It is important to consider all available options during query optimization.

vi) Beware of language dependent dates literals

Users may interpret a date in the format MMDDYY (such as 01/02/20) as DDMMYY when using English (UK), or YYMMDD for the Japanese language. To avoid language dependencies, use a form that is language-neutral whenever possible. For example, the date form of 20200102 is always interpreted as YYYYMMDD because this form is language-neutral. This recommendation does not fully apply for the form 2020-01-02, because older date and time types (DATETIME and SMALLDATETIME) that still depend on language can appear as YYYY-MM-DD for some, and YYYY-DD-MM for others. On the other hand, when using newer date and time types (DATE, DATETIME2, and DATETIMEOFFSET), the form 2020-01-02 is always interpreted as YYYY-MM-DD. To summarize, when date literals are required, the recommendation is to use the form YYYYMMDD as shown in Query 3-7, which interprets the date same way regardless of the language or date type.

 

Query 3-7

-- setting a value for different variables using a date type

declare @Date1 DATETIME = '20130102'

declare @Date2 SMALLDATETIME = '20130102'

declare @Date3 DATE = '20130102'

declare @Date4 DATETIME2 = '20130102'

declare @Date5 DATETIMEOFFSET ='20130102'

select @date1,@date2,@date3,@date4, @Date5

 

 

Example of Query 3-7 results

 

vii) Use execution plans

An understanding of execution plans is key to improving query performance. Execution plans are very useful to understand the flow of the data, and to identify the most expensive part of the query or what operators the query optimizer used. For example, a Scan operation is much slower than a Seek operation.

For more information about query optimizer operators, see the Microsoft TechNet topic at http://technet.microsoft.com/en-us/library/ms191158.aspx

Example for an estimated execution plan

 

b) SOLIDWORKS PDM database recommendations and rules

  • Beware of SQL Server compatibility - Ensure that the SQL Server version in use supports all T-SQL functions and expressions. For example, T-SQL 2012 functions such as EOMONTH or CHOOSE do not work in environments that use the SQL Server 2005 or 2008 versions.

     

  • Check queries against new SOLIDWORKS PDM major releases – Unlike SOLIDWORKS PDM API methods that are backward compatible, SQL queries might not work on SOLIDWORKS PDM versions other than the system for which it is written. Sometimes, the restructuring of database tables is due to changes in the product. As result, if there is a change to a column name, it is possible that any query using the previous column name will not function in a newer release. Another example is when adding new columns or tables. Although the queries will still work, they will not take into consideration the new changes.

     

  • Use queries for read-only tasks – It is not permissible to use queries to update or modify the SOLIDWORKS PDM database and it may breach End User License Agreement (EULA). As part of this policy, Report Generator prevents queries from running if they include words such as “update” or “drop”.

     

  • Do not make changes to database structure – Any custom views, table indexes, scalar or table functions, or anything that makes changes to the file vault database structure or data is not permissible. To prevent accidental modifications, always ensure to back up the database before writing and testing queries.

     

  • Test queries against large databases – It is always a good practice to test query performance with both small and large databases. This is especially true if the query runs against all documents or projects from a vault. A SQL query should use as few server resources as possible and ideally take no longer than one minute to complete.

     

  • Beware of the Universal Time Convention (UTC) format - The SOLIDWORKS PDM database stores most dates and times in the UTC format. The system converts dates and times to local time zone format on the client computers that access the vault. When writing queries that involve date and time, consider converting the values to local times before returning the result, as shown in the next example.

     

  • For web applications consider using SOLIDWORKS PDM Professional Web API instead of SQL queries. Web API can be used to build Representation State Transfer (REST-ful) web applications using SOLIDWORKS PDM Professional 2022 or newer. It consists of controller APIs that manage various product data operations from your web site.

     

Query 3-8

-- query example to retrieve Date column from Revision table in UTC and local format

Select

Date As 'Date in UTC'

,DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),Date) As 'Date in local'

From Revisions

 

Example of Query 3-8 results

 

i) Report Generator general considerations

  • The data that a query accesses does not follow the permissions of the logged in user. To work around this, you can restrict a query to run for specific users or groups by using the context menu from Report Generator.

     

  • Is not possible to use temporary tables because Report Generator prevents use of the “drop” command, which is required to remove a temporary table. Preventing words such as “drop” and “update” is mandatory to ensure database security. The alternative solution for temporary tables is to use table variables instead (such as Declare @Temp1 as table (DocumentID int […]) or common table expressions (such as With Temp2 as (select DocumentID from Documents) Select * from Temp2).

     

  • When choosing SQL query input variables, consider the available report query arguments. These are FileID, UserID, ProjectID, GroupID, StatusID, and String. With the exception of String, all of the arguments can take values from selected files or from the logged in user. For more information about how to write and run reports, see the Knowledge Base article ID QA00000115829 and the chapter “SOLIDWORKS PDM Reports” in the SOLIDWORKS PDM Administration Guide.

     

4) Querying the SOLIDWORKS PDM Database

a) SOLIDWORKS PDM database structure

The SOLIDWORKS PDM database has deep integration within SQL Server. This ensures better overall performance and takes full advantage of important functionalities. For example, a main characteristic of the SOLIDWORKS PDM database is the use of over 900 stored procedures that provide many advantages including execution plan retention and reuse, network bandwidth conservation, and improved security. In Query 4-1, the actual count for each database object type appears in the list. For example, over 200 tables have primary keys, foreign keys, column constraints, indexes, etc. There are also scalar and table functions and table triggers. However, it is best that you not use such object types when writing queries. Therefore, these object types are outside of the scope of this document.

 

Query 4-1

select type_desc, count(*) as 'No'

from sys.objects

group by type_desc

order by 2 desc

 

 

Example of Query 4-1 results for a SOLIDWORKS PDM 2025 SP3 database

 

It is possible to join Database tables by using primary and foreign keys such as DocumentID, ProjectID, or VariableID. Additionally, the most used tables have clustered and non-clustered indexes to ensure better overall performance. Almost all SOLIDWORKS PDM database tables have self-explanatory names that suggest their main role. You can obtain more information about the column types, primary keys, triggers, or indexes by expanding the table index, columns, or keys in SQL Server Management Studio as shown in the next image. To gain a quick understanding about the content of a table, you can run the Select Top 1000 Rows command from the context menu. This command runs a query against all columns and retrieves the first 1000 rows.

 

User table columns, keys, triggers and indexes

 

You can group most tables into one of the following categories. However, there are exceptions such as the Upgrades table, which lists previous database upgrades and hotfix installations, or the SystemInfo table, which contains generic vault information. The categories include:

  • Document details (such as Documents, DocumentConfiguration)
  • Projects to store information about folders (Projects, DocumentsInProjects)
  • File versioning (Revisions, DocumentRevisionConfiguration)
  • Document references (Xrefs, XrefConfiguration)
  • Metadata (VariableValue)
  • Replication (ArchiveServerStored, ArchiveServers, ArchiveServerNeighbour)
  • Bill of Materials (BomSheetValue, BomSheets, etc.)
  • Document change history (HistoryRename, HistoryDelete, etc.)
  • Workflows (Workflows, State, Transtions, etc.)
  • Notifications for changes to documents or projects (Notifications, Messages)
  • Cards for files, folders, search, or templates (Cards, CardLists, CardControls)
  • Custom columns for the search tool or SOLIDWORKS PDM File Explorer (CustomColumn)
  • Search (SearchFavorites)
  • Cold storage (ColdStorage, ColdStoreSchemas, etc.)
  • User and group rights (GroupRights, UserRights, etc.)
  • Tasks (Tasks, TasksInstances)
  • Reports to store report queries and settings (ReportQueries)
  • Serial numbers (SerialNumbers, SerialNumberPushBackCounters)
  • Variables (Variable, VariableAliases)

i) Database diagram

  • There are two main categories of qualified tables to use in reports. The first category is tables that store various vault customizations and settings (Settings, Cards, UserRights, Workflows, CustomColumns, HistorySystem, etc.). The second and main category is tables that store information about managing documents and projects (Documents, Projects, Revisions, Xrefs, HistoryDelete, etc.).
  • The following database diagram shows the common tables used in SOLIDWORKS PDM. With reference to this diagram, the Documents table stores the main documents data and the Revisions table stores data for file version management. Tables such as Workflows, Status, Transitions, and TransitionHistory are part of workflows management. The VariableValue table stores metadata, the Users table stores user details, and data in the DocumentsInProjects table is used in the Documents and Projects tables.
Common tables in a SOLIDWORKS PDM database

 

When retrieving information from specific database tables, it is important to understand their relationship. Therefore, it is a best practice to generate a database diagram of the SOLIDWORKS PDM database.

ii) Database tables

When writing reports or troubleshooting problems, the most frequently used tables in a SOLIDWORKS PDM database are Documents, Projects, Revisions, Xrefs, and VariableValues. It is possible to join all of these tables with secondary tables to display additional columns. For example, the configuration name from the DocumentConfiguration table, variable names from Variable table, and the object type description from the ObjectType table. Note that several database tables are out of scope and are therefore are not documented. In addition, do not treat the explanations in this chapter as the actual role definition of a table or column. The descriptions for these objects are mainly from the perspective of what type of information you can extract to generate reports.


 

Documents table

The Documents table stores the main document data and has one of the highest number of possible table joins in the database. The Documents table columns are:

Column NameData TypeAllow Nulls
DocumentIDintNOT NULL
Filenamenvarchar(255)NULL
LockProjectintNULL
UserIDintNULL
LockDomainnvarchar(255)NULL
LockPathnvarchar(255)NULL
BusybitNOT NULL
FlushedintNULL
DefValStoredintNULL
RevGenCounterintNULL
LatestRevisionNointNULL
CurrentStatusIDintNULL
WorkingVersionModifiedintNULL
ExtensionIDintNULL
LockDatedatetimeNULL
UserDocRefsModifiedbitNULL
DeletedbitNOT NULL
SharedintNULL
LockViewIDuniqueidentifierNULL
LinkbitNULL
DocTypeIDintNULL
ObjectTypeIDintNULL
FlagsintNOT NULL

In the Documents table:

  • The DocumentID represents the unique ID for any file in the vault.
  • The Filename is the name of the document. However, depending on the ObjectTypeID, the Filename can also be the name of a virtual document, a saved BOM, a cut-list, or an item (such as DOC-000000581.).
  • The LockProject, UserID, LockDomain, LockPath, LockDate, and LockViewID data stores information about what folder, user, computer, path, date and local ViewID a checked out (locked) file has. If the requirement is to return only checked in files, it is possible to use the conditions LockProject=2 (which represents “no project”) or UserID=1 (which represents “no user”).
  • The LatestRevisionNo column shows the latest version. You can use this column to filter metadata or file references that only match the latest version (when using proper joins).
  • The CurrentStatusID column can be joined with the Status table to obtain, for example, the workflow state name.
  • The ExtensionID column can be joined with the FileExtension table to retrieve file extensions (such as sldprt).
  • The Deleted and ObjectTypeID data is often used to filter the results to show files that are not permanently deleted (in recycle bin) (such as Deleted = 0), and to show only normal documents (such as ObjectTypeID = 1).
  • The Shared column displays a value of 2 or more (depending on how many folders the file is shared between) when a document is shared, and a value of 1 when the document is not shared.
  • The DocTypeID column can be joined with the DocType table to retrieve the name of the document category.

     

Projects table

The Projects table stores information about folders. For example, adding a folder to the vault adds an entry to this table. The Projects table columns are:

Column NameData TypeAllow Nulls
ProjectIDintNOT NULL
Namenvarchar(255)NULL
StatusIDintNULL
StartTimedatetimeNULL
DeadlinedatetimeNULL
TimeEstimateintNULL
BusybitNOT NULL
MoveCountintNULL
Pathnvarchar(260)NULL
DeletedbitNOT NULL
FolderTypeintNOT NULL
   

In the Projects table:

  • The ProjectID represents the unique ID for the folder. This is also a primary key. If the ProjectID column is joined with the DocumentInProjects table, it is possible to link the Projects table with any other table that has a DocumentID column.
  • The Name column returns the folder name
  • The StartTime, Deadline, and TimeEstimate data is used in project deadline notifications that are defined in the folder properties as shown in the following image.
Folder properties

 

  • The Deleted column displays a value of 1 if a folder is deleted and a value of 0 if it is not.
  • The Path is the folder path. This column is often used in computed columns to return the full file path (such as P.Path + D.Filename).
  • The FolderType column displays a value of 1 for normal folders and a value of 2 for item folders.

     

Revisions table

The Revisions table keeps track of all individual document versions. Checking in a file creates in a new row in the table that with information about the user that checked in the file. The Revisions table columns are:

Column NameData TypeAllow Nulls
DocumentIDintNOT NULL
RevNrintNOT NULL
DatedatetimeNULL
UserIDintNULL
CommentntextNULL
FileSizeintNULL
FileDatedatetimeNULL
Rootnvarchar(255)NULL
WebRootnvarchar(255)NULL
ProjectNamenvarchar(255)NULL
FlushedDatedatetimeNULL
LockDatedatetimeNULL
ActiveConfigurationIDintNULL
VersionUIDbigintNOT NULL
FileTimeStampdatetimeNULL

In the Revisions table:

  • The DocumentID is the unique ID for the file from Documents table and the RevNr column indicates document version;
  • The Date and FlushedDate columns display the time of check in for the document.
  • The UserID, Comment, and FileSize data stores user, comment, and file size information at the time of document check in.
  • The Root column shows the root of the local file vault view during check in. This information is used to build the correct reference paths when files with references are cached in vault views in different locations.
  • The VersionUID is a unique ID given to each file version at check in. This information is used in replicate vaults to identify which server contains which version.

Xrefs table

The Xrefs table stores information about file references. The Xrefs table columns are:

Column NameData TypeAllow Nulls
DocumentIDintNOT NULL
RevNrintNOT NULL
XRefDocumentintNOT NULL
XRefProjectIDintNOT NULL
XRefRevNrintNULL
HasFullPathbitNOT NULL
RelativePathnvarchar(300)NULL
RefCountintNOT NULL
XRefIDintNOT NULL
RefCountEditfloatNULL
RefTimeStampdatetimeNULL
XrTypeintNOT NULL

In the Xrefs table:

  • The DocumentID and RevNr columns show the unique document ID and version of the “parent” file that contains a reference.
  • The XRefDocument and XRefRevNr columns show which “child” document ID and version that the parent is referencing.
  • The XRefID is a unique ID for this parent-child reference relationship. This ID is used to join the XRefConfiguration table to retrieve additional reference information about what configuration relationship the reference contains.
  • The XrType data represents the reference type. For example, 1 for normal references and 6 for contextual references.

     

VariableValue table

The VariableValue table stores all variable values for documents and folders. The VariableValue table columns are:

Column NameData TypeAllow Nulls
VariableIDintNOTNULL
DocumentIDintNOTNULL
ProjectIDintNOTNULL
RevisionNointNOTNULL
ConfigurationIDintNOTNULL
ValueTextnvarchar(max)NOTNULL
ValueIntintNULL
ValueFloatfloatNULL
ValueDatedatetimeNULL
ValueCachenvarchar(64)NOTNULL
IsLongTextbitNOTNULL

In the VariableValue table:

  • The VariableID identifies the variable holding the value, which can be joined with the Variable table to retrieve the variable name or other variable properties such as unique, mandatory, etc.
  • The DocumentID and RevisionNo columns display the document ID and version for which the value applies
  • The ProjectID column shows the project ID for which the value applies. When the variable value is for a file, the ProjectID has a value 2 (to indicate “no folder”). When the variable value is for a folder, the DocumentID, RevisionNo, and ConfigurationID columns all display a value of 1 (to indicate “no file”);
  • The ConfigurationID identifies the configuration that contains the value. ConfigurationID can be joined with the DocumentConfiguration table to get the actual configuration name.
  • The ValueText and ValueCache columns display the actual variable value (metadata). Both types of data serve the same role, however the ValueCache data is limited to the first 64 characters of the variable value.
  • The ValueText data stores the complete variable value. If the value is longer than 64 characters, then the IsLongText is set to 1. When writing queries that need to return variable values, the recommendation is to use ValueCache primarily for best performance, provided the values are less than 64 characters. The other value type columns are for integer, float, and date values.

     

It is important to understand that variable values save to the database only when adding or change the values (that is, the database stores only unique values). If a user creates a new version of a document and there is no change in a variable value from the previous version, there will not be a new entry in the VariableValue table. These “missing” values do not indicate that those versions have empty values, however, you need to retrieve the topmost change when querying for a value.

b) Generating reports

Reports use SQL queries. Therefore, all of the examples in this document are queries that you can run in SQL Server Management Studio. It may also be possible to reuse the queries in Report Generator (.crp) files or in other applications. Information about how to transform a SQL query into a report file is out of the scope of this document. For this information, see the chapter “SOLIDWORKS PDM Reports” in the SOLIDWORKS PDM Administration guide.

The following SQL query examples and explanations describe the different type of reports that you can generate against a SOLIDWORKS PDM vault database. There are 27 query examples grouped in categories such as documents, variable value, user settings, statistics, and vault administration. You can find many more SQL query and report examples in the SOLIDWORKS Knowledge Base.

To avoid repetitive data in similar code, the document does not repeat explanations from one query to another. For example, Query 4-2 explains almost all inner joins and columns. However, successive queries skip this information unless a new type of join or column is being used.

All code explanations for queries appear in green text and either start with two hyphens (“--”), or appear between the characters “/*” and “*/” (such as /* SQL comment */).

i) Reports for documents

For reports based on documents in the file vault, you can retrieve data such as general document details as shown in Query 4-2 and document version specific data as shown in Query 4-3. Query 4-4 returns a list of replicated server data for replicated environments. Query 4-5 shows how to get a basic list with reference information for drawings and assemblies.

  • Documents details – Common tables for this type of report include Documents, DocumentsInProjects, Projects, Revisions, Status, and Users.

     

Query 4-2

/* Is a good practice to split your query in 2 parts: Input section and Execution section like shown below; In this example for the user Input section it is required to enter the filename for which we get details for */

----START OF USER INPUT SECTION----

Declare @FN(260) = N'DOC-00%' -- Enter a filename or a part of it (e.g.'Wave Washer.slprt' or 'Wave%' where % is a SQL wildcard character).

----END OF USER INPUT SECTION-----

/* Note that for a better understanding of this query "Select" statements should be read last; they are also the last part that is executed in the query, for example an alias defined in Select will not work on Where statement */

----QUERY EXECUTION SECTION—

Select

D.Filename -- Filename from Documents table

,S.Name as 'Status name' -- from Status table. Use the “as” keyword to show a different column name in the result

,D.LatestRevisionNo --latest version number from Documents table

,DT.TypeName as 'Category' -- from DocType table

,U.FullName as 'Modified by' -- User’s full name from Users table

, CAST(DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),R.Date) as smalldatetime) as 'Checked In' -- Check in date from Revision table, converted for minutes accuracy

,DC.ConfigurationName -- from DocumentConfiguration table

, P.Path+D.Filename as 'File path' -- This information is from 2 tables Projects and Documents

/* Next is "From" statement; note that main document information is in table Documents */

From Documents D -- D is the alias assigned to the table and can then be referenced in the rest of the query instead of the full table name ;

--To join Projects table (which has information about parent folder path) we first need to include DocumentsInProjects. The join will assign an alias to the new table and then define which column in the two tables has the matching value to join on.

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

-- Joining Revision table to get version information

Inner Join Revisions R on D.DocumentID = R.DocumentID

-- Adding Users table to get the name of user that created the last version

Inner Join Users U on R.UserID = U.UserID

-- Adding DocumentConfiguration to get the active configuration name

Inner Join DocumentConfiguration DC on DC.ConfigurationID = R.ActiveConfigurationID

-- Adding Status to get the name of the current workflow state

Inner Join Status S on S.StatusID = D.CurrentStatusID

-- Adding DocType to get the category name

Inner Join DocType DT on DT.DocTypeID = D.DocTypeID

-- Adding FileExtension to filter by file type

Inner Join FileExtension FE on FE.ExtensionID = D.ExtensionID

/*Note that "Where" statement is where we filter the results. Also when querying an SOLIDWORKS PDM database it is a good practice to skip unwanted records such as deleted files, internal components or items. Another category of records that have to be removed are system records such as UserID=1, GroupID=1 or WorkflowID=1, when defining a join they are often removed automatically but sometimes they need to be explicitly removed. */

Where

D.Filename like @Filename -- we filter documents based on user input

and D.LatestRevisionNo = R.RevNr -- only latest version

and D.Deleted = 0 -- only files that are not deleted

and D.ObjectTypeID = 1 -- only normal files

and LOWER(FE.Extension) = 'slddrw' -- only drawings. Note that the extension could be stored with varying upper or lowercase in the database so to match regardless of spelling the extension is converted to lowercase.

/*Optional the results can be ordered */

Order By R.Date Desc -- We order by date in descending order

 

 

Example of Query 4-2 results

 

  • Document versions – Common tables for this report are Documents and Revisions. For checked in versions, you can use additional tables such as Users, FileExtension or DocumentConfiguration to get user details, file extensions, or configuration names.

     

Query 4-3

--Query to list specific version details for drawings

----USER INPUT SECTION----

Declare @FN(260) = N'DOC-00%' /* <= Enter a filename or a part of it (e.g.'Wave Washer.slddrw' or 'Wave%' or '%' for all drawing files) */

--QUERY EXECUTION SECTION--

Select D.Filename

--In order to have versions displayed in "3/4" format it is required to make a conversion to nvarchar (text) and concatenate the columns

, CAST(R.RevNr as NVARCHAR(4)) + '/' + CAST(D.LatestRevisionNo as NVARCHAR(4)) As 'Version'

, U.FullName as 'Checked In by'

, CAST(DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),R.Date) as smalldatetime) as 'Checked In'

, DC.ConfigurationName

, R.Root+P.Path+D.Filename as 'File path'

From Documents D

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

Inner Join Revisions R on D.DocumentID = R.DocumentID

Inner Join Users U on R.UserID = U.UserID

Inner Join DocumentConfiguration DC on DC.ConfigurationID = R.ActiveConfigurationID

Inner Join FileExtension FE on FE.ExtensionID = D.ExtensionID

Where D.Filename like @Filename

and D.Deleted = 0 and D.ObjectTypeID = 1

and LOWER(FE.Extension) = 'slddrw' -- only drawings

Order By D.DocumentID, R.RevNr -- We order by DocumentID and Version

 

 

Example of Query 4-3 results

 

  • Document and replication details – You can use ArchiveServers to get the replication server details and ArchiveServerStored for information about replicated files.

Query 4-4

-- Query to get replication information for specific part documents

----USER INPUT SECTION----

Declare @FN(260) = N'SHAFT%' /* <= Enter a filename or part of the filename */

----Query EXECUTION SECTION--

Select D.Filename

, CAST(R.RevNr as NVARCHAR(4)) + '/' + CAST(D.LatestRevisionNo as NVARCHAR(4)) As 'Version'

, U.FullName as 'Checked In by'

, CAST(DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),R.Date) as smalldatetime) as 'Check In date'

, R.Comment as 'Check In comment'

--in the next column we get a list with servers where the file version is replicated to by running a sub-query against ArchiveServers; this method is also explained in detail in Query 5-2

, COALESCE (SUBSTRING((SELECT(', '+ A.ArchiveServerName)

FROM ArchiveServers A

Inner Join ArchiveServerStored ASD on ASD.ArchiveServerID = A.ArchiveServerID

WHERE ASD.VersionUID = R.VersionUID -- we "join" the sub-query on the unique version

and ASD .ArchiveType = 1 -- Only normal files

ORDER BY A.ArchiveServerName -- we order the list alphabetically

FOR XML PATH('') --function FOR XML PATH wrote the results in a list format (e.g. ", TS-APW8X64-SWK, W7X64C1")

), 3, 1000 ) --SUBSTRING function parameters to remove first ', ' from list

,'') as 'Replicated on servers' --COALESCE function parameter to remove NULLs

From Documents D

Inner Join Revisions R on D.DocumentID = R.DocumentID

Inner Join Users U on R.UserID = U.UserID

Inner Join FileExtension FE on FE.ExtensionID = D.ExtensionID

Where D.Filename like @FD = 0 and D.ObjectTypeID = 1

and LOWER(FE.Extension) = 'sldprt' -- we retrieve only parts

Order By D.DocumentID, R.RevNr

 

 

Example of Query 4-4 results

 

  • Document references – This report requires using the Xrefs table to retrieve references. The report uses a recursive method to loop through all document references. This example retrieves only the file structure and reference type. However, it is possible to extract addition information by combining with Queries 4-2 or 4-3.

     

Query 4-5

-- Query example to get file references with references type details. Note a similar example using common table expressions is provided in Query 5-4

----USER INPUT SECTION----

Declare @FN(255) = '_MITER SAW.SLDASM' -- Type the exact filename

Declare @PN(255) = 'Prototype - X32'-- Type the exact name of parent folder

----QUERY EXECUTION SECTION--

--We first declare several variables

Declare @DI

Declare @PI

Declare @II

Declare @RI

--@XR

Declare @XT( iIndex Int, ParentID Int, DocumentID Int, XrType Int, Sort NVARCHAR(255))

Set @iIndex = 0 -- The tree level of the reference

--@DF @FS

Set @DocID = (Select D.DocumentID from

Documents D

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

where lower(D.Filename) = lower(@FileName) and lower(P.Name) = lower(@ProjectFolder))

-- We add parent document as the first entry in @XT

Insert Into @XrefTable( iIndex, ParentID, DocumentID, XrType, Sort )

Select Distinct @iIndex, 0, Doc.DocumentID, 1, 1

From Documents Doc Where Doc.DocumentID= @DocID

--While loop required to get file references

While( 1=1)

Begin

Set @RowCount=(Select COUNT(*) FROM @XrefTable)

Set @iIndex = @iIndex + 1

Insert Into @XrefTable( iIndex, ParentID, DocumentID, XrType, SORT )

Select Distinct @iIndex, X.DocumentID, X.XRefDocument, X.XrType

--Next column is made only to sort references in alphabetic order

, SORT + RIGHT('000'+ CAST((ROW_NUMBER() OVER(ORDER BY D.Filename)) AS varchar(max)),4)

From @XR

inner join XRefs X on X.DocumentID = r.DocumentID

Inner Join Documents D on D.DocumentID = X.XRefDocument

Where

r.iIndex = (@iIndex - 1) and

X.XrType <> 2 --we skip circular references to avoid repeated loops

--Next is the condition to exit the loop

IF @RowCount=(SELECT COUNT(*) FROM @XrefTable)

BREAK

--Next we need to update the sort order for each new reference level

Update @XrefTable

Set Sort= Sort + '0000'

Where iIndex < @iIndex

End

/* Note that because we used Update command means this query can't be used in Report Generator */

Select

Case When X.ParentID = 0 then D.Filename

Else '|' + REPLICATE('__', iIndex) + D.Filename -- shows tree lines in results

End As 'File names'

, Case

When XrType = 1 Then 'Normal' -- normal references

When XrType = 6 Then 'Contextual' -- e.g. "part in part" reference

End as 'Reference type' -- shows reference type

From @XX

Inner join Documents D on D.DocumentID = X.DocumentID --Join required to get filenames

Order by Sort

 

Example of partial Query 4-5 results compared with the Contains tab in SOLIDWORKS PDM File Explorer

 

When compared with the Contains tab, there is a second type of reference with the name Contextual.

ii) Reports for variable values

The VariableValue table stores variable values for both documents (see Query 4-6) and folders (see Query 4-7). A common request when generating reports that contain variable values is to have variable names as columns. Query 4-8 shows such an example.

It is a common mistake for this type of report not to consider that a variable value for a document is stored per DocumentID, per RevisionNo, per VariableID, and per ConfigurationID. To avoid duplicate values and incorrect results, the recommendation is to use these four columns in SQL statements.

  • Specific document – The main table for retrieving variable values is the VariableValue table, can be joined with tables such as Variable to get variable names or Revisions to get version details.

Query 4-6

--Query to retrieve specific file metadata

----QUERY INPUT SECTION--------

Declare @FN(260) = N'DOC-000000538.SLDDRW' -- Enter a filename

----QUERY EXECUTION SECTION---

Select

D.Filename -- column from Documents table

,CAST(R.RevNr as NVARCHAR(4)) + '/' + CAST(D.LatestRevisionNo as NVARCHAR(4)) As 'Version'

,DC.ConfigurationName -- configuration name retrieved from DocumentConfiguration table

,V.VariableName

,VV.ValueText as 'Value' -- ValueCache can be used instead of ValueText for performance reasons, but it should take into account IsLongText column.

,P.Path+D.Filename as 'File path' -- We use this column also in case there are duplicate filenames

From Documents D

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

Inner Join Revisions R on D.DocumentID = R.DocumentID

Inner Join Users U on R.UserID = U.UserID

Inner Join FileExtension FE on FE.ExtensionID = D.ExtensionID

-- Only return the versions where value is explicitly added or changed

Inner Join VariableValue VV on VV.DocumentID = R.DocumentID and VV.RevisionNo = R.RevNr

Inner Join Variable V on VV.VariableID = V.VariableID

Inner Join DocumentConfiguration DC on DC.ConfigurationID = VV.ConfigurationID

Where

D.Filename like @Filename

and D.Deleted = 0 -- exclude deleted files

and D.ObjectTypeID = 1 -- only normal files

and ConfigurationName <> '@' --exclude @ configuration

Order By R.DocumentID, R.RevNr, V.VariableName Desc -- We order by DocumentID, Version and variable name

 

Example of Query 46 results

 

  • Specific folder – The VariableValue table also contains the variable values for folders.

Query 4-7

--Query to retrieve specific folder variable values

--USER INPUT SECTION—

Declare @FN(260) = N'PRJ-000001' -- Enter a folder name

----QUERY EXECUTION SECTION--

Select

P.Name

,P.Path 'Folder path'

,V.VariableName as 'Variable name'

,VV.ValueText as 'Variable Value'

From Projects P

Inner Join VariableValue VV on VV.ProjectID = P.ProjectID

Inner Join Variable V on VV.VariableID = V.VariableID

Inner Join DocumentConfiguration DC on DC.ConfigurationID = VV.ConfigurationID

Where

P.Name like @FOLDERNAME

and VV.DocumentID = 1 /* only folder metadata */

Order By P.ProjectID, VV.VariableID

 

Example of Query 4-7 results

 

  • Specific variables – The next example returns a list of the latest values of multiple variables. The report uses a temporary variable table to store the variable names and a PIVOT function to change the row-based results into column-based results.

Query 4-8

--Query example to retrieve the latest values of multiple variables by file and configuration.

---User Input Section----

Declare @IN(4000)

Declare @FN(255)

--Alter the line below with the names of the variables enclosed with [] that you want to include

Set @InitialVariableList='[Author], [Date], [Description]'

--Alter the line below with a path/filename filter if required

Set @FileFilter='DOC-000000316%'

---Query Execution Section---

Declare @SN(Max)

SET @SQLScript = '

--next a temporary table is created to store variable IDs based on user’s input, removing the commas and brackets

Declare @VT(VarID INT)

INSERT INTO @VV

WHERE VariableName IN ('+ REPLACE(REPLACE(@InitialVariableList,'[',''''),']','''')+')

--next is created a temporary table to store values for the selected variables

Declare @TT( DocID INT, ConfigurationName NVARCHAR(255), VariableName NVARCHAR(255), ValueText NVARCHAR(Max) )

INSERT INTO @TempVariables (DocID, ConfigurationName, VariableName, ValueText)

SELECT DocumentID, ConfigurationName, VariableName, ValueText

FROM VariableValue VV

INNER JOIN (SELECT DocumentID AS DocID, ConfigurationID AS ConfID, VariableID AS VarID, MAX(RevisionNo) AS RevMax

FROM VariableValue

--next join is created to filter only variables defined in input section

INNER JOIN @VV=VariableValue.VariableID

GROUP BY DocumentID, ConfigurationID, VariableID) as T

ON VV.DocumentID=T.DocID AND VV.ConfigurationID=T.ConfID

AND VV.VariableID=T.VarID AND VV.RevisionNo=T.RevMax

INNER JOIN DocumentConfiguration ON DocumentConfiguration.ConfigurationID=T.ConfID

INNER JOIN Variable

ON Variable.VariableID=T.VarID

--next a pivot method is used to get variable names as columns

SELECT FileName, ConfigurationName, ' + @InitialVariableList + '

FROM

(SELECT Path + Filename AS FileName, ConfigurationName, VariableName, ValueText

FROM Documents

INNER JOIN

(SELECT DocumentsInProjects.DocumentID, Projects.Path AS Path

FROM DocumentsInProjects LEFT JOIN Projects ON Projects.ProjectID=DocumentsInProjects.ProjectID) AS P

ON P.DocumentID=Documents.DocumentID

INNER JOIN

@TT

ON T.DocID=Documents.DocumentID

WHERE FileName LIKE ''' + @FileFilter +

''') AS SourceTable

PIVOT

(

MAX(ValueText)

FOR VariableName IN

(' + @InitialVariableList + ')

) AS PivotTable

ORDER BY FileName, ConfigurationName'

--dynamic SQL has to be used to handle special characters used

Exec sp_executesql @SQLScript

 

Example of Query 4-8 results

iii) Reports for database settings

The previous report examples are mainly for SOLIDWORKS PDM users. This section provides report examples for use by vault administrators and others who implement and manage SOLIDWORKS PDM. Vault settings are saved in tables such as Settings (see Query 4-9) or MenuCmds (see Query 4-10).

  • General Settings – The Settings table stores the main user settings that are available in SOLIDWORKS PDM Administration Tool. The next example retrieves only settings from the Reference Dialog page of the Settings dialog box.

     

Query 4-9

--Query that returns Reference Dialog settings per user

--USER INPUT SECTION---

Declare @UN(250)= 'Admin' -- Use % sign to return all users settings or replace it with a specific user name i.e. Admin

--QUERY EXECUTION SECTION--

DECLARE @t1 TABLE (UserID int, Username ntext, Setting ntext, VarID int, Value ntext);

INSERT INTO @t1

SELECT U.UserID, U.Username

, Case VarID --next we replace variable IDs with the actual variable name; note that these names are not stored in database

WHEN 43 THEN 'Auto select reference files to get latest when checking out.'

WHEN 11 THEN 'Always work with latest &version of files.'

WHEN 14 THEN 'Check out files silently without showing the check out dialog box.'

WHEN 15 THEN 'Try to check out all referenced files when the referencing file is checked out.'

WHEN 23 THEN 'Do not mark &referenced files when changing state.'

WHEN 44 THEN 'Enable the get version command in SOLIDWORKS Add-in.' END AS 'ReferenceDialogSetting', S.VarID, S.Value

FROM Settings S

Inner Join Users U ON U.UserID = S.UserID

where S.VarID in (11,14,15,23,43,44) and Username not like '%Deleted%' and UserName like @UserName

UNION ALL

--This second table was required because the show “not rebuilt” warning has value 1 when is disabled compared with other Reference Dialog settings

SELECT U.UserID, U.Username, Case VarID

WHEN 30 THEN 'Show "not rebuilt" warnings on drawings and assemblies in need of rebuilding.'

END AS 'ReferenceDialogSetting', S.VarID, CASE WHEN S.Value LIKE '0' THEN '1' WHEN S.VALUE LIKE '1' THEN '0' END AS 'VALUE'

FROM Settings S

Inner Join Users U ON U.UserID = S.UserID

where S.VarID = 30 and Username not like '%Deleted%' and UserName like @UserName

--The actual select statement for the query

SELECT UserName, Setting, CASE WHEN Value Like '0' THEN 'OFF' When Value like '1' Then 'ON' Else Value END AS 'Status' from @t1

Order by UserID, VarID

 

Example of Query 4-9 results

 

  • Menu Settings – These settings are available in the MenuCmds table. Note that all entries for UserID = 1 are the “default” settings for menu commands. These entries are useful to determine what users made changes to the default settings.

     

Query 4-10

--Query to return user(s) available/enabled commands in SOLIDWORKS PDM File Explorer menus

Declare @UN(150) = N'Dan' /* <== Optional you can update variable @UserName (i.e. 'JohnSmith'); if is left NULL or using value N'%' it will return all the users menu settings */

Select U.Username,

/* Note that in many cases settings are stored as numerical values in the database and instead of using a simple join it is required to use “Case” statements as shown in the next two columns in order to display each separate setting. */

Case MenuID

When 2 Then 'Bar\\Actions'

When 3 Then 'Bar\\Display'

When 4 Then 'Bar\\Modify'

When 5 Then 'Bar\\Tools'

When 16 then 'Background Right Click'

When 17 then 'File Right Click'

When 18 then 'Folder Right Click'

ELSE 'Unknown Menu'

END AS 'Menu name'

,Case M.CommandID

when 1 then '' when 2 then '' when 3 then '---------------------------------' when 4 then 'Check out' when 5 then 'Check In' when 6 then 'Undo Check Out' when 7 then 'Get latest' when 8 then 'Get Version >' when 11 then '' when 12 then 'Generate Item' when 13 then 'Browse to item' when 14 then 'Copy' When 15 then 'Paste' when 16 then 'Paste as reference' when 17 then 'Paste shared' when 18 then 'Change State >' when 20 then 'Increment Revision' when 23 then 'Label...' when 24 then 'History' when 25 then 'View File' when 26 then 'File Preview' when 27 then 'Split Preview Tab' when 28 then 'Preview Placement' when 29 then 'Properties' when 30 then 'Favorite Searches >' when 31 then 'EPDM Searches >' when 34 then 'Notify >' when 41 then 'Inbox' when 42 then 'Clear Local Cache' when 43 then 'Items' when 44 then 'Report Generator' when 45 then 'EPDM Adminstration' when 46 then 'Options >' when 47 then 'Send To >' when 48 then 'Delete' when 49 then 'Rename' when 50 then 'Refresh' when 51 then 'Cut' when 52 then 'Update >' when 53 then 'Show Files' when 54 then 'Show Bills of Materials' when 55 then 'Show Search results' when 56 then 'Copy Tree...' when 71 then 'Update References...'

when 74 then 'View >' when 75 then 'Sort By >' when 76 then 'New >' when 94 then 'Add File To Vault' when 100 then 'Work Off-line' when 105 then 'Card Preview' when 111 then 'Browse to Item >' when 122 then 'Search Tool...' when 124 then 'Edit...'

ELSE 'Unknown Command'

End AS [Command Name]

--, M.CmdIndex /* Uncomment this line to get command indexes(order numbers) */

--, M.SubMenuID /* Uncomment this line to get the sub-menus IDs */

From Users U inner join MenuCmds M ON U.UserID = M.UserID

where

MenuID NOT IN (1, 8, 22, 23, 24, 25,26,27, 28, 29, 30, 33, 37, 38, 39, 40,41, 42, 43, 44, 45,46, 49, 50, 51, 52, 54, 55, 62, 72, 73 ) /* removing the pre-defined menus such as the change state sub-menu */

AND U.UserName <> 'Admin' /* without permissions for Admin user */

AND U.UserName not like'%Deleted%' /* skip permissions for deleted users */

AND U.username LIKE COALESCE(@UserName, '%') /* we check if a specific user was provided in query user input section*/

order by UserName ASC, MenuID DESC, CmdIndex ASC

 

Example of Query 4-10 partial results

 

iv) Statistics

Statistics data is useful for vault maintenance or project management. You can also generate statistics to show an audit trail for various data. For example, you can use the Revision table to get file version history data (see Query 4-11). For SOLIDWORKS PDM operations such as rename, delete, and rollback, you can use the History type tables (such as HistoryRename, HistoryDelete, HistoryRollback, etc.)

The example in Query 4-11 collects data first in common table expressions. However, the query performance may be affected when is used against large databases because such tables cannot be indexed. The main advantage is that you can use the query in a report file.

 

Query 4-11

--Query that return total file versions, total files size and cumulative total files size per each month

With

FS (FileSize, DocID, RevNr, Date, MN) As

(select CONVERT(bigint,R.FileSize), r.DocumentID ,r.RevNr , r.FlushedDate,

DENSE_RANK() OVER (ORDER by CONVERT(NVARCHAR(7),r.FlushedDate,102)) --Column used to get a unique number for each month

FROM Revisions R

JOIN Documents D ON D.DocumentID = R.DocumentID

WHERE D.ObjectTypeID = 1 AND D.Deleted <> 1),

MF (MN, MFS, FV) as --CTE (Common table expression) created to get file size monthly totals

(Select MN, SUM(FileSize)/(1024*1024), Count(Date)FROM FS Group by mn),

MFWT (MN, TMFS) AS --CTE created to get cumulative file size totals

(Select MF1.MN,SUM(MF2.MFS)FROM MF MF1

INNER JOIN MF MF2 on MF1.MN >= MF2.MN

GROUP BY MF1.MN)

--next is the actual select statement of the query where we call all CTEs

select

CONVERT(NVARCHAR(7),FS.Date,102) As Month

,MF.FV as 'Total file versions per month'

,MF.MFS as 'Total files size per month (MB)'

,MFWT.TMFS as 'Cumulative total file size per month (MB)'

,Convert(DECIMAL(10,2),(CONVERT(DECIMAL(10,2),(MFWT.TMFS-(select MFWT2.TMFS from MFWT MFWT2 WHERE MFWT2.MN= MFWT.MN-1)))/CONVERT(DECIMAL(10,2), NULLIF((select MFWT2.TMFS from MFWT MFWT2 WHERE MFWT2.MN= MFWT.MN-1),0))*100)) as 'Total growth per month (%)'

FROM MFWT

INNER JOIN FS on FS.MN=MFWT.MN

INNER JOIN MF on FS.MN=MF.MN

Group by CONVERT(NVARCHAR(7),FS.Date,102),MFWT.MN, MF.MFS, MF.FV, MFWT.TMFS

order by MFWT.MN

 

Example of Query 4-11 results

 

The next example uses temporary tables, which improve the query performance significantly due to the possibility to index these tables. Note that using a Data Type of int instead of NVARCHAR(10) for Date columns further improves performance.

Query 4-12

--Query to get statistic for SOLIDWORKS PDM operations grouped by days

--First we retrieve files version data

Create Table #MD(DocumentID int, RevNr int, Date NVARCHAR(10), UserID int, LockProject int, LatestRevisionNo int, Deleted bit, Users NVARCHAR(128))

Insert Into #MD(DocumentID, RevNr, Date, UserID, LockProject, LatestRevisionNo, Deleted, Users)

Select R.DocumentID, R.RevNr, Convert(NVARCHAR(10),R.date, 103), R.UserID, D.LockProject, D.LatestRevisionNo, D.Deleted, U.Username

FROM Revisions R

INNER JOIN Documents D ON D.DocumentID = R.DocumentID

INNER JOIN Status S on D.CurrentStatusID = S.StatusID

INNER JOIN Users U on U.UserID = R.UserID

WHERE S.WorkflowID > 1 -- only from a valid workflow

AND ObjectTypeID = 1 -- no items, virtual components, cut lists or BOMs

--next we create a non-clustered index to improve performance

create nonclustered index IX_MD on #MD (DocumentID,Date, UserID)

--We collect data from Xrefs table to get references amount

Create Table #XD (DocumentID int , RevNr int,UserID int, XRefID int, Date NVARCHAR(10))

Insert into #XD (DocumentID, RevNr, UserID, XRefID, Date)

SELECT X.DocumentID, X.RevNr,R.UserID, XRefID, Convert(NVARCHAR(10),RefTimeStamp, 103)

from Xrefs X

INNER JOIN Revisions R on X.DocumentID = R.DocumentID and X.RevNr = R.RevNr

INNER JOIN Documents D ON D.DocumentID = X.DocumentID

INNER JOIN Status S on D.CurrentStatusID = S.StatusID

WHERE X.XrType = 1 -- only normal references

AND S.WorkflowID > 1 -- only from a valid workflow

AND D.ObjectTypeID = 1 -- no items, virtual components, cut lists or BOMs

create nonclustered index IX_XD on #XD (DocumentID,Date, UserID)

--We extract from TransitionHistory details about change state operations

Create Table #TD (DocumentID int , RevNr int,UserID int, Date NVARCHAR(10))

Insert into #TD (DocumentID, RevNr,UserID, Date)

Select TH.DocumentID, RevNr, TH.UserID, Convert(NVARCHAR(10),date, 103)

from TransitionHistory TH

INNER JOIN Documents D ON D.DocumentID = TH.DocumentID

INNER JOIN Status S on D.CurrentStatusID = S.StatusID

WHERE

S.WorkflowID > 1 -- only from a valid workflow

AND ObjectTypeID = 1 -- no items, virtual components, cut lists or BOMs

create nonclustered index IX_TD on #TD (DocumentID,Date, UserID)

--We get details about deleted files from table HistoryDelete

Create Table #DD (DocumentID int,UserID int, Date NVARCHAR(10))

Insert Into #DD (DocumentID,UserID, Date)

Select DocumentID, HD.UserID, Convert(NVARCHAR(10),Time, 103) from HistoryDelete HD where HD.DocumentID <> 0-- Exclude folders

create nonclustered index IX_DD on #DD (DocumentID,Date, UserID)

--Next is the actual select statement for the query where we reorder by month all the collected data from temporary tables

SELECT

MD.Date 'Day',

(SELECT COUNT(MD1.DocumentID) FROM #MD MD1 WHERE MD1.date = MD.Date and MD1.RevNr = 1) as 'NewFiles',

(SELECT COUNT(MD1.DocumentID) FROM #MD MD1 WHERE MD1.date = MD.Date) as 'NewFileVersions',

(SELECT COUNT(XD.DocumentID) FROM #XD XD WHERE XD.date = MD.Date) as 'NewFileRreference',

(SELECT COUNT(TD.DocumentID) FROM #TD TD WHERE TD.date = MD.Date) as 'TransitionOperations',

(SELECT COUNT(DD.DocumentID) FROM #DD DD WHERE DD.date = MD.Date) as 'DeleteOperations'

FROM #MD MD

GROUP BY MD.Date

ORDER BY RIGHT(MD.Date,4),SUBSTRING(MD.Date,4,2),LEFT(MD.Date,2)

Drop Table #MD Drop Table #XD Drop Table #TD Drop Table #DD

 

Example of Query 4-12 partial results

 

v) Reports for vault administration

The SOLIDWORKS PDM Administration tool provides built-in functionality to modify and view vault settings. However, in some cases it is useful to get a complete overview of a particular setting type. For example, instead of opening each data card to see what control options are in use, you can create a list of all cards and the variables details used in the cards (see Query 4-13), or get more details about tasks that ran (see Query 4-19).

  • Cards – During implementations or when troubleshooting, retrieving all of the data card details can help identify redundant data or wrong settings. The main tables to use for this purpose are Cards, CardControls, and Variable.

Query 4-13

--Query example to list all cards and variables used on them

Select C.CardName,

ISNULL(P.Path,'') as [Saved under path],

V.VariableName,

--next column is checking variable settings that are used in card controls

CASE V.FlagUnique WHEN 1 then 'Yes' ELSE '' END as [Unique],

CASE V.FlagMandatory WHEN 1 then 'Yes' ELSE '' END as [Mandatory],

CASE V.FlagFreeUpdateAllVersion WHEN 1 then 'Yes' ELSE '' END as [FreeUpdateAllVersion],

CASE V.FlagFreeUpdateLatestVersion WHEN 1 then 'Yes' ELSE '' END as [FreeUpdateLatestVersion],

CASE C.CardType

WHEN 0 then 'File Card'

WHEN 1 then 'Folder Card'

WHEN 2 then 'Template Card'

WHEN 3 then 'Search Card'

WHEN 4 then 'Item Card'

END as [Card type],

--next we list extension(s) defined for a datacard

Extensions = COALESCE(SUBSTRING((SELECT ( ', ' + CE.Extension )

FROM CardExtensions CE

WHERE CE.CardID = CC.CardID

ORDER BY

CE.Extension

FOR XML PATH( '' )

), 3, 1000 ) ,''),

--next we list card controls settings

CASE (CC.CheckFlag & 4) WHEN 4 then 'Yes' ELSE '' END as [Read-only],

CASE CC.Preview WHEN 1 then 'Yes' ELSE '' END as [Show in Explorer],

CASE (CC.CheckFlag & 16) WHEN 16 then 'Yes' ELSE '' END as [Multiline],

CASE CC.WritesAllCfgs WHEN 1 then 'Yes' ELSE '' END as [Update all configs],

CASE CC.DefaultValueType

WHEN 0 then ''

WHEN 1 then 'Text value (' + CAST(CC.DefaultValue as nVarChar(255)) + ')'

WHEN 2 then 'Special value'

WHEN 3 then 'Serial (' + (Select Name from SerialNumbers where SerialNumberID = CC.SerialNumberID) + ')'

WHEN 4 then 'Folder variable value (' + (Select VariableName from Variable where VariableID = CC.FolderCardVariableID) + ')'

WHEN 5 then 'File variable value (' + (Select VariableName from Variable where VariableID = CC.FolderCardVariableID) + ')'

ELSE ''

END as [Default value],

CASE (CC.CheckFlag & 64) WHEN 64 then 'Yes' ELSE '' END as [Default overwrites]

From CardControls CC

Inner Join Cards C on C.CardID = CC.CardID

Inner Join Projects P on C.ProjectID = P.ProjectID

Inner Join Variable V on CC.VariableID = V.VariableID

-- next we remove non-valid entries

Where CC.VariableID > 1

and V.VariableType < 0xffff

-- we order result by card type, card name, variable and card path

Order by C.CardType, C.CardName, V.VariableName, P.Path

 

Example of Query 4-13 partial results

 

  • Variables – The main information is in the Variable and Attribute tables. The following example is similar to Query 4-13 because of the variable settings columns they have in common.

Query 4-14

--Query to list all variable mappings details in a file vault

Select V.VariableName as [Variable Name],

CASE V.VariableType

WHEN '1' THEN 'Text'

WHEN '2' THEN 'Integer'

WHEN '3' THEN 'Decimal Number'

WHEN '4' THEN 'Yes or No'

WHEN '5' THEN 'Date'

END as [Type],

--next we get columns for variable settings

CASE V.FlagUnique WHEN 1 then 'Yes' ELSE '' END as [Unique],

CASE V.FlagMandatory WHEN 1 then 'Yes' ELSE '' END as [Mandatory],

CASE V.FlagFreeUpdateAllVersion WHEN 1 then 'Yes' ELSE '' END as [FreeUpdateAllVersion],

CASE V.FlagFreeUpdateLatestVersion WHEN 1 then 'Yes' ELSE '' END as [FreeUpdateLatestVersion],

--next we get mapping details

CASE WHEN A.BlockName IS NULL THEN '' ELSE A.BlockName END as [Block],

CASE WHEN A.AttributeName IS NULL THEN '' ELSE A.AttributeName END as [Attribute],

--we list file extensions defined per each mapping

Extensions = COALESCE(SUBSTRING((SELECT ( ', ' + FE.Extension )

FROM AttributeExtension AE, FileExtension FE

WHERE A.AttributeID = AE.AttributeID

and AE.ExtensionID = FE.ExtensionID

ORDER BY AE.AttributeID, FE.Extension

FOR XML PATH( '' )

), 3, 1000 ) ,'')

From Variable V

Left Join Attribute A on V.VariableID = A.VariableID

Where V.VariableType IN (1,2,3,4,5)

Order By V.VariableName

 

Example of Query 4-14 partial results

 

  • File Types ­– Additional information about file types is available in the FileExtension table, which stores information about the extension ID, name, and duplicate filename settings. The next example generates a list of duplicate filename settings in a vault. You can use this query other reports that require this type of information. Note that a programming method called "Bitflag" is used to store the duplicate settings. A bitflag is an integer value that is a power of 2, which allows storing multiple settings in one single value instead of in separate values.

Query 4-15

--Query to get vault duplicate filename settings

Select

Case

when Flags & 8 = 8 then 'Do not allow duplicate file names in this file vault'

when Flags = 0 then 'Allow duplicate file names in this file vault'

else 'Do not allow duplicate file names is enabled for: ' +

COALESCE (STUFF((Select ', '+Extension From FileExtension where Flags & 8 = 8 and Extension not in ( N'*',N' ', N'.')

FOR XML PATH('')

), 1,1,'')

,'')

END as 'Duplicate File Name Status'

from FileExtension where Extension = N'*'

 

Example of Query 4-15 results compared to duplicate file name settings.

 

  • User/Group permissions – These settings are in tables such as UserRights, GroupRights, UserStatusRights, or GroupStatusRights. In this report, the user and group permissions use bitflags to store multiple permission settings in one value as shown in Query 4-15.

Query 4-16

--Query to list group permissions per workflow state

SELECT

Workflows.Name + '.' + Status.Name as [State Name],

Groups.Groupname,

--next columns are listing the state permissions per group

CASE (GroupStatusRights.Type & 8) WHEN 8 then 'Yes' ELSE '' END as [Add/Rename],

CASE (GroupStatusRights.Type & 2) WHEN 2 then 'Yes' ELSE '' END as [Check Out],

CASE (GroupStatusRights.Type & 4) WHEN 4 then 'Yes' ELSE '' END as [Delete],

CASE (GroupStatusRights.Type & 256) WHEN 256 then 'Yes' ELSE '' END as [Destroy],

CASE (GroupStatusRights.Type & 32) WHEN 32 then 'Yes' ELSE '' END as [Increment Revision],

CASE (GroupStatusRights.Type & 16384) WHEN 16384 then 'Yes' ELSE '' END as [Permit Group Level-access],

CASE (GroupStatusRights.Type & 1) WHEN 1 then 'Yes' ELSE '' END as [Read File],

CASE (GroupStatusRights.Type & 16) WHEN 16 then 'Yes' ELSE '' END as [Share],

GroupStatusRights.GroupID,

Status.StatusID

FROM GroupStatusRights

INNER JOIN Status ON GroupStatusRights.StatusID = Status.StatusID

INNER JOIN Groups ON GroupStatusRights.GroupID = Groups.GroupID

INNER JOIN Workflows ON Workflows.WorkflowID = Status.WorkflowID

WHERE Status.Enabled = 1 AND Status.StatusType = 3 AND GroupStatusRights.Type <> 0 AND Status.Name IS NOT NULL

Order by [State Name], Groups.Groupname

 

Example of Query 4-16 partial results

 

  • Notifications – You can use tables such as Notifications, NotificationProjects, or NotificationUsers to retrieve information about configured notifications. The Messages table stores notifications that are already sent (if using the database message system). Query 5-1 provides an example for the latter case.

Query 4-17

--Query example to list user specific folder notifications

Select

'Folder Notification' as [Type],

P.Path AS [Path],

U.UserName as [User Name],

CASE N.FromStatus WHEN 1 THEN '' WHEN 2 THEN 'Any State' ELSE W2.Name + '.' + S2.Name END as [From State],

CASE N.ToStatus WHEN 1 THEN '' WHEN 2 THEN 'Any State' ELSE W1.Name + '.' + S1.Name END as [To State],

--Next we specify which type of notification is enabled

CASE N.ProjectDeadline WHEN 1 THEN 'Yes' ELSE '' END as [On Project Deadline?],

CASE N.OnlyModifier WHEN 1 then 'Yes' ELSE '' END as [Only to user last changing state?],

CASE N.OnlyCreator WHEN 1 then 'Yes' ELSE '' END as [Only to file creator?],

CASE N.IfLocked WHEN 1 then 'Yes' ELSE '' END as [On check out?],

CASE N.IfUnlocked WHEN 1 then 'Yes' ELSE '' END as [On check in?],

CASE N.IfAdded WHEN 1 THEN 'Yes' ELSE '' END as [On add?]

FROM NotificationProjects NP

Inner join Notifications N on NP.NotificationID = N.NotificationID

Inner join Users U on N.RecordOwner = U.UserID

--Next we join tables Status and Workflow twice to get details for source and target workflow states

Inner join Status S1 on N.ToStatus = S1.StatusID

Inner join Status S2 on N.FromStatus = S2.StatusID

Inner join Workflows W1 on S1.WorkflowID = W1.WorkflowID

Inner join Workflows W2 on S2.WorkflowID = W2.WorkflowID

Inner join Projects P on P.ProjectID = NP.ProjectID

where N.SystemNot = 0

Order by [Type], [Path], [User Name]

 

Example of Query 4-17 results

 

  • Workflows – The Workflow, Status, Transitions, TransitionCondition, and TransitionAction tables store information about the state and transition settings for a workflow.

Query 4-18

--Query to get workflows details

Select

--First columns are used to retrieve general information about workflow type, transition and state names

W.Name As 'Workflow Name'

, T.TransitionID as 'ID(Transition)'-- ID type columns are optional and used for troubleshooting mainly

, T.Name As 'Transition Name'

, T.Source As 'ID(Source)'

, (Select S.Name from Status S Where T.Source = S.StatusID) As [Source State],

T.Destination As 'ID(Destination)'

, (Select S.Name from Status S Where T.Destination = S.StatusID) As [Destionation State]

, Case T.Type

when 0 then 'Normal'

when 1 then 'Automatic'

when 2 then 'Parallel'

Else 'Unkown' End As 'Transition Type'

--next we get transition condition settings

, CASE CondType WHEN 1 THEN 'FilePath' WHEN 2 THEN 'Variable' WHEN 3 THEN 'Revision' WHEN 4 THEN 'Category' WHEN 5 THEN 'Object type' ELSE 'Unknow type' END AS 'Condition Type'

, CASE TC.VariableName WHEN '' THEN 'N/D' ELSE TC.VariableName END AS 'Conditions.VariableName'

, CASE TC.Configuration WHEN '' THEN 'N/D' ELSE TC.Configuration END AS 'Conditions.Configuration'

, CAST(TC.Argument AS NVARCHAR(MAX))as 'Conditions.Argument'

--next we retrieve transition action settings

, TA.TransitionActionID

, CASE TA.Type

WHEN 1 THEN 'Set Variable'

WHEN 2 THEN 'Send Mail'

WHEN 3 THEN 'Increment Revision'

WHEN 5 THEN 'Export data to XML'

WHEN 6 THEN 'Import data to XML'

WHEN 7 THEN 'Execute Command'

WHEN 8 THEN 'Execute Task'

ELSE 'Unknow type' END AS 'Action Type'

, TA.Description as 'Action Description'

, COALESCE(V.VariableName, 'N/D') as 'Action.VariableName'

, CASE TA.Configuration when '' Then 'N/D' ELSE TA.Configuration End As 'Action.Configuration'

, CASE TA.VarData WHEN '' THEN 'N/D' ELSE TA.VarData END AS 'Action.VarData'

, COALESCE((select Username from Users U where U.UserID = TA.UserID), 'N/D') AS 'Action.Send mail To'

, CASE CAST(TA.Message AS NVARCHAR(MAX))

WHEN '' THEN 'N/D'

ELSE CAST(TA.Message AS NVARCHAR(MAX)) END AS 'Action Argument'

, CASE COALESCE(TA.TaskID,0)

WHEN 0 then 'N/D' ELSE CAST(TA.TASKID AS NVARCHAR(MAX)) END AS 'Action.TaskID'

, CASE TA.Flags

when 1 THEN 'Run for files'

when 2 then 'Run for named BOMs'

when 3 then 'Run for named BOMs and files'

when 4 then 'Run for items'

when 5 then 'Run for items and files'

when 6 then 'Run for named BOMs and items'

when 7 then 'Run for items, named BOMs and files'

ELSE 'Unknown setting'

End As 'Action settings'

, COALESCE(FE.Extension,'N/D') as 'Action.Run for files'--using LEFT OUTER JOINS may lead to get unwanted NULLs as shown in query results for column 'Action Description'; NULLs can be avoided using COALESCE or ISNULL functions as shown for column 'Action.Run for files’

FROM Workflows W

INNER JOIN Transitions T on T.WorkflowID= W.WorkflowID OR W.InitialTransitionID = T.TransitionID

LEFT OUTER JOIN TransitionCondition TC on TC.TransitionID = T.TransitionID

LEFT OUTER JOIN TransitionAction TA on TA.TransitionID = T.TransitionID

LEFT OUTER JOIN Variable V ON TA.VariableID=V.VariableID

LEFT OUTER JOIN TransitionActionExtension TAE on TAE.TransitionActionID = TA.TransitionActionID

LEFT OUTER JOIN FileExtension FE on TAE.ExtensionID= FE.ExtensionID

where W.WorkflowID <> 1 and T.Enabled <> 0 -- we ignore non valid workflows and deleted transitions

Order By W.WorkflowID, T.TransitionID, TA.TransitionActionID

 

Example of Query 4-18 partial results

 

  • Tasks – The TaskInstances table is the main table that stores details about tasks. Because it is possible to limit the number of processed tasks from SOLIDWORKS PDM Administration, the query results for this report do not reflect the actual number of tasks.

Query 4-19

--Query example to get details for pending and processed tasks

Select T.TaskName,

Case

when TI.TaskStatus = 1 then 'Waiting for host'

when TI.TaskStatus = 2 then 'Starting up'

when TI.TaskStatus = 3 then 'In process'

when TI.TaskStatus = 4 then 'Retrying'

when TI.TaskStatus = 5 then 'Suspended'

when TI.TaskStatus = 6 then 'Cancelling'

when TI.TaskStatus = 7 then 'OK'

when TI.TaskStatus = 8 then 'Cancelled'

when TI.TaskStatus = 9 then 'Failed'

when TI.TaskStatus = 10 then 'Suspending'

when TI.TaskStatus = 11 then 'Resuming'

End AS [Task status],

DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),TI.StartTime) AS [StartTime],

DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),TI.EndTime) AS [EndTime],

DATEDIFF(second, TI.StartTime,TI.EndTime) as [Duration(sec)],

ISNULL(TI.CustomErrMsg,'') as [Error Message],

H.HostName as [Executed From (Task Host)],

U.Username as [Executed By],

H1.HostName as [Started From],

U1.Username as [Started By]

-- Some details about source files are stored in Task Variables table in xml format; next we retrieve DocumentID from these xml formats and use them in a sub-query with Documents table to get the filename

,(Select Filename from Documents where DocumentID = CAST(TV.ValueText AS XML).value('(sels/sel/doc_id)[1]', 'integer') ) as [Source file name]

--next we extract from the xml format entries the source version and path

,CAST(TV.ValueText AS XML).value('(sels/sel/ver)[1]', 'integer') as [Source Version]

,CAST(TV.ValueText AS XML).value('(sels/sel/path)[1]', 'nvarchar(max)') as [Path]

From TaskInstances TI

Inner Join Hosts H on H.HostID = TI.HostID

Inner Join Hosts H1 on H1.HostID = TI.InitHostID

Inner Join Tasks T on T.TaskID = TI.TaskID

Inner Join TaskVariables TV on TV.TaskInstanceID = TI.TaskInstanceID

Inner Join Users U on U.UserID = TI.StatusUserID

Inner Join Users U1 on U1.UserID = TI.InitUserID

Where TV.VariableName = '__selection_list'

--next we order tasks by type and by end time

Order by TI.TaskStatus, TI.EndTime

 

Example of Query 4-19 results

 

  • License – For SOLIDWORKS Enterprise PDM 2015 or older versions, the license usage data is in the ConisioMasterDb database and not in the PDM databases. The main table is LoggedInUsers.

Query 4-20

-- Query to list all Enterprise PDM licenses in use with logged in session details;

-- Important Note: Valid only for SOLIDWORKS Enterprise PDM 2015 or older. For newer versions of SOLIDWORKS PDM it is recommended to use SOLIDWORKS® Network License (SNL) applications

WITH CADEDITOR (Computer,Username,Vault, LoggedInTime) AS

(select liu.hostname , liu.username ,liu.DatabaseName, DATEDIFF(MINUTE, liu.login_time, Getdate()) from ConisioMasterDb.dbo.LoggedInUsers liu

Inner Join ConisioMasterDb.dbo.LoggedInUsers gliu on liu.spid = gliu.spid

Inner Join master..sysprocesses sp on liu.spid = sp.spid

where

upper(sp.program_name) like upper('%EdmServ%') -- check only EdmServer.exe

and gliu.ProgramType=0 --only CAD Editor license type

)

--Next a union between 2 select statements will be made; first select will return the total number of CAD Editor licenses used

select 'TOTAL (CAD Editor)' as License, ' ' as 'Vault', CONVERT(NVARCHAR(MAX),(Select COUNT(*) from (select distinct Computer,Username from CADEDITOR) as Temp1)) as 'Used licences' , CONVERT(NVARCHAR(MAX),COUNT(DISTINCT Computer)) as 'Computer(s)', CONVERT(NVARCHAR(MAX),COUNT(Distinct Username)) as 'User name(s)', ISNULL(SUM(LoggedInTime),0) as 'Logged in time (min)' FROM CADEDITOR

UNION ALL

--The next second select statement is to get logged in details for each user and vault connection

select '>> CAD Editor' as License, Vault, ' ' 'Used licence(s)', Computer as 'Computer(s)', Username as 'User name(s)', LoggedInTime as 'Logged in time(min)' FROM CADEDITOR

 

Example of Query 4-20 results

 

c) Troubleshooting

When troubleshooting SOLIDWORKS PDM issues, it can be very helpful to use queries to find the actual cause of a problem. This is especially true if there is also a SOLIDWORKS PDM client log error that points to a specific ArchiveID or DocumentID or contains a specific SQL error. When troubleshooting complex issues such as SQL performance, it is also a requirement to query system databases or use Dynamic Management Views.

Troubleshooting often requires that you write new queries, depending on the context of the problem. For this type of task, it is important to get the required results. It does not really matter how you write the query, unless the query causes performance issues in the production environment. When executing queries against live production databases, it sometimes happens that queried tables are locked by ongoing transactions, and fail to return any results in a timely manner. In such cases, it is possible to use the WITH (NOLOCK) hint in select statements (see Query 4-21) causing the select to not issue shared locks and not honor exclusive locks. This allows so-called “dirty reads”, which is data that is in process of modification at the time of select statement execution.

i) SOLIDWORKS PDM main commands

When checking the content of a table, a common approach is to write “Select * from” queries against tables that receive modifications as shown in Query 4-21. However, sometimes when querying multiple tables, it is good to join them and retrieve additional columns such as Filename or UserID for the person who last modified the document. Query 4-22 shows an example of this.

Query 4-21

--basic query example for troubleshooting

Select * from Documents

Select * from Revisions

Select * from VariableValue

Select * from VariableValue WITH (NOLOCK) --accept dirty reads

Example of Query 4-21 results

 

 

 

Query 4-22

--Query example to get VariableValue columns along with Filename and UserID details

Select D.Filename, R.UserID, VV.* -- using a table alias followed by * (e.g. R.* or VV.*) will return all columns for that table

From Documents D

Inner Join Revisions R On D.DocumentID = R.DocumentID

Inner Join VariableValue VV on VV.DocumentID = R.DocumentID and VV.RevisionNo= R.RevNr

Order by D.DocumentID DESC -- when testing an issue by adding files to reproduce a problem it can be very useful to order descending by DocumentID to show only the last added entries in tables

 

Example of Query 4-22 results

 

When troubleshooting SOLIDWORKS PDM commands, it is very important to understand what tables receive updates during each operation. For this reason, the following list shows common tables that are updated when applying specific SOLIDWORKS PDM operations on a single file. Be aware that there could be updates to many other tables depending on the command context. For example, when dealing with an assembly that has references, there are updates to Xref type tables also. There may also be updates to the ColdStorage type tables if the file was cold stored.

List of operations / common tables updated:

  • Add: ArchiveServerStored (if replication is enabled), Documents, DocumentRevisionConfiguration, DocumentsInProjects, Revisions, TransitionHistory, VariableValue
  • Check In: Documents, Revisions, VariableValue
  • Check Out: Documents
  • Change State: RevGenCounters, Revisions, TransitionHistory, UserRevCounters, UserRevs, VariableValue
  • Move: Documents, DocumentsInProjects, HistoryMove
  • Rename: Documents, HistoryRename
  • Delete: Documents, HistoryDelete
  • Rollback: HistoryRollback, DocumentRevisionConfiguration, Revisions, VariableValue

ii) Archive Server Service

Besides sending and receiving files between the file vault views and the file vault archive, the Archive Server Service is also responsible for Replication (see Query 4-23) and Cold Storage (see Query 4-24).

Query 4-23

--Basic query to show files versions that are not replicated on one or all servers

Select

ASD.* -- Statement type "

.*" will return all table columns

,D.Filename, R.RevNr, U.Username as 'Checked in by'

, DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),R.Date) as 'Check in date', A.ArchiveServerName as 'Replicated on Server'

--next we extract archive folder path that is in hexadecimal format, note that Query 4-3 has additional information about this method

, '\\' + RIGHT(CONVERT (NVARCHAR(MAX), CONVERT(Binary(4), D.DocumentID), 2),1) + '\\' + CONVERT (NVARCHAR(MAX), CONVERT(Binary(4), D.DocumentID), 2) as 'Archive Folder'

,P.Path as 'Vault Folder '

--main replication data is in table ArchiveServerStored

From ArchiveServerStored ASD

--next inner joins are made mainly to get file path and details for each file version

Inner Join Revisions R on ASD.VersionUID = R.VersionUID

Inner Join ArchiveServers A on A.ArchiveServerID = ASD.ArchiveServerID

Inner Join Documents D on R.DocumentID = D.DocumentID

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

Inner Join Users U on R.UserID = U.UserID

--we add ColdStorage table in case we want to skip versions that were cold stored

Left Outer Join ColdStorage CS on CS.DocumentID = R.DocumentID and CS.RevNr = R.RevNr

WHERE

D.Filename like '%' --Optional filter for filename

and A.ArchiveServerName like '%' -- optional filter to display only non-replicated versions for one server

and CS.Completed is null -- here we remove cold stored versions, optional we can comment this line to include them

-- next filter is to get file versions that are not replicated on all servers, we check if a version occurrence is smaller than the total number of archive servers which means is not on all servers; note that if we use “=” instead of "<" operator will return file versions replicated on all servers */

and (Select Count(ASD2.VersionUID) from ArchiveServerStored ASD2 WHERE R.VersionUID = ASD2.VersionUID) < (Select Count(A2.ArchiveServerID) from ArchiveServers A2)

Order by P.Path + D.Filename, R.RevNr

 

Example of Query 4-23 results

 

Query 4-24

--Show file versions that are cold stored

SELECT D.DocumentID,

P.Path + D.Filename AS [FilePath],

C.RevNr as [Version],

--next we check the type of cold storage

CASE CM.StoreAction WHEN 1 then 'Deleted' ELSE 'Moved' END as [Action],

CM.MediaName as [Media Name],

CM.DestFolderPath as [Cold store folder],

CAST( D.DocumentID as binary(4)) as [Archive name],

DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),C.Completed) AS [Cold store completed on],

A.ArchiveServerName

FROM Documents D

Inner Join ColdStorage C ON D.DocumentID = C.DocumentID

Inner Join DocumentsInProjects DIP ON D.DocumentID = DIP.DocumentID

Inner Join Projects P ON DIP.ProjectID = P.ProjectID

Inner Join ColdStorageMedia CM on C.MediaID = CM.MediaID

Inner Join ArchiveServers A on A.ArchiveServerID = CM.ArchiveServerID

WHERE D.Deleted = 0 --skip deleted files

ORDER BY [FilePath]

 

Example of Query 4-24 results

 

iii) SOLIDWORKS PDM Database Server Service

The main tables processed by the SOLIDWORKS PDM Database Server Service are DocumentActionInfo, BroadcastEvents, and ExportBatches. The entries from these tables are temporary and are normally empty outside of working hours if the service is running. The next example queries the type of events, pending notifications, and export batches (XML exports) that are in queue for the SOLIDWORKS PDM Server Service to process.

Query 4-25

-- Query examples showing totals for events\\pending notifications\\export batches from BroadcastEvent, DocumentActionInfo and ExportBatches tables; the second select statement lists pending notification details; note that if this query is run outside working hours it may not return any result.

SELECT

CASE EventType

WHEN 1 THEN 'Mail event(s)'

WHEN 2 THEN 'Project event(s)'

WHEN 3 THEN 'Document event(s)'

WHEN 7 THEN 'Replication event(s)'

WHEN 8 THEN 'Cold Storage event(s)'

ELSE 'Unknown event'

END AS 'Events\\Pending notifications\\Export batches'

,count(1) AS 'Total'

FROM BroadcastEvent

GROUP BY EventType

--next we add totals for pending notifications and export batches

UNION

SELECT 'Pending notification(s)', (select count(DocumentID) from DocumentActionInfo)

UNION

SELECT 'Pending export batches', (select count(*) from ExportBatches)

--next query example retrieves various details for pending notification such as user, date, notification type, transition

SELECT

REPLACE(REPLACE( NMT.Subject, '_ConisioFileName_', D.Filename) ,'_ConisioProjectName_', P.Name ) as 'Pending Notification type'

, DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),DAI.Time) As 'Date and Time'

, COALESCE(DAI.FilePath, 'N/D') as 'File path:'

, COALESCE(U.UserName, 'N/D') 'By user:'

, P.Name as 'Folder Name:'

, COALESCE(D.Filename, 'N/D') as 'File Name:'

, DAI.VersionNo as 'Document version number:'

, COALESCE(T.Name, 'N/D') as 'Workflow or Transition Name:'

, COALESCE(TH.Comment, 'N/D') as 'Transition Comment:'

FROM DocumentActionInfo DAI

Inner Join NotificationMsgTemplate NMT on NMT.ActionType = DAI.ActionType

Inner Join Users U on DAI.UserID = U.UserID

Inner Join TransitionHistory TH on DAI.DocumentID = TH.DocumentID and DAI.TransitionNo = TH.TransitionNr

Inner Join Transitions T on TH.TransitionID = T.TransitionID

Inner Join Projects P on P.ProjectID = DAI.ProjectID

Inner Join Documents D on D.documentID = DAI.DocumentID

Order By DAI.Time

 

Example of Query 4-25 results

 

iv) EXALEAD® OnePart

In EXALEAD OnePart Administration Console under Security Sources we can test user authentication for a security source with type SOLIDWORKS PDM. The results of the tests will return several tokens that may have the following format:

“epdm:sw:g::f::s:“

EXALEAD OnePart test user authentication example

 

For example, for security token result “epdm:pdmtest:g:2:f:1:s:10” can be interpreted as follow:

  • value g:2 means tested user has permissions from users group with ID = 2
  • value f:1 means tested user has permissions for vault folder with ID = 1
  • value s:10 means tested user has permissions for workflow state with ID = 10
  • the actual names of the above group, folder an workflow state can be retrieved by running Query 4-26

Query 4-26

--Query that returns specific Group name, the folder name and workflow state name

Select Groupname from Groups Where GroupID = 2

Select Name from Projects where ProjectID =1

Select Name from Status where StatusID = 10

 

Example of Query 4-26 results

 

v) Performance

For SOLIDWORKS PDM environments, database maintenance is very important to ensure optimum performance. Therefore, it is a strong recommendation for vaults that have a significant workload to use the reorganize or rebuild index tasks. Query 4-27 returns of list of defragmented indexes. This query is useful to determine if there are tasks that you should schedule to run more often.

Another type of query is to check the SQL Server settings. Query 4-28 returns a basic list with SQL Server settings that might affect performance. This query is useful to determine if the SOLIDWORKS PDM server installation does not take into account additional recommendations from documents such as “SolidPractices: SQL Server Performance” and “SolidPractices: Network and Hardware”.

  • Maintenance tasks – This type of query requires use of system tables such as sys.tables or sys.indexes.

Query 4-27

-- Query listing the index details if maintenance index rebuild or reorganize is required for a file vault

--First we create a common table expression to gather index data

With IndexStatistics as

(SELECT

CASE

--Next we check the type of maintenance task but only for non-heap types and indexes with more than 1000 pages

WHEN istats.avg_fragmentation_in_percent > 30 and istats.index_type_desc <> 'HEAP' and istats.page_count > 1000 THEN 'REBUILD'

WHEN istats.avg_fragmentation_in_percent > 5 and istats.avg_fragmentation_in_percent < 30 and istats.index_type_desc <> 'HEAP'

and istats.page_count > 1000 THEN 'REORGANIZE'

ELSE 'NO ACTION'

END AS 'Requires rebuild or reorder?'

, i.Name as IndexName

, istats.index_type_desc AS IndexType

, istats.page_count as 'Page Number'

--In next column we use a string function in order to add percentage sign

, STR(istats.avg_fragmentation_in_percent,5,2)+ ' %' as 'Average index fragmentation'

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'Limited') istats on i.index_id = istats.index_id and i.object_id= istats.object_id

--Next we clean the results by removing system data that is not relevant

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

AND istats.alloc_unit_type_desc = 'IN_ROW_DATA')

--Next is the actual select statement where we call the previously created common table expression

Select * from IndexStatistics

Where [Requires rebuild or reorder?] <> 'NO ACTION' -- skip from results database tables that do not require rebuild or reorder; if no results are returned means no maintenance action is required

Order by [Average index fragmentation] Desc

 

Example of Query 4-27 results

 

  • SQL Server settings – You can retrieve this information from tables such as sys.configurations or sys.database_files.

Query 4-28

/* Query that returns SQL Server and database settings that may affect performance; has to be executed against SOLIDWORKS PDM database.

This query has 2 select statements, in first we gather some of the SQL Server settings and in the second tempDB and production SOLIDWORKS PDM database information */

Select

(Select @@Version) As 'Server Version Details'

, (SELECT value FROM sys.configurations C WHERE description = 'Maximum size of server memory (MB)') as 'Maximum size of server memory (MB)'

, (SELECT value FROM sys.configurations C WHERE description = 'Minimum size of server memory (MB)') as 'Minimum size of server memory (MB)'

--Next we gather database and tempdb files settings and because they may use different collations they will have to be temporary inserted in a variable table named @DBData

Declare @DT([Database Name] NVARCHAR(260), [File Size in MB] Int, [Autogrowth status] NVARCHAR(260), [Growth value] NVARCHAR(260), [File path] NVARCHAR(260), [Recovery Model] NVARCHAR(260))

--in the next insert we gather some of the SOLIDWORKS PDM database settings

Insert into @DBData([Database Name], [File Size in MB], [Autogrowth status], [Growth value], [File path], [Recovery Model] )

SELECT S.name AS FileName

, S.size*1.0/128 AS FileSizeinMB

, 'Autogrowth status' =

CASE max_size

WHEN 0 THEN 'Autogrowth is off.'

WHEN -1 THEN 'Autogrowth is on.'

ELSE 'Log file will grow to a maximum size of 2 TB.' END

, 'Growth Value'=

CASE

WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0 AND is_percent_growth = 0 THEN STR(S.growth*1.0/128,6) + ' MB'

ELSE ' ' + CAST(S.growth as NVARCHAR)+' %' END

, S.physical_name -- file path

, (SELECT recovery_model_desc FROM sys.databases where sys.databases.name = DB_Name())

from sys.database_files as S

--Next insert if for tempDB database settings

Insert into @DBData([Database Name], [File Size in MB], [Autogrowth status], [Growth value], [File path], [Recovery Model] )

SELECT name AS FileName

, size*1.0/128 AS FileSizeinMB

, 'Autogrowth status' =

CASE max_size

WHEN 0 THEN 'Autogrowth is off.'

WHEN -1 THEN 'Autogrowth is on.'

ELSE 'Log file will grow to a maximum size of 2 TB.' END

, 'Growth Value'=

CASE

WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0 AND is_percent_growth = 0 THEN STR(growth*1.0/128,6) + ' MB'

ELSE ' ' + CAST(growth as NVARCHAR)+' %' END

, physical_name -- File path

, (SELECT recovery_model_desc FROM sys.databases where sys.databases.name = 'tempDB') --recovery model

FROM tempDB.sys.database_files

--Below we return all the content of variable table @DBData

Select * from @DBData

Order By [Database Name]

 

Example of Query 4-28 results

 

This query is based on recommendations from Knowledge Base articles and SolidPractice documents. For example, you can use Server Version Details to determine if a supported edition of SQL Server is in use. The maximum server memory should be the total memory minus 2 GB reserved for the operating system. It is also a recommendation that this memory be greater than the size of the production database (such as TSQLDemo.mdf). It is a recommendation to leave the minimum server memory as 0 (zero).

Checking the vault database settings in the second result set reveals that the growth value is too small. On the other hand, tempdb is configured correctly and has an initial size of 2.5 GB, which should prevent autogrowth or autoshrink that are required only for unpredicted cases. According to SOLIDWORKS PDM recommendations, the recovery model is set to simple.

5) Special Methodologies

a) Handling query results

  • Totals - In some cases, it is required to show totals, cumulative totals, or a percentage in result columns. Query 4-11 shows such an example, where the totals are generated for the vault file size, and then grouped per month. Another option to get totals is grouping with rollup. This gives the advantage of showing the total values in rows and not columns. For example, Query 5-1 lists all messages that are stored in the Messages table and groups them per user. Using the Group by option with rollup calculates totals for each user, and then for each messages type and user. The rows containing such totals receive a NULL value. However, you can replace these NULLs as shown in the next example.

Query 5-1

--Query to get the number of all database notifications sent and not deleted

With A([User(s)], [Notifications for/type], Qty) As

(Select

--the first column will return null due to 'with rollup' option therefore it will be replaced

CASE WHEN Username is null and M.Type is null then isnull(Username,' All users') Else Username End AS 'Totals Users',

--the second column will return notifications type, but due to 'with rollup' option we will get nulls for total user notifications therefore it will be replaced

CASE

WHEN M.Type = 1 THEN '>>Text Messages'

WHEN M.Type = 2 THEN '>>File Notifications'

WHEN M.Type = 3 THEN '>>Folder Notifications'

WHEN M.Type = 4 THEN '>>External Notifications'

WHEN M.Type = 5 THEN '>>Item Notifications'

WHEN M.Type = 6 THEN '>>Task Notifications'

WHEN M.Type IS NULL Then isnull(convert(NVARCHAR,M.Type),' All notifications for ') + COALESCE(U.Username, 'all users')

ELSE 'Unknown Notifications'

END AS 'Notification Type',

--the third column will contain the total notifications for all users, per user and per notification type

COUNT(MessageID) Over (Partition by Username,M.Type) 'Quantity'

From Messages M

Inner Join Users U on U.UserID = M.Recipient

Where U.Username not like '%delete%'

Group By MessageID, Username, M.Type with rollup)

Select Distinct [User(s)],[Notifications for/type] , Qty from A

Order By 1,2 -- is not mandatory to use column names, we can use the numbers from the order they are written

 

Example of Query 5-1 results

 

Another aspect when getting totals, averages, or quantities is that they add more cost for query execution. For example, Query 4-11 experiences a significant performance drop when executed against large vaults. In these cases, it is important to limit these types of columns unless they are necessary or ensure that the query is optimized as much as possible. As a last resort, you can use temporary tables with indexes as shown in Query 4-12.

 

  • Multiple results– In some cases, it might be useful to retrieve a list instead of single value columns. For example, Query 4-4 uses this method to get a list of server names in which the file is replicated, and Query 4-15 returns a list of file extensions that do not allow duplicates. To achieve this result, you can create a subquery and use XML transformation (such as FOR XML PATH) to transform the results in a list. The last modification is to remove the comma from the beginning of the list by using either the string function STUFF or SUBSTRING.

Query 5-2

--Query to retrieve group members

select G.Groupname as 'Group Name',

-- in the next column we use a sub query to get a list of all users within a group

COALESCE(STUFF((SELECT ', '+U.Username From Users U --we use ", " to get a list

Inner Join GroupMembers GM on U.UserID = GM.UserID -- join to get group members

Inner Join Groups G1 on G1.GroupID = GM.GroupID -- join to get group details

Where U.Enabled = 1 and U.UserID > 1 -- filter only active users*

and G1.GroupID = G.GroupID -- We join the sub-query

ORDER BY U.Username -- we order the alphabetically

FOR XML PATH('') -- we create a list in XML format (e.g. ", Dan, David")

),1,1,'') -- STUFF arguments in order to remove ", " at the beginning of list

,'N/D') 'Users' -- COALESCE argument to set N/D if there are no members

From Groups G

Where G.GroupID > 1 -- we remove system groupID=1

Order by G.Groupname

 

Example of Query 5-2 results

 

  • Retrieving files and archive folder paths – Archive folders and archive file versions are stored on disk using hexadecimal names. In the SOLIDWORKS PDM database, the DocumentIDs and versions store as decimal values. It is possible to retrieve this type of data in the correct format by using hexadecimal conversions based on the ArchiveID and DocumentID columns. The next example shows how to retrieve the archive folder path and file path for a specific DocumentID. The actual DocumentID value appears in the query in hexadecimal format because the source of the value is from an error in the SOLIDWORKS PDM client log.

Query 5-3

-- Query example to get archive folder path and file path for a specific DocumentID (hexadecimal format)

Select

'' + P.Path + D.Filename as 'File path' -- we retrieve the files path

, '\\' + RIGHT(CONVERT (NVARCHAR(MAX), CONVERT(Binary(4), D.DocumentID), 2),1) + '\\' + CONVERT (NVARCHAR(MAX), CONVERT(Binary(4), D.DocumentID), 2) as 'Archive folder path' --we retrieve the archive folder path by transforming first in hexadecimal and then in string (e.g. 3\\00000193)

/* Optional uncomment the below line to get the same archive folder path but using an undocumented system function, sys.fn_varbintohexsubstring, to transform binary into string */

--, '\\' + right(sys.fn_varbintohexsubstring(0, CAST(D.DocumentID as binary(4)),1,0),1) + '\\' + sys.fn_varbintohexsubstring(0, CAST(D.DocumentID as binary(4)),1,0) as 'Archive folder path'

From Documents D

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

WHERE

D.DocumentID = 0x00000193 -- We filter by a DocumentID provided in an SOLIDWORKS PDM client log (See Fig.5); note that the Archive entry from the log has a hexadecimal format and if we add for value D.DocumentID characters "0x" at the beginning of the hexadecimal value "00000193" no additional transformations is required e.g. “= 0x00000193”

Order by P.Path + D.Filename

SOLIDWORKS PDM Client log sample

 

 

Example of Query 5-3 results

 

b) Recursive queries

In some situations, you might be able to solve a query performance issue by using a different method to retrieve the same data. This could also be the case for recursive queries. For example, an alternative for the while-loop recursive method shown in Query 4-5 is to use a common table expression (CTE) method that starts with a CTE for an anchor method and then another CTE for the recursive members as shown in the following query script:

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition -- Anchor member is defined.

UNION ALL

CTE_query_definition -- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

In the next example, Query 4-5 was rewritten to use a CTE recursive method.

Query 5-4

--Query to list file references using common table expression recursive method

----USER INPUT SECTION-----

DECLARE @FN(255) = '_MITER SAW.SLDASM'-- Type the exact filename

DECLARE @PN(255) = 'Prototype - X32'-- Type the exact name of parent folder

----Query EXECUTION SECTION--

DECLARE @DI

SET @DocID = (Select D.DocumentID from Documents D

Inner Join DocumentsInProjects DP on D.DocumentID = DP.DocumentID

Inner Join Projects P on P.ProjectID = DP.ProjectID

where lower(D.Filename) = lower(@FileName) and lower(P.Name) = lower(@ProjectFolder));

WITH DocumentRefs (Filename, ParentDocumentID, RefDocumentID, ParentRevNr,RefRevNr,RefLatestVersion, Xrtype, XrefID, RefCount, Level, sort)

as

(

-- Anchor member definition

(Select (Select(@FileName)), (Select(@DocID)), null, (Select LatestRevisionNo from Documents where DocumentID = @DocID),null,null, Null, Null, Null, 0 , CAST(0 as varchar(max)) -- Dummy entry to list also the main parent document

UNION ALL

SELECT RefD.filename,XR.DocumentID, XR.XRefDocument, XR.RevNr, XR.XRefRevNr,RefD.LatestRevisionNo, XR.XrType, XR.XRefID, XR.RefCount , 1 as level, CAST(ROW_NUMBER() OVER(ORDER BY RefD.Filename) AS varchar(max))

FROM XRefs XR

INNER JOIN Documents RefD ON RefD.DocumentID=XR.XRefDocument

INNER JOIN Documents ParD ON ParD.DocumentID=XR.DocumentID and XR.RevNr = ParD.LatestRevisionNo

WHERE XR.XrType<>2 AND RefD.Deleted=0 AND ParD.deleted=0 and XR.DocumentID = @DocID)

UNION ALL

-- Recursive member definition

SELECT RefD1.Filename, XR1.DocumentID , XR1.XRefDocument, XR1.RevNr, XR1.XRefRevNr,RefD1.LatestRevisionNo, XR1.XrType ,XR1.XRefID, XR1.RefCount , Level + 1, CAST((SORT + RIGHT('0000'+CAST(ROW_NUMBER() OVER(ORDER BY RefD1.Filename) AS varchar(max)),4)) AS varchar(max))

FROM XRefs XR1

INNER JOIN Documents RefD1 ON RefD1.DocumentID=XR1.XRefDocument

INNER JOIN Documents ParD1 ON ParD1.DocumentID=XR1.DocumentID and XR1.RevNr = ParD1.LatestRevisionNo

INNER JOIN DocumentRefs DR on XR1.DocumentID = DR.RefDocumentID

WHERE XR1.XrType<>2 AND RefD1.Deleted=0 AND ParD1.deleted=0

)

--the actual select statement

Select

Level

, '|' + replicate('__', Level) + DR.Filename as 'Tree'

, CAST(RefRevNr as VARCHAR(15))+'/'+CAST(RefLatestVersion AS VARCHAR(15)) As Version

, Case

When XrType = 1 Then 'Normal'

When XrType = 6 Then 'Contextual' -- e.g. "part in part" reference type

End as 'Reference type' --shows reference type

From DocumentRefs DR

Inner join Documents D on D.DocumentID = DR.RefDocumentID

INNER JOIN DocumentsInProjects DIP ON DIP.DocumentID=D.DocumentID

INNER JOIN Projects P ON P.ProjectID=DIP.ProjectID

Order By sort

 

Example of Query 5-4 partial results (in this case, the parent assembly does not appear in the list).

 

c) Querying multiple SQL Server databases

In situations where a single SQL Server instance hosts multiple SOLIDWORKS PDM databases, it can be useful to run a query against all of these databases. To accomplish this, it is possible to use the undocumented sp_msforeachdb stored procedure that allows retrieving information from each database within a SQL instance.

Query 5-5

create table #temp001 ( [VaultName] varchar(100), [Total Documents] int, [Destroyed DocumentIDs] int)

exec sp_msforeachdb

--first we check if it is an SOLIDWORKS PDM database

'if exists(select * from [?].sys.tables t where t.name = ''SystemInfo'')

begin

insert into #temp001

/* next we insert 2 columns: total documents and a computed column that will give an estimation of destroyed documents */

select ''?'', COUNT(1), (MAX(DocumentID) - COUNT(1)) from [?]..Documents

end'

select * from #temp001

Order By [Total Documents] Desc

drop table #temp001

 

Example of Query 5-5 results

 

We hope that you find this document informational and useful and request that you leave a <a href="https://www.surveymonkey.com/r/SolidPractices" rel="nofollow">brief feedback</a> about the topics that you want us to cover in the next revision of this document. Click here for a complete list of SolidPractices documents available from DS SOLIDWORKS Corp.
Comments 0