Issue
Administrators of an instance may find that while reviewing or writing Scripts within the ServiceNow Platform that query a Database View they are only able to obtain a maximum of 10,001 records from any Glide Query regardless of how many rows the actual Database view actually contains.
As an example of this issue, suppose the following brief Scripted Glide Query was expected to return 28,931 rows:
var gr = new GlideRecord("u_my_database_view");
gr.addQuery("incident_active", "true");
gr.query();
However, upon running the script, it is found to return exactly 10,001 rows. Performing an equivalent query in the query filter of the table however returns the expected number.
Cause
Each ServiceNow instance has a built-in default maximum setting of 10,001 rows to return for scripted Glide Queries based on a DatabaseView from that instance.
This maximum number of rows to return from a Glide Query based on a Database view is limited through the use of a System Property on the instance (having the name glide.db.max_view_records). Like many other system properties, the system property that controls this maximum rows to return from a database view query has a default value for the property which is to be used if the property is not found in the list of System Properties on that instance. This default limit is 10,001 records.
Resolution
To correct this issue, the system property controlling this number of return values should be explicitly declared and set to the needed value. If this system property is already found on the instance (having already been added to the system to correct this issue), it should be set to an appropriate value as needed for proper functionality of any scripts using that property.
Thus, to correct this issue this property can be added to the instance using the following steps:
Log into the instance with an account having admin rights to the instance.
In the Filter navigator type system_properties.list and press the Enter key.
A list of properties will appear. First search the list to ensure there is not already a property with the name glide.db.max_view_records. If this property is found to already exist on the system, adjust the Value field for this property to the required value. However, if this property is not yet found on the instance (forcing the use of the default value of 10,001 records), continue with these steps to add the property to the instance.
Click the New button on the list of System Properties.
Fill in the following fields on the new System Property record form:
Name: glide.db.max_view_records
Description (optional): Property to control the maximum number of records to be returned in a scripted Glide query on a Database View.
Type: integer
Value: <Type the appropriate value>
Leave the remaining fields as set by default on the new record.
The Value property should be set to an appropriate value as needed to include the functionality needed in the Scripted glide queries (i.e. 75000).
Note: You will want to ensure you use a large enough value to accomplish what is needed in the script but at the same time not such a large value that might allow very long-running scripts to inversely cause issues of the performance of the instance.
Click the Submit button on the new record.
After adding this property, scripted queries on a Database view should then allow a maximum number of records up to the Value set for the property.
Related Links
Note: In a few cases it may be necessary to reduce the value below the default setting of 10,001 for some reason. In those cases the same procedures as above can be used to set a limited lower than the 10,001 record default.
Note: Deleting this system property will revert the maximum value to the default setting of 10,001 on that instance.
Note: This issue can also occur with ODBC Connections which are run on a Database View. See the following KB Article for more details on that particular issue:
KB Article KB0779234 - ODBC Query returns 10000 rows even when it is expected to return more returns
