sql error: null

Showing posts with label null. Show all posts

Friday, March 16, 2012

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?
Here's an example...
The table contains this data, all items are varchar
32611317
3261Non-Client
32612
3261mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz
|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
David Portas
SQL Server MVP
|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegro ups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>
|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
David Portas
SQL Server MVP
|||"Aaron [SQL Server MVP]" wrote:

> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?Here's an example...
The table contains this data, all items are varchar
3261 1317
3261 Non-Client
3261 2
3261 mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
--
David Portas
SQL Server MVP
--|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegroups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
--
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" wrote:
> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

Friday, February 24, 2012

"Like" Stored Procedure Help Needed

Jeff,

> where (((title like '@.SearchTerm%' or @.Searchterm IS NULL) or
> (Document like '@.SearchTerm%' or @.SearchTerm IS NUll))and
You are surrounding the variable name with apostrophes and this is not the
same as concatenating the value of the variable with the wildcard '%'.
'@.SearchTerm%' --> @.SearchTerm + '%'
Example:
declare @.s varchar(25)
set @.s = 'sql server'
select '@.s%', @.s + '%'
go
so your statement should be like:
...
where
(((title like @.SearchTerm + '%' or @.Searchterm IS NULL) or
(Document like @.SearchTerm + '%' or @.SearchTerm IS NUll))and
...
AMB
"Jeff" wrote:

> What is wrong with my Stored Procedure? The select Statement returns corr
ect
> results...why not the Stored Procedure?
>
> select *
> from tbldocument
> where (title like 'ros%') or
> (document like 'asdf%') and
> Submitterid = '1'
>
> Create Procedure PROCEDURE SearchDocument
> (
> @.SearchTerm char (200) = NULL,
> @.SubmitterID int = null
> )
> as
>
> SELECT dbo.tblDocument.DocumentID,
> dbo.tblDocument.Title,
> dbo.tblSubmitter.SubmitterName
> FROM dbo.tblDocument join dbo.tblSubmitter
> on (tbldocument.submitterid = tblsubmitter.submitterid)
> where (((title like '@.SearchTerm%' or @.Searchterm IS NULL) or
> (Document like '@.SearchTerm%' or @.SearchTerm IS NUll))and
> (dbo.tblDocument.SubmitterID = @.submitterID or @.SubmitterID is null)
)
>Me have so much to learn!!
Thanks!!
"Alejandro Mesa" wrote:
> Jeff,
>
> You are surrounding the variable name with apostrophes and this is not the
> same as concatenating the value of the variable with the wildcard '%'.
> '@.SearchTerm%' --> @.SearchTerm + '%'
> Example:
> declare @.s varchar(25)
> set @.s = 'sql server'
> select '@.s%', @.s + '%'
> go
> so your statement should be like:
> ...
> where
> (((title like @.SearchTerm + '%' or @.Searchterm IS NULL) or
> (Document like @.SearchTerm + '%' or @.SearchTerm IS NUll))and
> ...
>
> AMB
> "Jeff" wrote:
>

"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

Thursday, February 16, 2012

"expanding" updates

Hi,
i need to "expand" a raw datatable like

1, 'Harry', 'London'
2, 'Peter', null
3, null, 'New York'

into this format

1, 'Harry', 'London'
2, 'Peter', 'London'
3, 'Peter', 'New York'

so, for several columns, null values should be replaced with the values of the previous (non-null) row. the raw data is bulk-inserted and cannot be modified before.

is there anything other (faster!) than using a cursor ?
i like to have on single update command to do the job.does each row in the table have a unique sequential key?|||Originally posted by Paul Young
does each row in the table have a unique sequential key?

yes, the first column is an identity|||why not join the table to its self based on the identity column?

select *
from yourbcptable ybt1
join yourbcptable ybt2 on ybt1.IdentityColumn - 1 = ybt2.IdentityColumn
where ybt1.IdentityColumn > 1

This assumes your identity seed = 1|||yes, the identity seed is 1, so your select works,

but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?|||either a row at a time approach or run the update till you no longer have nulls or wrap everything up in a stored procedure.

probably should have added more to the where caluse

where ybt1.IdentityColumn > 1
and ytb1.col1 is null or ybt1.col2 is null

BTW, I would NOT use a CURSOR as these tend to be resource intensive.|||i got closer. the following update will nearly do the job, but the update order needs to be by the identity column. is there a way to set the order for updates ?

update mytable set
@.Column1 = Column1 = isnull( Column1, @.Column1 ),
@.Column2 = Column2 = isnull( Column2, @.Column2 )

this will set column1 to column1 (keep the value) when it is not null or will set column1=@.column1, which holds then "not null"-value of the rows before. works fine - but only when processed in correct order !|||No guaranties on the order. SQL Server will process in the most efficient manner.

You might try setting a clustered index on the key, but I would check the results very closely.|||--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T|||Originally posted by kukuk
--create table T (a int identity, N varchar(10), C varchar(10))
/*
delete from T
insert into T (n,c) select 'Harry','London'
insert into T (n,c) select 'Peter',null
insert into T (n,c) select null,'New York'
*/
select * from T
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1
select * from T

correct - that's what we discussed before,
but what about multiple null rows in order like

1, 'Peter', 'london'
2, 'Markus', null
3, 'Mary', null

for row 3 the row (id-1) will be null as well?
there might be many null-rows after one data-row.|||:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)|||Originally posted by kukuk
:)

while exists (select * from T where a>1 and (C is null or N is null) )
update nxt set N=isnull(nxt.N,prv.N),C=isnull(nxt.C,prv.C)
from T nxt join T prv on nxt.a=prv.a+1

(will work only if the first row does not contain nulls)

or

update x set
N=isnull(N,(select top 1 N from T as y where x.a>y.a and y.N is not null order by a desc)),
C=isnull(C,(select top 1 C from T as z where x.a>z.a and z.C is not null order by a desc))
from T as x

(expensive one)

the first one seems to be the one ...
i'll give it a try and come back later|||hi kukuk,

i think i got it:

declare @.A int, @.B varchar(20), @.C varchar(20)

select top 1 @.A = A from Tbl order by ID
while @.@.RowCount>0 begin
update Tbl
set @.B= B= isnull( B, @.B ),
@.C= C= isnull( C, @.C ),
@.A=A+1
from Tbl
where A=@.A
end

its a mix of your first select and my variable-version. your select works fine too, but it is much more expensive than the one above.

thanks to all for your time
markus|||Yes but the number of iterations will be equal to nuimber of rows in the table.|||Originally posted by kukuk
Yes but the number of iterations will be equal to nuimber of rows in the table.

true, but i don't care as long as it runs much faster.

Monday, February 13, 2012

"Error 14114: '(null)' is not configured as a distributor".

When I try to configure this server to be it's own distributor, the wizard
gets as far as "Configuring distributor" when I get the error that it cannot
be set up as the distributor for itself because "Error 14114: '(null)' is
not configured as a distributor".
How can I fix this problem ?
Thanks
Try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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

Saturday, February 11, 2012

"Concatenate Null Yields Null"

Thanks to the good graces of members I have found the COALESECE() FUNCTION,
and it should fix my problem but still, I'm confused.
I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
question and according to what I read in the Documents on Line for Sql Server
2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
Sadly it returns a NULL value. What gives'
--
Ed Warren> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
Current session settings override default settings specified at the database
or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> Thanks to the good graces of members I have found the COALESECE()
> FUNCTION,
> and it should fix my problem but still, I'm confused.
> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
> --
> Ed Warren|||Thanks for the enlightenment. That's what is happening. Now how does one
set the value for an (OLe DB session). From the caller or can this be done
on the server side (by setting options for the user', session). I must
admit I'm new to SQL server 2005 and have overlooked many of the 'gottchas'
Again, thanks.
--
Ed Warren
"Dan Guzman" wrote:
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> Current session settings override default settings specified at the database
> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
> connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> > Thanks to the good graces of members I have found the COALESECE()
> > FUNCTION,
> > and it should fix my problem but still, I'm confused.
> >
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> >
> > --
> > Ed Warren
>|||You'll need to set SET CONCAT_NULL_YIELDS_NULL OFF from the client
application after connecting using ODBC/OLE DB. It can be set on the server
side for the duration of a stored procedure execute by including the SET
statement in the proc. However, as you hinted in your original message, the
proper way to handle NULLs is with COALESCE, et. al.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:B59289CA-EA9A-413A-9290-9D12D2C772D0@.microsoft.com...
> Thanks for the enlightenment. That's what is happening. Now how does one
> set the value for an (OLe DB session). From the caller or can this be
> done
> on the server side (by setting options for the user', session). I must
> admit I'm new to SQL server 2005 and have overlooked many of the
> 'gottchas'
> Again, thanks.
> --
> Ed Warren
>
> "Dan Guzman" wrote:
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> Current session settings override default settings specified at the
>> database
>> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default
>> when
>> connecting via OLE DB or ODBC. This includes SQL Server Management
>> Studio.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
>> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
>> > Thanks to the good graces of members I have found the COALESECE()
>> > FUNCTION,
>> > and it should fix my problem but still, I'm confused.
>> >
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> >
>> > --
>> > Ed Warren

Thursday, February 9, 2012

"box" appearing instead of <NULL>

We are seeing occasions where a column value that should be NULL, is
displaying a little "box". This "box" actually prints when the column is
sent to a report. When selecting data, if the "box" is present, it is not
included when looking for NULLs in the column. If the select statement says
NOT NULL, the "box" is selected.
Does anyone know what is causing this?
We are running SQL Server 2000 version 8.00.761
Thanks.This is a non-printing character of some sort, probably passed in when
translating an input string through a different program or library.
Assuming it's the first character, take the row in question, and do this:
SELECT [1st char] = ASCII(SUBSTRING(<column_name>, 1, 1))
FROM
<table_name>
WHERE <clause to identify that bad row>;
Now you will get an answer like 4 or 3 or 0... so now you can take that
<answer> and say
SELECT <column_name> = REPLACE(<column_name>, CHAR(<answer> ), '')
FROM <table_name>;
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:7B29C100-1844-471D-B093-30A1130AB8CE@.microsoft.com...
> We are seeing occasions where a column value that should be NULL, is
> displaying a little "box". This "box" actually prints when the column is
> sent to a report. When selecting data, if the "box" is present, it is not
> included when looking for NULLs in the column. If the select statement
> says
> NOT NULL, the "box" is selected.
> Does anyone know what is causing this?
> We are running SQL Server 2000 version 8.00.761
> Thanks.

"box" appearing instead of <NULL>

We are seeing occasions where a column value that should be NULL, is
displaying a little "box". This "box" actually prints when the column is
sent to a report. When selecting data, if the "box" is present, it is not
included when looking for NULLs in the column. If the select statement says
NOT NULL, the "box" is selected.
Does anyone know what is causing this?
We are running SQL Server 2000 version 8.00.761
Thanks.This is a non-printing character of some sort, probably passed in when
translating an input string through a different program or library.
Assuming it's the first character, take the row in question, and do this:
SELECT [1st char] = ASCII(SUBSTRING(<column_name>, 1, 1))
FROM
<table_name>
WHERE <clause to identify that bad row>;
Now you will get an answer like 4 or 3 or 0... so now you can take that
<answer> and say
SELECT <column_name> = REPLACE(<column_name>, CHAR(<answer>), '')
FROM <table_name>;
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:7B29C100-1844-471D-B093-30A1130AB8CE@.microsoft.com...
> We are seeing occasions where a column value that should be NULL, is
> displaying a little "box". This "box" actually prints when the column is
> sent to a report. When selecting data, if the "box" is present, it is not
> included when looking for NULLs in the column. If the select statement
> says
> NOT NULL, the "box" is selected.
> Does anyone know what is causing this?
> We are running SQL Server 2000 version 8.00.761
> Thanks.

Friday, January 27, 2012

using Inner join when the field in my data table has the Null default value:

I have a datatable : Data_Table and a look up table: Lk_table. Myfield that Iuse in Inner Join is defined in both thedata and look table.

So I build my query like this:

SELECT * FROM dbo. Data_Table INNER JOIN

dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

The pb, sometimes Ihave myfield still with its default null value in the datatable: Data_Table.So, I end up getting 0 record when I execute the query shown above.

How do I turn that around so that even if myfield in Data_Tableis Null, I still get the records from Data_Table. (I don t want a set ofrecords including all possible values from the look up table: Lk_Table)

Try to use LEFT join instead of inner join:

SELECT * FROM dbo. Data_Table LEFT JOIN dbo. Lk_table
ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

|||

Try to use LEFT join instead of inner join:

SELECT * FROM dbo. Data_Table LEFT JOIN dbo. Lk_table
ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield

Or use such query:

SELECT * FROM dbo. Data_Table ,dbo. Lk_table
WHERE dbo.Data_Table.MyField = dbo.Lk_table.Myfield

OR dbo.Data_Table.MyField is null

Friday, March 16, 2012

"SUM" of a varchar column?

"SUM" of a varchar column?

Friday, February 24, 2012

"Like" Stored Procedure Help Needed

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"IS NOT NULL" is not working

Thursday, February 16, 2012

"expanding" updates

Monday, February 13, 2012

"Error 14114: '(null)' is not configured as a distributor".

"Do Not Allow Null" fields suddenly accept Nulls

"Do Not Allow Null" fields suddenly accept Nulls

"Do Not Allow Null" fields suddenly accept Nulls

Saturday, February 11, 2012

"Concatenate Null Yields Null"

Thursday, February 9, 2012

"box" appearing instead of <NULL>

"box" appearing instead of <NULL>

Friday, January 27, 2012

using Inner join when the field in my data table has the Null default value:

sql error

Blog Archive

About Me