Item counts...

I have a group w/ two fields, such as:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 1
Burglary; 1
Burglary; 1
Arrest; 1
Citation Issued; 1

However, I need it to do the following:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 3
Arrest; 1
Citation Issued; 1

I have set the INCIDENT TYPE fields to 'suppress on duplications'; however, how do I get the field in the NUMBER OF EVENTS field to successfully "count" the same type of events in the INCIDENT TYPE fields if I do this?

Sorry, I haven't used Crystal Reports (now using version 11) since my VB6.0 days (been awhile).I'm no guru myself, but try this:

Suppress the detail line and make a group footer with two fields, field1 (incident type from your example), and a formula field that is Sum(field2, field1). If you didn't have a group on field1, make one.

Good Luck,
Larry

Item count in collapsed region

Initially I have collapsed regions on my report with drill-down possibility.
How can I show the number of items that are directly beneath that region?
Something like:
+ Beverages (4)
+ Cigarettes (6)
...I didnt try this, but maybe something like:
=Fields!beverages.Value & " (" &
count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
"Marcus Phass" wrote:
> Initially I have collapsed regions on my report with drill-down possibility.
> How can I show the number of items that are directly beneath that region?
> Something like:
> + Beverages (4)
> + Cigarettes (6)
> ...
>
>|||It always says that "the scope parameter must be set to name of containing
group..." error. If I omit the scope, it counts the total number of items
below "Beverages", but I want to display only the number of items in the
level directly below (not all levels below). Countrows gives the same error.
"NI" <NI@.discussions.microsoft.com> wrote in message
news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> I didnt try this, but maybe something like:
> =Fields!beverages.Value & " (" &
> count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
>
> "Marcus Phass" wrote:
> > Initially I have collapsed regions on my report with drill-down
possibility.
> > How can I show the number of items that are directly beneath that
region?
> >
> > Something like:
> >
> > + Beverages (4)
> > + Cigarettes (6)
> > ...
> >
> >
> >|||Try CountDistinct. It worked I tried.
= Fields!Beverages.Value & "(" & CountDistinct(Fields!sub-beverages.Value) &
")"
"Marcus Phass" wrote:
> It always says that "the scope parameter must be set to name of containing
> group..." error. If I omit the scope, it counts the total number of items
> below "Beverages", but I want to display only the number of items in the
> level directly below (not all levels below). Countrows gives the same error.
>
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> > I didnt try this, but maybe something like:
> >
> > =Fields!beverages.Value & " (" &
> > count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
> >
> >
> >
> > "Marcus Phass" wrote:
> >
> > > Initially I have collapsed regions on my report with drill-down
> possibility.
> > > How can I show the number of items that are directly beneath that
> region?
> > >
> > > Something like:
> > >
> > > + Beverages (4)
> > > + Cigarettes (6)
> > > ...
> > >
> > >
> > >
>
>|||Yep, it works! Thanks!
"NI" <NI@.discussions.microsoft.com> wrote in message
news:23233751-33C6-4AB8-9905-66251FBD7640@.microsoft.com...
> Try CountDistinct. It worked I tried.
> = Fields!Beverages.Value & "(" & CountDistinct(Fields!sub-beverages.Value)
&
> ")"
> "Marcus Phass" wrote:
> > It always says that "the scope parameter must be set to name of
containing
> > group..." error. If I omit the scope, it counts the total number of
items
> > below "Beverages", but I want to display only the number of items in the
> > level directly below (not all levels below). Countrows gives the same
error.
> >
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:699E0462-CDC7-4FFA-8C61-2C631672A5F4@.microsoft.com...
> > > I didnt try this, but maybe something like:
> > >
> > > =Fields!beverages.Value & " (" &
> > > count(Fields!sub-beverages.Value,"Group-sub-beverages") & ")"
> > >
> > >
> > >
> > > "Marcus Phass" wrote:
> > >
> > > > Initially I have collapsed regions on my report with drill-down
> > possibility.
> > > > How can I show the number of items that are directly beneath that
> > region?
> > > >
> > > > Something like:
> > > >
> > > > + Beverages (4)
> > > > + Cigarettes (6)
> > > > ...
> > > >
> > > >
> > > >
> >
> >
> >

Issues with removing replication

SQL Server 2005 Standard replicating to an identical server.

My issue:

When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.

The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.

Please Help!

Can this be caused by a long running transaction which is not completed yet?

This posting is provided AS IS with no warranties, and confers no rights.

|||i don't think so since i tried deleting a single row and it sat there for 16 minutes. i had to kill it since no one could connect to the database. but before this situation deleting a couple hundred rows would take about 1 second.|||

Can you try to connect to "master" using 'sa' and do sp_who (sp_who2) and see "who" is accessing that database?

This posting is provided AS IS with no warranties, and confers no rights.

|||i did
USE master
EXEC sp_who

i returned 37 processes. i don't know how to display it on the forum.|||

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

|||If you want to remove replication, and you have limited resources on your box, try stopping sql server agent service first, then do the uninstall. Depending on how many rows you have in MSrepl_commands, it may take a while.|||

Before you post the result from sp_who2, can you check the "blkby" value where "DBName" is equal to the target database? I just try to understand if any process to that database is blocked by some other processes?

This posting is provided AS IS with no warranties, and confers no rights.

|||

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

|||

Greg Y wrote:

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

It

doesn't matter which command I try. And even if I try to delete

something from the GUI it fails. It's not limited to data either. If I

try to delete an object, or try to disable replication I get the same

result; SQL lockup.

|||

Madhu K Nair wrote:

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

I've tried this. SQL locks up. This only happens on 1 server. I've tried uninstalling and re-installing sql server 2005. i still get the same result.|||

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

|||

Greg Y wrote:

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

i'm actually unable to try stopping the service until this weekend. it's our main production server so i can't interrupt service. the only hardware constraints we may run into is HDD read/write. we only have a pair of 15K SCSI drives in RAID1. we plan on upgrading to 8 drives in RAID10. but other than that the servers are 4 way Intel dual cores with 8 physical cores and 8 logical cores, and 16GB of RAM.

It's transactional replication with a single publication. i'm not sure how to find the number of rows in the locations you asked for though?|||

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

|||

Greg Y wrote:

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

We've done performance monitoring analysis for other reasons and the disks weren't even a bottleneck. They are the only part of the system that was really stressed, but no issues. We even had a consulting firm come in and spend a couple months scripting out a load test which went really well.

Is it possible that corrupt data in the database could be causing this issue?

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
Pushkar
I don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?
|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?
|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:

> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issues with creating SQL Server Jobs

Hi,
My application creates 4-5 SQL Server jobs on the production server. I just
want to know what is the impact of creating larger number of jobs on
production server.
Will it hamper it performance, except that SQL Server Agent service should
be running?
Thanks in advance,
PushkarI don't think SQL Jobs use significant resourcesl unless they are
running. The issue would be how often are your jobs scheduled to run?
What do they do?|||I don't think SQL jobs use significant resources unless they are
runnng. The question would be how often are they scheduled to run? What
do they do?|||Maybe, are you thinking in terms of refresh or something like that?
sysjobs,sysjobsteps,sysjobhistory... Do you get the point?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jon" wrote:
> I don't think SQL jobs use significant resources unless they are
> runnng. The question would be how often are they scheduled to run? What
> do they do?
>

Issue with distinct count

I have a table which contains activities, and I am trying to extract a section that has less than a certain number of activities for a given date range. I am receiving an error of

Incorrect syntax near 'activityid' for my select clause. Any help would be greatly appreciated.

Code Snippet

Query I'm working with
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare @.sectionid int, @.maxcount int, @.stdate datetime, @.enddate datetime
set @.sectionid = 37
set @.maxcount = 5
set @.stdate = '01/01/06'
set @.enddate = '01/31/06'

select distinct (count activityid) <= @.maxcount, a.sectionid
from activity a
where
a.sectionid = @.sectionid
AND
(a.date between @.stdate and @.enddate)

Table Definition
>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE PROCEDURE [dbo].[p_Activity_Create]
(
@.ActivityID INT OUTPUT,
@.SectionID INT,
@.ActivityCategoryID INT,
@.STIAssessmentTestID INT,
@.Name VARCHAR(30),
@.Date DATETIME,
@.MaximumScore DECIMAL(6,2),
@.WeightAddition DECIMAL(9,6),
@.WeightMultiplier DECIMAL(9,6),
@.IsAssessment BIT,
@.IsHomework BIT,
@.IsScored BIT,
@.MayBeDropped BIT,
@.IsDropped BIT,
@.IsComplete BIT,
@.Unit VARCHAR(50)

)
AS

SET NOCOUNT ON

INSERT INTO [dbo].[Activity]
(SectionID,
ActivityCategoryID,
STIAssessmentTestID,
[Name],
[Date],
MaximumScore,
WeightAddition,
WeightMultiplier,
IsAssessment,
IsHomework,
IsScored,
MayBeDropped,
IsDropped,
IsComplete,
Unit)
VALUES
(@.SectionID,
@.ActivityCategoryID,
@.STIAssessmentTestID,
@.Name,
@.Date,
@.MaximumScore,
@.WeightAddition,
@.WeightMultiplier,
@.IsAssessment,
@.IsHomework,
@.IsScored,
@.MayBeDropped,
@.IsDropped,
@.IsComplete,
@.Unit)

SET @.ActivityID = SCOPE_IDENTITY()
GO

Table data
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ActivityID SectionID Name Date ActivityCategoryID STIAssessmentTestID MaximumScore WeightAddition WeightMultiplier IsAssessment IsHomework IsScored MayBeDropped IsDropped IsComplete 11 37 Homework 9/20/2005 49 NULL 25 0 1 0 1 1 1 0 0 14 37 Midterm 10/16/2005 51 NULL 100 0 1 1 0 1 1 0 0 38 37 Homework 1/16 1/16/2006 49 NULL 25 0 1 0 1 1 1 0 0 39 37 Homework 1/17 1/17/2006 49 NULL 25 0 1 0 1 1 1 0 0 40 37 Extra 1/18 1/18/2006 52 NULL 50 0 1 0 0 1 1 0 0 41 37 Homework 1/19 1/19/2006 49 NULL 25 0 1 0 1 1 1 0 0 12 37 Quiz 1/20/2006 50 NULL 50 0 1 0 0 1 1 0 0 13 37 Test 4/20/2006 51 NULL 100 0 1 0 0 1 1 0 0 15 37 Final 5/20/2006 51 NULL 100 0 1 1 0 1 1 0 0 1 42 Homework 9/20/2005 5 NULL 25 0 1 0 1 1 1 0 0 4 42 Midterm 10/16/2005 7 NULL 100 0 1 1 0 1 1 0 0 2 42 Quiz 1/20/2006 6 NULL 50 0 1 0 0 1 1 0 0 3 42 Test 4/20/2006 7 NULL 100 0 1 0 0 1 1 0 0 5 42 Final 5/20/2006 7 NULL 100 0 1 1 0 1 1 0 0 26 43 Homework 9/20/2005 85 NULL 25 0 1 0 1 1 1 0 0 29 43 Midterm 10/16/2005 87 NULL 100 0 1 1 0 1 1 0 0 27 43 Quiz 1/20/2006 86 NULL 50 0 1 0 0 1 1 0 0 42 43 Homework 4/17 4/17/2006 85 NULL 25 0 1 0 1 1 1 0 0 43 43 Extra 4/18 4/18/2006 88 NULL 50 0 1 0 0 1 1 0 0 44 43 Homework 4/19 4/19/2006 85 NULL 25 0 1 0 1 1 1 0 0 28 43 Test 4/20/2006 87 NULL 100 0 1 0 0 1 1 0 0 45 43 Extra 4/21 4/21/2006 88 NULL 50 0 1 0 0 1 1 0 0 30 43 Final 5/20/2006 87 NULL 100 0 1 1 0 1 1 0 0 16 49 Homework 9/20/2005 61 NULL 25 0 1 0 1 1 1 0 0 19 49 Midterm 10/16/2005 63 NULL 100 0 1 1 0 1 1 0 0 17 49 Quiz 1/20/2006 62 NULL 50 0 1 0 0 1 1 0 0 18 49 Test 4/20/2006 63 NULL 100 0 1 0 0 1 1 0 0 20 49 Final 5/20/2006 63 NULL 100 0 1 1 0 1 1 0 0 21 50 Homework 9/20/2005 65 NULL 25 0 1 0 1 1 1 0 0 24 50 Midterm 10/16/2005 67 NULL 100 0 1 1 0 1 1 0 0 22 50 Quiz 1/20/2006 66 NULL 50 0 1 0 0 1 1 0 0 23 50 Test 4/20/2006 67 NULL 100 0 1 0 0 1 1 0 0 25 50 Final 5/20/2006 67 NULL 100 0 1 1 0 1 1 0 0 31 53 Homework 9/20/2005 121 NULL 25 0 1 0 1 1 1 0 0 34 53 Midterm 10/16/2005 123 NULL 100 0 1 1 0 1 1 0 0 32 53 Quiz 1/20/2006 122 NULL 50 0 1 0 0 1 1 0 0 33 53 Test 4/20/2006 123 NULL 100 0 1 0 0 1 1 0 0 35 53 Final 5/20/2006 123 NULL 100 0 1 1 0 1 1 0 0 6 67 Homework 9/20/2005 33 NULL 25 0 1 0 1 1 1 0 0 9 67 Midterm 10/16/2005 35 NULL 100 0 1 1 0 1 1 0 0 7 67 Quiz 1/20/2006 34 NULL 50 0 1 0 0 1 1 0 0 8 67 Test 4/20/2006 35 NULL 100 0 1 0 0 1 1 0 0 10 67 Final 5/20/2006 35 NULL 100 0 1 1 0 1 1 0 0

43 row(s) affected)

The problem is with the line:

select distinct (count activityid) <= @.maxcount, a.sectionid

To me this looks like pseudocode.

You might try something more like:

select sectionId,
count(distinct activityId)
from activity a
where a.sectionId = @.sectionId
and a.date between @.stDate and @.endDate
group by sectionId
having count(distinct activityId) <= @.maxCount