Question:
Hi there. I manage a SilverStripe installation (4.1.2) installed on Windows Server 2016, IIS 10 with a connected MSSQL database.
There have been no issues up until today with the files tab allowing the viewing and uploading of images. An internal team use the CMS pages to add content and ‘insert images’. When they attempt to do so, the following error is produced (it also produces when going direct to the ‘Files’ tab).
Couldn't run query: SELECT DISTINCT "File_Live"."ID", "File_Live"."Name" AS "_SortColumn0" FROM "File_Live" WHERE ("File_Live"."ID" IN (?, ?, ?, ?, ?, ?)) AND (("File_Live"."ID" NOT IN (?, ?, ?, ?, ?, ?) OR "File_Live"."ID" IS NULL)) ORDER BY "_SortColumn0" ASC IMSSP, -52, Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.
Please note, there are hundreds more '?'s than I have pasted here. I have looked around the forums and found little in the way of reference for this error. I understand this error may be caused by the query returning too many values but from what I can see this query is part of the core functionality of SilverStripe? Also the fact I have changed nothing on my side and it randomly stopped working has left me unable to know where to start looking.
Any info or advice would be greatly appreciated. Thank you for your time.
It does look like you’ve bumped up against a MSSQL limit there. Do you have a lot of files in the directory you’re trying to view? My guess is that there’s nothing random about it, but rather you’ve just reached the number of files which means the database server limits have been hit.
Thank you for your insight. After looking more carefully inside the database tables, there were legacy references going back years. I believe you were correct that there were simply too many and MSSQL refused to query such a large number.
I am not sure it is the cleanest fix but I simply deleted all rows within the ‘dbo.File’ table which were older than 6 months. It feels more like a short term workaround but SilverStripe now functions as expected, just with no references to any legacy media in the CMS. The files remain on the server but many are still in use and unlikely to be reused so it works for my client. I do not know the implication on any other tables (I cannot find any detailed documentation on the SS database tables), but I tested this method in a staging environment with no issues so I was happy to go ahead into production.
Thanks for your input, I will mark this question as answered.