Deployments endpoint error

jasper · 8 November 2021 13:04

Upgraded OD Server to 2021.2.7727 running into an error when using the deployments endpoint. Trying to get the latest deployment id for a specific environment with the following

https://company.com/api/deployments?take=1&environments=Environments-6&projects=AutomatedAccounting&taskState=success

I’m getting the following error, subtracted 100’s of @taskid parameters

Exception occurred while executing a reader for SELECT *\r\nFROM [dbo].[Deployment]\r\nWHERE ((([SpaceId] = ‘Spaces-1’)))\r\nAND ([ProjectId] IN (@projectid1))\r\nAND ([EnvironmentId] IN (@environmentid1))\r\nAND ([TaskId] IN (@taskid1, @taskid2, … \r\nORDER BY [Created] DESC\r\nSQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

This has been working perfectly fine until we upgraded. Any idea what is wrong?

charles.hague · 8 November 2021 14:23

Hi Jasper,

Thank you for reaching out to us with your query.

It looks like you might be encountering the following issue:

github.com/OctopusDeploy/Issues

Environment and project filters against deployments API endpoint don't work when used in conjunction with the TaskState filter

opened 02:52AM - 07 Sep 21 UTC

KennethBates

kind/bug state/backlog team/fire-and-motion

### Team - [X] I've assigned a team label to this issue ### Severity One repo…rt with a workaround ### Version 2021.2.7428 ### Latest Version Not applicable ### What happened? SQL error 8003 can occur when querying Deployments API endpoint with a TaskState filter. If you were to use the env or project filters in the GET, I would expect only the tasks matching all filter criteria would be returned (e.g. only successful tasks associated with project X in env X). Instead, it looks like we run a SQL query against every task matching only the state filter, which can be many, many more than expected. This can lead to the below error. ### Reproduction See source report ### Error and Stacktrace ```shell SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. ``` ``` ### More Information Report: https://octopus.zendesk.com/agent/tickets/75093 ### Workaround - Remove the TaskState filter - Tighten retention so as to cut down the total number of tasks that will be queried

I’ve checked and it doesn’t seem that a fix is available just yet. You may want to subscribe to the issue above to be notified when one is released.

I hope this is helpful. Please let me know if you have any questions.

Best Regards,

Charles

jasper · 8 November 2021 15:42

Yes - omitting TaskState filter and I do not get the error. Do you know which version of OD introduced the error and is it possible to roll OD back? This error is blocking the a production deployment, and I need to find a workaround until fixed.

charles.hague · 8 November 2021 16:14

Hi Jasper,

Thank you for getting back to me with your query.

Unfortunately I can’t determine which version of Octopus Server this issue was introduced in. I have asked the relevant team if they can advise and will update you once I hear back from them.

One possible workaround might be to remove the taskState filter from the API call which should allow the query to complete correctly. You could then filter the results by taskState in your script/application after they have been returned.

I hope this is helpful. I’ll let you know once I hear back from the relevant team.

Best Regards,

Charles

jasper · 8 November 2021 18:56

Hi Charles,
That’s a reasonable workaround - thanks

system · 9 December 2021 19:44

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.