Common PCORnet Menu-Driven Query Errors

This page lists various common error messages that may be encountered when setting up and running PCORnet Menu-Driven Queries. Errors are listed with the exact wording they appear with in the DataMart Client. Some issues have different error messages associated with them depending on the relational database platform that is being used, each of those error messages are listed below for clarity.

Errors when opening a PCORnet Menu-Driven Query:

  • Connection timeout

    1. When connecting to Oracle: "Connection request timed out"
    2. When connecting to SQL Server: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.)"
    3. When connecting to Postgres: "Connection establishment timeout. Increase Timeout value in ConnectionString."
  • Keyword not supported: data source

    Parameter name: Keyword

Errors when running a PCORnet Menu-Driven Query:

  • Command timeout

    1. When executing against Oracle: "user requested cancel of current operation"
    2. When executing against SQL Server: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
    3. When executing against Postgres (error displayed in DataMart Client log file): "An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> Npgsql.NpgsqlException: 57014: canceling statement due to statement timeout"

Solutions


  1. Connection timeout

    This is caused by the DataMart Client being unable to connect to your specified database before timing out. The following steps may resolve this problem:
    1. Navigate to your PCORnet CDM Model Settings and increase the connection timeout value.
    2. Test the connection. If successful, click OK on all windows to save your changes. Be sure to restart your DataMart Client before attempting to run another request.
    3. If testing is unsuccessful, consult your local Database Administrator to make sure you are connecting to the correct database and that it can accept external connections.
    4. If the database is configured correctly and you are using the correct connection settings, there could be a firewall within your local IT environment that is blocking the DataMart Client from reaching your database. In this case, contact your PopMedNet Administration team for further assistance.
  2. Keyword not supported: data source

    Parameter name: Keyword

    This is typically caused by the Adapter field within your PCORnet CDM Model Settings being set to 'ESP Request' rather than 'PCORnet CDM'. To resolve:
    1. Navigate to your PCORnet CDM Model Settings and make sure the Adapter field is set to 'PCORnet CDM'. After making necessary changes, click OK on all windows to save. Be sure to restart your DataMart Client before attempting to run another request.
    2. If the problem persists, contact your PopMedNet Administration team.
  3. Command timeout

    This is caused by the SQL query taking longer to execute than the allotted amount of time specified in the Command Timeout field within your PCORnet CDM Model Settings. To resolve:
    1. Navigate to your PCORnet CDM Model Settings and enter a larger value in the Command Timeout field. Note that depending on the size and optimization of your database and the complexity of the SQL, some queries could take over an hour to run. Click OK on all windows to save changes. Be sure to restart your DataMart Client before attempting to run another request.
    2. If you have concerns surrounding how long queries are taking to execute, contact your PopMedNet Administration team and/or your local Database Administrator.
  4. Object reference not set to an instance of an object.

    This is typically expected if you edit your DataMart/Network settings within your DataMart Client and try to run a request without restarting your DataMart Client first. To resolve:
    1. Close and reopen your DataMart Client.
    2. If the problem persists, contact your PopMedNet Administration team.
  5. Query run indefinitely (no error presented)

    This can be caused by a variety of issues. To resolve:
    1. Ensure you are using the correct DataMart Client version (currently v5.3 or newer)
      1. This can be done by opening the DataMart Client and clicking on the blue 'About' link in the bottom-right corner.
    2. Ensure you are using a supported relational database management system
      1. PCORnet MDQs currently support:
        1. Oracle 11 & 12
        2. Postgres 9.4 & 9.5
        3. SQL Server 2012 & 2014
    3. Test your database connection from the DataMart Client settings to ensure the database is running
    4. Close and reopen your DataMart Client.
    5. Click on the blue 'About' link in the bottom-right corner of the DataMart Client and click Packages. Check off all adapter processor packages in the list and click Delete Packages.
    6. Verify that you are using a strong, reliable internet connection
    7. Verify that the DataMart Client is not being blocked by any internal security software (this may require consulting your local IT Security team).
    8. Database indexes may need to be created or adjusted to improve execution times. Please consult your local Database Administrator to run analytics on the problematic MDQ(s) and adjust indexes accordingly
    9. If the problem persists, contact your PopMedNet Administration team.
  6. Unable to determine the query adapter to use based on the primary criteria terms.

    This is caused by the Adapter field within your PCORnet CDM Model Settings being set to a Summary Table value rather than 'PCORnet CDM'. To resolve:
    1. Navigate to your PCORnet CDM Model Settings and make sure the Adapter field is set to 'PCORnet CDM'. After making necessary changes, click OK on all windows to save. Be sure to restart your DataMart Client before attempting to run another request.
    2. If the problem persists, contact your PopMedNet Administration team.
  7. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    This is typically caused by incorrect information entered in
    the Database field within your PCORnet CDM Model Settings, the error is specific to Oracle databases. To resolve:
    1. On the machine that your Oracle database is installed on, open the tnsnames.ora file. The location may vary depending on Operating System and IT environment, but the default location within a Windows environment is: C:\oraclexe\app\oracle\product\[your oracle version]\network\admin\tnsnames.ora. Within this file, copy the applicable 'service_name' exactly as it appears and enter it in the 'Database' field within your DataMart Client's PCORnet CDM Model Settings. Click OK on all windows to save changes. Be sure to restart your DataMart Client before attempting to run another request.
    2. If this does not resolve the error, there may be a configuration issue with your Oracle database or a local firewall blocking the DataMart Client from connecting. Contact your local Database Administrator/IT to confirm the database is configured correctly and able to receive external connections (e.g. from the DataMart Client).
    3. If your local environment is set up correctly and the problem persists, contact your PopMedNet Administration team.
  8. ORA-12545: Network Transport: Unable to resolve connect hostname

    This is typically caused by incorrect information entered in
    the Server field within your PCORnet CDM Model Settings, the error is specific to Oracle databases. To resolve:
    1. On the machine that your Oracle database is installed on, open the tnsnames.ora file. The location may vary depending on Operating System and IT environment, but the default location within a Windows environment is: C:\oraclexe\app\oracle\product\[your oracle version]\network\admin\tnsnames.ora. Within this file, copy the applicable 'host' exactly as it appears and enter it in the 'Server' field within your DataMart Client's PCORnet CDM Model Settings. Click OK on all windows to save changes. Be sure to restart your DataMart Client before attempting to run another request.
    2. If this does not resolve the error, there may be a configuration issue with your Oracle database or a local firewall blocking the DataMart Client from connecting. Contact your local Database Administrator/IT to confirm the database is configured correctly and able to receive external connections (e.g. from the DataMart Client).
    3. If your local environment is set up correctly and the problem persists, contact your PopMedNet Administration team.
  9. Table or field does not exist/invalid object

    This could be caused by a couple of different issues sourcing from either the schema entered in your PCORnet CDM Model Settings
    or the formatting within your relational database. To resolve:
    1. Navigate to your PCORnet CDM Model Settings and make sure you entered your schema exactly as it appears within your database (including capitalization - this field is case-sensitive). After making necessary changes, click OK on all windows to save. Be sure to restart your DataMart Client before attempting to run another request.
    2. If this does not resolve the issue, there may be a table/field formatting mismatch between what a Menu-Driven Query is expecting and what exists in your database. This is most common in Postgres, as Menu-Driven Queries require fields and tables to be uppercase across all supported database platforms (Postgres natively defaults to lowercase while Oracle and SQL Server default to uppercase). This can be resolved by contacting your local Database Administrator to make sure all fields and tables are named correctly according to the PCORnet v3 CDM specifications and are all uppercase within your database.
    3. If the problem persists, contact your PopMedNet Administration team.
  10. Cannot click Run (button greyed out).

    This is typically caused by either the Adapter field within your PCORnet CDM Model Settings being set to 'Modular Program' rather than 'PCORnet CDM', or you do not have full DataMart Administrator permissions associated with your PopMedNet account. To resolve:

    1. Navigate to your PCORnet CDM Model Settings and make sure the Adapter field is set to 'PCORnet CDM'. After making necessary changes, click OK on all windows to save. Be sure to restart your DataMart Client before attempting to run another request.

    2. If this does not work, contact your PopMedNet Administration team with your account name and network name requesting the ability to respond to PCORnet Menu-Driven Queries.