Create an automatic SUM ROW

Hello.
In a previous program i used to create reports, I had the option to enter a "SUM ROW".

I saw that in RS2005 I can check the "add total" when using the report wizard and it will create for me a row with SUM for my column.

Is there a way to create a row like this automatically with out the wizard (a sum row for all my columns instead of creating a textbox with "sum" for each column)?

Thanks in advance,
Roy.

Hi,

nom this behaviour is influenced by the normal summing / grouping operations. A sum in a row has nothing to do with this. I don′t think that the standard behaviour of the wizrad cab be changed in Reporting Services.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Hi Jens, Thanks for the replay.

I don't want the "standard behaviour" of the wizard to change.
I'll like the "SUM" je is makeing in the entire row.
what i whanted to know is:
if i'm making my own report, not with the wizard, is there a simple way to create a sum row like the wizard with out creating a textbox for every column manually?

Thanks in advance,
Roy.
|||

After making the report with the wizard you can simply use the Expression =SUM(Fields!SomeValueinQuery1.Value + Fields!SomeValueinQuery2.Value + Fields!SomeValueinQuery3.Value +Fields!SomeValueinQuery4.Value) OR use the appropiate ReportItems!Textbox1.Value, but there is no function like in Excel which automatically tries to gues what you want to summarize (even this function in Excel sometime guesses wrong what you want to achieve :-) )

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Crash with "Failed Assertion"

Hello,
I have a table with 2 indexes.
My C++ program deletes some data from this table and then
does bcp data in. It does it twice in one run.
After I run my program for the first time I get the
correct result. When I run it again, the first bcp is
successful, however there is no data from the second bcp.
If I repeat it again, the result is correct; one more
time - the data from the first bcp only.
If I try to debug it, everything works fine.
If I run it without break points in the program, I
duplicate a problem.
SQL Server log records an error:
Failed Assertion = '(logMode != nonlogged) || (dbt-
>dbt_dbid == TEMPDBID)'
and creates a SQLdump file, which I don't know how to
interprit.
If I remove indexes from the table, everything works fine.
Does anyone understand what's going on?
I would appriciate any help.
Thanks.Are your indexes unique indexes? Chances are thats why it's failing the
second time, if you're using the same data in you're BCP load.
-Morgan
"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in message
news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> Hello,
>
> I have a table with 2 indexes.
> My C++ program deletes some data from this table and then
> does bcp data in. It does it twice in one run.
> After I run my program for the first time I get the
> correct result. When I run it again, the first bcp is
> successful, however there is no data from the second bcp.
> If I repeat it again, the result is correct; one more
> time - the data from the first bcp only.
> If I try to debug it, everything works fine.
> If I run it without break points in the program, I
> duplicate a problem.
> SQL Server log records an error:
> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >dbt_dbid == TEMPDBID)'
> and creates a SQLdump file, which I don't know how to
> interprit.
> If I remove indexes from the table, everything works fine.
> Does anyone understand what's going on?
> I would appriciate any help.
> Thanks.
>
>|||Thank you for the reply.
My indexes are not unique, nor cluster.
Tanya.
>--Original Message--
>Are your indexes unique indexes? Chances are thats why
it's failing the
>second time, if you're using the same data in you're BCP
load.
>-Morgan
>"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
message
>news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
>> Hello,
>>
>> I have a table with 2 indexes.
>> My C++ program deletes some data from this table and
then
>> does bcp data in. It does it twice in one run.
>> After I run my program for the first time I get the
>> correct result. When I run it again, the first bcp is
>> successful, however there is no data from the second
bcp.
>> If I repeat it again, the result is correct; one more
>> time - the data from the first bcp only.
>> If I try to debug it, everything works fine.
>> If I run it without break points in the program, I
>> duplicate a problem.
>> SQL Server log records an error:
>> Failed Assertion = '(logMode != nonlogged) || (dbt-
>> >dbt_dbid == TEMPDBID)'
>> and creates a SQLdump file, which I don't know how to
>> interprit.
>> If I remove indexes from the table, everything works
fine.
>> Does anyone understand what's going on?
>> I would appriciate any help.
>> Thanks.
>>
>
>.
>|||Any replication on the tables involved?
How about triggers?
If the answer to the above is "no", the only thing I can think of is that
you're running out of space in the logfile and you have configured for
"Simple" recovery model. It would work in "debug" because SQLServer would
have time to remove the previous checkpoint...
Bruce
"Tanya Bardakh" <anonymous@.discussions.microsoft.com> wrote in message
news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
> Thank you for the reply.
> My indexes are not unique, nor cluster.
> Tanya.
>
> >--Original Message--
> >Are your indexes unique indexes? Chances are thats why
> it's failing the
> >second time, if you're using the same data in you're BCP
> load.
> >
> >-Morgan
> >
> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
> message
> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> >> Hello,
> >>
> >>
> >> I have a table with 2 indexes.
> >> My C++ program deletes some data from this table and
> then
> >> does bcp data in. It does it twice in one run.
> >>
> >> After I run my program for the first time I get the
> >> correct result. When I run it again, the first bcp is
> >> successful, however there is no data from the second
> bcp.
> >> If I repeat it again, the result is correct; one more
> >> time - the data from the first bcp only.
> >>
> >> If I try to debug it, everything works fine.
> >>
> >> If I run it without break points in the program, I
> >> duplicate a problem.
> >>
> >> SQL Server log records an error:
> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >> >dbt_dbid == TEMPDBID)'
> >> and creates a SQLdump file, which I don't know how to
> >> interprit.
> >>
> >> If I remove indexes from the table, everything works
> fine.
> >>
> >> Does anyone understand what's going on?
> >>
> >> I would appriciate any help.
> >> Thanks.
> >>
> >>
> >>
> >
> >
> >.
> >|||Thank you, Bruce
I don't have any triggers & replications.
Space should not be an issue, since I have plenty of it
(30 GB). Actually I am reloading same data (about 200
rows) over and over again. The only thing is it's
successful the first time and crashes SQL server on the
subsequent run.
>--Original Message--
>Any replication on the tables involved?
>How about triggers?
>If the answer to the above is "no", the only thing I can
think of is that
>you're running out of space in the logfile and you have
configured for
>"Simple" recovery model. It would work in "debug"
because SQLServer would
>have time to remove the previous checkpoint...
>Bruce
>"Tanya Bardakh" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
>> Thank you for the reply.
>> My indexes are not unique, nor cluster.
>> Tanya.
>>
>> >--Original Message--
>> >Are your indexes unique indexes? Chances are thats why
>> it's failing the
>> >second time, if you're using the same data in you're
BCP
>> load.
>> >
>> >-Morgan
>> >
>> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
>> message
>> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
>> >> Hello,
>> >>
>> >>
>> >> I have a table with 2 indexes.
>> >> My C++ program deletes some data from this table and
>> then
>> >> does bcp data in. It does it twice in one run.
>> >>
>> >> After I run my program for the first time I get the
>> >> correct result. When I run it again, the first bcp is
>> >> successful, however there is no data from the second
>> bcp.
>> >> If I repeat it again, the result is correct; one more
>> >> time - the data from the first bcp only.
>> >>
>> >> If I try to debug it, everything works fine.
>> >>
>> >> If I run it without break points in the program, I
>> >> duplicate a problem.
>> >>
>> >> SQL Server log records an error:
>> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
>> >> >dbt_dbid == TEMPDBID)'
>> >> and creates a SQLdump file, which I don't know how to
>> >> interprit.
>> >>
>> >> If I remove indexes from the table, everything works
>> fine.
>> >>
>> >> Does anyone understand what's going on?
>> >>
>> >> I would appriciate any help.
>> >> Thanks.
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||You should contact Product Support (http://support.microsoft.com) who will
be able to help you figure this out.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"bcarson@.asgoth.com" <anonymous@.discussions.microsoft.com> wrote in message
news:027e01c39d82$9d9c2070$a301280a@.phx.gbl...
> Thank you, Bruce
> I don't have any triggers & replications.
> Space should not be an issue, since I have plenty of it
> (30 GB). Actually I am reloading same data (about 200
> rows) over and over again. The only thing is it's
> successful the first time and crashes SQL server on the
> subsequent run.
>
> >--Original Message--
> >Any replication on the tables involved?
> >How about triggers?
> >
> >If the answer to the above is "no", the only thing I can
> think of is that
> >you're running out of space in the logfile and you have
> configured for
> >"Simple" recovery model. It would work in "debug"
> because SQLServer would
> >have time to remove the previous checkpoint...
> >
> >Bruce
> >
> >"Tanya Bardakh" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0de201c39d64$5d354b10$a401280a@.phx.gbl...
> >> Thank you for the reply.
> >>
> >> My indexes are not unique, nor cluster.
> >>
> >> Tanya.
> >>
> >>
> >> >--Original Message--
> >> >Are your indexes unique indexes? Chances are thats why
> >> it's failing the
> >> >second time, if you're using the same data in you're
> BCP
> >> load.
> >> >
> >> >-Morgan
> >> >
> >> >"Tanya Bardakh" <tbardakh@.dysanalytics.com> wrote in
> >> message
> >> >news:0cf001c39d5d$a3068a20$a401280a@.phx.gbl...
> >> >> Hello,
> >> >>
> >> >>
> >> >> I have a table with 2 indexes.
> >> >> My C++ program deletes some data from this table and
> >> then
> >> >> does bcp data in. It does it twice in one run.
> >> >>
> >> >> After I run my program for the first time I get the
> >> >> correct result. When I run it again, the first bcp is
> >> >> successful, however there is no data from the second
> >> bcp.
> >> >> If I repeat it again, the result is correct; one more
> >> >> time - the data from the first bcp only.
> >> >>
> >> >> If I try to debug it, everything works fine.
> >> >>
> >> >> If I run it without break points in the program, I
> >> >> duplicate a problem.
> >> >>
> >> >> SQL Server log records an error:
> >> >> Failed Assertion = '(logMode != nonlogged) || (dbt-
> >> >> >dbt_dbid == TEMPDBID)'
> >> >> and creates a SQLdump file, which I don't know how to
> >> >> interprit.
> >> >>
> >> >> If I remove indexes from the table, everything works
> >> fine.
> >> >>
> >> >> Does anyone understand what's going on?
> >> >>
> >> >> I would appriciate any help.
> >> >> Thanks.
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

CR Out of Memory Error

I am using VB6(SP6) / CR(8.0.1) / WISE INSTALLER(8.1) to distribute a small program. The data is all contained in MS Access 2000 tables and I am using ADO to create each report recordset.

In CR designer I use a TTX file to design the report. This TTX file contains all colums from the recordset and their descriptions i.e. Name, Type, Length and example data.

I create a distribution CD using Wise Installer. Once installed on a Win98, Win2000, Win XP computer everything runs great until the user attempts to run any of the CR reports. On screen the SQL query runs and then calls the CR report. The CR viewer appears and immediately we get an "Out of Memory" error response from CR.

My only remedy is to install MS Office Pro or MS Access 2000-2002. I have use (paid for support) at VB, CR and Wise and received no fix for this problem. VB says it CR, CR says its VB and Wise simply doesn't know?

Thanks in advance for any help on this problem...Maybe you have a memory leak in your VB program?|||Don't think this is the problem... as installing access or office pro solves the problem. This could not fix a memory leak inside the vb program. This problem only occurs if we install to a new computer..

Thanks for your reply

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.
Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

counting the years in multiple rows question

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :

Code Snippet

SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))

but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))

P.S i want to be able to use it in a where clause :

Code Snippet

select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@.Exp)

thanks in advance

You have to use the grouping together with the having clause:

declare @.Exp as int

select max(A) as A, max(B) as B from jobap

where JobAp.ind = JobApExp.JobAp

HAVING SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > @.Exp

|||

create table #Personnel(id int,name varchar(20))
insert #Personnel select 1,'madhu'
insert #Personnel select 2 ,'zyx'

create table #PersonnelExp (ID int,fmdt datetime,todt datetime)
delete #PersonnelExp
insert #PersonnelExp select 1,'1990-1-1','1999-1-1'
insert #PersonnelExp select 1,'1999-1-2','2002-1-2'
insert #PersonnelExp select 1,'2003-1-2','2007-1-2'
insert #PersonnelExp select 2,'1995-1-1','1999-1-1'
insert #PersonnelExp select 2,'1999-1-2','2004-1-2'
insert #PersonnelExp select 2,'2005-1-2','2007-1-2'

select a.ID,a.Name,b.TotExp From #Personnel a,
(select id,SUM(DATEDIFF(year , fmdt, todt )) As TotExp from #PersonnelExp group by id) b
where a.ID=b.ID
and totexp>12

Check this script

Madhu

|||

You may want to consider performing the DATEDIFF on the day or month level, as the year shows the year difference between the two dates.

IE

select datediff(yy, '01/01/2006', '12/31/2006')

select datediff(yy, '12/31/2006', '01/01/2007')

The first returns 0, even though it's a full year. And the second returns 1 even though it's only been 1 day.

Counting rows

hi all

quick question

is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks

tiborPlease restate your requirement in a clearer way. Do you need the row no for each row ? If that is the case, what happens if a row is deleted ?

Counting Connections

I have a VB6 program that uses ADO to communicate with a SQL Server database
(and an Access database optionally). Is there a way that I can tell how
many connections that there are at any one time? I'd like to do this to
Access as well if possible?
Rick Ledermanselect count(*) from master.dbo.sysprocesses
where SPID > 50
The 'where SPID > 50' restriction eliminates system connections. I have no
clue how to do this in Access.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rick Lederman" <rick@.soaringsoftware.com> wrote in message
news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> I have a VB6 program that uses ADO to communicate with a SQL Server
database
> (and an Access database optionally). Is there a way that I can tell how
> many connections that there are at any one time? I'd like to do this to
> Access as well if possible?
> Rick Lederman
>|||Goeff,
Thanks, that works great, now to see if I can find how to do it in Access.
Rick
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23MUhVvEwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> select count(*) from master.dbo.sysprocesses
> where SPID > 50
> The 'where SPID > 50' restriction eliminates system connections. I have
no
> clue how to do this in Access.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rick Lederman" <rick@.soaringsoftware.com> wrote in message
> news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> database
>|||"Rick Lederman" <rick@.soaringsoftware.com> wrote in message
news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> I have a VB6 program that uses ADO to communicate with a SQL Server
database
> (and an Access database optionally). Is there a way that I can tell how
> many connections that there are at any one time?
By one instance of your application on one computer, or by all copies of
your application on all computers?

> I'd like to do this to Access as well if possible?
With VB6 and ADO you can control the number of connections in use. In
Access, if you use ADO you can also control the connection count in use.
There is absolutely no way to tell how many connections will be used (or are
being used) by Access if you are relying on Jet to manage the database
access. The query that Geoff provided will give you an idea of how many
connections are use at any given time.
Steve|||Try using a Pass-through query in Access to get the info.
-Sue
On Mon, 1 Nov 2004 15:13:53 -0500, "Rick Lederman"
<rick@.soaringsoftware.com> wrote:

>Goeff,
>Thanks, that works great, now to see if I can find how to do it in Access.
>Rick
>"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>news:%23MUhVvEwEHA.1984@.TK2MSFTNGP14.phx.gbl...
>no
>