Parameter missing value after deployment

Hi Friends,

I'm using MSRS 2005. I have created a drillthrough report using parameter. If I try to execute locally it works fine. I am able to call sub report from the master report by passing parameter. But, If I rey to access the report after deploying on to reporting server, I am getting error patameter xxxx is missing a value. What could be wrong? Please help

Thanks & Regards,

Naveen J V

This may be something to do with deployment. I've sometimes found that after deploying my report parameters don't behave as expected. The solution in the past has either to try to re-deploy (sometimes several times) or delete the reports from the report server and deploy again. A warning would be that the second approach the reportID's (GUIDs) wi8ll be re-created so usage stats will get screwed up.

To test whether it's a deployment issue, I suggest deploying to a different target directory to see if the reports work from there.

|||

Hi Adams

Thanks for the quick turnaround. I have tried deploying it into different servers many times. Everywhere, it throws the same error.

Thanks & Regards,

Naveen J V

parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

parameter is missing a value.

Hi Friends,

I have created a child report with hidden parameter. When I call this report from the Master report using a link, I am getting error 'the xxxx parameter is missing a value'.

From, master report, if I click a value, it should take that value to child report where it filters for that value and should display the child report.

How to overcome this?

Thanks & Regards,

Naveen J V

Have you set any default value for the parameter in your subreport?

|||

Hi,

No default value has been set.

Regards,

Naveen J V

|||Ok, then try giving a default value for your hidden parameter. Give the same expression that you have used in the value field for the parameter. This might work. Not sure.|||

Hi,

I already tried this option, but I'm still getting the same error.

Thanks & Regards,

Naveen

|||

All the parameters created in sub-report should get some value, so if you have more than one parameter in the sub-report and from the master report you are passing value of only one parameter then obviously the value of other parameters will be missing. I suggest you to set the default value for the parameters in the sub report, so that the report get displayed even if you are passing wrong values from the master report.

By the way what is the expression that you are passing? And how many parameters you have?

|||

Hi Rohit,

My sub report has only one parameter. In my master report, If I click a particular column, it should take me to the sub report, where I should be able to see the filtered report based on the passed value.

It is working for one of my report which uses a different cube.

Thanks & Regards,

Naveen

|||

Can you give the expression that you are passing?

|||

Rohit,

While creating a report, in Query builder I selected a parameter (which referes to a member of a dimension). Same parameter I'm using in my report. I am not using any expressions.

Thanks & Regards,

Naveen

|||

what is the expression you are writting in the navigation properties for the parameter of your sub report?

|||

Hi,

Jump to Report ->Sub_rpt (sub report name)

Parameter name -> DLVLTM

Parameter value -> =Fields!TM.Uniquename

Regards,

Naveen J V

|||

Actually everything seems to be fine. It may be a data issue though.

anyways again give it a try:

Go to Sub report and in the report parameter window try by making available values non-queried.

|||

Hi Rohit,

If I dont hide the parameter, the report works fine. If I hide, only then I get this error. In the present context, it should be hidden. Based on the selection made from the master report, it should disply the child report.

I dont understand wht could be the problem?

Regards,

Naveen

|||

Hi Rohit,

I'm getting the same error. Please refer to my rprevious mail.

Regards,

Naveen J V

parallelism sql 2000

Dear friends,
I've been reading some docs about parallelism but I'm still not sure how it
works. My doubt is:
To work with parallelism, do I need to use partitioned tables/index? If yes,
how?
I'm using a dell power edge 6000 with 4 processors.
kind regards
BertoliYou don't need to have partitioned tables to use parallelism. All you need
are multiple CPU's. Parallelism is the striping of a query across multiple
CPU's. In many cases, this speed up the query. In a minority of cases, it
slows down the query. However, you can turn off parallelism for that query
by using OPTION (MAXDOP 1).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Bertoli" <bertoli@.bertoli.com.br> wrote in message
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
Dear friends,
I've been reading some docs about parallelism but I'm still not sure how it
works. My doubt is:
To work with parallelism, do I need to use partitioned tables/index? If yes,
how?
I'm using a dell power edge 6000 with 4 processors.
kind regards
Bertoli|||SQL Server will parallelize queries as it is able (if you allow it to do
so). You do NOT need to create partitioned tables to use this feature.
Keith Kratochvil
"Bertoli" <bertoli@.bertoli.com.br> wrote in message
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I've been reading some docs about parallelism but I'm still not sure how
> it
> works. My doubt is:
> To work with parallelism, do I need to use partitioned tables/index? If
> yes,
> how?
> I'm using a dell power edge 6000 with 4 processors.
> kind regards
> Bertoli
>|||tks guys
Bertoli
"Bertoli" <bertoli@.bertoli.com.br> escreveu na mensagem
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I've been reading some docs about parallelism but I'm still not sure how
it
> works. My doubt is:
> To work with parallelism, do I need to use partitioned tables/index? If
yes,
> how?
> I'm using a dell power edge 6000 with 4 processors.
> kind regards
> Bertoli
>|||You can get more info from BOL by searching for
* Max degree of parallelism
Thanks
Ajay Rengunthwar

parallelism sql 2000

Dear friends,
I've been reading some docs about parallelism but I'm still not sure how it
works. My doubt is:
To work with parallelism, do I need to use partitioned tables/index? If yes,
how?
I'm using a dell power edge 6000 with 4 processors.
kind regards
BertoliYou don't need to have partitioned tables to use parallelism. All you need
are multiple CPU's. Parallelism is the striping of a query across multiple
CPU's. In many cases, this speed up the query. In a minority of cases, it
slows down the query. However, you can turn off parallelism for that query
by using OPTION (MAXDOP 1).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Bertoli" <bertoli@.bertoli.com.br> wrote in message
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
Dear friends,
I've been reading some docs about parallelism but I'm still not sure how it
works. My doubt is:
To work with parallelism, do I need to use partitioned tables/index? If yes,
how?
I'm using a dell power edge 6000 with 4 processors.
kind regards
Bertoli|||SQL Server will parallelize queries as it is able (if you allow it to do
so). You do NOT need to create partitioned tables to use this feature.
--
Keith Kratochvil
"Bertoli" <bertoli@.bertoli.com.br> wrote in message
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I've been reading some docs about parallelism but I'm still not sure how
> it
> works. My doubt is:
> To work with parallelism, do I need to use partitioned tables/index? If
> yes,
> how?
> I'm using a dell power edge 6000 with 4 processors.
> kind regards
> Bertoli
>|||tks guys
Bertoli
"Bertoli" <bertoli@.bertoli.com.br> escreveu na mensagem
news:%23ZJszXxYGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I've been reading some docs about parallelism but I'm still not sure how
it
> works. My doubt is:
> To work with parallelism, do I need to use partitioned tables/index? If
yes,
> how?
> I'm using a dell power edge 6000 with 4 processors.
> kind regards
> Bertoli
>|||You can get more info from BOL by searching for
* Max degree of parallelism
Thanks
Ajay Rengunthwar

Pakage running in Sql server 2005

hi friends,

i created the one package for import the data from excel to server 2005

in intellegence services i add the package after deploy

if we run the package (by giving the connection working fine)

BUT I WANT TO RUN THE PACKAGE BY DAILY AUTOMATICALLY(just like a scheduler)

is it possible if possible please give me the coding for auto run a packages

regards

koti

Hi ya,

You would need to deploy it first to file system or Ms Sql Server and then place it as a job in sql server agent. Schedule that job to run daily.

Hope that helps

Cheers

Rizwan

pages split

Hi friends,
We have read only DB (no modifications) on sql2k5(sp2) and when running Perf
Monitor we see spikes of pages split counter, sometimes up to 100 or even
higher. I just wonder what might contribute to that behavior? There is one
stored proc where temp table is created, populated and then clustered index
is created on it. But once index is created no more modifications on temp
table are done.
I'd appreciate any input.
Thanks a lot in advanceHi
What is FILLFACTOR you specify when create an index?Consider increasing the
fill factor of your indexes.
"falconer" <me@.isp.net> wrote in message
news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Hi friends,
> We have read only DB (no modifications) on sql2k5(sp2) and when running
> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
> even higher. I just wonder what might contribute to that behavior? There
> is one stored proc where temp table is created, populated and then
> clustered index is created on it. But once index is created no more
> modifications on temp table are done.
> I'd appreciate any input.
> Thanks a lot in advance|||Would this be the reason since it is just the creation of a clustered index?
I am curious about this because I would hope that the engine just stuffs
each page 100% full (assuming that is FF default) with no splitting at all
during creation. And since the OP stated there wasn't any further
modification to the table there should be no splitting after index creation
either.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi
> What is FILLFACTOR you specify when create an index?Consider increasing
> the fill factor of your indexes.
> "falconer" <me@.isp.net> wrote in message
> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
>> even higher. I just wonder what might contribute to that behavior? There
>> is one stored proc where temp table is created, populated and then
>> clustered index is created on it. But once index is created no more
>> modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>|||He said that there are no modifications on temp table since CI was created.
I was assumed that the OP might have a procedure to rebuild indexes and
there he might change FF from default to smething else.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9l33o21f2e37@.corp.supernews.com...
> Would this be the reason since it is just the creation of a clustered
> index? I am curious about this because I would hope that the engine just
> stuffs each page 100% full (assuming that is FF default) with no splitting
> at all during creation. And since the OP stated there wasn't any further
> modification to the table there should be no splitting after index
> creation either.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100
>> or even higher. I just wonder what might contribute to that behavior?
>> There is one stored proc where temp table is created, populated and then
>> clustered index is created on it. But once index is created no more
>> modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>|||yeah, that's what puzzles me - no inserts, deletes, or updates whatsoever.
I have suspicion something's going on in TempDB.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
> He said that there are no modifications on temp table since CI was
> created. I was assumed that the OP might have a procedure to rebuild
> indexes and there he might change FF from default to smething else.
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine just
>> stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting after
>> index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100
>> or even higher. I just wonder what might contribute to that behavior?
>> There is one stored proc where temp table is created, populated and
>> then clustered index is created on it. But once index is created no
>> more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>|||Forgot to mention that we saw those spikes under stress test simulating
workload of hundreds users. I managed drastically reduce # of split by
replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT. I
got no explanation to it. Any thoughts?
"Falconer" <me@.isp.net> wrote in message
news:JS7hj.2983$vp3.2661@.edtnps90...
> yeah, that's what puzzles me - no inserts, deletes, or updates
> whatsoever. I have suspicion something's going on in TempDB.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine just
>> stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting after
>> index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when
>> running Perf Monitor we see spikes of pages split counter, sometimes
>> up to 100 or even higher. I just wonder what might contribute to that
>> behavior? There is one stored proc where temp table is created,
>> populated and then clustered index is created on it. But once index is
>> created no more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>>
>|||and 2nd measure that finished splits off completely was removing CREATE
INDEX statement on temp table. SORT op showed up in exec plan, but overall
performance in terms of io and exec time remained about the same, even a bit
better. Who'd have thought...
"Falconer" <me@.isp.net> wrote in message
news:LZ8hj.2999$vp3.1848@.edtnps90...
> Forgot to mention that we saw those spikes under stress test simulating
> workload of hundreds users. I managed drastically reduce # of split by
> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
> I got no explanation to it. Any thoughts?
> "Falconer" <me@.isp.net> wrote in message
> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting
>> after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider
>> increasing the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when
>> running Perf Monitor we see spikes of pages split counter, sometimes
>> up to 100 or even higher. I just wonder what might contribute to that
>> behavior? There is one stored proc where temp table is created,
>> populated and then clustered index is created on it. But once index
>> is created no more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>>
>>
>|||I would have thought. I fairly regularly see client's putting indexes
(especially clustered) on temp tables and then accessing them just ONCE. In
almost every case I have seen like that the work do do the index is MORE
than the query savings when using the index.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
> and 2nd measure that finished splits off completely was removing CREATE
> INDEX statement on temp table. SORT op showed up in exec plan, but overall
> performance in terms of io and exec time remained about the same, even a
> bit better. Who'd have thought...
>
> "Falconer" <me@.isp.net> wrote in message
> news:LZ8hj.2999$vp3.1848@.edtnps90...
>> Forgot to mention that we saw those spikes under stress test simulating
>> workload of hundreds users. I managed drastically reduce # of split by
>> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
>> I got no explanation to it. Any thoughts?
>> "Falconer" <me@.isp.net> wrote in message
>> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there
>> wasn't any further modification to the table there should be no
>> splitting after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider
>> increasing the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>>> Hi friends,
>>>
>>> We have read only DB (no modifications) on sql2k5(sp2) and when
>>> running Perf Monitor we see spikes of pages split counter, sometimes
>>> up to 100 or even higher. I just wonder what might contribute to
>>> that behavior? There is one stored proc where temp table is created,
>>> populated and then clustered index is created on it. But once index
>>> is created no more modifications on temp table are done.
>>> I'd appreciate any input.
>>>
>>> Thanks a lot in advance
>>
>>
>>
>>
>>
>|||Life is all about learning, heh?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5fhm25ie9de@.corp.supernews.com...
>I would have thought. I fairly regularly see client's putting indexes
>(especially clustered) on temp tables and then accessing them just ONCE.
>In almost every case I have seen like that the work do do the index is MORE
>than the query savings when using the index.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
>> and 2nd measure that finished splits off completely was removing CREATE
>> INDEX statement on temp table. SORT op showed up in exec plan, but
>> overall performance in terms of io and exec time remained about the same,
>> even a bit better. Who'd have thought...
>>
>> "Falconer" <me@.isp.net> wrote in message
>> news:LZ8hj.2999$vp3.1848@.edtnps90...
>> Forgot to mention that we saw those spikes under stress test simulating
>> workload of hundreds users. I managed drastically reduce # of split by
>> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table
>> SELECT. I got no explanation to it. Any thoughts?
>> "Falconer" <me@.isp.net> wrote in message
>> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there
>> wasn't any further modification to the table there should be no
>> splitting after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>>> Hi
>>> What is FILLFACTOR you specify when create an index?Consider
>>> increasing the fill factor of your indexes.
>>>
>>> "falconer" <me@.isp.net> wrote in message
>>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>>> Hi friends,
>>>
>>> We have read only DB (no modifications) on sql2k5(sp2) and when
>>> running Perf Monitor we see spikes of pages split counter,
>>> sometimes up to 100 or even higher. I just wonder what might
>>> contribute to that behavior? There is one stored proc where temp
>>> table is created, populated and then clustered index is created on
>>> it. But once index is created no more modifications on temp table
>>> are done.
>>> I'd appreciate any input.
>>>
>>> Thanks a lot in advance
>>>
>>>
>>
>>
>>
>>
>>
>

pages split

Hi friends,
We have read only DB (no modifications) on sql2k5(sp2) and when running Perf
Monitor we see spikes of pages split counter, sometimes up to 100 or even
higher. I just wonder what might contribute to that behavior? There is one
stored proc where temp table is created, populated and then clustered index
is created on it. But once index is created no more modifications on temp
table are done.
I'd appreciate any input.
Thanks a lot in advance
Hi
What is FILLFACTOR you specify when create an index?Consider increasing the
fill factor of your indexes.
"falconer" <me@.isp.net> wrote in message
news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Hi friends,
> We have read only DB (no modifications) on sql2k5(sp2) and when running
> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
> even higher. I just wonder what might contribute to that behavior? There
> is one stored proc where temp table is created, populated and then
> clustered index is created on it. But once index is created no more
> modifications on temp table are done.
> I'd appreciate any input.
> Thanks a lot in advance
|||Would this be the reason since it is just the creation of a clustered index?
I am curious about this because I would hope that the engine just stuffs
each page 100% full (assuming that is FF default) with no splitting at all
during creation. And since the OP stated there wasn't any further
modification to the table there should be no splitting after index creation
either.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi
> What is FILLFACTOR you specify when create an index?Consider increasing
> the fill factor of your indexes.
> "falconer" <me@.isp.net> wrote in message
> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||He said that there are no modifications on temp table since CI was created.
I was assumed that the OP might have a procedure to rebuild indexes and
there he might change FF from default to smething else.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9l33o21f2e37@.corp.supernews.com...
> Would this be the reason since it is just the creation of a clustered
> index? I am curious about this because I would hope that the engine just
> stuffs each page 100% full (assuming that is FF default) with no splitting
> at all during creation. And since the OP stated there wasn't any further
> modification to the table there should be no splitting after index
> creation either.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
|||yeah, that's what puzzles me - no inserts, deletes, or updates whatsoever.
I have suspicion something's going on in TempDB.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
> He said that there are no modifications on temp table since CI was
> created. I was assumed that the OP might have a procedure to rebuild
> indexes and there he might change FF from default to smething else.
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o9l33o21f2e37@.corp.supernews.com...
>
|||Forgot to mention that we saw those spikes under stress test simulating
workload of hundreds users. I managed drastically reduce # of split by
replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT. I
got no explanation to it. Any thoughts?
"Falconer" <me@.isp.net> wrote in message
news:JS7hj.2983$vp3.2661@.edtnps90...
> yeah, that's what puzzles me - no inserts, deletes, or updates
> whatsoever. I have suspicion something's going on in TempDB.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>
|||and 2nd measure that finished splits off completely was removing CREATE
INDEX statement on temp table. SORT op showed up in exec plan, but overall
performance in terms of io and exec time remained about the same, even a bit
better. Who'd have thought...
"Falconer" <me@.isp.net> wrote in message
news:LZ8hj.2999$vp3.1848@.edtnps90...
> Forgot to mention that we saw those spikes under stress test simulating
> workload of hundreds users. I managed drastically reduce # of split by
> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
> I got no explanation to it. Any thoughts?
> "Falconer" <me@.isp.net> wrote in message
> news:JS7hj.2983$vp3.2661@.edtnps90...
>
|||I would have thought. I fairly regularly see client's putting indexes
(especially clustered) on temp tables and then accessing them just ONCE. In
almost every case I have seen like that the work do do the index is MORE
than the query savings when using the index.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
> and 2nd measure that finished splits off completely was removing CREATE
> INDEX statement on temp table. SORT op showed up in exec plan, but overall
> performance in terms of io and exec time remained about the same, even a
> bit better. Who'd have thought...
>
> "Falconer" <me@.isp.net> wrote in message
> news:LZ8hj.2999$vp3.1848@.edtnps90...
>
|||Life is all about learning, heh?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5fhm25ie9de@.corp.supernews.com...
>I would have thought. I fairly regularly see client's putting indexes
>(especially clustered) on temp tables and then accessing them just ONCE.
>In almost every case I have seen like that the work do do the index is MORE
>than the query savings when using the index.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
>

Page totals in last page

Friends,
I am working on a report where I have to print all the page totals, in the last page.
Could you please give us some sort of solution.
Regards
JCYou need to use Three formulae
Formula1 Reset having the code

Whileprintingrecords;
numbervar x:=0;
Formula2 Sum having the code
whileprintingrecords;
numbervar x:=Tonumber(x+DatabaseField);
Formula3 Display having the code
whileprintingrecords;
numbervar x;
Place Formula1 at Page Header
Place Formula2 at Details section
Place Formula3 at Page Footer

Now suppress Formula1 and Formula2