precisions of DateTime

Hi,
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:

> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:

> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:

> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga..
.
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP
--sql

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig Hessel
I've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>
|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>
|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>> Sorry for incomplete info here. This is infrequent problem we have.
>> We have SQL 2000 (sp3?). Our internet team inserts rows into
>> a table. Don't know the mechanism they use (yet). We use a
>> VB6 app to read the rows. Two rows have appeared in the table
>> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
>> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
>> for year. When I did SELECT CONVERT (float, colname) I got
>> -61024.0 (or some value pretty close to that).
>> Has anyone see anything similar?
>> Craig Hessel
>>
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
--
David Portas
SQL Server MVP
--

Possible todo some form of DISTINCT filtering?

Hi guys, I have a dataset that returns rows that look like the following
field | value
--
1 | 7
1 | 7
2 | 4
8 | 90
Is it possible to remove the duplicate (1 | 7) row directly within a LIST
control (or similar?)? I cannot use a DISTINCT directly in the SQL as
elsewhere I need to display the duplicate row.
Thanks for any help
TazSilly me, thats what grouping is for lol.
Thanks anyways
Taz

Possible to access no. rows in groups outside the table?

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.

Thanks again.

|||

Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.

Shyam

|||

Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:

Group1 Total: 4

Group2 Total: 7

Group3 Total: 22

Then a single textbox at the top of the page would say

Group1 + Group2 = 11

Group1 + Group2 = 50% of Group3.

How would I accomplish this using the invisible list?

Thanks again.

|||

Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.

In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:

=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))

=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))

=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))

And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName

Shyam

Possible Bug Using TOP and Paging via a Temp Table

This is not a "bug" per se, as I know that the order of rows in SQL
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>

Possible BUG in RS.

When populating a matrix and having an expression to set the background of
the row , in this case I am setting alternate shading of rows to make reading
easier, I would expect the whole matrix row to to be shaded, not just the
groups in the row that have a value in them.
To simplify my descritpion here, I ask MS why does RS only allow the data
groups that have a value in them set the background to that of the expression
I specify.
Surely the group should set the background attribute I specify regardless of
a value contained in the group.
The resultant display is like a checker board effect, as groups in the row
with data will be grey background and those without the value in the same
group will remain transparent.
Has anyone else had similar issues with Matrices in RS?
cheers
PaulYou want to read the following blog article:
http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PaulQld" <PaulQld@.discussions.microsoft.com> wrote in message
news:E2EFE16E-98F0-4DC2-A55D-40709956C697@.microsoft.com...
> When populating a matrix and having an expression to set the background of
> the row , in this case I am setting alternate shading of rows to make
> reading
> easier, I would expect the whole matrix row to to be shaded, not just the
> groups in the row that have a value in them.
> To simplify my descritpion here, I ask MS why does RS only allow the data
> groups that have a value in them set the background to that of the
> expression
> I specify.
> Surely the group should set the background attribute I specify regardless
> of
> a value contained in the group.
> The resultant display is like a checker board effect, as groups in the row
> with data will be grey background and those without the value in the same
> group will remain transparent.
> Has anyone else had similar issues with Matrices in RS?
> cheers
> Paul
>|||Robert, thankyou for your link to Chris's blogg.
Paul
"Robert Bruckner [MSFT]" wrote:
> You want to read the following blog article:
> http://blogs.msdn.com/chrishays/archive/2004/08/30/223068.aspx
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "PaulQld" <PaulQld@.discussions.microsoft.com> wrote in message
> news:E2EFE16E-98F0-4DC2-A55D-40709956C697@.microsoft.com...
> > When populating a matrix and having an expression to set the background of
> > the row , in this case I am setting alternate shading of rows to make
> > reading
> > easier, I would expect the whole matrix row to to be shaded, not just the
> > groups in the row that have a value in them.
> >
> > To simplify my descritpion here, I ask MS why does RS only allow the data
> > groups that have a value in them set the background to that of the
> > expression
> > I specify.
> >
> > Surely the group should set the background attribute I specify regardless
> > of
> > a value contained in the group.
> >
> > The resultant display is like a checker board effect, as groups in the row
> > with data will be grey background and those without the value in the same
> > group will remain transparent.
> >
> > Has anyone else had similar issues with Matrices in RS?
> >
> > cheers
> > Paul
> >
> >
>
>