Azure Synapse "Message Type Not Found" error when running stored procedure: Appears when lines of code reach certain line length

Michael King 0

Hi,

We need some technical guidance from either a Azure Synapse specialist to resolve an issue which we are unable to understand / fix despite running through the sql logic bit-by-bit, line-by-line. Any guidance or help would be much appreciated.

We have a stored procedure in Azure Synapse Analytics which is erroring with a "Message Type Not Found" when the last select command goes past 1645 lines in the coding. The script has a last select command and lists out a set of fields to pull back the data.

The error message appears within the last select command just 6 fields / lines of code short of the end of the stored procedure. The stored procedure when run by a pipeline in Azure Synapse creates a table with the last 6 fields returning nulls.

On working through the script there are some interesting points:

1 - When spaces and comments are removed and the stored procedure header are all removed, then more fields can be added to the last select command without the error messaging appearing. Ie we can run the script with all the fields included in the last select command apart from the last field before the error message appears. It seems to suggest the error is related to the length of scripting.

2 - The error is not related to the specific fields being called as if you remove a field from higher up in the last select command then add the last field at the end, then the script will work without the error.

3 - The same script runs without any issues in Microsoft SQL Server Management Studio

4 - We are running the stored procedure and script on Azure Synapse Analytics with a serverless solution and so although i have read Microsoft Azure articles about resource classes and restrictions, this should not apply when using a serverless solution.

5 - We are running the scripting within the Azure Synapse Analytics web portal and running this via stored procedures and pipelines. We are also running the script by just highlighting and running the select commands listed within the stored procedure and still the error message is appearing.

6 - When using select * command pointing to the last cte / temp table and removing the line-by-line last select command which lists out every temp table field, then the script works without the error. This reduces the number of lines used in the last select command.

Any technical guidance to help us understand / resolve our issue would be much appreciated.

Many thanks.

Mike King

Erland Sommarskog 112.7K Reputation points MVP

2024-07-22T18:56:10.4433333+00:00

The same script runs without any issues in Microsoft SQL Server Management Studio

So where are you running it, when you get an error? Or is there both a script and a stored procedure? It is not really clear to me.

As for the error message, "message type" in the context of SQL Server makes me think of Service Broker. Although, this completely different since this is Synapse serverless, which I am not familiar with. (My expertise is in SQL Server itself.)
Michael King 0 Reputation points

2024-07-22T23:12:04.5633333+00:00

Hi Erland,

Many thanks for your reply. To make it a little clearer, let me answer your specific questions.

1 - So where are you running it, when you get an error?

We are running the stored procedure and script within the Azure Synapse Analytics Web Portal on the "Develop" section.

2 - Or is there both a script and a stored procedure? It is not really clear to me.

As we are testing the output we are running the script two different ways:

2.1 - Running the script as a stored procedure via a pipeline; and

2.2 - Testing the script by highlighting the inner lines of code within our stored procedure and running the highlighted lines of the script within the "Develop" section.

Both running the highlight lines of code and also the stored procedure via the pipeline result in the same "Message Type Not Found" message when the last few fields were added in the last select command.
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-07-23T18:39:33.91+00:00

Hi @Michael King
Thank you for your question and using the Microsoft Q&A platform.

Based on the information you provided, it seems like the issue is related to the length of the stored procedure. When you remove spaces and comments, you are able to add more fields to the last select command without the error message appearing. This suggests that the error is related to the length of the script.

One possible solution is to break up the stored procedure into smaller parts. You can create multiple stored procedures that each perform a specific task and then call them from a main stored procedure. This can help to reduce the length of the script and may resolve the issue.

Another possible solution is to optimize the script to reduce its length. You can try to simplify the logic and remove any unnecessary code. This can help to reduce the length of the script and may resolve the issue.

It is also possible that the issue is related to a specific field or data type. You can try to isolate the field that is causing the issue and see if there are any issues with its data type or formatting.

You can also refer to the below document for more help:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#constraints

Hope this helps. Please let me know if you have any further questions.
Erland Sommarskog 112.7K Reputation points MVP

2024-07-23T19:35:33.6133333+00:00

I assume that when you run the procedure, you don't create the procedure in the pipeline, but the procedure is there already?

It is quite funny that execution from SSMS works, but you get this weird error when you run it from the portal or your pipeline. I mean, if it is only a matter of running the procedure, the length would have the same effect on execution, no matter where you run it from.

But, as I said, Synapse Serverless is not my game, so I don't have an idea of what is going on.
Michael King 0 Reputation points

2024-07-24T17:34:49.14+00:00

Hi @NIKHILA NETHIKUNTA

Many thanks for your reply which has been really useful.

Having made some further tests and changes to the script it looks like it may be linked to a character limit allowed in the stored procedure which is resulting in the "Message Type Not Found" error where the last set of fields in the last select command not being able to be returned.

We renamed one of the cte tables used in the script logic to just be referenced as "a" and this meant the script worked. Previously, the cte was referenced as "Pipeline_Inc_Flight_Times". It suggests that the error is not linked to the number of lines of code although the number of characters.

Is there any limit you know about? If our assumption is correct, then we have not been able to find any limit constraint listed on any web searches we have made and this is not necessarily documented anywhere by Microsoft. The link you provided in your last reply was useful and listed out the constraints within Azure Synapse for serverless solutions although there was no mention of a character constraint within stored procedures or scripts.

Is there any response from Microsoft to this which could explain fully why the character length may impact scripts erroring etc and if so what the actual limit may be?

Many thanks

Mike
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-07-26T18:29:13.0666667+00:00

Hi @Michael King
Can you please provide the query which you are trying to run so that we can repro from our side?
Thanks
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-07-29T08:22:44.43+00:00

Hi @Michael King
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Michael King 0 Reputation points

2024-08-20T07:24:37.11+00:00

Hi Nikhilanethikunta,

We completed some further investigations and we believe there is a limit on the volume of characters allowed within a stored procedure created in Synapse Analytics which is around 65,000. It would be good to get some confirmation from Microsoft about this as we have inferred this as the reason. Regarding sending over the script, we are unable to send this externally as the work relates to internal calculations within the company etc. Is it possible to confirm our inference is correct? Many thanks. Mike

1 answer

NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-08-20T16:26:05.51+00:00

Hi @Michael King
Your inference about the character limit for stored procedures in Azure Synapse Analytics is indeed correct. The maximum size for a stored procedure in Synapse Analytics is limited to 64 KB (approximately 65,536 characters). This limit is in place to ensure optimal performance and manageability within the system.

If you find that your stored procedure exceeds this limit, you might consider breaking it down into smaller, modular procedures or optimizing the code to fit within the constraints.

For more information, please refer to the below link:
https://learn.microsoft.com/en-us/azure/synapse-analytics/synapse-link/synapse-link-for-sql-known-issues

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 112.7K Reputation points MVP

2024-08-20T21:06:29.51+00:00

May I humbly suggest an improvement of the error message for this condition? :--)

NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-08-21T05:08:23.7933333+00:00

Hi @Erland Sommarskog
Appreciate if you could share the feedback on our feedback channel which would be open for the user community to Upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insights into the potential impact of implementing the suggested feature.

Erland Sommarskog 112.7K Reputation points MVP

2024-08-21T20:52:13.6233333+00:00

OK, submitted at https://feedback.azure.com/d365community/idea/f4550b33-ff5f-ef11-a4e5-6045bdb55b8c
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Synapse "Message Type Not Found" error when running stored procedure: Appears when lines of code reach certain line length

1 answer

Your answer