Description
In the Strategy 2021 release, Parameterized Query is enabled by default for all newly created database connections against a list of MicroStrategy certified Diamond Gateways . With the Parameterized Query option enabled, reports containing text filters and prompts will be executed using parameter markers (?) during SQL execution.
Although there is no limit set on how many parameters we can have in one report or query in Strategy, each gateway may have a limitation on parameters number on the database side. Once the number of parameters exceeds the maximum limitation of database, the following error is received after enabling Parameterized Query:
The incoming request has too many parameters.
One common example of a parameterized query is shown below:
select distinct a11.CUSTOMER_ID CUSTOMER_ID,
a11.CUST_LAST_NAME CUST_LAST_NAME,
a11.CUST_FIRST_NAME CUST_FIRST_NAME
from lu_customer a11
where a11.CUST_LAST_NAME in (?, ?, ?, ?, ?, … (? * 2100) …, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?).
The following error is obtained for the SQL Server since the SQL Server supports a maximum of 2100 parameters:
[Strategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server] The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.The limitation varies for the different gateways. Here, we list the expected parameters number limitation for Diamond Gateways.
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww | The Strategy Technical Support Team
Diamond GatewayParameters Number LimitationReferenceAmazon RedshiftN/ADocumentation regarding the limitation in Redshift has not been found. Testing shows it works for 3000 parameters. IBM Db216000https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.htmlAzure Synapse Analytics2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Microsoft SQL Server2100https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15Oracle1000https://www.ibm.com/support/pages/ora-01795-maximum-number-expressions-list-1000-0Teradata2536https://docs.teradata.com/reader/bBJcqMYyoxECDlJRAz9Dgw/ZmMJSlE91qowaUXxYi52ww |
