If you've stumbled upon this post it most likely means that you need to change the type and/or format of one or more table column of a SQL Server using a standard ALTER query avoiding a Command Timeout and - most importantly without having to restart the whole Database.
The Problem
Let's take for example the following Attachments SQL table:
|
1 2 3 4 5 |
CREATE TABLE [dbo].[Attachments]( [ID] [int] IDENTITY(1,1) NOT NULL, [Data] [image] NULL, [FileName] [nvarchar](250) NULL, [Description] [nvarchar](250) NULL) |
And let's say we want to change the Description column from nvarchar(250) to nvarchar(MAX). Needless to say, the table is live on a production server and contains hundreds of thousands records, therefore any attempt to change that data type using the following ALTER query:
|
1 |
ALTER TABLE Attachments ALTER COLUMN Description nvarchar(max) NULL; |
Would likely hit a Connection Timeout.
What can we do?
The Fix
The best workaround I found to overcome such scenario is to take advantage of SP_CONFIGURE, a built-in Stored Procedure which can be used to display or change global configuration settings for the SQL server. More specifically, the setting that we want to change is "remote query timeout", which defaults to 600 (seconds).
The plan is to bring it to 0, execute our query (without risking a timeout anymore) and then setting back to 600 immediately after. Here's how we can do that:
|
1 2 3 4 5 |
EXEC SP_CONFIGURE 'remote query timeout', 0; reconfigure; ALTER TABLE Attachments ALTER COLUMN Description nvarchar(max) NULL; EXEC SP_CONFIGURE 'remote query timeout', 600; reconfigure; |
However, before doing that, it might be the case of executing SP_CONFIGURE in display mode in order to be 100% sure that the default value to restore is 600:
|
1 |
EXEC SP_CONFIGURE; |
If that's not the case, just change the relevant value in the previous query accordingly.
