"The SQL Server cannot obtain a LOCK resource at this time" error

Hi there,
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
Ken Wells
President
Smart Computing Solutions, LLC
All it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC
|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (o
r
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedur
e
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Acce
ss
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data
(or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't ge
t
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 o
n
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 databa
se
> application on PC-2 that needs to modify the SQL data on PC-1. I have tri
ed
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways
of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Acce
ss
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update
to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding update
s
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I trie
d
> to ask Microsoft (using my one free question) but my (valid) Product ID wa
s
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on o
ne
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"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?