bug found in query

sql server 2000 versions 8.00.760 and 8.00.818
i found a strange bug. maybe it's not a bug, but it sure seems like one
to me. these tables are from a database for a cots product called
facility center.
the "bug" occurs down at the very bottom when you run the queries
specified. see my comments down there.
just to explain a little,
spbldinf_code is a building name
spbldrom_code is a room number
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom__spcharge]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spcharge] DROP CONSTRAINT spbldrom__spcharge
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom__code]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sprmutil] DROP CONSTRAINT spbldrom__code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spbldrom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spbldrom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spcharge]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spcharge]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sprmutil]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sprmutil]
GO
CREATE TABLE [dbo].[spbldrom] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[splocation_code] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spbldloc_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[saclass_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[saclstyp_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[phone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[area] [decimal](12, 4) NULL ,
[notes] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[occ_vac] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[storageyn] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[location_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[subloc_type] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL ,
[haz_mat] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reservable_flag] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[spcharge] (
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spcharge_seq_num] [int] NOT NULL ,
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[area] [decimal](12, 4) NULL ,
[pct] [decimal](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL ,
[owner_sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sprmutil] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[sprmutil_seq_num] [int] NOT NULL ,
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spprgrm_code] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[spromuse_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[pct] [numeric](12, 4) NULL ,
[area] [numeric](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spromsub_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[spanlink_num] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spbldrom] ADD
CONSTRAINT [DF__spbldrom__area__6FE99F9F] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spbldrom__reserv__18427513] DEFAULT (0) FOR
[reservable_flag],
CONSTRAINT [xpkspbldrom] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spbldrom_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF226spbldrom] ON [dbo].[spbldrom]([spbldinf_code],
[spbldloc_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF228spbldrom] ON [dbo].[spbldrom]([spbldinf_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF229spbldrom] ON [dbo].[spbldrom]([spbldinf_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF433spbldrom] ON [dbo].[spbldrom]([saclass_code],
[saclstyp_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF877spbldrom] ON [dbo].[spbldrom]([splocation_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [DF__spcharge__area__2CBDA3B5] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spcharge__pct__2DB1C7EE] DEFAULT (0) FOR [pct],
CONSTRAINT [xpkspcharge] PRIMARY KEY NONCLUSTERED
(
[sporg_code],
[spcharge_seq_num],
[spbldinf_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF236spcharge] ON [dbo].[spcharge]([sporg_code]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF274spcharge] ON [dbo].[spcharge]([spbldinf_code],
[spbldrom_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF275spcharge] ON [dbo].[spcharge]([spbldinf_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF276spcharge] ON [dbo].[spcharge]([spbldinf_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [XPKsprmutil] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spfloors_code],
[spbldrom_code],
[sprmutil_seq_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF1040sprmutil] ON [dbo].[sprmutil]([sporg_code]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [spbldrom__spcharge] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [spbldrom__code] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '1', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '2', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '3', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '4', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '5', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '6', '1')
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '1', 'org', 1)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '2', 'org', 2)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '3', 'org', 3)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '4', 'org', 4)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '1', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '2', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '3', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '4', '1', 1)
go
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
those two queries work properly. rooms 5 and 6 are returned because
they do not exist in the spcharge and/or sprmutil table.
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', NULL, 'org', 10)
set concat_null_yields_null off
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
the two queries correctly return rooms 5 and 6.
set concat_null_yields_null on
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
now the query for spcharge returns nothing. the one row with a null
value in the spcharge.spbldrom_code has prevented the query from
returning anything when it should still return rooms 5 and 6. i realize
that the concat_null_yields_null value is different, but imho it should
still return rooms 5 and 6.
if you run
set concat_null_yields_null on
select spbldinf_code + spbldrom_code from spcharge
set concat_null_yields_null off
select spbldinf_code + spbldrom_code from spcharge
you get record sets returned for both queries. the null value returned
in the top one apparently makes the not in clause in the previous
queries fail to work properly.
is there any way to report this to microsoft without paying for a
support call?> select spbldinf_code, spbldrom_code from spbldrom
> where spbldinf_code + spbldrom_code not in
> (select spbldinf_code + spbldrom_code from spcharge)
> go
> now the query for spcharge returns nothing. the one row with a null
> value in the spcharge.spbldrom_code has prevented the query from
> returning anything when it should still return rooms 5 and 6.
This isn't a bug it's standard SQL behaviour.
Because the subquery
(select spbldinf_code + spbldrom_code from spcharge)
includes a NULL, the predicate NOT IN returns UNKNOWN for every row in the
outer query, therefore nothing is returned. This is logical enough - if a
value in the set is UNKNOWN then you can't know whether any given value is
NOT IN the set.
I think the result you are looking for is this:
SELECT spbldinf_code, spbldrom_code
FROM spbldrom
WHERE spbldinf_code + spbldrom_code
NOT IN
(SELECT spbldinf_code + spbldrom_code
FROM spcharge
WHERE spbldinf_code + spbldrom_code IS NOT NULL)
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> This is logical enough - if a
> value in the set is UNKNOWN then you can't know whether any given value is
> NOT IN the set.
that doesn't make any sense.
set A = 1, 2, 3
set B = 1, 2, NULL
if i'm trying to find all in A that aren't in B, then since
A.3 <> B.1 and
A.3 <> B.2 and
A.3 <> B.NULL
A.3 is not in set B
makes much more sense than saying "i can't figure out whether or not 3 is in
set B" when it's perfectly clear that there is no 3 in set B.|||> A.3 <> B.1 and
> A.3 <> B.2 and
> A.3 <> B.NULL
Yes, that's the logic. But the result of that last comparison is UNKNOWN not
TRUE (anything compared to NULL is UNKNOWN). Therefore the result of the NOT
IN predicate is also UNKNOWN. This is a common "gotcha" in SQL.
--
David Portas
SQL Server MVP
--

bug found in query

sql server 2000 versions 8.00.760 and 8.00.818
i found a strange bug. maybe it's not a bug, but it sure seems like one
to me. these tables are from a database for a cots product called
facility center.
the "bug" occurs down at the very bottom when you run the queries
specified. see my comments down there.
just to explain a little,
spbldinf_code is a building name
spbldrom_code is a room number
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom__spcharge]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spcharge] DROP CONSTRAINT spbldrom__spcharge
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom__code]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sprmutil] DROP CONSTRAINT spbldrom__code
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[spbldrom]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spcharge]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[spcharge]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sprmutil]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[sprmutil]
GO
CREATE TABLE [dbo].[spbldrom] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[splocation_code] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spbldloc_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[saclass_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[saclstyp_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[phone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[area] [decimal](12, 4) NULL ,
[notes] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[occ_vac] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[storageyn] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[location_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[subloc_type] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL ,
[haz_mat] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reservable_flag] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[spcharge] (
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spcharge_seq_num] [int] NOT NULL ,
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[area] [decimal](12, 4) NULL ,
[pct] [decimal](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL ,
[owner_sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_
AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sprmutil] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[sprmutil_seq_num] [int] NOT NULL ,
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[spprgrm_code] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[spromuse_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[pct] [numeric](12, 4) NULL ,
[area] [numeric](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[spromsub_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spbldrom] ADD
CONSTRAINT [DF__spbldrom__area__6FE99F9F] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spbldrom__reserv__18427513] DEFAULT (0) FOR
[reservable_flag],
CONSTRAINT [xpkspbldrom] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spbldrom_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF226spbldrom] ON [dbo].[spbldrom]([spbldinf
_code],
[spbldloc_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF228spbldrom] ON [dbo].[spbldrom]([spbldinf
_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF229spbldrom] ON [dbo].[spbldrom]([spbldinf
_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF433spbldrom] ON [dbo].[spbldrom]([saclass_
code],
[saclstyp_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF877spbldrom] ON [dbo].[spbldrom]([splocati
on_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [DF__spcharge__area__2CBDA3B5] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spcharge__pct__2DB1C7EE] DEFAULT (0) FOR [pct],
CONSTRAINT [xpkspcharge] PRIMARY KEY NONCLUSTERED
(
[sporg_code],
[spcharge_seq_num],
[spbldinf_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF236spcharge] ON [dbo].[spcharge]([sporg_co
de]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF274spcharge] ON [dbo].[spcharge]([spbldinf
_code],
[spbldrom_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF275spcharge] ON [dbo].[spcharge]([spbldinf
_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF276spcharge] ON [dbo].[spcharge]([spbldinf
_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [XPKsprmutil] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spfloors_code],
[spbldrom_code],
[sprmutil_seq_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF1040sprmutil] ON [dbo].[sprmutil]([sporg_c
ode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [spbldrom__spcharge] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [spbldrom__code] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '1', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '2', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '3', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '4', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '5', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '6', '1')
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '1', 'org', 1)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '2', 'org', 2)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '3', 'org', 3)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '4', 'org', 4)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '1', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '2', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '3', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '4', '1', 1)
go
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
those two queries work properly. rooms 5 and 6 are returned because
they do not exist in the spcharge and/or sprmutil table.
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', NULL, 'org', 10)
set concat_null_yields_null off
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
the two queries correctly return rooms 5 and 6.
set concat_null_yields_null on
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
now the query for spcharge returns nothing. the one row with a null
value in the spcharge.spbldrom_code has prevented the query from
returning anything when it should still return rooms 5 and 6. i realize
that the concat_null_yields_null value is different, but imho it should
still return rooms 5 and 6.
if you run
set concat_null_yields_null on
select spbldinf_code + spbldrom_code from spcharge
set concat_null_yields_null off
select spbldinf_code + spbldrom_code from spcharge
you get record sets returned for both queries. the null value returned
in the top one apparently makes the not in clause in the previous
queries fail to work properly.
is there any way to report this to microsoft without paying for a
support call?> select spbldinf_code, spbldrom_code from spbldrom
> where spbldinf_code + spbldrom_code not in
> (select spbldinf_code + spbldrom_code from spcharge)
> go
> now the query for spcharge returns nothing. the one row with a null
> value in the spcharge.spbldrom_code has prevented the query from
> returning anything when it should still return rooms 5 and 6.
This isn't a bug it's standard SQL behaviour.
Because the subquery
(select spbldinf_code + spbldrom_code from spcharge)
includes a NULL, the predicate NOT IN returns UNKNOWN for every row in the
outer query, therefore nothing is returned. This is logical enough - if a
value in the set is UNKNOWN then you can't know whether any given value is
NOT IN the set.
I think the result you are looking for is this:
SELECT spbldinf_code, spbldrom_code
FROM spbldrom
WHERE spbldinf_code + spbldrom_code
NOT IN
(SELECT spbldinf_code + spbldrom_code
FROM spcharge
WHERE spbldinf_code + spbldrom_code IS NOT NULL)
David Portas
SQL Server MVP
--|||David Portas wrote:

> This is logical enough - if a
> value in the set is UNKNOWN then you can't know whether any given value is
> NOT IN the set.
that doesn't make any sense.
set A = 1, 2, 3
set B = 1, 2, NULL
if i'm trying to find all in A that aren't in B, then since
A.3 <> B.1 and
A.3 <> B.2 and
A.3 <> B.NULL
A.3 is not in set B
makes much more sense than saying "i can't figure out whether or not 3 is in
set B" when it's perfectly clear that there is no 3 in set B.|||> A.3 <> B.1 and
> A.3 <> B.2 and
> A.3 <> B.NULL
Yes, that's the logic. But the result of that last comparison is UNKNOWN not
TRUE (anything compared to NULL is UNKNOWN). Therefore the result of the NOT
IN predicate is also UNKNOWN. This is a common "gotcha" in SQL.
David Portas
SQL Server MVP
--

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server ?
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this ?
Thanks for any help.
Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server ?
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this ?
> Thanks for any help.
|||I hope that installing service packs should not be a
problem....

>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each[vbcol=seagreen]
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
and[vbcol=seagreen]
2000[vbcol=seagreen]
SQL
>.
>
|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:[vbcol=seagreen]
> I hope that installing service packs should not be a
> problem....
>
>
> install SQL Server
>
> Remember to
>
> competing with each
>
> and
>
> 2000
>
> SQL

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server '
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this '
Thanks for any help.Check out
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/instsql/in_runsetup_0svo.asp
Peter
"Government does not solve problems; it subsidizes them."
Ronald Reagan
>--Original Message--
>Can I install SQL Server 7.0 SP2 as a Default version and
>SQL Server 2000 SP3 as a named version on a WIndows 2000
>SP3 server '
>We will run Connect-Care (Customer-First) Application
>against SQL Server 7.0 version and Solomon 5.5 against
SQL
>Server 2000 version.
>Anybody see any problem with this '
>Thanks for any help.
>.
>|||Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server '
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this '
> Thanks for any help.|||I hope that installing service packs should not be a
problem....
>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
>> Can I install SQL Server 7.0 SP2 as a Default version
and
>> SQL Server 2000 SP3 as a named version on a WIndows
2000
>> SP3 server '
>> We will run Connect-Care (Customer-First) Application
>> against SQL Server 7.0 version and Solomon 5.5 against
SQL
>> Server 2000 version.
>> Anybody see any problem with this '
>> Thanks for any help.
>.
>|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> I hope that installing service packs should not be a
> problem....
>
>>--Original Message--
>>Rick,
>>Yep, should work fine. I have found it preferable to
> install SQL Server
>>7.0 first, then install SQL 2000 as a named instance.
> Remember to
>>configure your memory settings so that they aren't
> competing with each
>>other for RAM.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Rick wrote:
>>Can I install SQL Server 7.0 SP2 as a Default version
> and
>>SQL Server 2000 SP3 as a named version on a WIndows
> 2000
>>SP3 server '
>>We will run Connect-Care (Customer-First) Application
>>against SQL Server 7.0 version and Solomon 5.5 against
> SQL
>>Server 2000 version.
>>Anybody see any problem with this '
>>Thanks for any help.
>>.

Both Versions of .NET & Reporting Services

I have a windows 2000 SP4 server on which I have installed Reporting Services
but keep getting a "Access Denied 401" error.
Could this be because I am running both versions of .NET
.net 1.0 SP1
.net 1.1
?
--
EdgarNot as long as ASP.NET is using the latest version (1.1). You might try
aspnet_regiis -i in the 1.1 directory.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:769E4D70-3104-4D12-AE5C-690664353C7C@.microsoft.com...
>I have a windows 2000 SP4 server on which I have installed Reporting
>Services
> but keep getting a "Access Denied 401" error.
> Could this be because I am running both versions of .NET
> .net 1.0 SP1
> .net 1.1
> ?
> --
> Edgar|||Brian,
When I installed RS, the setup automatically put it in the "Default" Web
Site. I have since moved it to a different Web Site & Copied all the
permissions, etc. Does RS have to be on its on dedicated Web Site (Ip
Address) or can it simply be made part of a different web site as a new
Virtual Directory (mysite.com/Reports)?
"Brian Welcker [MS]" wrote:
> Not as long as ASP.NET is using the latest version (1.1). You might try
> aspnet_regiis -i in the 1.1 directory.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "mresanchez" <mresanchez@.donot-spam.com> wrote in message
> news:769E4D70-3104-4D12-AE5C-690664353C7C@.microsoft.com...
> >I have a windows 2000 SP4 server on which I have installed Reporting
> >Services
> > but keep getting a "Access Denied 401" error.
> >
> > Could this be because I am running both versions of .NET
> > .net 1.0 SP1
> > .net 1.1
> > ?
> > --
> > Edgar
>
>|||It can be in any web site. However, unless you are running Windows Server
2003, all application within a web site share the same version of the
frameworks (since there is only one worker process). On Windows Server 2003,
you can create multiple application pools with different versions of the
Frameworks and then assign applications to them.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:6620EDB4-04EB-4A1E-83DD-28153B2F02DB@.microsoft.com...
> Brian,
> When I installed RS, the setup automatically put it in the "Default" Web
> Site. I have since moved it to a different Web Site & Copied all the
> permissions, etc. Does RS have to be on its on dedicated Web Site (Ip
> Address) or can it simply be made part of a different web site as a new
> Virtual Directory (mysite.com/Reports)?
> "Brian Welcker [MS]" wrote:
>> Not as long as ASP.NET is using the latest version (1.1). You might try
>> aspnet_regiis -i in the 1.1 directory.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "mresanchez" <mresanchez@.donot-spam.com> wrote in message
>> news:769E4D70-3104-4D12-AE5C-690664353C7C@.microsoft.com...
>> >I have a windows 2000 SP4 server on which I have installed Reporting
>> >Services
>> > but keep getting a "Access Denied 401" error.
>> >
>> > Could this be because I am running both versions of .NET
>> > .net 1.0 SP1
>> > .net 1.1
>> > ?
>> > --
>> > Edgar
>>