sql error: returned

Showing posts with label returned. Show all posts

Friday, February 24, 2012

"IS NOT NULL" is not working

We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegroups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?|||Try,
where ISDATE(dateofbusiness) <> 0
--
Message posted via http://www.sqlmonster.com|||2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
--
Message posted via http://www.sqlmonster.com|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegroups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||And to emphasize futher the same statement with the count(*) function
returned no results. And without the count function and group by it
will return results, however, when keying in on an additional field
such as storeid like "where storeid = 34 and DateofBusiness is not
null" nothing returns. If you filter by store id, i see dateofbusiness
had values in it?
set transaction isolation level read uncommitted
select
dateofbusiness,
count (*)
from
Temp_HstSalesByInterval
where dateofbusiness is not null
group by
dateofbusiness|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorrectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
--
Message posted via http://www.sqlmonster.com|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
Maybe we are wrong about the zeros. But threw that out for you,
Jon
--
Message posted via http://www.sqlmonster.com|||> Why or how did you manage to write all zeros on a date/time field? In SQL
there is no such thing as 00:00:00?
Midnight is not a valid time?|||create table lookatdate (d datetime null)
insert lookatdate values ('09/04/2004')
select * from lookatdate
returns 2004-09-04 00:00:00.000|||This never happenend. Jon mistook reading one field as two fields.
09/23/2004 00:00:00:000|||> This never happenend. Jon mistook reading one field as two fields.
> 09/23/2004 00:00:00:000
Okay, maybe another reason to visit the link from FAQ #5006 to generate
insert statements, instead of presenting sample data in heap style...
http://vyaskn.tripod.com/code.htm#inserts|||Well maybe there something to what you are saying, but why does it work
in the other databases on the same server?|||Ken,
I have another suggestion which may sound odd. But consider this:
"Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL."
"The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. (Microsoft)"
I am thinking that when you build the resultset with "not null" it evaluates the criteria as "unknown" and thereby won't produce any resultset. But when you trim or otherwise convert the datatype then the query returns a resultset.
Try a couple other approaches to see if you get a resultset:
select * from tblName
where not (datefield) is null
-or-
select somefield where datefield is not null
I am also wondering if building the resultset with the all "*" isn't masking the issue either? But either way, NOT NULL evaluates to a true condition if the value is not null. Otherwise, I think the field "as defined" is unknown.
I tried to replicate your error with a test database and couldn't. I tried several db_options and ansi null defaults and still couldn't reproduce your error. I do vaguely remember this problem, but can't for the life of me remember what cause it or how I fixed it.
Sorry I couldn't be more of a help,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
The first suggestion is to make sure that FKstoreid 34 actually has data.
If so, then if the query works on other databases on the same server, that
would suggest something is peculiar to the data in that database.
What happens if you restore that particular db to another SQL server with
the same version and run the query? If it still returns no results, that
would suggest definitely something in the data is causing the issue.
At this stage in SQL Server 2000's lifecyle, it seems unlikely that your
data has uncovered a bug in the query engine, but it's possible. Try adding
an identity column as a primary key and see if that makes a difference.
Maybe grouping on a date column in a heap...?
Ron
--
Ron Talmage
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104273359.147443.196280@.f14g2000cwb.googlegroups.com...
> And to emphasize futher the same statement with the count(*) function
> returned no results. And without the count function and group by it
> will return results, however, when keying in on an additional field
> such as storeid like "where storeid = 34 and DateofBusiness is not
> null" nothing returns. If you filter by store id, i see dateofbusiness
> had values in it?
> set transaction isolation level read uncommitted
> select
> dateofbusiness,
> count (*)
> from
> Temp_HstSalesByInterval
> where dateofbusiness is not null
> group by
> dateofbusiness
>|||Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||I'm wondering if it's possible to create a new table and populate it with
the contents of the old one - preferably by bcp'ing the data out and back in
again.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104333999.756145.81940@.f14g2000cwb.googlegroups.com...
Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||Hi Jon
select * from tblName
where not (datefield) is null
this produces no records
select somefield where datefield is not null
This will work, but when you add "and fkstoreid = anynumber" nothing
returns. If you query only on fkstoreid only you will see the results
and dateofbusiness is populated. I did an update to this table to set
datetime field with refreshed values and that did not fix it.
The actual select statement lists the fields not (*). both have the
same affect.|||> select somefield where datefield is not null
> This will work, but when you add "and fkstoreid = anynumber" nothing
> returns.
This does not seem possible. How large is your database? I am wondering if
you would be able to post a copy of it online and someone could try to
reproduce the problem on your actual data. I am guessing there is something
else inaccurate here that you're not catching...|||Hi Tom,
We did this with DTS.|||Ken,
Another (way out there) suggestion. Maybe, just maybe, there is a constraint on the fkStoreId between parent and child? I know this sounds weird, but maybe when you consider the where clause is failing when you combine both the fkStoreId and DateofBusiness fields in the same statement.
The condition is returning false for some reason. I know it's really a long shot, but could there be an issue with the Stores table referential integrity? Is the fkStoreId an orphan? I seriously doubt that this is the culprit, but weirder things have been know to happen in SQL world.
I did create your temp table and inserted rows of data, but couldn't reproduce the error. I don't know what the parent table looks like so I could do anything on that thread.
Good luck,
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||This is great news. It's hard to say what happened here. If you have a
copy of the database before you did the DBREINDEX, I'd be tempted to run
DBCC CHECKTABLE and see if it found anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104344333.997556.246980@.c13g2000cwb.googlegroups.com...
Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||Jon, when I recreated the nonunique clustered index DateOfBusiness
order was changed from descending to ascending. "IS NOT NULL" on
dateofbusiness works. When it was set back to descending it doesn't
work.
On the same table in other databases, descending does not have an issue
with the "IS NOT NULL" not working. However, this table has 3 million
records and the tables in the other databases have less than 200,000
records.
Throughing this out to see if you can recreate the issue?|||Ken,
Sorry if you did this already, but have you posted the result of
SELECT @.@.VERSION ? Also, is there anything you can see different with
this particular database, such as a compatibility level setting or
something?
Steve Kass
Drew University
Ken wrote:
>Jon, when I recreated the nonunique clustered index DateOfBusiness
>order was changed from descending to ascending. "IS NOT NULL" on
>dateofbusiness works. When it was set back to descending it doesn't
>work.
>On the same table in other databases, descending does not have an issue
>with the "IS NOT NULL" not working. However, this table has 3 million
>records and the tables in the other databases have less than 200,000
>records.
>Throughing this out to see if you can recreate the issue?
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||There has a been a security hotfix issued since then. That would bring the
version to 8.00.818:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104349480.164105.285990@.c13g2000cwb.googlegroups.com...
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||Hm. Are the "working" databases on the same instance of SQL Server? If
not, and the others are a later version,
try installing 8.00.818 (or later) and see if the problem goes away.
There are sometimes bug fixes in security hotfixes, since the hotfixes
are cumulative. If that's not the problem, I'll see if I can come up
with some other questions!
SK
Ken wrote:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
>Edition on Windows NT 5.2 (Build 3790: )
>compatability on all databases 8.0
>
>|||Ken,
"nonunique clustered index DateOfBusiness order was changed from descending to ascending. "IS NOT NULL" on dateofbusiness works. When it was set back to descending it doesn't work."
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
I was leading toward the index as my next suggestion. The index is a composite and is made up of different datatypes. When you were doing the query the index is backwards (desc) order for date and acs for the other two fields. That's probably why the query never returned anything. I can replicate this error on a test database with 30 million call records using essentially the same logic. I'll let you know later what happens.
I probably wouldn't use mix order for my indices. Maybe that was what I had done in the past and cause a problem? Anyway, glad you were able to correct the problem.
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, please replicate the error. Why does it only happen on tables
with a large number of rows?
My other thought could still be a data issue depending on what gets
evaluated first?|||> My other thought could still be a data issue depending on what gets
> evaluated first?
The WHERE clause does not work that way. Put the individual clauses in any
order, and the result will be the same...|||Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||I hear ya. Nevertheless, the patch is a security hotfix and closes a
vulnerability. As another poster mentioned, fixes are cumulative and it is
possible that your issue may have been addressed there.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104353652.360283.184350@.f14g2000cwb.googlegroups.com...
Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||Ken,
Maybe this newsgroup thread will help. Sorry I didn't remember this,
but it looks to me like the same bug you're seeing, and 8.00.818 fixed it:
http://groups-beta.google.com/groups?hl=en&q=8.00.818+fix+bug+kass+-turkish&qt_s=Search+Groups
SK
Ken wrote:
>Hi Tom,
>The hotfix does not address the particular problem in any
>documentation. We have a lot of servers (clustered) and my boss never
>lets me do anything on a guess, only until I have found the problem and
>I can qualify it. If I could validate that the problem was server wide
>and this occurrance was a documented bug or issue, I would implement
>this asap.
>I do appreciate your assistance. Your suggestion may be a fix to this,
>but I'm on a supertanker thats not easy to steer or deploy changes.
>
>|||Jon, I have a sneaky suspicioun that its in the data. When we move the
same data to another table it produces the same error. If we move or
create different data it does not. Weird.

"IS NOT NULL" is not working

"Invalid Cursor State" - REPOST

MS-SQL 2000, Service Pack 3a and 4
I have customers that are starting to get "Invalid Cursor State" messages
returned from my Clarion MS-SQL. based application.
It appears to happen when i am issuing a "SELECT SUM(TOTAL) FROM..."
statement, but i can be sure of that. I cant pinpoint when the problem
starts. The solution has been to close the screen im on, which closes
connection to the database.
Where can i start to look for this problem?
TIA
Tim MorrisonTim Morrison wrote:
> I have customers that are starting to get "Invalid Cursor State"
> messages returned from my Clarion MS-SQL. based application.
>
Tried reading this article?
http://support.microsoft.com/kb/831997
- Peter
--
Hi! I'm a .signature *virus*!
Copy me into your ~/.signature to help me spread!|||Yup. SP4 is applied to the machine giving me the most problems. It does
however happen to SP3a machines as well.
Tim
"Peter Lykkegaard" <plykkegaard@.nospam.nospam> wrote in message
news:eZOsIYKPHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Tim Morrison wrote:
>> I have customers that are starting to get "Invalid Cursor State"
>> messages returned from my Clarion MS-SQL. based application.
> Tried reading this article?
> http://support.microsoft.com/kb/831997
> - Peter
> --
> Hi! I'm a .signature *virus*!
> Copy me into your ~/.signature to help me spread!
>

Thursday, February 16, 2012

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:

"FOR XML Explicit" why poor performance, and any "quick wins" ?

s I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tw

s I can make right now will be of benefit.
> Thanks
> Steve ...

Friday, February 24, 2012

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"Invalid Cursor State" - REPOST

Thursday, February 16, 2012

"FOR XML Explicit" why poor performance, and any "quick wins" ?

"FOR XML Explicit" why poor performance, and any "quick wins" ?

sql error

Blog Archive

About Me