Running batches of reports

Hi,
we are making an interesting use of report services...we are actually
using it to generate programming specification for Informatica Mappings
from our mapping spreadsheets..LOL!!
Anyway, we have a couple of hundre mapping specifications and we want
to generate the couple of hundred PDFs associated with those
mappings...I recall reading in my hitchhikers guide how to do this but
that book is about 1,500 miles away from me at the moment.
Would anyone be kind enough to please share the command I need to enter
at the command line to run a report with specific parameters and spit
the output into a specific PDF file also specified as a parameter. I'd
be ever so grateful to not have to wait a few days to go home and read
my book!!
Thanks
Peter
www.peternolan.comHi All,
I got somewhere on this.....if I export and save I get this on the
address line of the browser...
http://ger-eutst/ReportServer?%2fIDW+Reports%2fMetadata%2fMIN0102A+-+Infa+Mappings+Detailed+Documentation&spreadsheet_name=StagingToODSMappingOAP&spreadsheet_version=V00.14&mapping_name=m_ODS_MTL_DEMAND&rs%3aCommand=Render&rs%3AFormat=PDF
So, surely there is some way to enter some kind of similar command via
a command line or via an vb.net program to capture a list of such
pdsf....
Anybody done this before?
Thanks
Peter
www.peternolan.com

running and exporting report using visual basic 6.0 (not using .net)

Hello,
I saw the post below that it is possible if we develop programs with C# or
VB.NET.
My question is
Is it possible to develop the program using visual basic 6.0?
If so, which reporting services dll should be referenced in visual basic 6.0
project?
Could you explain in detail what to do in visual studio IDE environment?
Thanks in advance,
StpnK
<<copied from the previous posts>>
>Yes, Reporting Services provide a set of Web Services API that you can call
>to generate report. Form your client app project (Win Form App), set web
>reference to the reporting services and look into its Render() methods. You
>can also learn more aboubt this from SQL Server2005 Book on Line.
>
"Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> Hello ,All
> I am new to the reporting services and my question maybe stupid .
>
> I created some report and published it on the server.
> Now my question is : does it possible at all to run this report from my
> client (in C# )and to export the result to CSV format also from client
> Is there any web service that I can use which is calling for my report
> ?
> Please ,help me to understand this .
> Thanks a lot.
> Smugliy
>
>
>You would have to use the soap toolkit and do lots and lots of roll your own
coding. And even then there might be some incompatibilities.
Your best bet is to use URL integration. This is very straight forward and
easy to implement. Generate the URL string, embed the IE browser control (if
all you want to do is show the report) and set the url for the control.
There are lots of examples on the web of how to embed the browser control
and set the URL.
Books online has lots of examples of URL integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"stpnK" <ssdfatepano917@.optonline.net> wrote in message
news:47f3122d$0$15166$607ed4bc@.cv.net...
> Hello,
> I saw the post below that it is possible if we develop programs with C# or
> VB.NET.
> My question is
> Is it possible to develop the program using visual basic 6.0?
> If so, which reporting services dll should be referenced in visual basic
> 6.0 project?
> Could you explain in detail what to do in visual studio IDE environment?
> Thanks in advance,
> StpnK
> <<copied from the previous posts>>
>>Yes, Reporting Services provide a set of Web Services API that you can
>>call
>>to generate report. Form your client app project (Win Form App), set web
>>reference to the reporting services and look into its Render() methods.
>>You
>>can also learn more aboubt this from SQL Server2005 Book on Line.
> "Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
> news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
>> Hello ,All
>> I am new to the reporting services and my question maybe stupid .
>> I created some report and published it on the server.
>> Now my question is : does it possible at all to run this report from my
>> client (in C# )and to export the result to CSV format also from client
>> Is there any web service that I can use which is calling for my report
>> ?
>> Please ,help me to understand this .
>> Thanks a lot.
>> Smugliy
>>
>
>|||Thanks for your reply. Bruce.
Let me explain details.
I have a VB6 program (A.EXE) keeping running on the server. This program
should do lots of things such as monitoring the incoming files, reading the
files and do various kinds of tasks depending on the file type and other
conditions on the file...
With certain type of files, this program needs to call report-generator
(dll) (this is the program I want to write) of SSRS 2005 (passing
parameters) so that the report-generator saves the output file as PDF format
on the network folder. After that this program picks up this PDF file and
send emails to the customer with PDF reports attachments.
So to speak, there is no user interaction, no report viewer required...
(1) Is it doable with vb6.0 projects (possibly connecting to SSRS without
user interaction)?
If so, can you tell me the sample URL site? or attach the sample?
(2) I think it is doable with C#.NET or VB.NET...
Can you introduce the URL of samples? Or attach the sample if you have any?
Best Regards,
stpnK
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ul0WYuNlIHA.1184@.TK2MSFTNGP04.phx.gbl...
> You would have to use the soap toolkit and do lots and lots of roll your
> own coding. And even then there might be some incompatibilities.
> Your best bet is to use URL integration. This is very straight forward and
> easy to implement. Generate the URL string, embed the IE browser control
> (if all you want to do is show the report) and set the url for the
> control. There are lots of examples on the web of how to embed the browser
> control and set the URL.
> Books online has lots of examples of URL integration.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "stpnK" <ssdfatepano917@.optonline.net> wrote in message
> news:47f3122d$0$15166$607ed4bc@.cv.net...
>> Hello,
>> I saw the post below that it is possible if we develop programs with C#
>> or VB.NET.
>> My question is
>> Is it possible to develop the program using visual basic 6.0?
>> If so, which reporting services dll should be referenced in visual basic
>> 6.0 project?
>> Could you explain in detail what to do in visual studio IDE environment?
>> Thanks in advance,
>> StpnK
>> <<copied from the previous posts>>
>>Yes, Reporting Services provide a set of Web Services API that you can
>>call
>>to generate report. Form your client app project (Win Form App), set web
>>reference to the reporting services and look into its Render() methods.
>>You
>>can also learn more aboubt this from SQL Server2005 Book on Line.
>>
>> "Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
>> news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
>> Hello ,All
>> I am new to the reporting services and my question maybe stupid .
>> I created some report and published it on the server.
>> Now my question is : does it possible at all to run this report from my
>> client (in C# )and to export the result to CSV format also from client
>> Is there any web service that I can use which is calling for my report
>> ?
>> Please ,help me to understand this .
>> Thanks a lot.
>> Smugliy
>>
>>
>>
>|||I use the URL method in two ways. One, I have used it for integrating in old
type of programs. For instance, we use In-Touch a real time MMI (in the real
time control world the environment that interacts with the user is called
MMI, man machine interface). For that I embed a browser control. However, in
your case you have no GUI and so you would need to go to a lower level,
getting some dll that will go to a website and then get the stream back and
do with it as you will. Personally I think this is going to be a mess.
I think a vb.net or c# program using web services would be a much better way
to go that using URL and VB6.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"stpnK" <ssdfatepano917@.optonline.net> wrote in message
news:47f6b3df$0$15199$607ed4bc@.cv.net...
> Thanks for your reply. Bruce.
> Let me explain details.
> I have a VB6 program (A.EXE) keeping running on the server. This program
> should do lots of things such as monitoring the incoming files, reading
> the
> files and do various kinds of tasks depending on the file type and other
> conditions on the file...
> With certain type of files, this program needs to call report-generator
> (dll) (this is the program I want to write) of SSRS 2005 (passing
> parameters) so that the report-generator saves the output file as PDF
> format
> on the network folder. After that this program picks up this PDF file and
> send emails to the customer with PDF reports attachments.
> So to speak, there is no user interaction, no report viewer required...
> (1) Is it doable with vb6.0 projects (possibly connecting to SSRS without
> user interaction)?
> If so, can you tell me the sample URL site? or attach the sample?
> (2) I think it is doable with C#.NET or VB.NET...
> Can you introduce the URL of samples? Or attach the sample if you have
> any?
> Best Regards,
> stpnK
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ul0WYuNlIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> You would have to use the soap toolkit and do lots and lots of roll your
>> own coding. And even then there might be some incompatibilities.
>> Your best bet is to use URL integration. This is very straight forward
>> and easy to implement. Generate the URL string, embed the IE browser
>> control (if all you want to do is show the report) and set the url for
>> the control. There are lots of examples on the web of how to embed the
>> browser control and set the URL.
>> Books online has lots of examples of URL integration.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "stpnK" <ssdfatepano917@.optonline.net> wrote in message
>> news:47f3122d$0$15166$607ed4bc@.cv.net...
>> Hello,
>> I saw the post below that it is possible if we develop programs with C#
>> or VB.NET.
>> My question is
>> Is it possible to develop the program using visual basic 6.0?
>> If so, which reporting services dll should be referenced in visual basic
>> 6.0 project?
>> Could you explain in detail what to do in visual studio IDE environment?
>> Thanks in advance,
>> StpnK
>> <<copied from the previous posts>>
>>Yes, Reporting Services provide a set of Web Services API that you can
>>call
>>to generate report. Form your client app project (Win Form App), set web
>>reference to the reporting services and look into its Render() methods.
>>You
>>can also learn more aboubt this from SQL Server2005 Book on Line.
>>
>> "Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
>> news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
>> Hello ,All
>> I am new to the reporting services and my question maybe stupid .
>> I created some report and published it on the server.
>> Now my question is : does it possible at all to run this report from my
>> client (in C# )and to export the result to CSV format also from client
>> Is there any web service that I can use which is calling for my
>> report
>> ?
>> Please ,help me to understand this .
>> Thanks a lot.
>> Smugliy
>>
>>
>>
>>
>

running and exporting report through code (C#)

Hello ,All
I am new to the reporting services and my question maybe stupid .
I created some report and published it on the server.
Now my question is : does it possible at all to run this report from my
client (in C# )and to export the result to CSV format also from client
Is there any web service that I can use which is calling for my report ?
Please ,help me to understand this .
Thanks a lot.
SmugliyYes, Reporting Services provide a set of Web Services API that you can call
to generate report. Form your client app project (Win Form App), set web
reference to the reporting services and look into its Render() methods. You
can also learn more aboubt this from SQL Server2005 Book on Line.
"Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> Hello ,All
> I am new to the reporting services and my question maybe stupid .
> I created some report and published it on the server.
> Now my question is : does it possible at all to run this report from my
> client (in C# )and to export the result to CSV format also from client
> Is there any web service that I can use which is calling for my report
> ?
> Please ,help me to understand this .
> Thanks a lot.
> Smugliy
>
>|||Thanks a lot ,Norman
"Norman Yuan" wrote:
> Yes, Reporting Services provide a set of Web Services API that you can call
> to generate report. Form your client app project (Win Form App), set web
> reference to the reporting services and look into its Render() methods. You
> can also learn more aboubt this from SQL Server2005 Book on Line.
>
> "Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
> news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> > Hello ,All
> > I am new to the reporting services and my question maybe stupid .
> >
> > I created some report and published it on the server.
> > Now my question is : does it possible at all to run this report from my
> > client (in C# )and to export the result to CSV format also from client
> > Is there any web service that I can use which is calling for my report
> > ?
> > Please ,help me to understand this .
> > Thanks a lot.
> > Smugliy
> >
> >
> >
>|||Your other option is URL integration. It too will work. It allows you to
pick the rendering format. One point with either URL or web services. By
default CSV is in unicode format, if you don't want this then you can also
tell Reporting Services that you want it in ASCII format.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smugliy" <Smugliy@.discussions.microsoft.com> wrote in message
news:184E2D63-E618-4C35-A0C5-5F3A647B3803@.microsoft.com...
> Hello ,All
> I am new to the reporting services and my question maybe stupid .
> I created some report and published it on the server.
> Now my question is : does it possible at all to run this report from my
> client (in C# )and to export the result to CSV format also from client
> Is there any web service that I can use which is calling for my report
> ?
> Please ,help me to understand this .
> Thanks a lot.
> Smugliy
>
>sql

Running an SSRS 2005 report against a stored procedure with multiple

Hi, I'm looking to confirm what I suspect - that if I have a single
stored procedure that returns 2 results, I can only "design" against the
first result in the designer.
I realize that I can create multiple data sources, and call the stored
proc twice with a parameter to indicate which of the 2 results to return
- I just want to verify that you CAN'T create a single data source that
calls a stored proc that returns two (or more) results, and then try to
design the report against both results.
Thanks,
KevinRS can only use one dataset from a stored procedure.
"Kevin" wrote:
> Hi, I'm looking to confirm what I suspect - that if I have a single
> stored procedure that returns 2 results, I can only "design" against the
> first result in the designer.
> I realize that I can create multiple data sources, and call the stored
> proc twice with a parameter to indicate which of the 2 results to return
> - I just want to verify that you CAN'T create a single data source that
> calls a stored proc that returns two (or more) results, and then try to
> design the report against both results.
> Thanks,
> Kevin
>

Running a Stored procedure or a query

Hi Guys
I woud like to run either a stored procedure before runing the actual
report. The stored procedure will create and update a table and on the second
run the report query will run and produce the report.
Is it possible to achieve this?
Regards
Tofigh> I woud like to run either a stored procedure before runing the actual
> report. The stored procedure will create and update a table and on the
second
> run the report query will run and produce the report.
> Is it possible to achieve this?
I guess your report is scheduled. Just edit the scheduled job and insert a
step that executes the stored procedures before the step that causes the
report to run.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks for the reply, the report is not scheduled. the users will run the
report on demand.
Regards
Tofigh
"Dejan Sarka" wrote:
> > I woud like to run either a stored procedure before runing the actual
> > report. The stored procedure will create and update a table and on the
> second
> > run the report query will run and produce the report.
> >
> > Is it possible to achieve this?
> I guess your report is scheduled. Just edit the scheduled job and insert a
> step that executes the stored procedures before the step that causes the
> report to run.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Then maybe you can use two SPs - one outer that is called from the report
and returns the data, and inside it you first make a call to the second
procedure that prepares the tables.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"TA" <TA@.discussions.microsoft.com> wrote in message
news:9C1789C5-6FF7-469B-888A-6B0E7547F2BA@.microsoft.com...
> Thanks for the reply, the report is not scheduled. the users will run the
> report on demand.
> Regards
> Tofigh
> "Dejan Sarka" wrote:
> > > I woud like to run either a stored procedure before runing the actual
> > > report. The stored procedure will create and update a table and on the
> > second
> > > run the report query will run and produce the report.
> > >
> > > Is it possible to achieve this?
> >
> > I guess your report is scheduled. Just edit the scheduled job and insert
a
> > step that executes the stored procedures before the step that causes the
> > report to run.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > www.SolidQualityLearning.com
> >
> >
> >

Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

Running a SSRS report in a report viewer control in .net

Do I need the report server running in order view a SSRS report in a report viewer control? I've created my report in .net and it works great when I'm viewing it in visual studio, but can I run it my web app without the report server.
The ReportViewer supports local mode where the report definition is distributed with the application and the application feeds the report with data. In local mode, the Report Server is not needed. More details here.sql

Running a SSRS report in a report viewer control in .net

Do I need the report server running in order view a SSRS report in a report viewer control? I've created my report in .net and it works great when I'm viewing it in visual studio, but can I run it my web app without the report server.
The ReportViewer supports local mode where the report definition is distributed with the application and the application feeds the report with data. In local mode, the Report Server is not needed. More details here.

Running a report to populate a cell

Hello,
I need to run several reports using the information in couple of cells of a
table as input parameters, and eventually create a report combining the
result from sub queries into one report.
In other words:
ColA ColB ColC ColD
9898 001 145
8789 000 454
I would like to run a query for every row using ColA and ColB as input
parameter and populate ColD accordingly
Is this possible in Reporting Services?I knew It was a bit cryptical ;)
I am not sure how to accomplish what you mentioned. The current report has
over 200 rows, would it be possible to create 400+ parameters and run
queries for each pair that way?
Here is a more detailed explanation of what I am trying to do
The data is about stock items in our inventory
so each line lists a stock item and some info on it like where it sits, how
many do we have etc.
For each line I need to run a query to get me how many we shipped last year.
then I will extend this and run a query for each item to get setup costs
etc.
the end result will be one table with all the detailed information, the
intend is to generate a formula to determine safety stock levels and reorder
quantities electronically
Thanks
"Skeptical" <skep@.yahoo.com> wrote in message
news:%23gnfkdveEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I need to run several reports using the information in couple of cells of
a
> table as input parameters, and eventually create a report combining the
> result from sub queries into one report.
> In other words:
> ColA ColB ColC ColD
> 9898 001 145
> 8789 000 454
>
> I would like to run a query for every row using ColA and ColB as input
> parameter and populate ColD accordingly
> Is this possible in Reporting Services?
>|||Take a look at the sample reports that come with SRS... the product catalog
example sounds a lot like what you're looking for.
Rather than creat 400+ parameters in a single report, the reports are broken
down by subject matter and detail level. Then, via initiating actions (see
SRS books online) a row value is presented as a hyperlink. When the user
clicks the link, it passes the value as a parameter to another report which
is generated. Although it is another report, it appears to the user as the
same report/site.
Good luck!
X
"Skeptical" wrote:
> I knew It was a bit cryptical ;)
> I am not sure how to accomplish what you mentioned. The current report has
> over 200 rows, would it be possible to create 400+ parameters and run
> queries for each pair that way?
> Here is a more detailed explanation of what I am trying to do
> The data is about stock items in our inventory
> so each line lists a stock item and some info on it like where it sits, how
> many do we have etc.
> For each line I need to run a query to get me how many we shipped last year.
> then I will extend this and run a query for each item to get setup costs
> etc.
> the end result will be one table with all the detailed information, the
> intend is to generate a formula to determine safety stock levels and reorder
> quantities electronically
> Thanks
>
>
> "Skeptical" <skep@.yahoo.com> wrote in message
> news:%23gnfkdveEHA.3028@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I need to run several reports using the information in couple of cells of
> a
> > table as input parameters, and eventually create a report combining the
> > result from sub queries into one report.
> >
> > In other words:
> >
> > ColA ColB ColC ColD
> > 9898 001 145
> > 8789 000 454
> >
> >
> > I would like to run a query for every row using ColA and ColB as input
> > parameter and populate ColD accordingly
> >
> > Is this possible in Reporting Services?
> >
> >
>
>|||Thanks,
Well does it store the new value in the cell or does it calculate it once
the user clicks on the cell? I think I found a way of easily doing it rather
than going through all the cells.
I run the first one and store it in a temp table then I run the second and
store it in a temp table and left outer join them into one master table.
I do not know if Reporting Services would let me play with temp tables yet,
but if it could I am almost done.
I have never seen those example reports, sounds like they worth a look.
Thanks again for your input
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:E9C07F89-F7A6-46D8-ACE8-00390DB859B3@.microsoft.com...
> Take a look at the sample reports that come with SRS... the product
catalog
> example sounds a lot like what you're looking for.
> Rather than creat 400+ parameters in a single report, the reports are
broken
> down by subject matter and detail level. Then, via initiating actions (see
> SRS books online) a row value is presented as a hyperlink. When the user
> clicks the link, it passes the value as a parameter to another report
which
> is generated. Although it is another report, it appears to the user as the
> same report/site.
> Good luck!
> X
>
> "Skeptical" wrote:
> > I knew It was a bit cryptical ;)
> >
> > I am not sure how to accomplish what you mentioned. The current report
has
> > over 200 rows, would it be possible to create 400+ parameters and run
> > queries for each pair that way?
> >
> > Here is a more detailed explanation of what I am trying to do
> >
> > The data is about stock items in our inventory
> >
> > so each line lists a stock item and some info on it like where it sits,
how
> > many do we have etc.
> >
> > For each line I need to run a query to get me how many we shipped last
year.
> > then I will extend this and run a query for each item to get setup costs
> > etc.
> >
> > the end result will be one table with all the detailed information, the
> > intend is to generate a formula to determine safety stock levels and
reorder
> > quantities electronically
> >
> > Thanks
> >
> >
> >
> >
> > "Skeptical" <skep@.yahoo.com> wrote in message
> > news:%23gnfkdveEHA.3028@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I need to run several reports using the information in couple of cells
of
> > a
> > > table as input parameters, and eventually create a report combining
the
> > > result from sub queries into one report.
> > >
> > > In other words:
> > >
> > > ColA ColB ColC ColD
> > > 9898 001 145
> > > 8789 000 454
> > >
> > >
> > > I would like to run a query for every row using ColA and ColB as
input
> > > parameter and populate ColD accordingly
> > >
> > > Is this possible in Reporting Services?
> > >
> > >
> >
> >
> >

Running a report file directly

Is it possible to run a report file directly without having to publish it to
the report server first?
Thanks
--
McGeeky
http://mcgeeky.blogspot.comNot in RS 2000, but it is touted as a feature of 2005!
Kulgan.sql

Running a Report deployed on Reporting Server from C#

I have a report deployed on reporting serice. I am using reporting web service.

How can we call the report, assign parameters and execute it on a webpage

Are you using the full-blown version of Visual Studio 2005? If so, use a report viewer.

I'm not sure what a "reporting web service" is.

|||ReportExecution2005.asmx|||

Is that a link? If so it isn't clickable.

|||

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.aspx

I was refereing to the Reporting Services Web Service

Its ReportExecutionService

|||

Ok. Either I'm very mistaken or you are quite confused. That is the service which allows reports to render and generate.

I don't think there is any claim in this document that is made that would indicate that you can use this service directly to view reports from a webpage.

What I was getting at is you most likely want to create a .net web application and use the report viewer within Visual Studio 2005.

|||

Yeah i was gonna do that approach, get the bytes from webserivces render and using response to write to the page

Do u know how we can use the reportviwer to use the bytes returned from the webserivce

|||

Yes. In a nutshell, you do the following:

1. Create a C# .Net web application.

2. Drag and drop a report viewer to the application.

3. Set the report viewer properties properly to point to the report.

|||

Here's code I used from my application to generate a report, and get the byte stream. You'll note code in there that is used by HTML reports to point to the location of the images returned from the report service (all of which need to be saved to disk in my case). However, you can probably do all of this via stream directly to the output stream as well. Ignore my application-specific code:

Code Snippet

// load the report meta data and execution data

ReportExecutionService res = new ReportExecutionService();

res.Credentials = System.Net.CredentialCache.DefaultCredentials;

res.Url = application.ReportServer + "ReportExecution2005.asmx";

ReportingService2005 rs = new ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

ReportService2005.DataSourceCredentials[] credentials = null;

ReportService2005.ReportParameter[] availableParameters = rs.GetReportParameters(reportPath, null, false, null, credentials);

string outputID = IdGenerator.GetRandomStringId(20, null, null, true, true);

string outputFileName = outputID + "." + ext;

bool gecko = false;

if (request != null && request.UserAgent != null && request.UserAgent.ToLower().Contains("firefox"))

{

gecko = true;

}

string deviceInfo = "/" + site.RewriteUrl(application.VirtualPathRoot + "/Content/Resource.aspx?type=tmp") + "&filename=" + outputID + "_" + (gecko ? "Gecko" : "") + "";

deviceInfo = deviceInfo.Replace("&s=" + site.Key, "&amp;s=" + site.Key);

string historyID = null;

string encoding = null;

string mimeType = null;

string extension = null;

ReportExecution2005.Warning[] warnings = null;

string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

res.ExecutionHeaderValue = execHeader;

execInfo = res.LoadReport(reportPath, historyID);

string sessionId = res.ExecutionHeaderValue.ExecutionID;

// add all execution parameters

PrepareReportServerExecutionParameters(res, reportParameterCollection, format, parameters, availableParameters, application, site);

// execute the report to a file

byte[] result = res.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = null;

try

{

stream = File.Create(application.TempFolder + outputFileName, result.Length);

stream.Write(result, 0, result.Length);

}

finally

{

if (stream != null)

{

stream.Close();

}

}

if (streamIDs != null && streamIDs.Length > 0)

{

for (int i = 0; i < streamIDs.Length; i++)

{

string encodingImage;

string mimeTypeImage;

System.IO.FileStream fs = null;

try

{

byte[] image = res.RenderStream("HTML4.0", streamIDs[i], null, out encodingImage, out mimeTypeImage);

fs = System.IO.File.OpenWrite(application.TempFolder + outputID + "_" + streamIDs[i]);

fs.Write(image, 0, Convert.ToInt32(image.Length));

}

catch (Exception e)

{

Log.Error(site, "Error rendering image stream for report (" + reportParameterCollection.ReportToken + "). " + e.Message + " " + e.StackTrace);

}

finally

{

if (fs != null)

{

fs.Close();

}

}

}

}

The "PrepareReportServerExecutionParameters()" method adds parameters to the report as follows:

Code Snippet

ReportExecution2005.ParameterValue parameter = new ReportExecution2005.ParameterValue();

parameter.Name = name;

parameter.Value = value;

reportServerParameterValues.Add(parameter);

The "reportServerParameterValues" collection is eventually funnelled into this call:

Code Snippet

res.SetExecutionParameters(reportParameters, "en-us");

I hope that all helps!

Michael

Running a Report deployed on Reporting Server from C#

I have a report deployed on reporting serice. I am using reporting web service.

How can we call the report, assign parameters and execute it on a webpage

Are you using the full-blown version of Visual Studio 2005? If so, use a report viewer.

I'm not sure what a "reporting web service" is.

|||ReportExecution2005.asmx|||

Is that a link? If so it isn't clickable.

|||

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.aspx

I was refereing to the Reporting Services Web Service

Its ReportExecutionService

|||

Ok. Either I'm very mistaken or you are quite confused. That is the service which allows reports to render and generate.

I don't think there is any claim in this document that is made that would indicate that you can use this service directly to view reports from a webpage.

What I was getting at is you most likely want to create a .net web application and use the report viewer within Visual Studio 2005.

|||

Yeah i was gonna do that approach, get the bytes from webserivces render and using response to write to the page

Do u know how we can use the reportviwer to use the bytes returned from the webserivce

|||

Yes. In a nutshell, you do the following:

1. Create a C# .Net web application.

2. Drag and drop a report viewer to the application.

3. Set the report viewer properties properly to point to the report.

|||

Here's code I used from my application to generate a report, and get the byte stream. You'll note code in there that is used by HTML reports to point to the location of the images returned from the report service (all of which need to be saved to disk in my case). However, you can probably do all of this via stream directly to the output stream as well. Ignore my application-specific code:

Code Snippet

// load the report meta data and execution data

ReportExecutionService res = new ReportExecutionService();

res.Credentials = System.Net.CredentialCache.DefaultCredentials;

res.Url = application.ReportServer + "ReportExecution2005.asmx";

ReportingService2005 rs = new ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

ReportService2005.DataSourceCredentials[] credentials = null;

ReportService2005.ReportParameter[] availableParameters = rs.GetReportParameters(reportPath, null, false, null, credentials);

string outputID = IdGenerator.GetRandomStringId(20, null, null, true, true);

string outputFileName = outputID + "." + ext;

bool gecko = false;

if (request != null && request.UserAgent != null && request.UserAgent.ToLower().Contains("firefox"))

{

gecko = true;

}

string deviceInfo = "/" + site.RewriteUrl(application.VirtualPathRoot + "/Content/Resource.aspx?type=tmp") + "&filename=" + outputID + "_" + (gecko ? "Gecko" : "") + "";

deviceInfo = deviceInfo.Replace("&s=" + site.Key, "&amp;s=" + site.Key);

string historyID = null;

string encoding = null;

string mimeType = null;

string extension = null;

ReportExecution2005.Warning[] warnings = null;

string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

res.ExecutionHeaderValue = execHeader;

execInfo = res.LoadReport(reportPath, historyID);

string sessionId = res.ExecutionHeaderValue.ExecutionID;

// add all execution parameters

PrepareReportServerExecutionParameters(res, reportParameterCollection, format, parameters, availableParameters, application, site);

// execute the report to a file

byte[] result = res.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = null;

try

{

stream = File.Create(application.TempFolder + outputFileName, result.Length);

stream.Write(result, 0, result.Length);

}

finally

{

if (stream != null)

{

stream.Close();

}

}

if (streamIDs != null && streamIDs.Length > 0)

{

for (int i = 0; i < streamIDs.Length; i++)

{

string encodingImage;

string mimeTypeImage;

System.IO.FileStream fs = null;

try

{

byte[] image = res.RenderStream("HTML4.0", streamIDs[i], null, out encodingImage, out mimeTypeImage);

fs = System.IO.File.OpenWrite(application.TempFolder + outputID + "_" + streamIDs[i]);

fs.Write(image, 0, Convert.ToInt32(image.Length));

}

catch (Exception e)

{

Log.Error(site, "Error rendering image stream for report (" + reportParameterCollection.ReportToken + "). " + e.Message + " " + e.StackTrace);

}

finally

{

if (fs != null)

{

fs.Close();

}

}

}

}

The "PrepareReportServerExecutionParameters()" method adds parameters to the report as follows:

Code Snippet

ReportExecution2005.ParameterValue parameter = new ReportExecution2005.ParameterValue();

parameter.Name = name;

parameter.Value = value;

reportServerParameterValues.Add(parameter);

The "reportServerParameterValues" collection is eventually funnelled into this call:

Code Snippet

res.SetExecutionParameters(reportParameters, "en-us");

I hope that all helps!

Michael

Running a Report Automatically

I want to be able to setup a report that runs everynight at a certain time. Anyone know how to schedule sql server reports?

Thanks

macca

On your report server:

For an existing or shared schedule

Site settings:

Running a report against multiple databases

We have multiple databases on the same server. When I publish a report to
the Report Server how do I point to the database I want to run the report
against?If you use Report Manager, click on the property tab for the report and then
on the Data Source sub-tab. From here you can enter in all necessary
connection information.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Doni G" <DoniG@.discussions.microsoft.com> wrote in message
news:81C1CFCE-7A33-4A3D-AB30-5430EA3EA628@.microsoft.com...
> We have multiple databases on the same server. When I publish a report to
> the Report Server how do I point to the database I want to run the report
> against?

Running a job from a Report using a button? (Is that possible)

Here is the deal. We have a mapping table that stores basic info (like
4 columns). These columns are used to populate parameter boxes in a
report (some parameters depend on the values of others and so we had
to use a mapping table for performance increase). The mapping table
is loaded hourly using a job that calls an SSIS package. Simple
stuff. However, let's assume the user logs in at 12:30 and there is
new data that is not showing, they cannot wait 30 minutes until the
job runs again. At the same time, we can't make the job run that
frequently. So we want to set up a "button" that the users can click
on and it will allow the job to run. We cannot have the job run every
time the report is loaded, takes too much time and would defeat the
purpose of performance enhancement.
So far I have this script in a dataset that points to the msdb
database on our reporting box:
exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
ID!!! I've pressed the ... button near the dataset to try to hard
code the parameter there, no luck.
Also, what would I use to add the user functionality, namely them
being able to click on something to invoke the job?
Thank you so much.I would like add buton to rerun an SSIS package. How did you add a button in
RS?
"Ayman" wrote:
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>|||Hi,
I think the problem is the "@.". Try to use a sproc instead of a text
command.
"Ayman" <aymantg@.gmail.com> schrieb im Newsbeitrag
news:1184855299.083046.294670@.z24g2000prh.googlegroups.com...
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:
> MrTim wrote:
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > to run. If I run 2 instances of it simultaneously, they will slow
> > down by a factor 5+. Reads and Duration of some transactions in
> > Profiler will jump up significanty.
> >
> > If anything you might have expected the second process to benefit
> > from data caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
> > MrTim wrote:
> > > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > > to run. If I run 2 instances of it simultaneously, they will slow
> > > down by a factor 5+. Reads and Duration of some transactions in
> > > Profiler will jump up significanty.
> > >
> > > If anything you might have expected the second process to benefit
> > > from data caching, but for some reason the opposite occurs.
> > >
> > > Anyone got any ideas why this might occur?
> > > On SP3 - cannot yet install SP4 because of AWE.
> >
> > A 5 minute run is extremely long. Running two 5 minute queries will amp
> > up the cpu and disk activity accordingly and force each of them to take
> > much longer to complete - not to mention all other queries running. You
> > usually will not see a change in the number of reads or cpu values for
> > each. You should see a significant change in duration, however. A 5
> > minute operation is probably flushing all good data out of cache
> > multiple times while it scans tables. Not to mention that the disks are
> > probably quite busy during the operation, making each one take longer as
> > they contend for physical reads - which are very, very slow.
> >
> > I would suggest you get the performance of the 5 minute query down to a
> > few seconds if possible, run it off-hours, or run it off a dedicated
> > reporting server to keep it's activity from degrading the performance of
> > other queries.
> >
> > Have a look at the execution plan for the 5 minute query and see if it
> > can be tuned.
> >
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:
> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> > run.
> > If I run 2 instances of it simultaneously, they will slow down by a factor
> > 5+. Reads and Duration of some transactions in Profiler will jump up
> > significanty.
> >
> > If anything you might have expected the second process to benefit from
> > data
> > caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> >
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.
MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>
|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>
|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as lon
g
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes ru
n
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as l
ong
> to run.
>
> "David Gugick" wrote:
>|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processe
s
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What typ
e
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Runing 1st report of the day Hangs

Anyone run in to this issue? Seems to abort on the first report, then when
you rerun it, it works fine.
Happens on different reports, no consistency there.Kris,
I don't recall seeing this.
Do you have any scheduled reports?
Is it the first interactive report of the day that hangs? Or the first
scheduled report of the day?
Also, it may be worth specifying which version and/or service pack you
see this on.
Andrew Watt
MVP - InfoPath
On Fri, 14 Oct 2005 09:54:05 -0700, "Kris"
<Kris@.discussions.microsoft.com> wrote:
>Anyone run in to this issue? Seems to abort on the first report, then when
>you rerun it, it works fine.
>Happens on different reports, no consistency there.sql