increase the speed of the report

Hello,

I am working on a report in SQL Server Reporting Services 2000.

[CODE]
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = case when @.callDate = '' then [Call Day] else @.callDate end
[/CODE]

>> I have apromt for the user to enter the date.
>> If the user does not enter any date, then the report will show all the first 200 records.
>> This query is running too slow.

To increase the speed of the report , could somebody help me build the where clause only when something is in the filters ?

Thank you,

This should do what you want:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

|||

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

|||

urpalshu wrote:

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

You need to use boolean logic here, you stated in some cases no date is entered.

By the way I hope call day is of type date time...

In any even if you sometimes have a value for @.callDate and other times it is null the sproc should be this:

@.callDate datetime= NULL --do you need a default ?

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)

Make sure Call Day is of the right type (datetime). If it is varchar, you will need to change the data type. You can strip the day time month year using various date functions.

Jon

|||

Thank you,

I changed the Call Date to datetime,

if @.callDate IS NULL AND @.destNbr = '' AND @.origNbr = '' AND @.btn = '' AND @.invoiceNbr = '' AND @.destMobile = ''
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
END
ELSE
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND [Dest Nbr] = case when @.destNbr = '' then [Dest Nbr] else @.destNbr end
AND [Orig Nbr] = case when @.origNbr = '' then [Orig Nbr] else @.origNbr end
AND [BTN] = case when @.btn = '' then [BTN] else @.btn end
AND [invoice nbr] = case when @.invoiceNbr = '' then [invoice nbr] else @.invoiceNbr end
AND [dest mobile] = case when @.destMobile = '' then [dest mobile] else @.destMobile end
END

Can we improve the speed on this query?

Please help

|||

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

|||

sluggy wrote:

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

Its amazing how people dont listen, did I not just post this like the third post ?

|||

You sure did, but the original poster was still stuck, so i expanded upon it for him. You will see i mentioned "what he had already done with the @.callDate parameter" - this acknowledges your post.

But this is not the place for a flame war, so let it go.

sluggy

Increase the Rendering Timing of Reports

Hi,

We are using SQL Server 2005 Reporting Services for creating Reports. But report execution is taking bit time to give results.

Is there any way around to increase the rendering timing ?

Thx

First step would be to find where the delay is. There is a table called ExecutionLog in the reportserver database catalog. you can query this table and look at columns - TimeDataRetrival, Time processing, time rendering for this report to find out where the delay is. If the delay is in TimeDataRetrival, it means that your SQL query performance is the one to be blamed. You can optimize the query which is used in the report and get over it. NOTE: Always open the ExecutionLog table with no lock hint.

Incorrect syntax near ',' using a Multi-value parameter

I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','I fixed it by changing my '=' signs to 'IN', so that it would search for the multi-value parameter within the values selected.

Incorrect SET options all of a sudden?

We've been running a SQL Server based application for some time (Access
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Incorporating Form Authentication with Report Services

Hi all,

I am new to reporting services in SQL SERVER 2005. I configured my reporting server which is currently runnung when i type //localhost/reportserver in the URL it give me results. Is there anyway of incorporating security in ASP.NET 2.0 using Form Authentication So long as the user clicks on the link having been authenticated he can access the reports on the reporting server. For example I am working on an academic project whereby when user logins, I create a link that him to report server as in

//localhost/reportserver and when user clicks on that link, he can be able to download whatever report he might need.

I am running all these one local machine.

Could someone help me out

Try one of these links. I am beginning to think Russell is the SSRS god.

http://blogs.msdn.com/bimusings/archive/2005/11/29/497848.aspx

http://blogs.msdn.com/bimusings/archive/2005/12/05/500195.aspx

http://blogs.msdn.com/bimusings/archive/2005/09/08/462544.aspx

Ron

Inconsistent Subscription Success

Hi,
I've got a production SQL Reporting Services installation and have
created some file share subscriptions for one of the reports.
Sometimes the subscriptions work and sometimes they don't. My customer
has now had enough and wants to have them working all of the time
(unsurprisingly!).
I have spent the whole day testing various things to try to get some
consistency and haven't been able to prove anything. If, for example,
I create 5 new subscriptions (either to run all at the same time or to
run one minute after each other), any number of the subscriptions will
run properly (ie. and will create the file on the file share) -
sometimes none will run, sometimes a few will run, sometimes all five
will run. I have just created eight new subscriptions in an absolutely
identical manner and only one of the eight ran properly (and it wasn't
the first or last one).
I cannot find any error messages anywhere in the system, ie. in event
viewer, in the SQL RS logs, in the SQL logs, etc., and as nothing is
changing on the system - ie. local user rights, NTFS permissions, IIS
permissions, SQL permissions, etc. - I can't work out why the
subscriptions work sometimes and not others.
I've looked through a lot of the Google postings and can see that other
people have similar things.
If anyone can offer any suggestions so that I can get SQL RS to work
properly, please let me know - I'll really appreciate it.
Cheers,
Rich
(MCSE MCSD MCDBA)Hello Richard,
Can you see all the corresponding jobs created for your subscriptions? When
a subscription does not work, can you see that the job in SQL Server Agent is
running or was triggered as expected?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi,
> I've got a production SQL Reporting Services installation and have
> created some file share subscriptions for one of the reports.
> Sometimes the subscriptions work and sometimes they don't. My customer
> has now had enough and wants to have them working all of the time
> (unsurprisingly!).
> I have spent the whole day testing various things to try to get some
> consistency and haven't been able to prove anything. If, for example,
> I create 5 new subscriptions (either to run all at the same time or to
> run one minute after each other), any number of the subscriptions will
> run properly (ie. and will create the file on the file share) -
> sometimes none will run, sometimes a few will run, sometimes all five
> will run. I have just created eight new subscriptions in an absolutely
> identical manner and only one of the eight ran properly (and it wasn't
> the first or last one).
> I cannot find any error messages anywhere in the system, ie. in event
> viewer, in the SQL RS logs, in the SQL logs, etc., and as nothing is
> changing on the system - ie. local user rights, NTFS permissions, IIS
> permissions, SQL permissions, etc. - I can't work out why the
> subscriptions work sometimes and not others.
> I've looked through a lot of the Google postings and can see that other
> people have similar things.
> If anyone can offer any suggestions so that I can get SQL RS to work
> properly, please let me know - I'll really appreciate it.
> Cheers,
>
> Rich
> (MCSE MCSD MCDBA)
>|||Hi, Ricardo.
Thanks for your reply.
Yes - the jobs all show in the SQL Server Agent jobs view in Enterprise
Manager. They show as Succeeded (<date> <time>). As SQL server
considers them to have succeeded, there is no entry in the event log.
During some of the testing, I modified one of the jobs (Notification
tab) so that it wrote to the Windows application event log "whenever
the job completes", and all that did was write an entry to the event
log to say the job had completed successfully!
When I look in the Execution Log table of the SQL RS database, I can
see that not all of the subscriptions show there.
When I look in the Subscriptions table of the SQL RS database, I can
see all of the subscriptions, but the ones that didn't work properly
still show a LastRun time of <NULL>.
Cheers,
Rich|||Hello Richard,
It seems that there is a mismatch between the jobs in SQL and the
subscriptions. Have you tried recreating the jobs? Stop the ReportServer
service, then delete all SQL Agent jobs related to reporting services (all
jobs that have category "Report Server"), and then start the ReportServer
service. It will recreate the necessary SQL Agent jobs.
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Thanks for your reply.
> Yes - the jobs all show in the SQL Server Agent jobs view in Enterprise
> Manager. They show as Succeeded (<date> <time>). As SQL server
> considers them to have succeeded, there is no entry in the event log.
> During some of the testing, I modified one of the jobs (Notification
> tab) so that it wrote to the Windows application event log "whenever
> the job completes", and all that did was write an entry to the event
> log to say the job had completed successfully!
> When I look in the Execution Log table of the SQL RS database, I can
> see that not all of the subscriptions show there.
> When I look in the Subscriptions table of the SQL RS database, I can
> see all of the subscriptions, but the ones that didn't work properly
> still show a LastRun time of <NULL>.
> Cheers,
>
> Rich
>|||As you suggested, we stopped the ReportServer service, deleted the
Report Server SQL Agent jobs, then restarted the ReportServer service,
and the Report Server jobs were recreated in the SQL Agent. However,
it hasn't helped resolve the problem.
I have just created five new run-once subscriptions - each configured
to run one minute after the last. The first three were successful, the
fourth wasn't, and the fifth was. Looking in the SQL Agent, all of
them show as Succeeded (<date> <time>). Again - all five subscriptions
were created in an absolutely identical manner.
Have you got any other suggestions?
Cheers,
Rich|||Hello Richard,
What is the status of the subscription in the Subscriptions page? Does it
show that all subscription run and all of them were successful?
In the logs, can you see the calls for all five subscriptions?
Ricardo.
"richard.warner@.zurich.com" wrote:
> As you suggested, we stopped the ReportServer service, deleted the
> Report Server SQL Agent jobs, then restarted the ReportServer service,
> and the Report Server jobs were recreated in the SQL Agent. However,
> it hasn't helped resolve the problem.
> I have just created five new run-once subscriptions - each configured
> to run one minute after the last. The first three were successful, the
> fourth wasn't, and the fifth was. Looking in the SQL Agent, all of
> them show as Succeeded (<date> <time>). Again - all five subscriptions
> were created in an absolutely identical manner.
> Have you got any other suggestions?
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Thanks for your quick reply again.
Sorry - I was mistaken in my last mail - three of the subscriptions ran
successfully and two of them didn't (not four and one, as I'd said).
In the Subscriptions page, the three successful subscriptions show as
"File xx.xx was written to xx", and the two unsuccessful subscriptions
show as "New subscription".
Which log are you referring to? If you're referring to the
ReportServer_<date>_<time>.log file in the SQL RS LogFiles directory,
then yes - I can see a line for the creation of each of the five
subscriptions. The line is as follows:
"aspnet_wp!subscription!bf4!<date>-<time>:: Subscription Created for
report /<folder>/<report> at <date>T<time> by <me>"
This line occurs five times and corresponds exactly with the times that
I created the subscriptions.
Cheers,
Rich|||Hello Richard,
If the subscription stays at "New subscription" then it means it hasn't run,
or (I think) something happened and it is retrying. Has the process
dealocked? Are you trying to write the same filename all the time? Is it
possible that there was a sharing violation? In the logs, after the
subscription was queued the first time, can you see whether RS is queing the
two "missing" subscriptions again? Has the status changed in the
Subscriptions page?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Thanks for your quick reply again.
> Sorry - I was mistaken in my last mail - three of the subscriptions ran
> successfully and two of them didn't (not four and one, as I'd said).
> In the Subscriptions page, the three successful subscriptions show as
> "File xx.xx was written to xx", and the two unsuccessful subscriptions
> show as "New subscription".
> Which log are you referring to? If you're referring to the
> ReportServer_<date>_<time>.log file in the SQL RS LogFiles directory,
> then yes - I can see a line for the creation of each of the five
> subscriptions. The line is as follows:
> "aspnet_wp!subscription!bf4!<date>-<time>:: Subscription Created for
> report /<folder>/<report> at <date>T<time> by <me>"
> This line occurs five times and corresponds exactly with the times that
> I created the subscriptions.
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Are you referring to the aspnet_wp.exe process? If so, there are no
events in the event log showing that the process has deadlocked and
been restarted.
Each subscription is created to write to a different file name.
I don't think it's possible that there was a sharing violation. In
each case, the file doesn't exist before the subscription runs and
nothing tries to open the file subsequently.
In the log file, RS isn't queuing the two missing subscriptions again.
The status hasn't changed in the Subscriptions page.
In the ReportServerService_<date>_<time>.log file, I can see the
successful subscriptions being run. These ran today at 13:23, 13:25
and 13:27. The missing ones were scheduled to run at 13:24 and 13:26.
Here is a typical section of the log from the successful subscriptions:
ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
EventPolling processing item ce6bf843-1a37-4c0a-aa76-218284fafc1a
ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
69782008-e708-4927-9eeb-f1640c7ec996 executed at 11/02/2005 13:27:04.
ReportingServicesService!schedule!d64!11/02/2005-13:27:04:: Creating
Time based subscription notification for subscription:
63b17fae-0d44-4d2e-8a75-c0ac68ebd080
ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
69782008-e708-4927-9eeb-f1640c7ec996 execution completed at 11/02/2005
13:27:04.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
EventPolling finished processing item
ce6bf843-1a37-4c0a-aa76-218284fafc1a
ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
NotificationPolling processing 1 more items. 1 Total items in internal
queue.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
NotificationPolling processing item
ceeebad5-0578-4b59-af37-5fd8305ddbac
ReportingServicesService!library!d64!11/02/2005-13:27:04:: i INFO: Call
to RenderFirst( '/<folder>/<report>' ) !-- this line modified by me in
Google post to hide folder/report name
ReportingServicesService!library!d64!11/02/2005-13:27:06:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server
system properties.
ReportingServicesService!notification!d64!11/02/2005-13:27:06::
Notification ceeebad5-0578-4b59-af37-5fd8305ddbac completed. Success:
True, Status: File E5.pdf was written to \\<server>\<share>,
DeliveryExtension: Report Server FileShare, Report: ARMReport1, Attempt
0 !-- this line modified by me again
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:06::
NotificationPolling finished processing item
ceeebad5-0578-4b59-af37-5fd8305ddbac
I have created 12 more subscriptions running at various intervals (1
minute, 2 minutes, 3 minutes, 5 minutes and 10 minutes) - mixing these
intervals up to see if it's anything to do with how far apart the jobs
are running. I know this is grabbing at straws, but I'm happy to try
anything! :-> I've just realised that the results from these
subscriptions will be in soon, so I'll hold on before posting this and
will include the results ...
New results from 12 subscriptions:
1 - (Time = 15:40) - Didn't run
2 - (Time = 15:41) - Didn't run
3 - (Time = 15:43) - Ran
4 - (Time = 15:45) - Ran
5 - (Time = 15:48) - Didn't run
6 - (Time = 15:50) - Didn't run
7 - (Time = 15:52) - Didn't run
8 - (Time = 15:53) - Ran
9 - (Time = 15:55) - Ran
10 - (Time = 15:58) - Didn't run
11 - (Time = 16:03) - Didn't run
12 - (Time = 16:13) - Didn't run
That seems fairly inconculsive to me!
Any other thoughts?
Cheers,
Rich|||Hello Richard,
I am talking about the process in the database. Are you running the reports
from a stored procedure, or a simple query? If you run the stored procedures
or queries in Query Analyzer at those intervals, does it always run and
return data? Do they deadlock?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Are you referring to the aspnet_wp.exe process? If so, there are no
> events in the event log showing that the process has deadlocked and
> been restarted.
> Each subscription is created to write to a different file name.
> I don't think it's possible that there was a sharing violation. In
> each case, the file doesn't exist before the subscription runs and
> nothing tries to open the file subsequently.
> In the log file, RS isn't queuing the two missing subscriptions again.
> The status hasn't changed in the Subscriptions page.
> In the ReportServerService_<date>_<time>.log file, I can see the
> successful subscriptions being run. These ran today at 13:23, 13:25
> and 13:27. The missing ones were scheduled to run at 13:24 and 13:26.
> Here is a typical section of the log from the successful subscriptions:
> ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> EventPolling processing item ce6bf843-1a37-4c0a-aa76-218284fafc1a
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
> 69782008-e708-4927-9eeb-f1640c7ec996 executed at 11/02/2005 13:27:04.
> ReportingServicesService!schedule!d64!11/02/2005-13:27:04:: Creating
> Time based subscription notification for subscription:
> 63b17fae-0d44-4d2e-8a75-c0ac68ebd080
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
> 69782008-e708-4927-9eeb-f1640c7ec996 execution completed at 11/02/2005
> 13:27:04.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> EventPolling finished processing item
> ce6bf843-1a37-4c0a-aa76-218284fafc1a
> ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
> NotificationPolling processing 1 more items. 1 Total items in internal
> queue.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> NotificationPolling processing item
> ceeebad5-0578-4b59-af37-5fd8305ddbac
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: i INFO: Call
> to RenderFirst( '/<folder>/<report>' ) !-- this line modified by me in
> Google post to hide folder/report name
> ReportingServicesService!library!d64!11/02/2005-13:27:06:: i INFO:
> Initializing EnableExecutionLogging to 'True' as specified in Server
> system properties.
> ReportingServicesService!notification!d64!11/02/2005-13:27:06::
> Notification ceeebad5-0578-4b59-af37-5fd8305ddbac completed. Success:
> True, Status: File E5.pdf was written to \\<server>\<share>,
> DeliveryExtension: Report Server FileShare, Report: ARMReport1, Attempt
> 0 !-- this line modified by me again
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:06::
> NotificationPolling finished processing item
> ceeebad5-0578-4b59-af37-5fd8305ddbac
> I have created 12 more subscriptions running at various intervals (1
> minute, 2 minutes, 3 minutes, 5 minutes and 10 minutes) - mixing these
> intervals up to see if it's anything to do with how far apart the jobs
> are running. I know this is grabbing at straws, but I'm happy to try
> anything! :-> I've just realised that the results from these
> subscriptions will be in soon, so I'll hold on before posting this and
> will include the results ...
> New results from 12 subscriptions:
> 1 - (Time = 15:40) - Didn't run
> 2 - (Time = 15:41) - Didn't run
> 3 - (Time = 15:43) - Ran
> 4 - (Time = 15:45) - Ran
> 5 - (Time = 15:48) - Didn't run
> 6 - (Time = 15:50) - Didn't run
> 7 - (Time = 15:52) - Didn't run
> 8 - (Time = 15:53) - Ran
> 9 - (Time = 15:55) - Ran
> 10 - (Time = 15:58) - Didn't run
> 11 - (Time = 16:03) - Didn't run
> 12 - (Time = 16:13) - Didn't run
> That seems fairly inconculsive to me!
> Any other thoughts?
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Sorry for the delay in my reply. I've made slight progress in that I
know that one of the two SQL RS IIS servers is causing a problem (I
probably should have said before that we have two SQL RS servers
talking to one SQL RS database). By stopping the ReportServer service
on the second SQL RS server, all of the subscriptions now work (even
though the subscriptions are only configured on one of the servers, and
are only saving files on the same server, etc - so as far as I was
concerned, the second server wasn't involved). I'll look into this
more tomorrow, but for now my solution is just to keep that service
stopped on the second server and lose the resilience that the second
SQL RS server was providing.
Thanks again for your help.
Rich

Incomplete Exports

Hello,
I hope that someone will be able to help us with a perplexing issue. When
generating a reporting in either Excel or PDF every 5th report or so, we will
get a corrupt / damaged file. The issue is repeatable but not reproducible.
The same report will fail a random number of times in a row, but will then
execute correctly. It appears that the corrupt file is really truncated.
The reports are big, but we don't have any very long columns or many rows.
However, they do contain quite a few charts. The final file size in excel
is ~1.4 MBs.
We have been unable to locate any errors in the logs. The web site
behaves like it successfully created the report. I've watched the memory
usage and it does not appear to be memory bound or disk bound.
I'm hoping for any suggestions on avenues to try or finding someone with a
similar problem.
Thank You,
--Chris Swinefurth
MID Technologies, Inc.You may want to try this:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
Adrian M.
MCP
"Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> Hello,
> I hope that someone will be able to help us with a perplexing issue.
> When
> generating a reporting in either Excel or PDF every 5th report or so, we
> will
> get a corrupt / damaged file. The issue is repeatable but not
> reproducible.
> The same report will fail a random number of times in a row, but will then
> execute correctly. It appears that the corrupt file is really truncated.
> The reports are big, but we don't have any very long columns or many
> rows.
> However, they do contain quite a few charts. The final file size in excel
> is ~1.4 MBs.
> We have been unable to locate any errors in the logs. The web site
> behaves like it successfully created the report. I've watched the memory
> usage and it does not appear to be memory bound or disk bound.
> I'm hoping for any suggestions on avenues to try or finding someone with
> a
> similar problem.
> Thank You,
> --Chris Swinefurth
> MID Technologies, Inc.
>|||Adrian,
Thank you for your reply. Unfortuntely, we are unable to utilize ether
suggesting in the KB article. 1) Sending a link to a report will not work as
we're pulling data from a non-database provider and we need a point-in-time
report. I believe that sending a link will produce another execution of the
report when the link is clicked and not when the subscription email is sent.
2) We've tried web archives, but unfortunately, printing becomes an issue.
IE renders extra pages and cuts off parts of the graphs.
Thanks again for your help. Any more suggestions would be greatly
appreciated.
--Chris
"Adrian M." wrote:
> You may want to try this:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> --
> Adrian M.
> MCP
>
> "Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
> message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> > Hello,
> > I hope that someone will be able to help us with a perplexing issue.
> > When
> > generating a reporting in either Excel or PDF every 5th report or so, we
> > will
> > get a corrupt / damaged file. The issue is repeatable but not
> > reproducible.
> > The same report will fail a random number of times in a row, but will then
> > execute correctly. It appears that the corrupt file is really truncated.
> > The reports are big, but we don't have any very long columns or many
> > rows.
> > However, they do contain quite a few charts. The final file size in excel
> > is ~1.4 MBs.
> > We have been unable to locate any errors in the logs. The web site
> > behaves like it successfully created the report. I've watched the memory
> > usage and it does not appear to be memory bound or disk bound.
> > I'm hoping for any suggestions on avenues to try or finding someone with
> > a
> > similar problem.
> > Thank You,
> > --Chris Swinefurth
> > MID Technologies, Inc.
> >
>
>

Including multiple values in where clause

Guys

I'm building a reporting application where users can create bespoke reports, one area I'm having an issue with is having multiple values for a particular column. It is possible to say in english "select all contacts from 'houston' and 'dallas' ", the expected result would be all contacts who have offices in both houston and dallas. In SQL server this is a little harder to make work as a location can't equal both houston and dallas (just on or the other and subsquently returns nothing). An "or" will return either dallas or houston, one or the other, or both.

I'm starting to think this may not be possible, but just can't believe that that's the case.

Hope this makes some sort of sense, as I would like to put this issue to bed once and for all.

Duncanselect <columns> from <table> where <city> in ('houston','dallas')|||

Something like this could work. (Of course, since you didn't provide the table DDL or sample data, it is impossible to 'get it right' for you.)

Code Snippet

SELECT *
FROM Contacts
WHERE ContactID EXIST (SELECT ContactID
FROM Contacts

WHERE Location IN ( 'Dallas', 'Houston' )

GROUP BY ContactID

HAVING count( ContactID ) > 1

)

|||

Duncan,

How about

select

<contact columns>

from <table>

where <city> = 'houston'

intersect

select

<contact columns>

from <table>

where <city> = 'dallas'

That way you only get the contacts with offices in both cities.

Dan

P.S. I think INTERSECT is only available in SQL Server 2005.

|||

( Arnie: give you code another look. )

(Also, won't INTERSECT result in zero rows returned in all cases? Do you mean Union?)

|||

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

|||I suppose your query is amorphous enough that it isn't clear whether or not you are including the LOCATION column, but If both of the queries that compose the INTERSECT include the LOCATION column -- 'Dallas' in one case and 'Houston' in the other -- won't the INTERSECT will exclude them? In fact won't the INTERSECT operator exclude the rows if there is any variation between them?|||

Thanks Kent -it was early...

This will work for you in both SQL 2000 and SQL 2005.

Code Snippet

DECLARE @.MyContacts table
( ContactID int,
City varchar(20)
)

SET NOCOUNT ON

INSERT INTO @.MyContacts VALUES ( 1, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 2, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 2, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 1, 'Austin' )
INSERT INTO @.MyContacts VALUES ( 3, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 3, 'Beaumont' )

SELECT *
FROM @.MyContacts
WHERE ContactID IN ( SELECT ContactID
FROM @.MyContacts
WHERE City IN ( 'Dallas', 'Houston' )
GROUP BY ContactID
HAVING count( DISTINCT City) > 1
)

ContactID City
-- --
2 Houston
2 Dallas

|||

Dan:

I just got done kicking myself for giving you a bit of "business" -- and you sure don't deserve it. I sometimes have problems with "pet" dislikes. I didn't realize that EXCLUDE and INTERSECT might be among them but I am certainly behaving that way. Yes, the truth is to some extent I try to avoid EXCLUDE and I feel that sometimes INTERSECT can get you in trouble too. I am sorry that I gave you a bit of stuff over this. Please forgive me.

Kent

|||

DanR1 wrote:

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

However, if he does indeed want the <city> information included in the resultset, INTERSECT will not work for him without wrapping in an outer query.

|||

Kent,

I also admit that my post to which you responded did not make it clear that "city-related" information must be excluded in order for the query to work. I presumed that the initial post in this thread was from someone who would realize that. (Sometimes I like to say only enough to help someone along, until I realize that I should post more details.)

I like to think of INTERSECT in terms of Venn diagrams. I think it is an incredibly useful set-theoretic capability to have in SQL Server.

I also see EXCEPT as a very useful function, e.g., to use when comparing a new dataset (table) received to see if it differs from the existing dataset. (I used it a lot with ORACLE, in the form of MINUS.)

You are certainly forgiven, but I don't think you did anything that required you to ask for forgiveness.

Cheers.

Dan

|||Thanks, Dan. Cheers. :-)|||Arnie, your queries has a typical mistake - what about a contact who has two offices in Dallas and none in Houston?

However, it can be easily fixed - replace HAVING() line in your query with this one:

having count(distinct city) > 1|||

Ennor,

Right after I hit the [Post] button, I actually thought about that. But from the OP, I assumed that wouldn't be an issue -and if if was, it would come up and we would 'fix' it.

However, your suggested alteration does indeed clear up the situation and make it a more robust solution.

Thanks for mentioning it.

|||

Guys

Thanks for all the replies, using the "where in" works a treat, wasn't sure that would work for this but, seems to.

Including multiple values in where clause

Guys

I'm building a reporting application where users can create bespoke reports, one area I'm having an issue with is having multiple values for a particular column. It is possible to say in english "select all contacts from 'houston' and 'dallas' ", the expected result would be all contacts who have offices in both houston and dallas. In SQL server this is a little harder to make work as a location can't equal both houston and dallas (just on or the other and subsquently returns nothing). An "or" will return either dallas or houston, one or the other, or both.

I'm starting to think this may not be possible, but just can't believe that that's the case.

Hope this makes some sort of sense, as I would like to put this issue to bed once and for all.

Duncanselect <columns> from <table> where <city> in ('houston','dallas')|||

Something like this could work. (Of course, since you didn't provide the table DDL or sample data, it is impossible to 'get it right' for you.)

Code Snippet

SELECT *
FROM Contacts
WHERE ContactID EXIST (SELECT ContactID
FROM Contacts

WHERE Location IN ( 'Dallas', 'Houston' )

GROUP BY ContactID

HAVING count( ContactID ) > 1

)

|||

Duncan,

How about

select

<contact columns>

from <table>

where <city> = 'houston'

intersect

select

<contact columns>

from <table>

where <city> = 'dallas'

That way you only get the contacts with offices in both cities.

Dan

P.S. I think INTERSECT is only available in SQL Server 2005.

|||

( Arnie: give you code another look. )

(Also, won't INTERSECT result in zero rows returned in all cases? Do you mean Union?)

|||

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

|||I suppose your query is amorphous enough that it isn't clear whether or not you are including the LOCATION column, but If both of the queries that compose the INTERSECT include the LOCATION column -- 'Dallas' in one case and 'Houston' in the other -- won't the INTERSECT will exclude them? In fact won't the INTERSECT operator exclude the rows if there is any variation between them?|||

Thanks Kent -it was early...

This will work for you in both SQL 2000 and SQL 2005.

Code Snippet

DECLARE @.MyContacts table
( ContactID int,
City varchar(20)
)

SET NOCOUNT ON

INSERT INTO @.MyContacts VALUES ( 1, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 2, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 2, 'Dallas' )
INSERT INTO @.MyContacts VALUES ( 1, 'Austin' )
INSERT INTO @.MyContacts VALUES ( 3, 'Houston' )
INSERT INTO @.MyContacts VALUES ( 3, 'Beaumont' )

SELECT *
FROM @.MyContacts
WHERE ContactID IN ( SELECT ContactID
FROM @.MyContacts
WHERE City IN ( 'Dallas', 'Houston' )
GROUP BY ContactID
HAVING count( DISTINCT City) > 1
)

ContactID City
-- --
2 Houston
2 Dallas

|||

Dan:

I just got done kicking myself for giving you a bit of "business" -- and you sure don't deserve it. I sometimes have problems with "pet" dislikes. I didn't realize that EXCLUDE and INTERSECT might be among them but I am certainly behaving that way. Yes, the truth is to some extent I try to avoid EXCLUDE and I feel that sometimes INTERSECT can get you in trouble too. I am sorry that I gave you a bit of stuff over this. Please forgive me.

Kent

|||

DanR1 wrote:

INTERSECT should work if no <city> information is in the selected columns.

He wants entries that match BOTH the Dallas and Houston criteria.

Dan

However, if he does indeed want the <city> information included in the resultset, INTERSECT will not work for him without wrapping in an outer query.

|||

Kent,

I also admit that my post to which you responded did not make it clear that "city-related" information must be excluded in order for the query to work. I presumed that the initial post in this thread was from someone who would realize that. (Sometimes I like to say only enough to help someone along, until I realize that I should post more details.)

I like to think of INTERSECT in terms of Venn diagrams. I think it is an incredibly useful set-theoretic capability to have in SQL Server.

I also see EXCEPT as a very useful function, e.g., to use when comparing a new dataset (table) received to see if it differs from the existing dataset. (I used it a lot with ORACLE, in the form of MINUS.)

You are certainly forgiven, but I don't think you did anything that required you to ask for forgiveness.

Cheers.

Dan

|||Thanks, Dan. Cheers. :-)|||Arnie, your queries has a typical mistake - what about a contact who has two offices in Dallas and none in Houston?

However, it can be easily fixed - replace HAVING() line in your query with this one:

having count(distinct city) > 1|||

Ennor,

Right after I hit the [Post] button, I actually thought about that. But from the OP, I assumed that wouldn't be an issue -and if if was, it would come up and we would 'fix' it.

However, your suggested alteration does indeed clear up the situation and make it a more robust solution.

Thanks for mentioning it.

|||

Guys

Thanks for all the replies, using the "where in" works a treat, wasn't sure that would work for this but, seems to.

Inactive Link and Calendar Controls

After installing SQL Server 2005 Reporting Services, report links to
sub-reports are not working and is producing an error. Also, when selecting a
day from the calendar (parameterized), an error occurs as well.
Line: 129
Char: 5
Error: 'event' is null or not an object
Code: 0
URL:
http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_LetterHave you looked at the xml code ebhind the report?
Had a similiar issue when migrating reports across. New reports were
fine. Found editing the xml was quick and fixed the issue.
Tom Bizannes
Microsoft Certified Professional
http://www.smartbiz.com.au
Sydney, Australia
Terry wrote:
> After installing SQL Server 2005 Reporting Services, report links to
> sub-reports are not working and is producing an error. Also, when selecting a
> day from the calendar (parameterized), an error occurs as well.
> Line: 129
> Char: 5
> Error: 'event' is null or not an object
> Code: 0
> URL:
> http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_Letter|||Thank you for your suggestion.
However, the following solution resolved the linking challenges.
SOLUTION:
Turned off ScriptScan on Anti-Virus software
Moved the following files between ASPNET_CLIENT folders based on ASP.NET
version being used
File -> WebUIValidation.js (cannot have file available in both
ASPNET_CLIENT folders â' causes conflicts)
"SmartbizAustralia" wrote:
> Have you looked at the xml code ebhind the report?
> Had a similiar issue when migrating reports across. New reports were
> fine. Found editing the xml was quick and fixed the issue.
> Tom Bizannes
> Microsoft Certified Professional
> http://www.smartbiz.com.au
> Sydney, Australia
> Terry wrote:
> > After installing SQL Server 2005 Reporting Services, report links to
> > sub-reports are not working and is producing an error. Also, when selecting a
> > day from the calendar (parameterized), an error occurs as well.
> >
> > Line: 129
> > Char: 5
> > Error: 'event' is null or not an object
> > Code: 0
> > URL:
> > http://reportserv/reports/pages/report.aspx?itempath=%2fDesigners_Council%2fReports%2fForm_Letter
>

In VS2005 where is Buisness Intelligence Project Option

Dear All,

I am using Visual studio .Net 2005. and i want to create report using sql server reporting services
for sql server 2000.
there is Buisness Intelligence Projects option in Visual studio .Net 2003 but this option
is not present in my vs2005 editor.
where is it exactaly resides or is there any way to do that.

Hi,

VS2005 does not have BI projects. These items are now part of SQL2005. You can install BI items from SQL2005 setup.

In touch with Reporting Services

Hello, All!
It`s the first time with Reporting Services and would like to know if there
are any performance problems when installed on the same server with a SQL
Server.
I`m a Database Administrator and I have a SQL Server into a DMZ with almost
200GB of data and nowadays the company is demanding a Reporting Service into
a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy a
SQL Server license just for this request don`t worth.
So, we decide to install Reporting Service on this server, but I have no idea
about how much recource is needed to handle it and if this installation could
cause any issues.
Anybody knows how Reporting Service works? Are there any best practices when
installing Reporting Services on a OLTP SQL Server ?
Thanks
Juliano Horta
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1You need to get IIS configured on that server before you can install RS,
which itself is a ASP.NET application. That means you will have to run IIS
and SQL Server on the same box. If the hardware can handle that will depend
on the work load. Also, if the RS is SQL Server RS2000, its report rendering
is quite CPU-hungry process and very slow. If a fairly big reports are
requested often, it will definitely slow things down. SQL Server2005 RS'
report rendering is said much better (than RS2000).
Yes, if you place RS in a box different from where SQL Server is, you need
another SQL Server license. You need to make decision based on your work
load and the possible cost of extra box/license.
"julianohorta via SQLMonster.com" <u13014@.uwe> wrote in message
news:8045aaa722294@.uwe...
> Hello, All!
> It`s the first time with Reporting Services and would like to know if
> there
> are any performance problems when installed on the same server with a SQL
> Server.
> I`m a Database Administrator and I have a SQL Server into a DMZ with
> almost
> 200GB of data and nowadays the company is demanding a Reporting Service
> into
> a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy
> a
> SQL Server license just for this request don`t worth.
> So, we decide to install Reporting Service on this server, but I have no
> idea
> about how much recource is needed to handle it and if this installation
> could
> cause any issues.
> Anybody knows how Reporting Service works? Are there any best practices
> when
> installing Reporting Services on a OLTP SQL Server ?
> Thanks
> Juliano Horta
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1
>