8

Can we use the following query to update ANSI_NULLS option.

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = status | 0x20000000 where name = 'tblClient'

I am not sure what it is doing. I got this after searching for a solution. Our db is SQL server 2008.

But while i am running this i got an error like this "Ad hoc updates to system catalogs are not allowed."

Please give your suggestions

10

Updating system tables directly is unsupported and unsafe. The structure of them has changed since 2000 so the code you have won't work against them anyway.

It is sometimes possible to use ALTER TABLE ... SWITCH to do this as a metadata only change however.

A full worked example below.

/*Create table with option off*/ 
SET ANSI_NULLS OFF; 

CREATE TABLE dbo.YourTable (X INT) 

/*Add some data*/ 
INSERT INTO dbo.YourTable VALUES (1),(2),(3) 

/*Confirm the bit is set to 0*/ 
SELECT uses_ansi_nulls, * 
FROM   sys.tables 
WHERE  object_id = object_id('dbo.YourTable') 

GO 

BEGIN TRY 
    BEGIN TRANSACTION; 
    /*Create new table with identical structure but option on*/
    SET ANSI_NULLS ON; 
    CREATE TABLE dbo.YourTableNew (X INT) 

    /*Metadata only switch*/
    ALTER TABLE dbo.YourTable  SWITCH TO dbo.YourTableNew;

    DROP TABLE dbo.YourTable; 

    EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT'; 

    /*Confirm the bit is set to 1*/ 
    SELECT uses_ansi_nulls, * 
    FROM   sys.tables 
    WHERE  object_id = object_id('dbo.YourTable') 

    /*Data still there!*/ 
    SELECT * 
    FROM dbo.YourTable

    COMMIT TRANSACTION; 
END TRY 

BEGIN CATCH 
    IF XACT_STATE() <> 0 
      ROLLBACK TRANSACTION; 

    PRINT ERROR_MESSAGE(); 
END CATCH; 

One caveat however is that this does not always work. Adding a check constraint with definition CHECK (X <> NULL) causes it to fail with

ALTER TABLE SWITCH statement failed. Table 'YourTableNew' has a column level check constraint 'CK_YourTableNew_X__37A5467C' on column 'X' that is not loadable for semantic validation.

This makes sense as the semantics of the constraint do change with the setting.

If you encounter this you could consider temporarily removing the problematic constraints etc and adding them back after the switch or just biting the bullet and recreating the table in a more conventional way.

| improve this answer | |
  • 1
    Thank you.. But then what about the data in your table? – Mahesh KP Apr 9 '12 at 10:17
  • @mahesh - That's still there. Try running SELECT * FROM dbo.YourTable at the end of the demo code. The data pages are unchanged. Just the table definition is updated. – Martin Smith Apr 9 '12 at 10:18
  • we have got around 100 tables, So we have to do this thing for all the tables. Is there any option to do it more simply like in any one step configuration or any method to automate this thing..:) – Mahesh KP Apr 9 '12 at 10:36
  • @mahesh - You probably could automate it using SMO to script the CREATE table statement or TSQL similar to here. Make sure you have good error handling! – Martin Smith Apr 9 '12 at 10:45
  • I am creating the new table by using the script table as option in SSMS for the particular table. In that case the name of constraints like primary key and foreign key needs to be changed manually. Is any other opton to get rid off this manual editing.. – Mahesh KP Apr 10 '12 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.