Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusOn Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn
'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/defaul...kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.
4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>
'case_jn'[vbcol=seagreen]
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/defaul...kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusThat looks like a bug with ISNULL; I switched it to use COALESCE instead and
it fixed the problem...
Hopefully someone reading this knows how to submit a SQL bug report to MS?
"mark" <wimark@.smsNOSAPM.at> wrote in message
news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> Hi,
> there is a small test case (SQL Server 2000 - 8.00.760):
> -- create a small table
> create table test1 (nr int null)
> insert into test1 values (1)
> insert into test1 values (2)
> -- now create a view
> create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> If you perform this select, the isnull_jn col is always 'J',
> although it should be 'N' in the second row:
> select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn isnull_jn
> -- -- --
> 1 J J
> 2 NULL J <<<=== error, should be N!
>
> If you redefine the query to:
> select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn case_jn
> -- -- --
> 1 J J
> 2 NULL N <<== ok!
> everything works fine.
> Any suggestions?
> --
> markus
>|||ISNULL is kind of notoriously NOT the same thing as COALESCE and CASE, so I
was skeptical that this was really a bug. But below is a repro which uses
ISNULL with a view and an "inline view" or derived table.
Now despite any differences between INSULL and COALESCE, these 2 queries
should return the same data. They do not.
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview as select nr, 'J' jn from test1 where nr = 1
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join (select nr, 'J' jn from test1 where nr = 1) testview
on test1.nr = testview.nr
where test1.nr=2
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join testview
on test1.nr = testview.nr
where test1.nr=2
--
nr jn isnull_jn
2 N
(1 row(s) affected)
nr jn isnull_jn
2 J
(1 row(s) affected)
David
Should definitely return the same data
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ebxMLqvJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> That looks like a bug with ISNULL; I switched it to use COALESCE instead
and
> it fixed the problem...
> Hopefully someone reading this knows how to submit a SQL bug report to MS?
>
> "mark" <wimark@.smsNOSAPM.at> wrote in message
> news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > there is a small test case (SQL Server 2000 - 8.00.760):
> >
> > -- create a small table
> > create table test1 (nr int null)
> > insert into test1 values (1)
> > insert into test1 values (2)
> > -- now create a view
> > create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> > If you perform this select, the isnull_jn col is always 'J',
> > although it should be 'N' in the second row:
> > select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn isnull_jn
> > -- -- --
> > 1 J J
> > 2 NULL J <<<=== error, should be N!
> >
> >
> > If you redefine the query to:
> > select test1.nr, jn, case when jn is null then 'N' else jn end as
> 'case_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn case_jn
> > -- -- --
> > 1 J J
> > 2 NULL N <<== ok!
> > everything works fine.
> >
> > Any suggestions?
> > --
> > markus
> >
> >
>|||On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.
>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
--
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
> >-- create a small table
> >create table test1 (nr int null)
> >insert into test1 values (1)
> >insert into test1 values (2)
> >-- now create a view
> >create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> >If you perform this select, the isnull_jn col is always 'J',
> >although it should be 'N' in the second row:
> >select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn isnull_jn
> >-- -- --
> >1 J J
> >2 NULL J <<<=== error, should be N!
> >
> >
> >If you redefine the query to:
> >select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn case_jn
> >-- -- --
> >1 J J
> >2 NULL N <<== ok!
> >everything works fine.
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
> >
> >Any suggestions?
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:
>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
markus
On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:

>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.

>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default...b;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com... [vbcol=seagreen]
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
'case_jn'
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
>
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:

>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default...b;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

BUG in SQL SERVER 2000 ?

I have an Access database that retrieves view definitions from an SQL server
database. I use this statement to get the view definitions :
select TABLE_NAME as VIEW_NAME,VIEW_DEFINITION
from INFORMATION_SCHEMA.VIEWS
I have a view thet is more then 4000 characters and then the call against
INFORMATION_SCHEMA returns NULL for the VIEW_DEFINITION. Why ?
If i query the syscomments table the view is splitted in two columns.
Regards
Robert Vabo
Senior developer
Gecko Informasjonssystemer AS
rv_nospam@.gecko.no
Remove the _nospam to send mail !
www.gecko.noDo you mean rows or columns in the syscomments table'
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Robert Vabo" <rvabo@.msn.com> wrote in message
news:%23jK0CCh8DHA.2116@.TK2MSFTNGP10.phx.gbl...
> I have an Access database that retrieves view definitions from an SQL
server
> database. I use this statement to get the view definitions :
> select TABLE_NAME as VIEW_NAME,VIEW_DEFINITION
> from INFORMATION_SCHEMA.VIEWS
> I have a view thet is more then 4000 characters and then the call against
> INFORMATION_SCHEMA returns NULL for the VIEW_DEFINITION. Why ?
> If i query the syscomments table the view is splitted in two columns.
>
> --
> Regards
> Robert Vabo
> Senior developer
> Gecko Informasjonssystemer AS
> rv_nospam@.gecko.no
> Remove the _nospam to send mail !
> www.gecko.no
>|||Sorry. I mean rows ofcourse.
But the problem is that the column VIEW_DEFINITION returns NULL when
querying the INFORMATION_SCHEMA
Regards
Robert Vabo
Senior developer
Gecko Informasjonssystemer AS
rv_nospam@.gecko.no
Remove the _nospam to send mail !
www.gecko.no
"SriSamp" <ssampath@.sct.co.in> skrev i melding
news:%23YBXKkh8DHA.452@.TK2MSFTNGP11.phx.gbl...
> Do you mean rows or columns in the syscomments table'
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Robert Vabo" <rvabo@.msn.com> wrote in message
> news:%23jK0CCh8DHA.2116@.TK2MSFTNGP10.phx.gbl...
> server
against
>

BUG in SQL SERVER 2000 ?

I have an Access database that retrieves view definitions from an SQL server
database. I use this statement to get the view definitions :
select TABLE_NAME as VIEW_NAME,VIEW_DEFINITION
from INFORMATION_SCHEMA.VIEWS
I have a view thet is more then 4000 characters and then the call against
INFORMATION_SCHEMA returns NULL for the VIEW_DEFINITION. Why ?
If i query the syscomments table the view is splitted in two columns.
--
Regards
Robert Vabo
Senior developer
Gecko Informasjonssystemer AS
rv_nospam@.gecko.no
Remove the _nospam to send mail !
www.gecko.noDo you mean rows or columns in the syscomments table'
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Robert Vabo" <rvabo@.msn.com> wrote in message
news:%23jK0CCh8DHA.2116@.TK2MSFTNGP10.phx.gbl...
> I have an Access database that retrieves view definitions from an SQL
server
> database. I use this statement to get the view definitions :
> select TABLE_NAME as VIEW_NAME,VIEW_DEFINITION
> from INFORMATION_SCHEMA.VIEWS
> I have a view thet is more then 4000 characters and then the call against
> INFORMATION_SCHEMA returns NULL for the VIEW_DEFINITION. Why ?
> If i query the syscomments table the view is splitted in two columns.
>
> --
> Regards
> Robert Vabo
> Senior developer
> Gecko Informasjonssystemer AS
> rv_nospam@.gecko.no
> Remove the _nospam to send mail !
> www.gecko.no
>|||Sorry. I mean rows ofcourse.
But the problem is that the column VIEW_DEFINITION returns NULL when
querying the INFORMATION_SCHEMA
--
Regards
Robert Vabo
Senior developer
Gecko Informasjonssystemer AS
rv_nospam@.gecko.no
Remove the _nospam to send mail !
www.gecko.no
"SriSamp" <ssampath@.sct.co.in> skrev i melding
news:%23YBXKkh8DHA.452@.TK2MSFTNGP11.phx.gbl...
> Do you mean rows or columns in the syscomments table'
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Robert Vabo" <rvabo@.msn.com> wrote in message
> news:%23jK0CCh8DHA.2116@.TK2MSFTNGP10.phx.gbl...
> > I have an Access database that retrieves view definitions from an SQL
> server
> > database. I use this statement to get the view definitions :
> > select TABLE_NAME as VIEW_NAME,VIEW_DEFINITION
> > from INFORMATION_SCHEMA.VIEWS
> >
> > I have a view thet is more then 4000 characters and then the call
against
> > INFORMATION_SCHEMA returns NULL for the VIEW_DEFINITION. Why ?
> > If i query the syscomments table the view is splitted in two columns.
> >
> >
> > --
> > Regards
> > Robert Vabo
> > Senior developer
> > Gecko Informasjonssystemer AS
> > rv_nospam@.gecko.no
> > Remove the _nospam to send mail !
> > www.gecko.no
> >
> >
>

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server O
S.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexe
d
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
--
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
--
CheersHi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
, SUM(<<numeric col1>> )
, SUM(<<numeric col2>> )
, COUNT_BIG(*)
FROM dbo.<<facttable>> AS pv
WHERE Balance = 1
GROUP
BY <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>
>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
GO
Thanks,
Arun|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegroups.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>> )
> , SUM(<<numeric col2>> )
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<vie
w>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
> GO
> Thanks,
> Arun
>|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert|||The value column is not nullable.
Cheers,
Space_AD
"Robert Klemme" wrote:

> Space_AD wrote:
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>|||We found that this problem occured after some deadlock in the underlying
tables.
But not sure how dead locks affect the indexed view .
Cheers,
Space_AD
"arun.ns@.gmail.com" wrote:

> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>> )
> , SUM(<<numeric col2>> )
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<vie
w>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>> )
> GO
> Thanks,
> Arun
>|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
Cheers,
Space_AD
"Space_AD" wrote:
[vbcol=seagreen]
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:
>

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server OS.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexed
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
Cheers
Hi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT<<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
,SUM(<<numeric col1>>)
,SUM(<<numeric col2>>)
,COUNT_BIG(*)
FROM dbo.<<facttable>>AS pv
WHEREBalance = 1
GROUP
BY <<col1>>
,<<col2>>
,<<col3>>
,<<col4>>
,<<col5>>
,<<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
GO
Thanks,
Arun
|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>
|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun
|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegrou ps.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>>)
> , SUM(<<numeric col2>>)
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert
|||The value column is not nullable.
Cheers,
Space_AD
"Robert Klemme" wrote:

> Space_AD wrote:
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>
|||We found that this problem occured after some deadlock in the underlying
tables.
But not sure how dead locks affect the indexed view .
Cheers,
Space_AD
"arun.ns@.gmail.com" wrote:

> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT<<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> ,SUM(<<numeric col1>>)
> ,SUM(<<numeric col2>>)
> ,COUNT_BIG(*)
> FROM dbo.<<facttable>>AS pv
> WHEREBalance = 1
> GROUP
> BY <<col1>>
> ,<<col2>>
> ,<<col3>>
> ,<<col4>>
> ,<<col5>>
> ,<<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>
|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
Cheers,
Space_AD
"Space_AD" wrote:
[vbcol=seagreen]
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:

Bug in indexed view?

We use SQL server 2000 sp3 database server in Windows 2000 Advanced server OS.
In production environment, one of the fact tables in our datamart has over
150 mil rows. A indexed view is built upon this fact table for reporting
purposes.
The problem is that when queried for the same criteria, the indexed view
returns different result than the underlying table. This problem has occured
twice in a single month. The problem was resolved upon rebuilding the indexed
view.
FYI. We have lots of data inserts as part of overnight batch process.
Queries:
--
SELECT sum(value)
FROM <<FactTableName>> (NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
OPTION (EXPAND VIEWS)
SELECT sum(value)
FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
WHERE portfolioid = 100
and cob = '30 Jan 2006'
Any help on this is appreciated.
--
CheersHi
Does the view based on a single table?
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||Yes, the indexed view is based on a single table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW <<view>> WITH SCHEMABINDING
AS
SELECT <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
, SUM(<<numeric col1>>)
, SUM(<<numeric col2>>)
, COUNT_BIG(*)
FROM dbo.<<facttable>> AS pv
WHERE Balance = 1
GROUP
BY <<col1>>
, <<col2>>
, <<col3>>
, <<col4>>
, <<col5>>
, <<col6>>
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
id = object_id(N'[dbo].[<<view>>]'))
DROP INDEX [dbo].[<<view>>].[<<idxname>>]
GO
CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
<<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
GO
Thanks,
Arun|||Hi!
Try remove "nolock" hint from your queries.
Micle.
"Space_AD" <SpaceAD@.discussions.microsoft.com> wrote in message
news:43DC2E60-C5E6-4B31-8B2F-893453A3C14C@.microsoft.com...
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced server
> OS.
> In production environment, one of the fact tables in our datamart has over
> 150 mil rows. A indexed view is built upon this fact table for reporting
> purposes.
> The problem is that when queried for the same criteria, the indexed view
> returns different result than the underlying table. This problem has
> occured
> twice in a single month. The problem was resolved upon rebuilding the
> indexed
> view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
> --
> Cheers
>|||I have already tried it. It does not do any dirty reads..
FYI.. The bug has occured again today.
Cheers,
Arun|||CREATE CI on col1 and col2 ONLY
<arun.ns@.gmail.com> wrote in message
news:1138796568.572051.8640@.g49g2000cwa.googlegroups.com...
> Yes, the indexed view is based on a single table.
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE VIEW <<view>> WITH SCHEMABINDING
> AS
> SELECT <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> , SUM(<<numeric col1>>)
> , SUM(<<numeric col2>>)
> , COUNT_BIG(*)
> FROM dbo.<<facttable>> AS pv
> WHERE Balance = 1
> GROUP
> BY <<col1>>
> , <<col2>>
> , <<col3>>
> , <<col4>>
> , <<col5>>
> , <<col6>>
> GO
>
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
> ON
> SET NUMERIC_ROUNDABORT OFF
> GO
> if exists (select * from dbo.sysindexes where name = N'<<idxname>>' and
> id = object_id(N'[dbo].[<<view>>]'))
> DROP INDEX [dbo].[<<view>>].[<<idxname>>]
> GO
> CREATE UNIQUE CLUSTERED INDEX [<<idxname>>] ON [dbo].[<<view>>] (
> <<col1>>, <<col2>>, <<col3>>,<<col4>>, <<col5>>, <<col6>>)
> GO
> Thanks,
> Arun
>|||Space_AD wrote:
> We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> server OS.
> In production environment, one of the fact tables in our datamart has
> over 150 mil rows. A indexed view is built upon this fact table for
> reporting purposes.
> The problem is that when queried for the same criteria, the indexed
> view returns different result than the underlying table. This problem
> has occured twice in a single month. The problem was resolved upon
> rebuilding the indexed view.
> FYI. We have lots of data inserts as part of overnight batch process.
> Queries:
> --
> SELECT sum(value)
> FROM <<FactTableName>> (NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> OPTION (EXPAND VIEWS)
> SELECT sum(value)
> FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> WHERE portfolioid = 100
> and cob = '30 Jan 2006'
> Any help on this is appreciated.
And there is no additional criterium that limits records in the view?
Could your problem be caused by NULL values? Just some 0.01 cents...
robert|||The value column is not nullable.
--
Cheers,
Space_AD
"Robert Klemme" wrote:
> Space_AD wrote:
> > We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> > server OS.
> >
> > In production environment, one of the fact tables in our datamart has
> > over 150 mil rows. A indexed view is built upon this fact table for
> > reporting purposes.
> >
> > The problem is that when queried for the same criteria, the indexed
> > view returns different result than the underlying table. This problem
> > has occured twice in a single month. The problem was resolved upon
> > rebuilding the indexed view.
> >
> > FYI. We have lots of data inserts as part of overnight batch process.
> >
> > Queries:
> > --
> > SELECT sum(value)
> > FROM <<FactTableName>> (NOLOCK)
> > WHERE portfolioid = 100
> > and cob = '30 Jan 2006'
> > OPTION (EXPAND VIEWS)
> >
> > SELECT sum(value)
> > FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> > WHERE portfolioid = 100
> > and cob = '30 Jan 2006'
> >
> > Any help on this is appreciated.
> And there is no additional criterium that limits records in the view?
> Could your problem be caused by NULL values? Just some 0.01 cents...
> robert
>|||we have found that this problem occured just after a deadlocking scenario
involving the underlying table.
But not sure why this deadlock is causing incorrect data in indexed view.
--
Cheers,
Space_AD
"Space_AD" wrote:
> The value column is not nullable.
> --
> Cheers,
> Space_AD
>
> "Robert Klemme" wrote:
> > Space_AD wrote:
> > > We use SQL server 2000 sp3 database server in Windows 2000 Advanced
> > > server OS.
> > >
> > > In production environment, one of the fact tables in our datamart has
> > > over 150 mil rows. A indexed view is built upon this fact table for
> > > reporting purposes.
> > >
> > > The problem is that when queried for the same criteria, the indexed
> > > view returns different result than the underlying table. This problem
> > > has occured twice in a single month. The problem was resolved upon
> > > rebuilding the indexed view.
> > >
> > > FYI. We have lots of data inserts as part of overnight batch process.
> > >
> > > Queries:
> > > --
> > > SELECT sum(value)
> > > FROM <<FactTableName>> (NOLOCK)
> > > WHERE portfolioid = 100
> > > and cob = '30 Jan 2006'
> > > OPTION (EXPAND VIEWS)
> > >
> > > SELECT sum(value)
> > > FROM <<IndexedViewName>> (NOEXPAND, NOLOCK)
> > > WHERE portfolioid = 100
> > > and cob = '30 Jan 2006'
> > >
> > > Any help on this is appreciated.
> >
> > And there is no additional criterium that limits records in the view?
> > Could your problem be caused by NULL values? Just some 0.01 cents...
> >
> > robert
> >
> >

Browser Role - Deletes

I'm trying to restrict my users from being able to delete or move any of the
reports. Using the Browser Role ( View Folders, View Reports ) still
allows them to get to the "Show Details" page with full access to delete my
reports. What am I missing?
TIA
TinaThe Show Details will still show the check box to delete the report. But
the user does not have permissions to do it and will receive an error. If
this is not the case, please let me know.
Make sure you apply permissions at the folder level as well.
| From: "Tina Smith" <tb.smith@.earthlink.net>
| Subject: Browser Role - Deletes
| Date: Tue, 18 Jan 2005 11:31:20 -0500
| Lines: 9
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <O1QclsX$EHA.2076@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: user-10ibco9.biz.mindspring.com 65.37.179.9
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40126
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm trying to restrict my users from being able to delete or move any of
the
| reports. Using the Browser Role ( View Folders, View Reports ) still
| allows them to get to the "Show Details" page with full access to delete
my
| reports. What am I missing?
|
| TIA
| Tina
|
|
||||Yes, they do receive an error stating they don't have the permissions.
I'd rather not show them the option to delete the reports when they don't
have the permissions to. Is there anyway to eliminate the "ShowDetails"
for the browser role?
Thanks
Tina
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:tR6$y7X$EHA.3360@.cpmsftngxa10.phx.gbl...
> The Show Details will still show the check box to delete the report. But
> the user does not have permissions to do it and will receive an error. If
> this is not the case, please let me know.
> Make sure you apply permissions at the folder level as well.
>
> --
> | From: "Tina Smith" <tb.smith@.earthlink.net>
> | Subject: Browser Role - Deletes
> | Date: Tue, 18 Jan 2005 11:31:20 -0500
> | Lines: 9
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <O1QclsX$EHA.2076@.TK2MSFTNGP15.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: user-10ibco9.biz.mindspring.com 65.37.179.9
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:40126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I'm trying to restrict my users from being able to delete or move any of
> the
> | reports. Using the Browser Role ( View Folders, View Reports ) still
> | allows them to get to the "Show Details" page with full access to delete
> my
> | reports. What am I missing?
> |
> | TIA
> | Tina
> |
> |
> |
>

browse to reports

Hi,
I have created reports on my local machine as a test.
I can view the reports fine but others can not browse to these reports on my
machine.
I am not using any code, just the front end report creating of reporting
services in Visual studio .net
How can I allow other to view these reports on my machine.
ThanksHi,
Surf to http://servername/reports, click on the properties-tab and add the
users you want to give access.
hth,
Pieter
"Farsh" <Farsh@.discussions.microsoft.com> wrote in message
news:1D124954-0038-4726-8BAC-CDBE70EC6F38@.microsoft.com...
> Hi,
> I have created reports on my local machine as a test.
> I can view the reports fine but others can not browse to these reports on
> my
> machine.
> I am not using any code, just the front end report creating of reporting
> services in Visual studio .net
> How can I allow other to view these reports on my machine.
> Thanks|||first of all u should tell whats the error ppl r getting when they r
trying to access ur report so that it could properly b pointed out n
rectified as per my understanding i believe this would b RSACCESSDENIED
error n u ve to add users login to PERMIT through REPORT MANAGER check
security features/tab in report manager
hope it ll answer|||Thanks
"Pieter van Maasdam" wrote:
> Hi,
> Surf to http://servername/reports, click on the properties-tab and add the
> users you want to give access.
> hth,
> Pieter
> "Farsh" <Farsh@.discussions.microsoft.com> wrote in message
> news:1D124954-0038-4726-8BAC-CDBE70EC6F38@.microsoft.com...
> > Hi,
> > I have created reports on my local machine as a test.
> > I can view the reports fine but others can not browse to these reports on
> > my
> > machine.
> > I am not using any code, just the front end report creating of reporting
> > services in Visual studio .net
> >
> > How can I allow other to view these reports on my machine.
> > Thanks
>
>

Brackets appear in column name

I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?

Thanks,

Matt

That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.

SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.

Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.

|||

Arnie,

The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?

Thanks,

Matt

|||

I should have included that square brackets are also included around 'reserved words' when used as object names.

You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.

As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.

|||

Arnie,

I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,

Matt

|||

Matt,

If that information helped you solve your issue, please mark the post as helpful.

Thanks

Brackets appear in column name

I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?

Thanks,

Matt

That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.

SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.

Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.

|||

Arnie,

The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?

Thanks,

Matt

|||

I should have included that square brackets are also included around 'reserved words' when used as object names.

You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.

As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.

|||

Arnie,

I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,

Matt

|||

Matt,

If that information helped you solve your issue, please mark the post as helpful.

Thanks

border line not displaying properly

hi,

when i preview a report in report manager, i am not able to view borderline (right side), but when i take a print out, it is printing properly all the border lines.

i am uisng a subreport and placing it in a table.when subreport width is smaller than the cell it is placed in, the border line on right side is not displaying but in print it is displaying

with regards

suresh babu

I wasn't able to duplicate this behavior. I created a 2" wide subreport with a simple textbox on it. I then created a parent report with a simple 1 row table and placed the subreport in the middle, 3" wide cell. The right-side borders showed up in Report Manager regardless of whether I placed the borders on the table cell or on the subreport body.

Would you mind opening an issue via the below url and attaching your reports so I can take a look?

http://lab.msdn.microsoft.com/ProductFeedback/Default.aspx

Thank you.

|||

I have a similar problem, although it doesn't involve a SubReport.

I have a table with about 8 columns. For 1 row in the table, I want several of the rows to be underlined - which was handled by setting the border property of the TextBoxes. The data was underlined until I entered formulas/expressions to 3 of the Text Boxes. Now, the boxes with expressions do not show the underlines when the report is displayed, but the underlines do show up on the printed report. Text boxes without expressions are fine.

|||

If you would send a sample report (preferably one that goes against one of our sample databases) to the above URL, we'll take a look.

Thanks, Donovan.

|||

Sorry, can't provide a sample.

I will provide a little more info. I tried removing the expressions from the boxes that did not have a border - the border did not come back.

In one row, I have 8 columns. I set the bottom border to 1Pt. Solid for 7 of the text boxes. The border displayed for 4 of the 7 text boxes.

I resolved my problem by setting the top border of the row below to 1 Pt. Solid. I did this for all 7 columns. The report now displays and prints fine.

I believe, but can't prove, that the top border is displaying for 3 columns and the bottom border is displaying for the other 4 columns.

I am pretty sure that the bottom border originally displayed for all 7 columns when I began the report design. I think the border disappeared when I added the expressions - but I can't say that this is absolutely correct.

border line not displaying properly

hi,

when i preview a report in report manager, i am not able to view borderline (right side), but when i take a print out, it is printing properly all the border lines.

i am uisng a subreport and placing it in a table.when subreport width is smaller than the cell it is placed in, the border line on right side is not displaying but in print it is displaying

with regards

suresh babu

I wasn't able to duplicate this behavior. I created a 2" wide subreport with a simple textbox on it. I then created a parent report with a simple 1 row table and placed the subreport in the middle, 3" wide cell. The right-side borders showed up in Report Manager regardless of whether I placed the borders on the table cell or on the subreport body.

Would you mind opening an issue via the below url and attaching your reports so I can take a look?

http://lab.msdn.microsoft.com/ProductFeedback/Default.aspx

Thank you.

|||

I have a similar problem, although it doesn't involve a SubReport.

I have a table with about 8 columns. For 1 row in the table, I want several of the rows to be underlined - which was handled by setting the border property of the TextBoxes. The data was underlined until I entered formulas/expressions to 3 of the Text Boxes. Now, the boxes with expressions do not show the underlines when the report is displayed, but the underlines do show up on the printed report. Text boxes without expressions are fine.

|||

If you would send a sample report (preferably one that goes against one of our sample databases) to the above URL, we'll take a look.

Thanks, Donovan.

|||

Sorry, can't provide a sample.

I will provide a little more info. I tried removing the expressions from the boxes that did not have a border - the border did not come back.

In one row, I have 8 columns. I set the bottom border to 1Pt. Solid for 7 of the text boxes. The border displayed for 4 of the 7 text boxes.

I resolved my problem by setting the top border of the row below to 1 Pt. Solid. I did this for all 7 columns. The report now displays and prints fine.

I believe, but can't prove, that the top border is displaying for 3 columns and the bottom border is displaying for the other 4 columns.

I am pretty sure that the bottom border originally displayed for all 7 columns when I began the report design. I think the border disappeared when I added the expressions - but I can't say that this is absolutely correct.