Saving Changes is Not Permitted - Table Design View - Allow Nulls - workaround

[Rx_]

The opinion of other Access designers using a SQL Server back end is requested.
Was this the best option for the situation? Is it a risky option?
This could be of interest to anyone that migrates an Access Database into SQL Server.

In SQL Server, while in a Table Design view, a field was changed to Allow Nulls. The SQL Server SSMS error "Saving changes is not permitted..."
SQL Server expected the entire table to be dropped and recreated for this change.

Instead, the SQL Server Tool Options "Prevent saving changes that require table re-creation" was turned off.
Then the Table Design allowed the "allow Nulls".
The Tool Options "Prevent saving changes that require table recreation" was put back into normal.

History: This change came from migrating an Access table into SQL Server five years ago. The table and data were inherited from a previous developer. The past developer wan't consistent with the Allow Nulls.

The Access Front End checked for data before transactions could be run on the entire row. So, this Allow Nulls was not an issue for many years.
This week, new regulatory requirements boiled down to the need for Allow Nulls on a few fields in this table.

The attachment shows the error message on top if the Allow Nulls are checked in the Table Design and saved.

To turn off this Warning, the Options dialogue box is shown in the center.

Important: In the Table Properties: Change Tacking, if either of these had been set to True, research shows the above results could have been catastrophic (a bad outcome).

Conclusion: Since most MSACCESS users migrate the database to SQL Server where the Change Tracking is false, this process is an acceptable way to change Allow Nulls in SQL Server without dropping and recreating the table.

It is not the intention to mislead anyone. If others can review this and comment, it would be greatly appreciated!

 

[Ranman256]

you could try renaming the entire field.
build it over with nulls.
then copy the data over.

BUT you may get 'the table must be dropped' msg.
I hate that thing.

 

[Rx_]

That is a good point.
A script could have added a new field with the Allow nulls.
An update query could move the data over to the new field.
Then, the old field could be renamed.
And, the new field renamed to the old field.
That would take care of tables that had the Table's Change Tracking set to True.

Since the conditions appeared to be safe, by turning off the warning (in SQL Options) and then just going into the Table Design and clicking the Allow Nulls then Saving, the warning didn't come up.

 

[kevlray]

I get the same message trying to modify a table from SQL Server Management Studio. I used to be able to do before SQL 2008. So I have learned how to run T-SQL code to modify the table without having to re-create the table.

 

[SQL_Hell]

I get the same message trying to modify a table from SQL Server Management Studio. I used to be able to do before SQL 2008. So I have learned how to run T-SQL code to modify the table without having to re-create the table.

Yep this is the correct thing to do, I did the same.

Learn "Alter Table" syntax, then you don't need to rebuild a table. Rebuilding tables is ok in development, if you must....but in production with a large amount of records it is a big no no.

Another thing to consider here is now the execution plans are affected once you've made the changes from NOT NULL to NULL, it has a drastic effect on performance.

 

[kevlray]

I need to learn more about execution plans. For better or worse, they do not let me create my own databases very often (just real little ones, for very small projects).

 

[Rx_]

That is a great point I didn't mention.
On smaller lookup tables, I have coded to drop and rebuilt.
This wan't that big of a table, maybe 80,000 records.
The result deleted all the data, ... but my setting was changed.
Lucky me... there is a [dbname]T (for test) to try out different things first.
It is my preference to backup production, restore it on test, try something out, then apply it to Production. Since the DBName is the same plus the T, it is easy to modify the script.

Thanks again everyone. When being the sole site expert in programming to the DBA, it is very valuable to get feedback from others.

 

[SQL_Hell]

Good stuff,

Just one thing to add, if you use Alter table the execution time for setting NOT NULL would be instant, compared to the time it takes to reinsert 80,000 rows.

Give it a try on your test DB, example syntax is


ALTER TABLE [People] ALTER COLUMN [Name] VARCHAR(40) NOT NULL

 

[Rx_]

The command parsed correctly, but errors and didn't change anything.
Of course... there is always something else
When one of the fields hit this from my method, it actually helped modify the other field that had access.

I would agree it should work in other situations.

use MyTestDatabase
go
ALTER TABLE wells_SHLBHL ALTER COLUMN [Footage1_FNLFSL] VARCHAR(3) NOT NULL;
go

Msg 5074, Level 16, State 1, Line 1
The object 'SSMA_CC$Wells_SHLBHL$Footage1_FNLFSL$disallow_zero_length' is dependent on column 'Footage1_FNLFSL'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Footage1_FNLFSL failed because one or more objects access this column.

 

[SQL_Hell]

Hi,

I assume 'SSMA_CC$Wells_SHLBHL$Footage1_FNLFSL$disallow_zer o_length' is a constraint, in which case you need to drop the constraint, alter the column and then recreate the constraint again.

 

[Rx_]

As usual, you are spot on. Totally agree with that assessment.
In most cases where we DBA want to understand exactly what is changing, I would probably tend to say it is the best way to proceed.

The method in my top post warned me of that in the other table and seemed to take care of it. While allowing Microsoft "automated assistance" to make all the changes shouldn't be taken lightly, in this case the relationships appeared to be simple and the changes work out.
Again, these are somewhat simple DB constructs migrated from Access to SQL Server.

Thanks for pointing this kind of thing out. Anyone using the method presented in the beginning should:
1. Make a backup of the SQL Database
2. Test the results including the other tables affected.
3. Most likely avoid the easy way if there are too many tables with joins and constraints or more complex Referential Integrity (RI).

 

[WayneRyan]

Rx,

In Management Studio, look for:

Tools > Options > Table and Database Designers

Uncheck the option "Prevent saving changes that require table re-creation"

Management studio will do all of this "behind the scenes".

Agree with the above points about learning, but it does save some time to
have the software help you out.

Wayne

 

[WayneRyan]

Rx,

Oops ... After rereading I see that you saw that option.

In ORACLE there is not choice. You must recreate the table(s).

Is SSMS, I tend to let the software do it. It will drop/refresh all of the
constraints etc. Much easier for me to let it do all the work.

Wayne

 

[SQLWayne]

I need to learn more about execution plans. For better or worse, they do not let me create my own databases very often (just real little ones, for very small projects).

Grant Fritchey has a free ebook available at Redgate's site on how to understand execution plans.

If you want something more complex in terms of data models, I have info on downloading the old Pubs or Northwind databases on my blog. And you can download Adventureworks from Microsoft, I think that's the one that Fritchey uses for his sample code, but don't quote me on that.

And if you want something REALLY complex, you can download the Stack Overflow database. It's also really BIG, something like 7-8GIG of 7Zip, expands to 35 gig or so.

 

[kevlray]

Grant Fritchey has a free ebook available at Redgate's site on how to understand execution plans.

If you want something more complex in terms of data models, I have info on downloading the old Pubs or Northwind databases on my blog. And you can download Adventureworks from Microsoft, I think that's the one that Fritchey uses for his sample code, but don't quote me on that.

And if you want something REALLY complex, you can download the Stack Overflow database. It's also really BIG, something like 7-8GIG of 7Zip, expands to 35 gig or so.

Thanks for the info. I am sure our team will make use of this ebook.