How to view new JSON data type in SSMS from Azure SQL?

Question

I created a new Azure SQL database and used the Azure GUI to create a table with the new JSON data type (released May 2024). I then started the latest SSMS and connected to the Azure database. Next I tried to view the table design and received this error:

The table being loaded into memory has a user-defined data type ('json') that is not recognized. Close all of your open database diagram and table designer windows. The new data type will be recognized when you re-open the diagram or table designer.

So I closed all the items it asked me to and reopened the table designer and received the same error again. I closed SSMS and reopened and opened table designer and again received the same error. Do we need a specific version of SSMS to view the new json data type? I have version 20.1

Answer 1

Hello Brian,

It seems like you're encountering an issue with SQL Server Management Studio (SSMS) recognizing the new JSON data type in Azure SQL Database, even though you're using the latest version (20.1). Here are a few steps and considerations that might help resolve this issue:

Steps to Resolve SSMS Issue with JSON Data Type

1. Ensure SSMS is Up-to-Date:
   • SSMS version 20.1 should support the JSON data type in Azure SQL Database. However, it's always good to check for updates. You can download the latest version from the SQL Server Management Studio download page.
2. Clear Cache and Restart SSMS:
   • Sometimes, SSMS caches certain metadata which can cause issues. Try clearing the cache and restarting SSMS:
     • Close SSMS completely.
     • Open a command prompt and run: sqlservr.exe -c -m -s SSMS
     • This command starts SQL Server in single-user mode (-m) with an instance named SSMS. Let it run and then close the command prompt.
     • Restart SSMS and try opening the table designer again.
3. Use Transact-SQL (T-SQL) to Interact with JSON Data:
   • If the table designer in SSMS continues to show errors, consider using T-SQL queries to interact with your table and JSON data type. For example:

CREATE TABLE MyJsonTable (

Id INT PRIMARY KEY,

Data JSON

);

You can execute queries like NSERT, UPDATE, and SELECT to work with JSON data directly.

1. Check Azure SQL Database Compatibility Level:
   • Ensure that your Azure SQL Database is set to a compatibility level that supports JSON data types. Compatibility level 150 or higher is required for JSON support. You can check and set the compatibility level using Azure Portal or T-SQL: `ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150;
2. Feedback and Support:
   • If the issue persists after trying these steps, consider providing feedback to Microsoft through the SSMS interface or checking the [Azure SQL Database documentation (https://docs.microsoft.com/en-us/azure/azure-sql/database/json-data?tabs=azure-powershell) for any known issues or updates.

Conclusion

SSMS version 20.1 should theoretically support the new JSON data type in Azure SQL Database. By ensuring SSMS is up-to-date, clearing cache, and using T-SQL for direct interaction, you should be able to work with JSON data effectively.

Answer 2

FYI, Azure Data Studio shows the data type as [sys].[json]; however, in the column properties shows it as bigint. Also if I try to add a new column as type sys.json it is not an option and can't be freeform typed in.

Answer 3

It sounds like there might be some confusion or a specific issue with how Azure Data Studio (ADS) is handling the JSON data type in Azure SQL Database. Let's address each point you mentioned:

1. Review Azure Data Studio Documentation:
   • Check the Azure Data Studio documentation for any specific information or known issues related to displaying or working with JSON data types in Azure SQL Database.
2. Use Transact-SQL (T-SQL) for Column Creation:
   • If Azure Data Studio’s UI is not allowing you to add a column with the JSON data type, consider using T-SQL queries directly. For example:

     
          ALTER TABLE YourTableName
     
          ADD NewColumn JSON;
     
     

     This allows you to bypass any UI limitations and directly define the column with the JSON data type.
3. Feedback and Reporting Issues:
   • If you continue to experience issues after updating Azure Data Studio, consider providing feedback or reporting the issue through the Azure Data Studio interface or on the GitHub issues page for Azure Data Studio.

Answer 4

SSMS 21.0 Preview is still missing support for JSON data types

I can't find a workaround, it will just crash in random places

https://feedback.azure.com/d365community/idea/d2e6f106-9fb8-ef11-95f5-6045bdbfaf80