SqlException in stored procedure of an Azure SQL database that goes away when updating stored procedure without any changes

Question

SqlException in stored procedure of an Azure SQL database that goes away when updating stored procedure without any changes

Andreas Fandrich 20

We have a stored procedure in an Azure SQL database which uses OPENJSON to access a json value. The field containing the value is checked with ISJSON to make sure that it really contains valid json. This stored procedure randomly throws an SqlException with this message:
JSON text is not properly formatted. Unexpected character '<' is found at position 0.

This error goes away when we just do an ALTER PROCEDURE without actually changing anything in the procedure. What can we do to fix this completely inexplicable behavior?

Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-06-29T08:01:20.0033333+00:00

Any chance that you can share the procedure code?

Beware that this is a public forum, so if you post it here, you will be sharing the code with the world. If you don't want to share the code, maybe you can still answer this question: Is the ISJSON check and the call to OPENJSON in the same statement, or in separate statements?
Andreas Fandrich 20 Reputation points

2026-06-29T08:15:21.58+00:00

The OPENJSON and the ISJSON check are of course in the same statement. What I absolutely do not understand, why this error goes away when I just re-save the procedure without any changes.
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-06-29T08:41:30.9766667+00:00
Hi @Andreas Fandrich
Thankyou for reaching microsoft Q&A!
The error indicates that OPENJSON is receiving a value that isn't valid JSON. The Unexpected character '<' message usually means the input starts with HTML or some other non-JSON content.

Since you're already validating the column with ISJSON(), this behavior is unexpected. The fact that running ALTER PROCEDURE without making any changes temporarily resolves the issue may point to execution plan caching or recompilation behavior rather than an issue with the procedure itself.

To better understand what's happening, could you share:

A simplified version of the stored procedure, particularly the OPENJSON statement.

The database compatibility level.

Whether the issue occurs on the same rows each time or only intermittently.

Whether the JSON column is being updated while the procedure is executing.

You can also verify that only valid JSON values are being passed to OPENJSON by running:

SELECT * WHERE ISJSON(JsonColumn) = 0;

This additional information will help determine whether the issue is related to the data itself or to the way the query is being executed.
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-07-01T01:37:57.8866667+00:00

Hi @Andreas Fandrich
I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Andreas Fandrich 20 Reputation points

2026-07-01T09:52:15.69+00:00

@Pilladi Padma Sai Manisha I'm sorry, but your answer doesn't help at all. As already stated the SQL statement looks like this:
select *
from openjson(...)
where isjson(...)
To my understanding this should be enough to check, if openjson is only given valid json. And the thing is, that it works fine most of the time, but randomly fails with the mentioned error. And when it fails a simple re-save of the procedure fixes the error. What strange thing is going on here?
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-07-02T05:26:43.24+00:00

Thank you for the clarification. I understand that you're already using ISJSON() before calling OPENJSON, and that the procedure works correctly most of the time. Based on what you've described, I would also expect OPENJSON to receive only valid JSON. The intermittent nature of the issue, along with the fact that simply recompiling the procedure using ALTER PROCEDURE (without any code changes) temporarily resolves it, does make this behavior unusual.

To help investigate further, could you share a simplified version of the code around the ISJSON() and OPENJSON logic, along with the database compatibility level? It would also be helpful to know whether the JSON expression passed to OPENJSON is exactly the same expression being validated by ISJSON(), or if it is derived through joins, computed expressions, or functions, and whether the issue occurs only under concurrent workload or can also be reproduced in a single execution.

This additional information will help narrow down whether the behavior is related to the query execution plan or another factor affecting the procedure.

Andreas Fandrich 20

The database's compatibility level is SQL Server 2022 (160).

This is the sql causing the strange behavior. @instanceId is a uniqueidentifier which is passed as a parameter to the procedure.

SELECT rf.[FieldId], rf.[Name], rf.[Description], rf.[FieldType], rf.[AdditionalData],
 	   val.[ValueSmallText], val.[ValueLongText], val.[ValueNumber], val.[ValueDate], val.ValueBool, val.     [ValuePerson], persVal.[json]
FROM dbo.[InstanceField] rf
INNER JOIN dbo.[InstanceFieldValue] val ON rf.[FieldId] = val.[FieldId]
OUTER APPLY(
 	SELECT u.[Id] AS UserId, u.[Login], u.[Name], u.[Email],
 		t.[Id] AS TeamId, t.[IntId], t.[Title], t.[TypeId]
 	FROM (OPENJSON(val.[ValueLongText], '$.Users')
 			WITH ( Id uniqueidentifier '$.Id' ) userVal
 	FULL OUTER JOIN OPENJSON(val.[ValueLongText], '$.Teams')
 			WITH ( Id uniqueidentifier '$.Id' ) teamVal ON 1=2 --dumb but necessary
 	)
 	LEFT JOIN dbo.[User] u ON userVal.[Id] = u.[Id]
 	LEFT JOIN dbo.[Team] t ON teamVal.[Id] = t.[Id]
 	WHERE  ISJSON(try_cast(val.[ValueLongText] as json)) > 0
 	FOR JSON PATH
 ) AS persVal(json)
WHERE rf.[InstanceId] = @instanceId

Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-07-03T05:55:53.96+00:00

Thanks for sharing the query and confirming that the database is running at compatibility level 160. That additional context is very helpful.

From the query you've shared, one thing that stands out is that the OPENJSON() calls are executed as part of the OUTER APPLY, while the ISJSON() check is applied later in the subquery. Although it appears that ISJSON() should prevent invalid JSON from being processed, SQL Server does not guarantee the order in which these operations are evaluated. Depending on the execution plan, the optimizer may attempt to execute OPENJSON() before applying the ISJSON() filter.

The error message, "Unexpected character '<' is found at position 0", suggests that ValueLongText contains data beginning with <, which is typically HTML or XML rather than JSON. Even if those rows are intended to be filtered out, they may still be evaluated by OPENJSON() if the optimizer chooses that execution plan.

The fact that simply running ALTER PROCEDURE without making any changes temporarily resolves the issue also points toward an execution plan or recompilation-related behavior rather than a problem with the stored procedure definition itself.

To narrow this down further, could you let us know whether the error always occurs for the same @instanceId or if it happens with different values? It would also be helpful to verify the contents of ValueLongText for the row that causes the failure to see whether it actually starts with < or contains non-JSON data. As another test, could you check whether adding WITH RECOMPILE to the stored procedure or using OPTION (RECOMPILE) changes the behavior?

If possible, I'd also recommend trying a version of the query where the rows are filtered for valid JSON before OPENJSON() is invoked. If that version consistently works, it would strengthen the case that this is related to the optimizer's execution plan rather than the stored procedure logic itself.

If the behavior remains reproducible even after restructuring the query, especially on compatibility level 160, it may indicate a SQL Server engine issue. In that case, a minimal reproducible example, including the table definitions, sample data, and stored procedure, would be very helpful for further investigation.
Andreas Fandrich 20 Reputation points

2026-07-03T06:22:45.6+00:00

Thank you for your answer. I'll try Erland's suggestion from below first.

The error occurrs with various @instanceId and I cannot exactly tell which. There are very much rows. And yes, in some of them the column ValueLongText contains html.
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-03T09:00:53.7966667+00:00

To narrow this down further, could you let us know whether the error always occurs for the same @instanceId or if it happens with different values? It would also be helpful to verify the contents of ValueLongText for the row that causes the failure to see whether it actually starts with < or contains non-JSON data. As another test, could you check whether adding WITH RECOMPILE to the stored procedure or using OPTION (RECOMPILE) changes the behavior?

Pilladi, before suggesting various random ideas, I would suggest that you read my posts. I know what the problem is - and what the solution is.

I realise that since this is a PCS case that you as a support professional feel compelled to help. But the point with PCS is exactly this: Support professionals can get help from expert volunteers like me. As a support professionals you have to be jack-of-all-trades and try to help with problems where you don't have sufficient knowledge.

As for your suggestions, I would say that once the plan has changed to perform OPENJSON before the call to ISJSON it is going to happen for all @instanceID. Adding OPTION(RECOMPILE) or WITH RECOMPILE could result in different behaviour for different @instanceId, which would make it even more difficult to work with. (Right now Andreas has a workaround that is quite lame, but which works in the short run.)

So just sit back and relax. I'm taking care about this case.

Answer accepted by question author

Erland Sommarskog 137.5K MVP Volunteer Moderator

You are making the assumption that SQL Server would be intelligent enough to think "Ah, this call to ISJSON is there to check if the OPENJSON call will succeed. I better call ISJSON before I call OPENJSON". Certainly, not an unreasonable assumption, but, oh no. Rather SQL Server goes: "Hm, it seems more efficient to call OPENJSON on the spot and do that funny call to ISJSON later". In this age of AI, it is almost refreshing to hear about software that is just outright stupid.

When you reload the procedure, the current query plan is removed from the cache, and with the first value you use to call the procedure, SQL Server decides on a plan where it does thing in the correct order. But then some later event triggers recompilation, and at this point, the parameters inspires SQL Server to use the silly plan.

The solution is simple, change

OPENJSON(jsoncol)

to

OPENJSON (try_cast(jsoncol as json))

try_cast will return NULL for columns where the text is not json.

Andreas Fandrich 20 Reputation points

2026-06-29T08:57:52.35+00:00

Thank you very much for this answer. I changed the statement accordingly. It will propably take one or two days to see if this has any effect and I will definitely report here if it helped.
Andreas Fandrich 20 Reputation points

2026-07-01T09:47:40.3633333+00:00

Hi Erland,

i added your suggestion with the try_cast inside the isjson two days ago and now checked the logs. Unfortunately it didn't help and the error still occurrs. I have absolutely no idea what I coulkd try next. Anyway thanks for the hint.
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:00:55.6533333+00:00

I'm afraid then that we need to see the code of the procedure to understand this better.Although, one thing to check is that you are not jumping to conclusion on where the error occurs (that's a mistake I've made myself more than once). As I understand, you were looking at the logs. Do the logs tell you procedure name and line number?
Andreas Fandrich 20 Reputation points

2026-07-01T10:10:51.7466667+00:00

This is all inside a Net Web App which is used by our customers. So I'm not experiencing the error myself, but get the reports from the customers. I checked the logs to see when and how often this happens. The logs tell me the procedure name and the line number in the Net code, but not in the sql code.
The problems started a few weeks ago. When the error starts, it persists until the re-save of the procedure. As a first workaround we catch the exception in the code and then call an exact copy of the procedure.. This fallback also creates a special entry in the log so we can easily search for it. So far this works for our customers, but I wouldn't consider it a s solution.
I'll post an anonymized version of the sql statement, but it is actually quite simple a "select from openjson where isjson".
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:23:51.16+00:00

Sorry, the anonymised version is not enough. Particularly it does not show whether you applied the try_cast suggestion as I intended. I understand that you don't want to share code in a public forum, but it also reduces your chances to get support. (With Microsoft External Staff you can communicate over private message to evade the need of disclosing the code publicly, but that channel is not open for volunteer experts like me.)

Since you say "customers" - are all customers using the same database? Or does each customer have their own database? Did you deploy the fix to all databases?
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:27:14.3933333+00:00

To get an accurate idea of where the exception is occurring, you can set up an extended-events session that captures the event error_reported. I have an article on my web site, Monitor Exceptions in Your Database, https://www.sommarskog.se/Short%20Stories/trace-exceptions.html, that describes how to set up the session and describes a view and a stored procedure to get the information from the event session in an easy way.
Andreas Fandrich 20 Reputation points

2026-07-01T10:52:27.16+00:00

Thanks again, I will set this up.
And regarding your question about customers and databases: The database is built for multiple tenants. We offer the software as an saas solution hosted by us, but we also have customers with their own system set up in their azure environment. Some of them then use multiple tenants for the divisions of the company. So far the problem only occurred in our sdatabase, but not in any customer's system. Our database currently has a size of 300GB, but we have one customer who already has more than 100GB.

Erland Sommarskog 137.5K MVP Volunteer Moderator

It seems that you did not follow the instructions. It was the argument to OPENJSON you should change, not the argument to ISJON. Thus change:

SELECT rf.[FieldId], rf.[Name], rf.[Description], rf.[FieldType], rf.[AdditionalData],
 	   val.[ValueSmallText], val.[ValueLongText], val.[ValueNumber], val.[ValueDate], val.ValueBool, val.     [ValuePerson], persVal.[json]
FROM dbo.[InstanceField] rf
INNER JOIN dbo.[InstanceFieldValue] val ON rf.[FieldId] = val.[FieldId]
OUTER APPLY(
 	SELECT u.[Id] AS UserId, u.[Login], u.[Name], u.[Email],
 		t.[Id] AS TeamId, t.[IntId], t.[Title], t.[TypeId]
 	FROM (OPENJSON(val.[ValueLongText], '$.Users')
 			WITH ( Id uniqueidentifier '$.Id' ) userVal
 	FULL OUTER JOIN OPENJSON(val.[ValueLongText], '$.Teams')
 			WITH ( Id uniqueidentifier '$.Id' ) teamVal ON 1=2 --dumb but necessary
 	)
 	LEFT JOIN dbo.[User] u ON userVal.[Id] = u.[Id]
 	LEFT JOIN dbo.[Team] t ON teamVal.[Id] = t.[Id]
 	WHERE  ISJSON(try_cast(val.[ValueLongText] as json)) > 0
 	FOR JSON PATH
 ) AS persVal(json)
WHERE rf.[InstanceId] = @instanceId

to

SELECT rf.[FieldId], rf.[Name], rf.[Description], rf.[FieldType], rf.[AdditionalData],
 	   val.[ValueSmallText], val.[ValueLongText], val.[ValueNumber], val.[ValueDate], val.ValueBool, val.     [ValuePerson], persVal.[json]
FROM dbo.[InstanceField] rf
INNER JOIN dbo.[InstanceFieldValue] val ON rf.[FieldId] = val.[FieldId]
OUTER APPLY(
 	SELECT u.[Id] AS UserId, u.[Login], u.[Name], u.[Email],
 		t.[Id] AS TeamId, t.[IntId], t.[Title], t.[TypeId]
 	FROM (OPENJSON(try_cast(val.[ValueLongText] as json), '$.Users')
 			WITH ( Id uniqueidentifier '$.Id' ) userVal
 	FULL OUTER JOIN OPENJSON(try_cast(val.[ValueLongText] as json), '$.Teams')
 			WITH ( Id uniqueidentifier '$.Id' ) teamVal ON 1=2 --dumb but necessary
 	)
 	LEFT JOIN dbo.[User] u ON userVal.[Id] = u.[Id]
 	LEFT JOIN dbo.[Team] t ON teamVal.[Id] = t.[Id]
 	WHERE  ISJSON(val.[ValueLongText]) > 0
 	FOR JSON PATH
 ) AS persVal(json)
WHERE rf.[InstanceId] = @instanceId

Casting the argument to ISJSON does not help. The problem, as I explained, is that the call to OPENJSON could be evaluated before the call to ISJSON, because the silly optimizer thinks that this is more efficient. To make sure that the query does not die, you need to ensure that the argument that OPENJSON sees either valid JSON or a NULL value. The TRY_CAST achieves that, since it returns NULL if the conversion fails.

I used TRY_CAST because that was the first thing that came to my mind, but you could also use

OPENJSON(CASE WHEN ISJSON(val.ValueLongText) = 1 THEN val.ValueLongText ELSE NULL END)

The important thing is that the filter in the WHERE clause is not sufficient.

(This is quite a classic problem, just a new twist with JSON. A common pattern is something like this:

SELECT ..., cast(stringcol as int) * 2
FROM   tbl
WHERE  isnumeric(stringcol) = 1

People are blown away then when the query fails with a conversion error, when it logically should not. The remedy is to change cast to try_cast.)

Andreas Fandrich 20 Reputation points

2026-07-03T06:18:53.6433333+00:00

@Erland Sommarskog thank you very much for clarification. I wonder, how I could have been so stupid not to see that. Now it makes perfetly sense!

1 additional answer

Your answer

Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-06-29T08:01:20.0033333+00:00

Any chance that you can share the procedure code?

Beware that this is a public forum, so if you post it here, you will be sharing the code with the world. If you don't want to share the code, maybe you can still answer this question: Is the ISJSON check and the call to OPENJSON in the same statement, or in separate statements?
Andreas Fandrich 20 Reputation points

2026-06-29T08:15:21.58+00:00

The OPENJSON and the ISJSON check are of course in the same statement. What I absolutely do not understand, why this error goes away when I just re-save the procedure without any changes.
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-06-29T08:41:30.9766667+00:00

Hi @Andreas Fandrich
Thankyou for reaching microsoft Q&A!
The error indicates that OPENJSON is receiving a value that isn't valid JSON. The Unexpected character '<' message usually means the input starts with HTML or some other non-JSON content.

Since you're already validating the column with ISJSON(), this behavior is unexpected. The fact that running ALTER PROCEDURE without making any changes temporarily resolves the issue may point to execution plan caching or recompilation behavior rather than an issue with the procedure itself.

To better understand what's happening, could you share:

A simplified version of the stored procedure, particularly the OPENJSON statement.

The database compatibility level.

Whether the issue occurs on the same rows each time or only intermittently.

Whether the JSON column is being updated while the procedure is executing.

You can also verify that only valid JSON values are being passed to OPENJSON by running:

SELECT * WHERE ISJSON(JsonColumn) = 0;

This additional information will help determine whether the issue is related to the data itself or to the way the query is being executed.
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-07-01T01:37:57.8866667+00:00

Hi @Andreas Fandrich
I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Andreas Fandrich 20 Reputation points

2026-07-01T09:52:15.69+00:00

@Pilladi Padma Sai Manisha I'm sorry, but your answer doesn't help at all. As already stated the SQL statement looks like this:
select *
from openjson(...)
where isjson(...)
To my understanding this should be enough to check, if openjson is only given valid json. And the thing is, that it works fine most of the time, but randomly fails with the mentioned error. And when it fails a simple re-save of the procedure fixes the error. What strange thing is going on here?
Pilladi Padma Sai Manisha 11,715 Reputation points Microsoft External Staff Moderator

2026-07-02T05:26:43.24+00:00

Thank you for the clarification. I understand that you're already using ISJSON() before calling OPENJSON, and that the procedure works correctly most of the time. Based on what you've described, I would also expect OPENJSON to receive only valid JSON. The intermittent nature of the issue, along with the fact that simply recompiling the procedure using ALTER PROCEDURE (without any code changes) temporarily resolves it, does make this behavior unusual.

To help investigate further, could you share a simplified version of the code around the ISJSON() and OPENJSON logic, along with the database compatibility level? It would also be helpful to know whether the JSON expression passed to OPENJSON is exactly the same expression being validated by ISJSON(), or if it is derived through joins, computed expressions, or functions, and whether the issue occurs only under concurrent workload or can also be reproduced in a single execution.

This additional information will help narrow down whether the behavior is related to the query execution plan or another factor affecting the procedure.
Andreas Fandrich 20 Reputation points

2026-07-02T06:02:26.9066667+00:00

The database's compatibility level is SQL Server 2022 (160).

This is the sql causing the strange behavior. @instanceId is a uniqueidentifier which is passed as a parameter to the procedure.

SELECT rf.[FieldId], rf.[Name], rf.[Description], rf.[FieldType], rf.[AdditionalData], val.[ValueSmallText], val.[ValueLongText], val.[ValueNumber], val.[ValueDate], val.ValueBool, val. [ValuePerson], persVal.[json] FROM dbo.[InstanceField] rf INNER JOIN dbo.[InstanceFieldValue] val ON rf.[FieldId] = val.[FieldId] OUTER APPLY( SELECT u.[Id] AS UserId, u.[Login], u.[Name], u.[Email], t.[Id] AS TeamId, t.[IntId], t.[Title], t.[TypeId] FROM (OPENJSON(val.[ValueLongText], '$.Users') WITH ( Id uniqueidentifier '$.Id' ) userVal FULL OUTER JOIN OPENJSON(val.[ValueLongText], '$.Teams') WITH ( Id uniqueidentifier '$.Id' ) teamVal ON 1=2 --dumb but necessary ) LEFT JOIN dbo.[User] u ON userVal.[Id] = u.[Id] LEFT JOIN dbo.[Team] t ON teamVal.[Id] = t.[Id] WHERE ISJSON(try_cast(val.[ValueLongText] as json)) > 0 FOR JSON PATH ) AS persVal(json) WHERE rf.[InstanceId] = @instanceId
Andreas Fandrich 20 Reputation points

2026-07-03T06:22:45.6+00:00

Thank you for your answer. I'll try Erland's suggestion from below first.

The error occurrs with various @instanceId and I cannot exactly tell which. There are very much rows. And yes, in some of them the column ValueLongText contains html.
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-03T09:00:53.7966667+00:00

To narrow this down further, could you let us know whether the error always occurs for the same @instanceId or if it happens with different values? It would also be helpful to verify the contents of ValueLongText for the row that causes the failure to see whether it actually starts with < or contains non-JSON data. As another test, could you check whether adding WITH RECOMPILE to the stored procedure or using OPTION (RECOMPILE) changes the behavior?

Pilladi, before suggesting various random ideas, I would suggest that you read my posts. I know what the problem is - and what the solution is.

I realise that since this is a PCS case that you as a support professional feel compelled to help. But the point with PCS is exactly this: Support professionals can get help from expert volunteers like me. As a support professionals you have to be jack-of-all-trades and try to help with problems where you don't have sufficient knowledge.

As for your suggestions, I would say that once the plan has changed to perform OPENJSON before the call to ISJSON it is going to happen for all @instanceID. Adding OPTION(RECOMPILE) or WITH RECOMPILE could result in different behaviour for different @instanceId, which would make it even more difficult to work with. (Right now Andreas has a workaround that is quite lame, but which works in the short run.)

So just sit back and relax. I'm taking care about this case.
Andreas Fandrich 20 Reputation points

2026-06-29T08:57:52.35+00:00

Thank you very much for this answer. I changed the statement accordingly. It will propably take one or two days to see if this has any effect and I will definitely report here if it helped.
Andreas Fandrich 20 Reputation points

2026-07-01T09:47:40.3633333+00:00

Hi Erland,

i added your suggestion with the try_cast inside the isjson two days ago and now checked the logs. Unfortunately it didn't help and the error still occurrs. I have absolutely no idea what I coulkd try next. Anyway thanks for the hint.
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:00:55.6533333+00:00

I'm afraid then that we need to see the code of the procedure to understand this better.Although, one thing to check is that you are not jumping to conclusion on where the error occurs (that's a mistake I've made myself more than once). As I understand, you were looking at the logs. Do the logs tell you procedure name and line number?
Andreas Fandrich 20 Reputation points

2026-07-01T10:10:51.7466667+00:00

This is all inside a Net Web App which is used by our customers. So I'm not experiencing the error myself, but get the reports from the customers. I checked the logs to see when and how often this happens. The logs tell me the procedure name and the line number in the Net code, but not in the sql code.
The problems started a few weeks ago. When the error starts, it persists until the re-save of the procedure. As a first workaround we catch the exception in the code and then call an exact copy of the procedure.. This fallback also creates a special entry in the log so we can easily search for it. So far this works for our customers, but I wouldn't consider it a s solution.
I'll post an anonymized version of the sql statement, but it is actually quite simple a "select from openjson where isjson".
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:23:51.16+00:00

Sorry, the anonymised version is not enough. Particularly it does not show whether you applied the try_cast suggestion as I intended. I understand that you don't want to share code in a public forum, but it also reduces your chances to get support. (With Microsoft External Staff you can communicate over private message to evade the need of disclosing the code publicly, but that channel is not open for volunteer experts like me.)

Since you say "customers" - are all customers using the same database? Or does each customer have their own database? Did you deploy the fix to all databases?
Erland Sommarskog 137.5K Reputation points MVP Volunteer Moderator

2026-07-01T10:27:14.3933333+00:00

To get an accurate idea of where the exception is occurring, you can set up an extended-events session that captures the event error_reported. I have an article on my web site, Monitor Exceptions in Your Database, https://www.sommarskog.se/Short%20Stories/trace-exceptions.html, that describes how to set up the session and describes a view and a stored procedure to get the information from the event session in an easy way.
Andreas Fandrich 20 Reputation points

2026-07-01T10:52:27.16+00:00

Thanks again, I will set this up.
And regarding your question about customers and databases: The database is built for multiple tenants. We offer the software as an saas solution hosted by us, but we also have customers with their own system set up in their azure environment. Some of them then use multiple tenants for the divisions of the company. So far the problem only occurred in our sdatabase, but not in any customer's system. Our database currently has a size of 300GB, but we have one customer who already has more than 100GB.
Andreas Fandrich 20 Reputation points

2026-07-03T06:18:53.6433333+00:00

@Erland Sommarskog thank you very much for clarification. I wonder, how I could have been so stupid not to see that. Now it makes perfetly sense!

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more