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 107K Reputation points

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.
NIKHILANETHIKUNTA 155 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 107K Reputation points

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 @NIKHILANETHIKUNTA

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

Share via

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