Azure data studio table designer error execution time out

Question

An error occurred while generating preview report: One or more errors occurred. (Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)

at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)

at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)

at System.Threading.Tasks.Task.Wait()

at Microsoft.Data.Tools.Sql.DesignServices.TableDesigner.TableDesigner.get_TableRowCount()

at Microsoft.Data.Tools.Sql.DesignServices.TableDesigner.TableDesigner.get_IsLargeTable()

at Microsoft.Data.Tools.Sql.DesignServices.CommitJob.CreatePreviewReport()

at Microsoft.Data.Tools.Sql.DesignServices.TableDesigner.TableDesigner.GenerateReport()

at Microsoft.SqlTools.SqlCore.TableDesigner.TableDesignerManager.GeneratePreviewReport(TableInfo tableInfo) in /_/src/Microsoft.SqlTools.SqlCore/TableDesigner/TableDesignerManager.cs:line 125

at Microsoft.SqlTools.ServiceLayer.TableDesigner.TableDesignerService.<>c__DisplayClass21_0.<<HandleGeneratePreviewReportRequest>b__0>d.MoveNext() in /_/src/Microsoft.SqlTools.ServiceLayer/TableDesigner/TableDesignerService.cs:line 127

--- End of stack trace from previous location ---

at Microsoft.SqlTools.ServiceLayer.TableDesigner.TableDesignerService.<>c__DisplayClass16_0`1.<<HandleRequest>b__0>d.MoveNext() in /_/src/Microsoft.SqlTools.ServiceLayer/TableDesigner/TableDesignerService.cs:line 81

Answer 1

That's a size-of-data operation, and those can take quite some time if the table is large. In a way, it is sort of silly to have a query timeout for operations that can take long time. Then again, you could also say that it is a wake-up call that maybe this is something you should not run from a UI.

You can think of things like the Table Designer as a tri-cycle. It may help to get you started, but as you grow up, you move over to a proper two-wheeled bicycle. That is, you learn to use DDL (Data Definition Language.) In this case, it is simple:

ALTER TABLE tbl ALTER COLUMN col nvarchar(50) [NOT] NULL

I've put NOT in brackets, since I don't know the current nullability for the column. But you need to repeat it, or else it may change.

Answer 2

Hello Ganbaatar Bold,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you you are having issues with Azure data studio table designer error execution time out.

To resolve your issue:

First thing to do is to increase Execution Timeout in Azure Data Studio by Go to File > Preferences > Settings in Azure Data Studio. And search for timeout. Then, adjust the SQL: Execution Timeout to a higher value, e.g., 600 seconds (10 minutes). - https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver16 and https://learn.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-ver16

Secondly, if the table is large, follow these best practices:

1. To perform operation in Smaller Batches if the table's size is a problem, split the operation into manageable batches using a temporary table:

     SELECT TOP (1000) *
     INTO TempTable
     FROM your_table_name;
     ALTER TABLE TempTable ALTER COLUMN your_column_name NVARCHAR(50) NOT NULL;

2. To perform indexing and locking considerations, large operations on indexed tables can be slow. Temporarily drop the index, alter the column, and recreate the index:

     DROP INDEX IndexName ON your_table_name;
     ALTER TABLE your_table_name ALTER COLUMN your_column_name NVARCHAR(50) NOT NULL;
     CREATE INDEX IndexName ON your_table_name(column_name);

3. Avoid Long Locks, if altering the column locks the table for an extended time, consider performing this during maintenance windows to minimize disruption.

https://stackoverflow.com/questions/38568359/how-to-alter-column-from-nvarcharmax-to-nvarchar50 and https://dba.stackexchange.com/questions/199649/will-altering-an-nvarcharmax-column-to-nvarchar50-lock-the-table

Thirdly, use Azure Data Studio Alternatives for large tables, it might be beneficial to perform the operation in SQL Server Management Studio (SSMS), which offers more robust tools for managing timeouts and connections. https://www.geeksforgeeks.org/disadvantages-of-always-using-nvarcharmax-in-sql and https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Use the associated links to read more for deeper understanding and steps.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.