Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@MartinSmith : Thanks you.. while searching i found this query to update the ansi_nulls option of an existing table. Is it possible using this query? –  mahesh Apr 9 '12 at 9:55
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
1  
Thank you.. But then what about the data in your table? –  mahesh 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
    
thank you very much... working perfectly.. –  mahesh Apr 9 '12 at 10:30
    
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 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
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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