Working with new data types in SQL Server can sometimes lead to unexpected errors. In this blog post, we will explore one such error – error 13603 – that occurs when using JSON custom paths.
When working with JSON documents in SQL Server, it is common to customize the output using the FOR JSON PATH clause. However, there are certain rules and restrictions that need to be followed to avoid errors.
Let’s take a look at an example that resulted in error 13603:
SELECT 1 as '.SQLAuth', 2 as 'SQLAuth..Pinal' FOR JSON PATH
When executing this query, you will encounter the following error:
Msg 13603, Level 16, State 1, Line 1 Property '.SQLAuth' cannot be generated in JSON output due to an invalid character in the column name or alias. Column names or aliases that contain '..', start or end with '.' are not allowed in queries that use the FOR JSON clause.
The error message clearly indicates that the column alias ‘.SQLAuth’ contains an invalid character (‘.’) that is not allowed in the JSON output. To resolve this error, we need to ensure that the column alias conforms to the rules specified by FOR JSON PATH.
It is worth noting that this error is specific to JSON output and does not occur when using XML PATH. However, a similar error, known as error 6850, is reported when illegal characters are found in the column or column alias used in FOR XML PATH.
Here is an example of the same mistake with XML PATH:
SELECT 1 as '.SQLAuth', 2 as 'SQLAuth..Pinal' FOR XML PATH
Executing this query will result in the following error:
Msg 6850, Level 16, State 1, Line 1 Column name '.SQLAuth' contains an invalid XML identifier as required by FOR XML; '.' (0x002E) is the first character at fault.
Although the error message is slightly different, it serves the same purpose of indicating an invalid character in the column or column alias used in the XML output.
It is important to be aware of these errors and ensure that column names and aliases adhere to the rules specified by the respective output formats (JSON or XML). By following these guidelines, you can avoid encountering error 13603 or error 6850 and ensure smooth execution of your queries.
Thank you for reading this blog post. Stay tuned for more articles on SQL Server concepts and best practices!