During scalability testing, we encountered a case where a query sent to an SQL Server 2008 data source failed with the following exception being thrown by the JDBC driver. We are using Microsoft's SQL Server JDBC Driver version 4.0.

Remote com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

The default MaxInCriteriaSize (1000) and MaxDependentInPredicates (50) for the sqlserver translator seems to allow an unsupported query to be generated.  We were able to work

around the problem by overriding the default values (https://community.jboss.org/message/794742), but I am wondering whether the default value for MaxDependentInPredicates should be changed.

The SQL Server 2012 documentation (http://technet.microsoft.com/en-us/library/ms143432.aspx) indicates the “maximum parameters per user-defined function” and “maximum parameters per stored  procedure” are limited to 2100.   I am not exactly sure which of these two settings specifies the limit for the number of the IN values, but I assume one of them does.  I checked the limits for SQL Server 2005 and found them to be the same.

Does it make sense to change the default setting for MaxDependentInPredicates  in the Teiid supplied sqlserver translator so that overriding it is not required (i.e. MaxDependentInPredicates = 2)?

In case it is helpful to understand our test in more detail, here is our setup.

We have two SQL servers.  One of them contains a database containing Person and Department fact tables.  The other SQL server contains a table containing a help desk Ticket fact table.  We created a view that joined the three tables, e.g.:

CREATE VIEW Test_view1 (

                TicketId integer,

                TicketType string,

                TicketDescription string,

                UserName string,

                DeptName string

              )

              AS select

              table1."TicketId" AS TicketId,

              table1."TicketType" AS TicketType,

              table1."TicketDescription" AS TicketDescription,

              table2."Name" AS UserName,

              table3."DeptName" AS DeptName

              FROM

              TicketsDb.Db2.dbo.Ticket table1

              INNER JOIN UsersDb.Db1.dbo.Person table2 on (table2.UserId = table1.UserId)

              INNER JOIN UsersDb.Db1.dbo.Department table3 on (table3.DeptId = table2.DeptId)

The Person table contains 10K records, the Department table contains 100 and the Ticket table contains 10M records.  Issuing a query that returns tickets for all persons caused the failure we encountered.  After changing MaxDependentInPredicates to 2, the query processed successfully.