SQL Server's maximum parameters

A brief desc of my project

So last year I had a project to develop some background jobs using python. Two of them were (1) Downloading PDFs the job I and (2) Sending downloaded PDFs that named using a unique customer id. I'll talk about the sending PDFs job, here's how this job works:

  1. Reads downloaded PDFs directory files' name and make sure that the files' name are registered in my database (downloading PDFs is another task of my another background job)
  2. Sends the PDFs to SFTP
  3. Updates flag in my database that PDFs with specified unqiue customer id have been sent

That's it, that's all.

The error comes out

After 10 months the job having been deployed in production and running without problems other than SFTP connection issues, there was an error like this:

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

The error saying that there was a query in my job may have a wrong syntax or table's fields I try to insert does not match with number of values I provided. But, unfortunately, seemed there was nothing wrong with queries I wrote.

The culprit

After googling about this error, I found the most possible reason of why the job throwing error in pyodbc github issues saying that SQL Server has the maximum number of parameters that is 2100 parameters. The limitation had been confirmed in microsoft's documentation. My guess was that the error was thrown in the very first step of how the background job works, which is "read downloaded PDFs directory files' name and make sure that the files' name are registered in my database". To make sure that files' name are registered in my database (remember that 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 a certain moment there were more than 4000 downloaded PDFs in my directory !.

Setup a little expriment

Ok, the microsoft confirmed it, but we still need to make it sure right ?. Here I use pyodbc a python library to work with database

  import pyodbc
  from setting import conn_string

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

  #num_of_params represents how many PDF files in my directory
  num_of_params = 1000
  #generating sql parameter placeholders
  question_marks = ','.join(['?' for _ in range(1, num_of_params)])
  #params represents my PDFs' name
  params = [str(i) for i in range(1, num_of_params)]
  # decalre sql statement
  sql = f"""
    SELECT customer_id FROM CUSTOMERS
    WHERE customer_id IN ({question_marks})
  """
  # execute sql statemant using declared parameters
  cursor.execute(sql, params)

where the conn_string is a connection string, you can read about this here

by changing num_of_params, I found that there are 3 behaviors of this case

  1. If num_of_params is less than 2099, then we are good

  2. If num_of_params is between 2099 - 2101, then we got error
    ('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 more than 2101, then we got error
    ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

Conclusion

Just be careful of your query parameters !