alternating item color

I was wondering if there was an easy way to do an alternating item
color in the details section like you can in a DataGrid?
Thanks!Found the solution in another post
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/649a54ddcc0e97bc/42b98c6e786e5173?q=DataGrid&rnum=8#42b98c6e786e5173

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I call
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa0400225912014:57:14.380
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa03017225912014:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg Holmes
You might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes
|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do you
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it go
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did was
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it go
> up by 2s. I also added an auto incrementing field to the table and you can
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>
|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attention
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>
|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
|||No, now that I'm using a text query in ASP.NET instead of a stored procedure,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I cal
l
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg HolmesYou might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do yo
u
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it g
o
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did wa
s
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it
go
> up by 2s. I also added an auto incrementing field to the table and you ca
n
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
>|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attentio
n
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
>|||No, now that I'm using a text query in ASP.NET instead of a stored procedure
,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for
).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column
.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>sql

Alternating Detail Sections

Is there a way to have two tables and have the detail section show
alternating records? (ie:51795 in one table and 51795P in the other)
Horizontallay
I need to compare records from two different datasets and have no idea how I
am going to do this?
--
Thank You, LeoHello Leo,
Insert field from different dataset is not supported in the table. However,
you may use function to transfer it. However, it still may not work as you
expect. The best option is using method to combine the data you want in one
dataset by using query or store prcedure.
Please feel free to respond back if you have further questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Alternating Detail Sections
| thread-index: AcS8VnfT0lQlTSXmSBqXC/bhzmR85A==| X-WBNR-Posting-Host: 64.91.16.201
| From: "=?Utf-8?B?VHJ1c3N3b3Jrc0xlbw==?=" <Leo@.noemail.noemail>
| Subject: Alternating Detail Sections
| Date: Wed, 27 Oct 2004 11:55:03 -0700
| Lines: 10
| Message-ID: <63592D60-D9BA-47F3-8325-FE139745CF93@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:33335
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there a way to have two tables and have the detail section show
| alternating records? (ie:51795 in one table and 51795P in the other)
| Horizontallay
|
| I need to compare records from two different datasets and have no idea
how I
| am going to do this?
|
|
| --
| Thank You, Leo
|

Alternating BackgroundColor for rows in a table

I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.

Alternating background colors in table

Is there anyway to have alternating background colors in a table that is in a list? The existing methods I've seen work except that the tables don't always start with the same background color, as so:

BEGIN LIST
TABLE 1
(white background)
(gainsboro background)
(white background)

TABLE 2
(gainsboro background)
(white background)
(gainsboro background)
END LIST

Hopefully this gives you an idea of what I'm talking about. In table 2 above, the first row should have a white background, just like in table 1. It appears that SSRS treats all rows within a list as 1 table.

Is there any solution for this problem?

Hi,

try this:

=Iif(RowNumber(Nothing) Mod 2, "gainsboro ", "White")

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens, but that's what I'm already using. The color alternates, but it doesn't distinguish between occurrences of the table within a single list.|||

THen you have to provide the scope in the paranthetes. So replace the word Nothing with the name of the scope.

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

Alternating background color

Got a dataset that is used to populate a table. Want to alternate the
background color on every other row in the displayed detail group. Easy
enough right? Here's the catch: the output is grouped at display time. A
query output might be:
KEY Value1 Value2
A 0 1
A 1 0
B 5 0
C 3 0
C 0 7
etc...
The DISPLAY output is grouped on the KEY, and the two values are summed to
give me a display such as:
KEY Value1 Value2
A 1 1
B 5 0
C 3 7
Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
"Grey")", it counts EVERY row returned from the original query, not the
grouped output, so I don't get a uniform white-grey-white pattern. Anyone
know a workaround for this?
TIA,
BrianOk, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here.
I created a little routine in the custom Code area of the report that simply
toggles and returns an integer value:
Dim Public bgColor As Integer = 0
Public Function alternateColor As Integer
If bgColor = 0
bgColor = 1
return bgColor
else
bgColor = 0
return bgColor
end if
End Function
When i put my method call in the background color on the entire table ROW,
the result was alternating COLUMN colors. This is because the method was
called for every cell (column) in the row. In order to get alternating ROW
color, I only called the alternateColor routine in the FIRST column in the
table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
column in the row would simply check the "Code.bgColor" value for its
current value, and base its color on that (iif(Code.bgColor = 0, "white",
"grey")).
Maybe this will come in handy for someone else someday....
Brian
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> Got a dataset that is used to populate a table. Want to alternate the
> background color on every other row in the displayed detail group. Easy
> enough right? Here's the catch: the output is grouped at display time. A
> query output might be:
> KEY Value1 Value2
> A 0 1
> A 1 0
> B 5 0
> C 3 0
> C 0 7
> etc...
> The DISPLAY output is grouped on the KEY, and the two values are summed to
> give me a display such as:
> KEY Value1 Value2
> A 1 1
> B 5 0
> C 3 7
> Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> "Grey")", it counts EVERY row returned from the original query, not the
> grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> know a workaround for this?
> TIA,
> Brian
>|||Great solution, I've been playing around with RowNumber for ages - this is
much better!!!
Thanks Brian!!!
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange
alternate highlighting results using the conventional method. This should
work nicely, cheers
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to
back colors instead of alternating the colors.
Any ideas?
Thanks,
Don
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>

Alternating BackColor

Hi
I wand to use an Alternating Backcolor in a Report for every Row. In don't
find this property.
How can i do it?
Many thanks.
AndyHi,
There is no property to do this
Here is the post replied by Bruce Johnson [MSFT]
Hope this helps
At the end of this posting are two reports that demonstrate how to alternate
row colors on a table and a matrix.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christian Larsen" <ChristianLarsen@.discussions.microsoft.com> wrote in
message news:608EB056-430E-44A2-AD02-1621CA41FC3F@.microsoft.com...
> Hi all.
> How do i get a different color on odd rows in a table or matrix?
> /Chrsitian
TableGreenBar.rdl
================================================================================<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RowNumber(Nothing) Mod 2,
"PaleGreen", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "Cornsilk", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
MatrixGreenBar.rdl
================================================================================<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Qty.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="Category">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.125in</Top>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Right>None</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "AliceBlue", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value & " " &
RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="Count">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Color">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Left>None</Left>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=Value</BackgroundColor>
<FontSize>1pt</FontSize>
<Color>=Value</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "AliceBlue", "White")</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code />
<Width>6.875in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.Country, SUM([Order
Details].Quantity) AS Qty, Categories.CategoryName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID
GROUP BY Customers.Country, Categories.CategoryName</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
"Andreas Szabo" <Andreas.Szabo_PLEASE_INSERT_ADD_complementa.ch> wrote in
message news:O%23rS43yuEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Hi
> I wand to use an Alternating Backcolor in a Report for every Row. In don't
> find this property.
> How can i do it?
> Many thanks.
> Andy
>

Alternate ways of deleting records - without logging

Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:

> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>

Alternate to sql web assistant

I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users.

these html files that are generated are processed via a JOB in sql server.

I am in process of replacing of the web assistant procedures.

Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure.

Is there any other possible way to do it?

Hi Brinda,

I have similar stored procedures that use web assistnant to generate xls files and then email them. I have heard that web assistant can be replaced with reporting services in SQL2K5, but I am unaware of the details of how to do this-if anyone has details on this I am interested to learn about it.

Thanks,

-Dave

alternate to formula

I have a table where one of the field is having formula, where UDF is used.
Is there any way by which formula can be removed and any other method is
used like trigger,
Because we can not have index on a formula based fieldCOMPUTED column? Can you show us the source?
"Vikram" <aa@.aa> wrote in message
news:eq0vE6aeGHA.3572@.TK2MSFTNGP03.phx.gbl...
>I have a table where one of the field is having formula, where UDF is used.
> Is there any way by which formula can be removed and any other method is
> used like trigger,
> Because we can not have index on a formula based field
>|||I think you cannot have an index on computed field either.
Though in SQL Server 2005 you can set the computed column as persisited and
create an index on it.|||Why not?
create table test (c1 int not null,c2 as c1*10)
create index ind_comp on test(c2)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:45CD1DD1-6AF4-4872-AD5D-28450AA30E23@.microsoft.com...
>I think you cannot have an index on computed field either.
> Though in SQL Server 2005 you can set the computed column as persisited
> and
> create an index on it.|||Oops.. sorry... you are right.. tea time for me :)
I it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met:
http://msdn2.microsoft.com/en-us/library/ms189292.aspx
In SQL 2000 a computed column is not persisted until it's used in an index,
while SQL 2005 has this new option that you've mentioned.
ML
http://milambda.blogspot.com/|||> I it with foreign key constraints
You can create an index on the column that has FK
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BB66B25E-F4A8-4163-B5C5-8C20A8734C48@.microsoft.com...
> Oops.. sorry... you are right.. tea time for me :)
> I it with foreign key constraints|||No.. i meant foreign key on a computed column.|||Yes ,it is true
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0A422A1B-2100-4D50-A610-AAD305AFDD3B@.microsoft.com...
> No.. i meant foreign key on a computed column.
>sql

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARR
All documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>
|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not
>
|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you[vbcol=seagreen]
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
..
>

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > Hi ,
> > Is Yukon not supporting all dbcc commands or if they are used they are
not
> > reliable .
> > If its not supporting where do I find the alternate DMVs for the same .
> >
> >
> > Thanks
> > ARR
> >
> >
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> > All documented DBCC commands in Yukon are backwards-compatible and are
> > supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been
deprecated
> > and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> > INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
> As
> > always, undocumented DBCC commands are not supported and we reserve the
> > right to alter or completely remove them at any time. Can you tell us
> what
> > piece of information you're looking for specifically?
> >
> > Thanks,
> > Ryan Stonecipher
> > Microsoft SQL Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> > > Hi ,
> > > Is Yukon not supporting all dbcc commands or if they are used they are
> not
> > > reliable .
> > > If its not supporting where do I find the alternate DMVs for the same
.
> > >
> > >
> > > Thanks
> > > ARR
> > >
> > >
> >
> >
>

alternate to DBCC command

Hi ,
Is Yukon not supporting all dbcc commands or if they are used they are not
reliable .
If its not supporting where do I find the alternate DMVs for the same .
Thanks
ARRAll documented DBCC commands in Yukon are backwards-compatible and are
supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats). As
always, undocumented DBCC commands are not supported and we reserve the
right to alter or completely remove them at any time. Can you tell us what
piece of information you're looking for specifically?
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> Is Yukon not supporting all dbcc commands or if they are used they are not
> reliable .
> If its not supporting where do I find the alternate DMVs for the same .
>
> Thanks
> ARR
>|||Thanks for the reply .
am looking for all documented DBCC command alternative .
Also looking for some difference between sql2000 sysobjects & Yukon . Do you
have any whitepaper for the same . Currently yukon has 46 DMVs but am not
getting info about all those . Please help for the same .
Thanks
ARR
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> All documented DBCC commands in Yukon are backwards-compatible and are
> supported. Some, such as INDEXDEFRAG and SHOWCONTIG have been deprecated
> and have enhanced Yukon equivalents in the syntax (ALTER INDEX for
> INDEXDEFRAG) or in the DMV catalog (such as dm_db_index_physical_stats).
As
> always, undocumented DBCC commands are not supported and we reserve the
> right to alter or completely remove them at any time. Can you tell us
what
> piece of information you're looking for specifically?
> Thanks,
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:ebf1qrpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
not[vbcol=seagreen]
>|||All the documented DBCC commands are reliable and none have alternatives in
Yukon except for the two that Ryan mentions below. What do you mean when you
say they're 'not reliable' and why do you want alternatives for the
operations they perform?
The DMVs will be fully documented in the BOL for Beta 3.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:OhBU2gqAFHA.608@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply .
> am looking for all documented DBCC command alternative .
> Also looking for some difference between sql2000 sysobjects & Yukon . Do
you
> have any whitepaper for the same . Currently yukon has 46 DMVs but am not
> getting info about all those . Please help for the same .
> Thanks
> ARR
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:em2jKzpAFHA.3492@.TK2MSFTNGP12.phx.gbl...
deprecated[vbcol=seagreen]
> As
> what
> rights.
> not
.[vbcol=seagreen]
>

Alternate to cursors

Hi,
I have a situation where I am loading data into a staging
table for multiple data sources. My next step is to pick up the
records from the staging table and compare with the data in the
database and based on the certain conditions, decide whether to insert
the data into the database or update an existing record in the
database. I have to do this job as an sp and schedule it to run on the
server as per the requirements. I thought that cursors are the only
option in this situation. Can anyone suggest if there is any other way
to achieve this in SQL 2005 please.

Thanks

SeshadriIn general this is done with two commands, an UPDATE of existing rows
followed by an INSERT of new rows. Very generally:

UPDATE Target
SET cola = A.cola,
colb - A.colb
FROM Staging as A
WHERE Target.keycol = Staging.keycol

INSERT Target
SELECT keycol, cola, colb
FROM Staging as A
WHERE NOT EXISTS
(SELECT * FROM Target as B
WHERE A.keycol = B.keycol)

Whether this fits your requirements is unknown because you didn't
provide much information. It would require knowing at least the table
definitions and keys, as well as the "certain conditions".

Roy Harvey
Beacon Falls, CT

On Mon, 01 Oct 2007 00:12:22 -0700, srirangam.seshadri@.gmail.com
wrote:

Quote:

Originally Posted by

>Hi,
I have a situation where I am loading data into a staging
>table for multiple data sources. My next step is to pick up the
>records from the staging table and compare with the data in the
>database and based on the certain conditions, decide whether to insert
>the data into the database or update an existing record in the
>database. I have to do this job as an sp and schedule it to run on the
>server as per the requirements. I thought that cursors are the only
>option in this situation. Can anyone suggest if there is any other way
>to achieve this in SQL 2005 please.
>
>Thanks
>
>Seshadri

Alternate to a not in query

-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)

insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')

-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0

This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):

Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null

To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.

Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null

Razvan,

Why worse?

The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.

My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.

It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.

> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.

I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.

I would write the query as:

Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0

In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

alternate to "NOT IN " query

any better way of writing this
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0Vikram
Select serialnumber from tbl_test2
where not exists
(select * from tbl_test where tbl_test.serialnumber
=tbl_test2.serialnumber )
and exportedflag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>|||Also:
Select SerialNumber from tbl_test2
left join tbl_test on tbl_test2.SerialNumber = tbl_test.SerialNumber
where tbl_test.serialNumber is null and exportedFlag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1

column2

row1

data1,1

data1,2

row2

data2,1

data2,2

row3

data3,1

data3,2

Please, if somebody can help me

Thanks

hey there

there is something on this page that may help you

http://blogs.msdn.com/chrishays/

|||

Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||

Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.

I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.