x

Changing the size of a column referenced by a schema-bound view in SQL Server

Hi, I'm trying to change the size of a column in sql server using:

 ALTER TABLE [dbo].[Address]    ALTER COLUMN [Addr1] [nvarchar](80) NULL

where the length of Addr1 was originally 40.

It failed, raising this error:

 The object 'Address_e' is dependent on column 'Addr1'. ALTER TABLE ALTER COLUMN Addr1 failed because one or more objects access this column.

I've tried to read up on it and it seems that because some views are referencing this column and it seems that SQL Server is actually trying to drop the column that raised the error.

Address_e is a view created by the previous DBA.

Is there any other way I can change the size of the column?

Thank you

Hari

more ▼

asked Dec 06 at 06:19 AM in Default

avatar image

kosmik5
11 1 3

Thank you anthony

4 days ago kosmik5
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Script out the Address_e view as drop and create, drop the view, modify the column, recreate the view.

As the view is schemabound, the only way to modify the underlying schema is to remove the views dependency on binding.

more ▼

answered Dec 06 at 09:18 AM

avatar image

anthony.green
3k 1 4 6

@kosmik5 Because the view needs to be dropped and created anew, please make sure that you script out the explicit grants to it (if any). After the view is dropped, Addr1 column is altered, and the view is created, apply the explicit grants. This needs to be done in order to avoid a situation when some login which was able to select data from the view is now greeted with permission denied error.

4 days ago Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x134
x21

asked: 4 days ago

Seen: 25 times

Last Updated: 4 days ago

Copyright 2017 Redgate Software. Privacy Policy