SQL Server Performance Forum – Threads Archive

Keep Dependency

I updated a table’s design and that was provide a default value for two bit fields and made them Not Nullable. When I tried to save the table design, I got a message that the table’s dependency with a UDF and a View would be lost. After that I noticed that the table has no dependency to mentioned UDF and View. Why this happened and how can I restore the dependency as before table change? I appreciate all replies.

If the table allready has null value in any fiels , you have to set any value first and then change to Not Nullable.
Luis Martin
Moderator
SQL-Server-Performance.com

Luis, I did provide values before I change to Not Nullable and I was able to save new situation. The problem is that I’ve lost the table’s dependency with a function and a view. Why this happened and how can I correct it? If I don’t care about this dependency what might happen?

quote:Originally posted by LuisMartin If the table allready has null value in any fiels , you have to set any value first and then change to Not Nullable.
Luis Martin
Moderator
SQL-Server-Performance.com

What was the design of the table before you updated the same? Anyway, I always recommend using ISQLW for such things and I suspect you are seeing this erroe in EM. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Gaurav, Here is the table structure: [Id] [int] IDENTITY (1, 1) NOT NULL ,
[PrimaryNumber] [int] NULL ,
[AccountNumber] [int] NULL ,
[LName] [varchar] (100) NULL ,
[LAddress] [varchar] (100) NULL ,
[LCity] [varchar] (50) NULL ,
[LState] [char] (2) NULL ,
[LZip] [numeric](18, 0) NULL ,
[LPA] [bit] NULL ,
[LPADate] [datetime] NULL ,
[ContactName] [varchar] (100) NULL ,
[ContactPhone] [varchar] (15) NULL ,
[STD] [bit] NULL ,
[TC] [bit] NULL ,
[LI] [bit] NULL Once again, I applied the following changes on a backup version and lost dependancy with the function and view, too. I did:
1. Updated the Null BIT fields to 0 (initial value).
2. changed the type of those fields to Not Null.
3. Provide initial value for the fields. Thanks,

Verify this by running sp_depends, before and after the modification.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>