Recently, I wrote (a rather long) post on Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server. As part of my research, I encountered a few interesting points about the current (and future) exception handling mechanisms in Microsoft SQL Server. I thought of sharing them with you via this post.

The days of RAISERROR are numbered

Almost for time immemorial now, RAISERROR has been a handy command for database developers and administrators alike. However, RAISERROR has a very major drawback in the fact that by default, it was unable to re-raise the original error and also unable to maintain the originating line number of an error. (You can re-raise error numbers from 13000 through 2147483647, but not 50000.)

However, despite of this, RAISERROR is a very useful, and widely used command – and I would go out on a limb here and say that no production application exists that does not use RAISERROR. Unfortunately, if we look at the preview documentation of SQL 11 (“Denali”), we are in for a surprise:

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. New applications should use THROW instead.” (Reference: http://msdn.microsoft.com/en-us/library/ms178592%28SQL.110%29.aspx)

The days of RAISERROR are numbered.

THROW – A comparison with RAISERROR

THROW is a command introduced in SQL 11 (“Denali”) to overcome the limitations of RAISERROR. You can read more about this from Books On Line at: http://msdn.microsoft.com/en-us/library/ee677615(SQL.110).aspx. The THROW command allows us to re-raise an error message, thus allowing us to raise the exception throw a series of nested programming objects. However, today, let us explore briefly a comparison between the RAISERROR and THROW commands, as mentioned in the “Denali” preview documentation.

Raising system and custom messages

RAISERROR had the ability to raise system messages, and also custom messages. Let us see if THROW can do the same.

--Select a system message which is less than 50000
SELECT TOP 1 *
FROM sys.sysmessages 
WHERE msglangid = 1033 and error < 50000
ORDER BY error DESC

--Attempt to RAISERROR and THROW this system message
RAISERROR (49903, 16, 1);
THROW 49903, 'RAM related message.', 1

Following is the output indicating that THROW cannot throw a system error message. When explicitly specified, the value of the error_number parameter in THROW must be greater than or equal to 50000 and less than or equal to 2147483647.

Msg 49903, Level 16, State 1, Line 8

Detected (null) MB of RAM. This is an informational message; no user action is required.

Msg 35100, Level 16, State 10, Line 9

Error number 49903 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

Now, let us try to raise a non-system message, that is not defined in sys.sysmessages

--Attempt to RAISERROR and THROW a non-system message, not defined in sys.sysmessages
RAISERROR (51000, 16, 1);
THROW 51000, 'This is a custom error!', 1

Following is the output indicating that RAISERROR failed because it failed to find the corresponding error message in the sys.sysmessages table. THROW however, succeeded in raising the error.

Msg 18054, Level 16, State 1, Line 2

Error 51000, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Msg 51000, Level 16, State 1, Line 3

This is a custom error!

Now, let us define a custom error message is sys.sysmessages and attempt to raise a non-system message:

--Add a new custom error message
EXEC sys.sp_addmessage @msgnum = 51000, @severity = 16, @msgtext  = N'This is a custom error! Error parameters are: %d.';

--Attempt to RAISERROR and THROW a non-system message
RAISERROR (51000, 16, 1);
THROW 51000, 'This is a custom error!', 1

Here’s the output. Both RAISERROR and THROW worked fine.

Msg 51000, Level 16, State 1, Line 5

This is a custom error! Error parameters are: (null).

Msg 51000, Level 16, State 1, Line 6

This is a custom error!

Handling message formatting

RAISERROR allows for substitution of parameters in the error message.

--Attempt to RAISERROR the non-system message created earlier
RAISERROR (51000, 16, 1, 999);

Here’s the output:

Msg 51000, Level 16, State 1, Line 2

This is a custom error! Error parameters are: 999.

Let’s see if THROW allows us to do this:

--Attempt to THROW the non-system message created earlier
THROW 51000, 'This is a custom error! Error parameter: %d', 1, 999

Here’s the output

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘,’.

THROW can therefore, not handle parameterization of message in the way that RAISERROR does. There is however, one other way. Here’s how you can do this:

--Attempt to THROW the non-system message created earlier
DECLARE @ErrorMsg VARCHAR(100) = FORMATMESSAGE(51000,999);
THROW 51000, @ErrorMsg, 1

Here’s the output:

Msg 51000, Level 16, State 1, Line 3

This is a custom error! Error parameters are: 999.

In this aspect, THROW and RAISERROR provide similar functionality, and RAISERROR can be replaced with minimal changes to the code. Luckily, FORMATMESSAGE is not being deprecated.

Changing Severity levels

RAISERROR allows us to specify severity levels greater than 18 if we use the WITH LOG option:

--Attempt to RAISERROR the non-system message created earlier - this time with a severity of 24
RAISERROR (51000, 24, 1, 999) WITH LOG;

Here’s the output:

Msg 2745, Level 16, State 2, Line 2

Process ID 55 has raised user error 51000, severity 24. SQL Server is terminating this process.

Msg 2745, Level 16, State 2, Line 2

Process ID 51 has raised user error 51000, severity 24. SQL Server is terminating this process.

Msg 51000, Level 24, State 1, Line 2

This is a custom error! Error parameters are: 999.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Unfortunately, THROW does not come with any way to set the severity level, when used outside of a TRY…CATCH construct. Within a TRY…CATCH construct, THROW obeys the severity level of the exception being raised. 

Please NOTE:

When used outside of a TRY…CATCH construct, THROW results in termination of the session.

Some other differences:

The following are some differences that I noticed:

1. Because we cannot change the severity level, there is no way to THROW a non-severe error
2. It is required that the statement before a THROW must end in a semi-colon (;)

Conclusion

Whether THROW is good or better than RAISERROR, I will leave the it up to the reader to decide. According to me, THROW has it’s advantages over RAISERROR, but needs to mature a little more. Any new code that I write for SQL 11 (“Denali”) will have THROW in them – it is better to start the migration from RAISERROR now itself rather than wait for the release of SQL Server after “Denali” when RAISERROR will simply fail to work.

Do you know of any other differences between RAISERROR and THROW? If yes, share them here, or write a blog post, and link back to this post.

Until we meet next time,

Be courteous. Drive responsibly.

As we continue our journey through the “Underappreciated features of SQL Server”, this week are are looking at a few of the T-SQL enhancements that the community felt did not get the deserved attention. This was in response to Andy Warren’s editorial of the same name on SQLServerCentral.com.

Today, we will look at exception handling in T-SQL using the TRY…CATCH statements introduced since SQL Server 2005. Because this is now almost 6 years old, I was a bit surprised to see this in the list of underappreciated features. Hence, while I will touch upon the essential points, I will provide pointers that would help you to get started on the use of TRY…CATCH and then explore a new related feature introduced in SQL 11 (“Denali”). I am posting this on a Thursday, because I will be covering the exception handling options from SQL 7/2000 onwards and also so that it the reader gets some time to experiment with the various options presented here over the week-end.

Exception handling in T-SQL – before SQL Server 2005

Before SQL Server 2005, exception handling was very primitive, with a handful of limitations. Let’s see how exception handling was done in the days of SQL Server 7/2000:

BEGIN TRANSACTION ExceptionHandling
   DECLARE @ErrorNum INT
   DECLARE @ErrorMsg VARCHAR(8000)

   --Divide by 0 to generate the error
   SELECT 1/0

   -- Error handling in SQL 7/2000 
   -- Drawback #1: Checking for @@Error must be done immediately after execution fo a statement
   SET @ErrorNum = @@ERROR
   IF @ErrorNum <> 0 
   BEGIN
      -- Error handling in SQL 7/2000 
      -- Drawback #2: When returning an error message to the calling program, the Message number,
      --              and location/line# are no longer same as the original error.
      --              Explicit care must be taken to ensure that the error number & message
      --              is returned to the user.
      -- Drawback #3: Error message is not controlled by the application, but by the T-SQL code!
      -- (Please note that there is only one 'E' in RAISERROR)
      SELECT @ErrorMsg = description FROM sysmessages WHERE error = @ErrorNum
      RAISERROR ('An error occured within a user transaction. Error number is: %d, and message is: %s',16, 1, @ErrorNum, @ErrorMsg) WITH LOG
      ROLLBACK TRANSACTION ExceptionHandling
   END

IF (@ErrorNum = 0)
   COMMIT TRANSACTION ExceptionHandling

While a revolutionary method in those days, to our “developed” eyes, a couple of issues that jump right as us:

1. You cannot monitor a batch for Exceptions/error, i.e. you cannot be sure that a particular block of code will be able to “catch” all errors that might happen in another block of code somewhere else
2. The checking for the return value of the @@ERROR system function is where most mistakes happen. This has to be checked immediately after the execution of the T-SQL statement that you want to monitor
3. You can inform the calling program/application about the error, however the error number, error description and originating line number change – the error number is now that of RAISERROR() and the error message is custom. The originating line number is that of the line from where the call to RAISERROR is made. When used with the “WITH LOG” option, this is big drawback when doing a post-error analysis
4. Because the error message is custom, the calling application has no control over the nature of the message (e.g. sometimes, we might want to customize the message text that is displayed to the users) – this cannot be done

Clearly, with the advent of nested operations and multi-tier architecture and patterns, this had a lot of drawbacks, and the world was in need of something new.

Exception handling in T-SQL – SQL Server 2005/2008

SQL Server 2005 saw a major change in the history and architecture of Microsoft SQL Server. SQL Server now started using the Visual Studio platform and was now Microsoft .NET framework based. One of the things that this move gave to SQL Server was the fact that it could now leverage the built-in TRY…CATCH functionality provided by the framework. To know more about this functionality, please refer an article from our esteemed MVP, Mr. Pinal Dave (blog).

Let’s see how our code would look and work when we implement TRY…CATCH blocks:

BEGIN TRANSACTION ExceptionHandling
   -- Error handling in SQL 2005 & up
   -- Monitor an entire batch of queries for errors
   BEGIN TRY
      --Some valid operations here
      SELECT 1/1

      --Divide by 0 to generate the error
      SELECT 1/0

      --Some valid operations here
      SELECT 1/2

      COMMIT TRANSACTION ExceptionHandling
   END TRY
   BEGIN CATCH
      -- Error handling in SQL 2005 & up
      -- Error information can be returned to the calling code as a result set
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;

      -- Or via RAISERROR
      DECLARE @ErrorNumber    INT            = ERROR_NUMBER()
      DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE()
      DECLARE @ErrorProcedure NVARCHAR(4000) = ERROR_PROCEDURE()
      DECLARE @ErrorLine      INT            = ERROR_LINE()

      RAISERROR ('An error occurred within a user transaction. 
                  Error Number        : %d
                  Error Message       : %s  
                  Affected Procedure  : %s
                  Affected Line Number: %d'
                  , 16, 1
                  , @ErrorNumber, @ErrorMessage, @ErrorProcedure,@ErrorLine)
       
      IF @@TRANCOUNT > 0
         ROLLBACK TRANSACTION ExceptionHandling
   END CATCH

As you can see, most of the problems are now resolved. Microsoft SQL Server provides specific functions that would return the correct information about an error occurring from anywhere within a block of monitored code (i.e. the code in “TRY”). One problem remains however – if your code uses RAISERROR, you would still have the problem of an incorrect error number (if not embedded within the message).

The basic structure and use of TRY…CATCH is quite simple as demonstrated above. There is much more to TRY…CATCH, which I will leave it upon the reader to explore.

Exception handling in T-SQL – SQL 11 (Code named: “Denali”)

The remaining drawback of RAISERROR mentioned above and the solution provided by Microsoft in it’s upcoming SQL Server release (SQL 11 – Code named: “Denali”) is handled very well by the renowned MVP, Mr. Jacob Sebastian (blog) here.

SQL 11/”Denali” comes with a new THROW command that helps the T-SQL code to re-raise the same error that has been encountered in a monitored block of code. While I was reading Jacob’s article, a question cropped up in my mind as to whether we could re-raise errors in a nested hierarchy. The concept of “inner exceptions” is still foreign to SQL Server, but THROW does allow for nesting. Please find below the script I developed to experiment with nesting exception-handling:

IMPORTANT NOTE: The THROW command must be preceded with a statement ending in a semi-colon (;)

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InnerProc' AND type = 'P')
    DROP PROCEDURE InnerProc
GO

CREATE PROCEDURE InnerProc
AS
BEGIN
    BEGIN TRANSACTION ExceptionHandling
       BEGIN TRY
          PRINT 'In the TRY block of the Inner Procedure...'
          SELECT 1/1

          RAISERROR('An error occured in the Inner procedure.',17,1)  --Line #10 considering CREATE PROC...as Line #1

          COMMIT TRANSACTION ExceptionHandling
       END TRY
       BEGIN CATCH
          SELECT ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;

          IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION ExceptionHandling

          PRINT 'Throwing error from the CATCH block of the INNER Procedure...';   
          --Preceding statement MUST be a semi-colon ';'
          THROW
       END CATCH
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'OuterProc' AND type = 'P')
    DROP PROCEDURE OuterProc
GO
CREATE PROCEDURE OuterProc
AS
BEGIN
    BEGIN TRY
        PRINT 'In the TRY block of the Outer Procedure...'
        EXEC InnerProc
    END TRY
    BEGIN CATCH
        PRINT 'In the CATCH block of the Outer Procedure...';
        --Preceding statement MUST be a semi-colon ';'
        THROW
    END CATCH
END
GO

--Executing the outer procedure
EXEC OuterProc

Edit (October 15, 2011): You can explore the differences between THROW and RAISEERROR in my post here – Sunset for RAISERROR and sunrise for THROW – SQL 11 (“Denali”) (End Edit)

Phew!…That was a long one. I hope that you now have a good overview of the powerful exception handling that Microsoft SQL Server provides. In the next release of your product, ensure that all our T-SQL code is protected from unhandled exceptions.

If you want to read more on exception handling, you can refer Erland Sommarskog’s (blog) post here.

If you have any good exception handling tips to share, do let us know by leaving your feedback.

Until we meet next time,

Be courteous. Drive responsibly.

This is part of the series of posts that I am running under the title “Under Appreciated Features of SQL Server“. The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Table-valued parameters are a new parameter type in SQL Server 2008. Because a lot of production systems still run Microsoft SQL Server 2005, I am not too surprised to find this on the underappreciated features list.

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Creating user-defined table types

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. Therefore, the creation of user defined table types is similar to creating a table. Let’s create a table data type for handling UNIQUEIDENTIFIER values:

USE AdventureWorks2008
GO
-- Create a GUIDTableType to hold a table of GUIDs/UNIQUEIDENTIFIERs
CREATE TYPE GUIDTableType AS TABLE 
    ( GUIDValue UNIQUEIDENTIFIER )
GO

Once created, you can use the following DMV to get a list of available table data types:

USE AdventureWorks2008
GO
SELECT * FROM sys.table_types

OR use the SSMS Object Explorer

Create routines consuming the user-defined table type

Let us create simple programming routines that consume the user-defined table type created above:

USE AdventureWorks2008
GO
IF EXISTS (SELECT * FROM sys.objects where name = 'proc_GetSalesOrdersbyRowGuid' AND type = 'P')
    DROP PROCEDURE proc_GetSalesOrdersbyRowGuid
GO
CREATE PROCEDURE proc_GetSalesOrdersbyRowGuid
    @SalesOrderRowGuids GUIDTableType READONLY
AS
BEGIN
    SELECT Sales.SalesOrderDetail.*
    FROM @SalesOrderRowGuids SalesOrderRowGuids
    INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.rowguid = SalesOrderRowGuids.GUIDValue
END
GO

USE AdventureWorks2008
GO
IF OBJECT_ID(N'dbo.func_SalesOrderDetailInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.func_SalesOrderDetailInformation;
GO
CREATE FUNCTION dbo.func_SalesOrderDetailInformation
    (@SalesOrderRowGuids dbo.GUIDTableType READONLY)
RETURNS @retSalesOrderDetailInformation TABLE 
(
    -- Columns returned by the function
    [SalesOrderID]          [INT]          NOT NULL, 
    [SalesOrderDetailID]    [INT]          NOT NULL,
    [CarrierTrackingNumber] [NVARCHAR](25) NULL,
    [OrderQty]              [SMALLINT]     NOT NULL,
    [ProductID]             [INT]          NOT NULL,
    [SpecialOfferID]        [INT]          NOT NULL,
    [UnitPrice]             [MONEY]        NOT NULL,
    [UnitPriceDiscount]     [MONEY]        NOT NULL,
    [LineTotal]             [NUMERIC]      NOT NULL,
    [ModifiedDate]          [DATETIME]     NOT NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    INSERT @retSalesOrderDetailInformation
    SELECT Sales.SalesOrderDetail.SalesOrderID,
           Sales.SalesOrderDetail.SalesOrderDetailID,
           Sales.SalesOrderDetail.CarrierTrackingNumber,
           Sales.SalesOrderDetail.OrderQty,
           Sales.SalesOrderDetail.ProductID,
           Sales.SalesOrderDetail.SpecialOfferID,
           Sales.SalesOrderDetail.UnitPrice,
           Sales.SalesOrderDetail.UnitPriceDiscount,
           Sales.SalesOrderDetail.LineTotal,
           Sales.SalesOrderDetail.ModifiedDate
    FROM @SalesOrderRowGuids SalesOrderRowGuids
    INNER JOIN Sales.SalesOrderDetail ON Sales.SalesOrderDetail.rowguid = SalesOrderRowGuids.GUIDValue
    
    RETURN
END
GO

Consuming the user-defined table type

Remember how we said that user-defined table types are similar to tables? That’s true! Here’s how to consume the newly user defined table type:

USE AdventureWorks2008
GO

DECLARE @SalesOrders GUIDTableType

INSERT INTO @SalesOrders VALUES ('B207C96D-D9E6-402B-8470-2CC176C42283'),
                                ('04C4DE91-5815-45D6-8670-F462719FBCE3'),
                                ('80667840-F962-4EE3-96E0-AECA108E0D4F'),
                                ('E9D54907-E7B7-4969-80D9-76BA69F8A836'),
                                ('AC769034-3C2F-495C-A5A7-3B71CDB25D4E')

EXEC proc_GetSalesOrdersbyRowGuid @SalesOrders

SELECT * FROM func_SalesOrderDetailInformation(@SalesOrders)

Here’s what you get for output from the stored procedure:

Here’s what you get for output from the function:

Please note that table data types cannot be targets of SELECT INTO and INSERT EXEC statements. It can however, be in the FROM clause of either of these. Also, just as is the case with other parameters, a table data type is scoped to the stored procedure, function or dynamic Transact-SQL text as applicable.

For application programmers:

Per Books On Line, “Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.” So, there you have it – pointers and by-reference passing are still alive and kicking!

Benefits of the table valued table types

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and there can be nothing better than the list provided by MSDN:

• Do not acquire locks for the initial population of data from a client
• Provide a simple programming model
• Enable you to include complex business logic in a single routine
• Reduce round trips to the server
• Can have a table structure of different cardinality
• Are strongly typed
• Enable the client to specify sort order and unique keys

Do you use user defined table data types? If yes, how do you find this feature? Do let us know!

Until we meet next time,

Be courteous. Drive responsibly.

For those of you who visit BeyondRelational regularly, you would have noticed that the web-site now has a new feature called “Ask”. The feature is a “personal forum” and allows you to approach one of the SQL Server experts associated with BeyondRelational directly with your queries. It facilitates a one-on-one interaction, which is what I prefer. The feature is still in the beta stage but with your support, it will definitely be a successful one.

Recently on the “Ask” feature, I received a question by one of the readers asking me how to clear out the SSMS history. For those of you who work with multiple SQL servers with multiple logins, the “Connect to Server” window seems to fill up very quickly, and starts to look something like this. Sometimes, these servers might not even be in existence (e.g.. if you frequently refresh your virtual RND/test environments).

You might notice that there is no way to clear out this history. So, how do you go about clearing out the clutter that working with SSMS generates?

The Manual Way

The following is a series of manual steps that you need to follow to clear out the SSMS history:

1. Close all open instances of SSMS on your workstation
2. Depending upon your version of the SSMS client tools, navigate to and delete the following:

SSMS Client Tools Version	Path	File to Delete
SQL 11 (“Denali”)	%USERPROFILE%AppDataRoamingMicrosoftSQL Server Management Studio11.0	SqlStudio.bin
SQL 2008	%USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server100ToolsShell	SqlStudio.bin
SQL 2005	%USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server90ToolsShell	mru.dat

3. Launch SSMS
4. It might take a little while to launch, as it recreates the “SqlStudio.bin”/”mru.dat”. Once launched, you will see that all SSMS history is gone! All that remains is the list of servers installed on my test server

The Automated Way

Unfortunately, there is not wizard or button that one can click and magically clear out the history. A Microsoft Connect case has already been logged for introduction of the same (you can read the case here). Let’s hope that by the time the first of the SQL 11 RCs come out, this feature is part of them.

Until we meet next time,

Be courteous. Drive responsibly.

I just had a busy travelling week-end, and hence today, we will start things off this week with a little incident that I encountered a while back. As with any development team, we have a bunch of servers lying around that are shared across the team purely for RND purposes. I had to run a particular test on one of these environments and that’s when this happened.

The situation was that my test required me to enable the xp_cmdshell feature of Microsoft SQL Server 2008. Hence, I proceeded with the following set of scripts, which are pretty straight-forward and “should” work on all environments, provided I have the appropriate permissions. I was connected to the server with the “sa” login and used the RECONFIGURE command so that I do not need a SQL Server restart for the changes to take effect.

sp_configure 'show advanced options',1;
reconfigure
go

sp_configure 'xp_cmdshell',1;
reconfigure
go

Although the scripts are quite simple and was connected using the highest possible privileges, the SQL Server just would not allow me to apply the changes. The error that it threw was as under:

Msg 5808, Level 16, State 1, Line 3

Ad hoc update to system catalogs is not supported.

As the test was somewhat urgent, I proceeded with the following work-around.

sp_configure 'show advanced options',1;
reconfigure with override
go

sp_configure 'xp_cmdshell',1;
reconfigure with override
go

Later in the day, I did some research and found that a team member had been toying around with the SQL Server as a practical hands-on experience for one of the examinations that the team member was preparing for. However, he had forgotten to roll-back the changes made by him before he put the server back into the “available” repository – which is what was causing the problem.

SQL Server has a setting called – “allow updates”. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it is doing in our case. Running the following statement to reset this back to 0 did the trick and I was now able to use RECONFIGURE again to reset the environment back to the “factory default”.

-- Reset the "allow updates" setting to the recommended 0
sp_configure 'allow updates',0;
reconfigure with override
go

-- Reset the environment back as the test is complete
sp_configure 'xp_cmdshell',0;
reconfigure
go

sp_configure 'show advanced options',0;
reconfigure
go

Lessons Learnt

This incident has refreshed in my memory the two basic rules that I have been taught by my father (way back when I was introduced to computers with Windows 3.1!)

1. Irrespective of whether you are doing a production change or a local RND change, always keep a log of the changes made
2. Once done, if the change was a purely researching change, always roll the environment back to the condition you found it in

Here’s something I don’t understand

By default, a user cannot see the advanced configuration options. Even when you run sp_configure on a clean install of SQL Server 2008, the “allow updates” option is right at the top! Why? If the product is not expecting users to use this setting, it should be hidden from the users view. If a user sees something, he/she is bound to use it.

If you work with a product and if you get a say during product evolution, request you to ensure that things which a user should not be using are either hidden from the user’s view, or are at least hard to access.

Until we meet next time,

Be courteous. Drive responsibly.