SQL Server's Maximum Parameters

A Brief Description of My Project

Last year, I worked on a project to develop some background jobs using Python. Two of these jobs were:

  1. Downloading PDFs: This job handled downloading PDFs.
  2. Sending Downloaded PDFs: This job sent the downloaded PDFs, which were named using unique customer IDs.

In this article, I’ll focus on the second job, which works as follows:

  1. Reads the names of downloaded PDF files from a directory and ensures that these file names are registered in the database (downloading PDFs is handled by another background job).
  2. Sends the PDFs to an SFTP server.
  3. Updates a flag in the database to indicate that the PDFs with the specified unique customer IDs have been sent.

That’s it. Simple enough, right?

The Error

After 10 months of running the job in production without any major issues (other than occasional SFTP connection problems), an error occurred: 

('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExe cDirectW)')

The error suggested that there might be a syntax issue in one of the queries or that the number of fields being inserted didn’t match the number of values provided. However, after reviewing the queries, everything seemed fine.

The Culprit

After some research, I discovered the likely cause of the error in a pyodbc GitHub issue. It turns out that SQL Server has a maximum limit of 2100 parameters per query. This limitation is confirmed in Microsoft’s documentation.

My suspicion was that the error occurred during the first step of the job: reading the names of downloaded PDF files and ensuring they are registered in the database. To check if the file names were registered (remember, the PDFs were named using customer_id), I wrote a (simplified) query like this: 

SELECT customer_id 
FROM CUSTOMERS 
WHERE customer_id IN (<file_names_in_PDFs_directory>);

Unfortunately, at one point, there were more than 4000 downloaded PDFs in the directory!

Setting Up a Small Experiment

Although Microsoft confirmed the limitation, I wanted to verify it myself. I used pyodbc, a Python library for working with databases, to test this.

Here’s the code I used: 

import pyodbc
from setting import conn_string

connection = pyodbc.connect(conn_string)
cursor = connection.cursor()

# num_of_params represents the number of PDF files in the directory
num_of_params = 1000
# Generating SQL parameter placeholders
question_marks = ','.join(['?' for _ in range(1, num_of_params)])
# params represents the names of the PDFs
params = [str(i) for i in range(1, num_of_params)]
# Declare the SQL statement
sql = f"""
    SELECT customer_id FROM CUSTOMERS
    WHERE customer_id IN ({question_marks})
"""
# Execute the SQL statement using the declared parameters
cursor.execute(sql, params)

The conn_string is a connection string. You can read more about it here.

By changing the value of num_of_params, I observed three different behaviors:

  1. If num_of_params is less than 2099, everything works fine.
  2. If num_of_params is between 2099 and 2101, the following error occurs:
    ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][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. (8003) (SQLExecDirectW)')
  3. If num_of_params is greater than 2101, the following error occurs:
    ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

Conclusion

Be mindful of the number of parameters in your SQL queries! If you’re working with a large number of parameters, consider breaking the query into smaller chunks or using alternative approaches to avoid exceeding SQL Server’s 2100-parameter limit.