Create an index in a PDF file

Hi!!

I have a report that is exported to a PDF file... Is possible to get an index in the beginning of the file? I have the page number at the bottom of each page but i would need an index...

Thx in advance!!

Any idea? Isn′t possible to do it? At the moment, I havent found anything linked with this...|||

Hi Sergio,

Do you mean a document map? Check out this link: http://msdn2.microsoft.com/en-us/library/ms156383.aspx

|||

Hi Brad!!

not exactly... i would like to appear something similar but in a page in the beginning of the PDF document including the page number where each element is, i.e. the tables of the document... is there any option to do this? it′s very similar to the document map but with the page number...

I see two problems:

- I was thinking of using "=Globals.PageNumber" but i can′t, it says me that only can be placed in the header or the footer...

- and the other problem is setting the content of the document map in a page of the PDF... could I do this?

Perhaps, this is a bit difficult but I am always looking for challenges ... any suggestions? thx in advance!!

Sergio

|||I know of no way to accomplish exactly what you are looking for sorry to say. The document map links to items as repeated on pages. But I am not sure what you mean by "setting the content of the document map in a page of the PDF". If you mean as a page, there is no way to control this.|||

Hi Brad,

that was exactly i ment: i want to show it as a page, but if there is no way to do it... anyway, thank you for your help...

Sergio

Create a Named Instance on top of a Default Instance

Hi

I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .\SQLEXPRESS.

Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?

Cheers

Chris

hi Chris,

you can not create one as you intend, but you can install an additional named instance for that.. or, you can modify the connection strings in the application code to connect to

Data Source=(Local); .....; User Instance=true;

instead of

Data Source=(Local)\SQLExpress; .....; User Instance=true;

regards

|||Hi Andrea

Thanks for the tip... I always wondered what user instances were for...

Now I just have to uninstall & reinstall SQL and enable it.

Cheers

Chris
sql

Create a datetime dimension table with start and end dates

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

CREATE TABLE DimensionTable

(

IdCol INT IDENTITY(1,1),

Timecol DATETIME

)

GO

DECLARE @.Startdate DATETIME

DECLARE @.Enddate DATETIME

SET @.Startdate = '20040101'

SET @.Enddate = '20080101'

WHILE @.Startdate < @.Enddate

BEGIN

INSERT INTO DimensionTable

(

TimeCol

)

SELECT @.Startdate

SET @.Startdate = DATEADD(mi,1,@.Startdate)

END

That could take some time :-)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot

Create a dataset with .Net code and render report from it. Possible?

Hi

I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.

My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...

I have tried to find a solution but so far, no luck.

Anyone have any ideas?

/F

If you use the reportviewer control in local mode you can do what you say. If you want this sent straight to hard copy that also can be achieved. I'm not sure if that meets your requirement.

Crass Tab Query

Hi!

I m using sql 2005.I have a table as showing below.

yearRegionloan_amtpur_idpurpose1981Andhra pradesh$20,000.00 1Animal Husbandary1981Arunachal Pradesh$110,000.00 1Animal Husbandary1981Assam$240,000.00 1Animal Husbandary1981Bihar$75,000.00 1Animal Husbandary1981Chhattisgarh$55,500.00 1Animal Husbandary1981Gujarat$77,500.00 1Animal Husbandary1982Goa$44,888.00 1Animal Husbandary1982Himachal pradesh$4,000.00 1Animal Husbandary1982Himachal pradesh$20,000.00 1Animal Husbandary1982Jammu and kashmir$30,000.00 1Animal Husbandary1882Jharkhand$35,000.00 1Animal Husbandary1982Karnataka$40,000.00 1Animal Husbandary1982Kerala $20,000.00 1Animal Husbandary1982Madhya pradesh$5,000.00 1Animal Husbandary

I want to produce report as by using crosstab query as showing bellow

Year Asam Hyadrabad goa arunachal pardesh etc.........

1981 1000.00 2000.00 8000.2 00000 000000 .....

1981

'

'

Is it possible by crosstab query ?or please suggest me another way as early as possible.

Thanx in advance.

Abhishek

http://www.databasejournal.com/features/mssql/article.php/10894_3516331_2

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
Dan
There will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in
>
|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of[vbcol=seagreen]
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in
>
|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>
|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.4ax.com... [vbcol=seagreen]
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables
>

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
DanThere will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.
4ax.com...
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
>
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables[vbcol=seagreen]
>

CR8.5 COM documentation

Hi

I'm a VB and CR newbie.

I have been looking for some documentation about which methods I can use on a crystal report object. I have found at lot of examples using google but almost none of them shows more than just creating the object and open the report.

I would like to send a SQL string to the report and execute it.

I'm using VB5

Where can I find the documentation about this?

cheers
Henryjust do an online search for documentation. i do have it, bt its 5.x MB, so i dont think it'll upload here...i cant remember the exact url i found it...
http://www.businessobjects.com
try that...
Also, try the samples in crystal. it should be in ur Crystal installed folder-->
E:\Crystal\Samples\En\Code\Visual Basic
tons of support!
(I can show u how slove ur prob, bt i only know in vc++)

CPU utilization not even

Hi
I have windows 2000 advanced server and sql server 2000 EE. It is used for
datawarehouse . A report, which used to finish in 20 minutes now it is
taking 4 hours to finish. It is same query same indexes same database. I
look at the perf counters one cpu(4) is at 70% (average)utilization others
are at 3% (average). I also checked the interupts are 560/sec, I guess it
is something abnormal, can someone help me out
Thanks,
Subbu.
Hi
In the server settings, check that Maximum Query Plan threshold is at least
5. Looks like your query is only using 1 processor. Also check the SQL
Server is configured to use all processors.
Both are on the Processor tab on the Properties page for the Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:#P4yg0A9EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have windows 2000 advanced server and sql server 2000 EE. It is used for
> datawarehouse . A report, which used to finish in 20 minutes now it is
> taking 4 hours to finish. It is same query same indexes same database. I
> look at the perf counters one cpu(4) is at 70% (average)utilization others
> are at 3% (average). I also checked the interupts are 560/sec, I guess
it
> is something abnormal, can someone help me out
> Thanks,
> Subbu.
>

CPU utilization not even

Hi
I have Windows 2000 advanced server and sql server 2000 EE. It is used for
datawarehouse . A report, which used to finish in 20 minutes now it is
taking 4 hours to finish. It is same query same indexes same database. I
look at the perf counters one cpu(4) is at 70% (average)utilization others
are at 3% (average). I also checked the interupts are 560/sec, I guess it
is something abnormal, can someone help me out
Thanks,
Subbu.Hi
In the server settings, check that Maximum Query Plan threshold is at least
5. Looks like your query is only using 1 processor. Also check the SQL
Server is configured to use all processors.
Both are on the Processor tab on the Properties page for the Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:#P4yg0A9EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have Windows 2000 advanced server and sql server 2000 EE. It is used for
> datawarehouse . A report, which used to finish in 20 minutes now it is
> taking 4 hours to finish. It is same query same indexes same database. I
> look at the perf counters one cpu(4) is at 70% (average)utilization others
> are at 3% (average). I also checked the interupts are 560/sec, I guess
it
> is something abnormal, can someone help me out
> Thanks,
> Subbu.
>

Couple of question about SQL 2000

Hi

I've bin using MySQL and now trying to convert it on to SQL 2000 and i've got a couple of questions

1. Ive got a SQL Script of a database generated by MySQL, can i run this script stright into SQL 2000 and if so how do i go about it ??

2. In my MySQL i have a Gender field which data type is Enum ('M','F'). How do i recreate this type of field in SQL 2000.

Thanks1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||Hi thanks for your help

just to be a pain, if i wanted 3 or 4 options would the field type be varchar and i'd have the sort the options out in my application

is this right

thanks again

Originally posted by rhigdon
1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||It's not going to compile...you need to determine the differences and develop a migration plan..

Do a google...

http://www.databasejournal.com/features/mssql/article.php/3087841

counting number of deleted rows

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks@.@.rowcount global variable holds the number of rows affected for the latest query.
something like this.

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN
DECLARE @.count1 int, @.count2 int ,@.count3 int, @.count4 int, @.count5 int,
@.count6 int, @.count7 int

DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
SET @.count1=@.@.rowcount
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
SET @.count2=@.@.rowcount
DELETE FROM SIC WHERE venue_id = @.new_venue_id
SET @.count3=@.@.rowcount
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
SET @.count4=@.@.rowcount
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
SET @.count5=@.@.rowcount
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
SET @.count6=@.@.rowcount
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id
SET @.count7=@.@.rowcount

select @.count1, @.count2,@.count3,@.count4,@.count5,@.count6,@.count7

END|||thats spot on, thanks|||Keep in mind (voice of experience speaking) that ANY SELECT operation will change the value of the @.@.ROWCOUNT system variable.
DECLARE @.recCount int
declare @.RowCount int
Select @.recCount = count(*) from osid (nolock)
select @.recCount
Select @.RowCount = @.@.ROWCOUNT
select @.RowCount

Not necessarily an issue - but something to be aware of, since I have had my buttocks spanked (Ohhhhhh...) on a few occasions by test stub code inserted or other such foolishness.|||Hi,

You can use @.@.ROWCOUNT global variable after each delete and assiging to other local variables to return etc.

I think it is helpful.

Cheers
Riaz

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks

Counting no. of records

Hi

I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:

Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test

I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.

Many thanks in advance

If an aggregate of that column doesn't already exist then create one using something like

SUM(Ref No) AS 'Number of Records'

You could do this in the SQL or you can create it as a function which would look something like

=SUM(Ref No)

This field should be added to the footer column of the group.

Hope this helps

|||Hi,
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")

I think you know, how to set page break after group ends....

Do this....You will get the answer?
If you have any queries..let me know...

M Sivakumar|||Thanks for your reply Harley.

I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.

Cheers|||Hi Siva

First of all thanks for your help in trying to help, greatly appreciated.

Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:

=CountRows(Fields!RefNo.Value)

the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:

The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Thank you again for your help

|||Sorry,

I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)

thanks|||

Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")

if u renamed into "refno"
=CountRows("refno")

I think, it will help your need.

|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.

The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:

=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.

I had a group set up as my criteria and have used this and seems to be working .

By the way, a good reference for this function and others supported by Microsoft use this website, its really good:

http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley

I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.

Cheers for your help too.