"Do Not Allow Null" fields suddenly accept Nulls

A problem that has just reared up in the past week... all fields that are se
t
to "Not Null" are now allowing nulls. We can delete a record from the field
and the database saves it just fine. Normally it would prompt an error
message saying the field does not accept null data.
This just started happening and it is happening across all of the databases
on the server. I've checked several references on what may be causing this
but have come up with nothing.
Strange...and not very usefull for data integrity. Can someone shed some
light?Hi Scarfie
What does "delete a record from the field" mean. Delete only applies to
whole rows, and whole rows can always be deleted no matter what Nullability
setting you have.
Do you mean you're changing the value of the field to something else?
How are you making that change? I suggest you NOT use Enterprise Manager for
this, as it is not intended to be a data management tool. You may be
changing the value to blank, which is not the same as a null.
Try in Query Analyzer:
update my_table -- whatever the name of your table is
set my_column = NULL -- use one of the columns you think doesn't allow
nulls
where <supply a meaningful condition for this table>
Let us know what happens. If you get an error, show us. If not, show us the
DDL for the table:
exec sp_help my_table
Also, what version are you using?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
>A problem that has just reared up in the past week... all fields that are
>set
> to "Not Null" are now allowing nulls. We can delete a record from the
> field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
> databases
> on the server. I've checked several references on what may be causing
> this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?|||What do you mean by "delete a record from the field"? Did you mean "delete
the value from the column"? Can you explain the exact process you are
following to delete, as well as what tool you are using to do this? Is this
something the average user will be able to do easily?
If the data type is CHAR/VARCHAR, and you are deleting the string it
contains using the DEL key, please keep in mind that an empty string is NOT
NULL, it is an empty string. There is an important distinction there that
many people miss.
http://www.aspfaq.com/
(Reverse address to reply.)
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
> A problem that has just reared up in the past week... all fields that are
set
> to "Not Null" are now allowing nulls. We can delete a record from the
field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
databases
> on the server. I've checked several references on what may be causing
this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?

"Do Not Allow Null" fields suddenly accept Nulls

A problem that has just reared up in the past week... all fields that are set
to "Not Null" are now allowing nulls. We can delete a record from the field
and the database saves it just fine. Normally it would prompt an error
message saying the field does not accept null data.
This just started happening and it is happening across all of the databases
on the server. I've checked several references on what may be causing this
but have come up with nothing.
Strange...and not very usefull for data integrity. Can someone shed some
light?Hi Scarfie
What does "delete a record from the field" mean. Delete only applies to
whole rows, and whole rows can always be deleted no matter what Nullability
setting you have.
Do you mean you're changing the value of the field to something else?
How are you making that change? I suggest you NOT use Enterprise Manager for
this, as it is not intended to be a data management tool. You may be
changing the value to blank, which is not the same as a null.
Try in Query Analyzer:
update my_table -- whatever the name of your table is
set my_column = NULL -- use one of the columns you think doesn't allow
nulls
where <supply a meaningful condition for this table>
Let us know what happens. If you get an error, show us. If not, show us the
DDL for the table:
exec sp_help my_table
Also, what version are you using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
>A problem that has just reared up in the past week... all fields that are
>set
> to "Not Null" are now allowing nulls. We can delete a record from the
> field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
> databases
> on the server. I've checked several references on what may be causing
> this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?|||What do you mean by "delete a record from the field"? Did you mean "delete
the value from the column"? Can you explain the exact process you are
following to delete, as well as what tool you are using to do this? Is this
something the average user will be able to do easily?
If the data type is CHAR/VARCHAR, and you are deleting the string it
contains using the DEL key, please keep in mind that an empty string is NOT
NULL, it is an empty string. There is an important distinction there that
many people miss.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
> A problem that has just reared up in the past week... all fields that are
set
> to "Not Null" are now allowing nulls. We can delete a record from the
field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
databases
> on the server. I've checked several references on what may be causing
this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?

"Do Not Allow Null" fields suddenly accept Nulls

A problem that has just reared up in the past week... all fields that are set
to "Not Null" are now allowing nulls. We can delete a record from the field
and the database saves it just fine. Normally it would prompt an error
message saying the field does not accept null data.
This just started happening and it is happening across all of the databases
on the server. I've checked several references on what may be causing this
but have come up with nothing.
Strange...and not very usefull for data integrity. Can someone shed some
light?
Hi Scarfie
What does "delete a record from the field" mean. Delete only applies to
whole rows, and whole rows can always be deleted no matter what Nullability
setting you have.
Do you mean you're changing the value of the field to something else?
How are you making that change? I suggest you NOT use Enterprise Manager for
this, as it is not intended to be a data management tool. You may be
changing the value to blank, which is not the same as a null.
Try in Query Analyzer:
update my_table -- whatever the name of your table is
set my_column = NULL -- use one of the columns you think doesn't allow
nulls
where <supply a meaningful condition for this table>
Let us know what happens. If you get an error, show us. If not, show us the
DDL for the table:
exec sp_help my_table
Also, what version are you using?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
>A problem that has just reared up in the past week... all fields that are
>set
> to "Not Null" are now allowing nulls. We can delete a record from the
> field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
> databases
> on the server. I've checked several references on what may be causing
> this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?
|||What do you mean by "delete a record from the field"? Did you mean "delete
the value from the column"? Can you explain the exact process you are
following to delete, as well as what tool you are using to do this? Is this
something the average user will be able to do easily?
If the data type is CHAR/VARCHAR, and you are deleting the string it
contains using the DEL key, please keep in mind that an empty string is NOT
NULL, it is an empty string. There is an important distinction there that
many people miss.
http://www.aspfaq.com/
(Reverse address to reply.)
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
> A problem that has just reared up in the past week... all fields that are
set
> to "Not Null" are now allowing nulls. We can delete a record from the
field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
databases
> on the server. I've checked several references on what may be causing
this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?

'' The definition of object [object name] has changed since it was compiled'' error when alt

Hello All

Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.

I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:

There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.

[stored procedure name] and [trigger name] are where the actual names appear in the message.

I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).

Has anyone seen this, and if so, how can I fix it?

Thanks in advance for your help!

Jay

Try the Transact-SQL forum.

|||Is there a specific reason that you didn't move to compatibilty 9.0 after the upgrade. You can check and upgrade.

--check compatibility level
EXEC sp_dbcmptlevel '<DatabaseName>'--change compatibility level to 9.0
EXEC sp_dbcmptlevel '<DatabaseName>', '90'
I don't think, it is directly related to your issue though!
|||

Hi

Unfortunately, the database was upgraded before I joined the company, so I'm unsure as to the reason. I suspect they felt it would be better seeing the original database was on SQL Server 2000. I'll give this a try and see what happens. Thanks for the reply!

J

'' The definition of object [object name] has changed since it was compiled'' error when alt

Hello All

Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.

I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:

There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.

[stored procedure name] and [trigger name] are where the actual names appear in the message.

I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).

Has anyone seen this, and if so, how can I fix it?

Thanks in advance for your help!

Jay

Try the Transact-SQL forum.

|||Is there a specific reason that you didn't move to compatibilty 9.0 after the upgrade. You can check and upgrade.

--check compatibility level
EXEC sp_dbcmptlevel '<DatabaseName>'--change compatibility level to 9.0
EXEC sp_dbcmptlevel '<DatabaseName>', '90'
I don't think, it is directly related to your issue though!
|||

Hi

Unfortunately, the database was upgraded before I joined the company, so I'm unsure as to the reason. I suspect they felt it would be better seeing the original database was on SQL Server 2000. I'll give this a try and see what happens. Thanks for the reply!

J

'' The definition of object [object name] has changed since it was compiled'' error when alt

Hello All

Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.

I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:

There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.

[stored procedure name] and [trigger name] are where the actual names appear in the message.

I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).

Has anyone seen this, and if so, how can I fix it?

Thanks in advance for your help!

Jay

Try the Transact-SQL forum.

|||Is there a specific reason that you didn't move to compatibilty 9.0 after the upgrade. You can check and upgrade.

--check compatibility level
EXEC sp_dbcmptlevel '<DatabaseName>'--change compatibility level to 9.0
EXEC sp_dbcmptlevel '<DatabaseName>', '90'
I don't think, it is directly related to your issue though!
|||

Hi

Unfortunately, the database was upgraded before I joined the company, so I'm unsure as to the reason. I suspect they felt it would be better seeing the original database was on SQL Server 2000. I'll give this a try and see what happens. Thanks for the reply!

J

'' The definition of object [object name] has changed since it was compiled'' error when alt

Hello All

Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.

I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:

There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.

[stored procedure name] and [trigger name] are where the actual names appear in the message.

I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).

Has anyone seen this, and if so, how can I fix it?

Thanks in advance for your help!

Jay

Try the Transact-SQL forum.

|||Is there a specific reason that you didn't move to compatibilty 9.0 after the upgrade. You can check and upgrade.

--check compatibility level
EXEC sp_dbcmptlevel '<DatabaseName>'--change compatibility level to 9.0
EXEC sp_dbcmptlevel '<DatabaseName>', '90'
I don't think, it is directly related to your issue though!
|||

Hi

Unfortunately, the database was upgraded before I joined the company, so I'm unsure as to the reason. I suspect they felt it would be better seeing the original database was on SQL Server 2000. I'll give this a try and see what happens. Thanks for the reply!

J