How do I do this in SQL?
I have a table I'm querying with the following fields (simplified
here):
facility_name char(15), drug_product char(64), total_cost money
How can I produce the following in SQL so my result can answer the
question
What are the top 10 drugs dispensed by facility_id when looking at
total_cost?
output should look like this:
facility_id drug_product total_cost
houston Simvastatin 500
houston Sertraline 400
...
next 8 for houston
...
chicago Epoetin 1000
chicago Atorvastatin 800
...
next 8 for chicago
...
etc.
I'm thinking this has to done in more than one statement since TOP will
not work here (as far as I can see) I miss the sequential record
"file" environment of foxpro/access here. I can do this in either of
those 2 apps with about 1 min of coding but I want to learn how to do
this in a SQL tbl.Without DDLs, here is a skeleton query:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.facility_id = t1.facility_id
AND t2.total_cost >= t1.total_cost ) <= 10 ;
You have not specified how you'd want to resolve the ties. If ties are
involved, change the correlation like:
WHERE t2.facility_id = t1.facility_id
AND ( t2.drug_product = t1.drug_product
AND t2.total_cost >= t1.total_cost )
OR t2.total_cost >= t1.total_cost )
In t-SQL you can easily use a TOP clause with ORDER BY to achieve similar
results. You may also want to refer to www.aspfaq.com/2120 for some similar
ideas
Anith|||Supposing there are no ties, then:
select a.*
from t as a
where (select count(*) from t as b where b.facility_name = a.facility_name
and b.total_cost <= a.total_cost) <= 10
AMB
"mwrobe" wrote:
> How do I do this in SQL?
> I have a table I'm querying with the following fields (simplified
> here):
> facility_name char(15), drug_product char(64), total_cost money
> How can I produce the following in SQL so my result can answer the
> question
> What are the top 10 drugs dispensed by facility_id when looking at
> total_cost?
> output should look like this:
> facility_id drug_product total_cost
> houston Simvastatin 500
> houston Sertraline 400
> ...
> next 8 for houston
> ...
> chicago Epoetin 1000
> chicago Atorvastatin 800
> ...
> next 8 for chicago
> ...
> etc.
> I'm thinking this has to done in more than one statement since TOP will
> not work here (as far as I can see) I miss the sequential record
> "file" environment of foxpro/access here. I can do this in either of
> those 2 apps with about 1 min of coding but I want to learn how to do
> this in a SQL tbl.
>|||Thank you both gentlemen. It looks like I need to read up and
experiment more on subqueries. Thanks for the link as well.
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Friday, March 16, 2012
"Timeout Expired" on large table change
I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple. :(
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
>> "Timeout expired. The timeout period elapsed prior to completion of the
>> operation or the server is not responding."
>> I can't figure out what's causing the problem.
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple. :(
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple. :(
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
>> "Timeout expired. The timeout period elapsed prior to completion of the
>> operation or the server is not responding."
>> I can't figure out what's causing the problem.
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple. :(
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.
Saturday, February 25, 2012
"Macro" statement
Is it possible to write a macro statement using Transact-SQL?
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino
Monday, February 13, 2012
"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?
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?
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?
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?
Subscribe to:
Posts (Atom)
