Subscriptions To Operational Reports And Options For Scaling Out

Sep 26, 2007

We use timed subscriptions to do almost all of our reporting. Reports are delivered (primarily via e-mail and printer) once they are completed and users don't have to "watch the pot boil" so to speak.

Apparently SSRS has some load balancing capability whereby it lets only a limited number of threads/reports run concurrently. We often reach this max and lock ourselves up on some very long-running reports, causing other important reports to wait a long time.

We've added some operational reports (ie. document prints) to the mix. These reports run off of OLTP data. They are very fast and very high priority. Waiting on them is not an option. Is there some way we can get SSRS to work on these operational reports in preference to other types of reports (eg. "just for kicks" reports)? I think we'd almost like to add another SSRS server and dedicate it to the operational reports. Ideally the new SSRS server would use the same Report Server database but would only work on subscriptions for certain documents.

Has anybody else tried to solve this problem? This MS document does really address subscriptions or load balancing by report: http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx

Any help would be very much appreciated.

View 6 Replies


ADVERTISEMENT

Data Warehousing :: How To Decide Whether It Is Required To Develop Operational And Analytical Reports

Jul 15, 2015

I have received some reports and I have been asked to decide whether these reports can be developed as an operational report or Analytical Reports.

Basically I wanted to understand what points needs to be considered while deciding whether I should go for Analytical reporting (Cubes) or Operation Reporting.

View 2 Replies View Related

Comparison Of SQL Server Reporting Services To Access Reports, Crystal Reports, Cognos Or Other Options

Nov 5, 2007

Hello SQL Server Experts, Data Analysts, and Report Writers et al:

re: Reporting Options with SQL Server

I wanted to propose an offshoot to the pryor thread:

Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?

I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?

Thank you to all, and I hope this is a beneficial discussion to others.

Hal1490



Hal9000

View 4 Replies View Related

How To Stop SSRS Reports Subscriptions

Mar 11, 2008



We have 7 SSRS reports subscription which execute every night at various time intervals.



Some times, due to database unavailability subscriptions can not access the source database, so these subscriptions continuously try to connect the database. (Even after setting connection time out.)



When the database is up again, all these subscriptions create bottleneck for the database and the whole system crashes.



So we want to stop subscriptions after some time interval (lets say 2 hours after it starts),

After that time interval what ever may be the status of the subscription (processing, done or fail) it should stop.



Please let me know the solution, Thanks in advance.

View 1 Replies View Related

Options For Generating Reports

Oct 26, 2007

Hi,

I am very new to RS and I have a scenario that I am not sure how to enact in the application I am building.

I have a report that generates remittances for an EFT payment file. The Remittances in this file are either printed or emailed to the company they are form. The output type and recipients email address are stored in the Db.

I would like the user to be able to click my Generate Remittance button, and instead of the report being rendered on screen, I would like it to automatically send all remittances marked for printing to the printer, and all marked email to be sent to the email address that is in the Db for that report.

I am thinking that my application will need to run the report twice, the first time reporting on only the ones needing printing (but then how do I automatically send the report to the users default printer without the report being rendered on screen), and then a second time reporting on the emailed ones and then automatically emailing them off to the individual recipients.

If anyone can shed some light on what I am needing to do, or maybe even suggest a better way to go about it, it would be much appreciated.

Cheers
Jason

View 1 Replies View Related

Parameter Fields Get Unselected On Reports Under My Subscriptions

Sep 11, 2007

Under My Subscriptions I have a report that runs daily. This report allows me to select certain items from a dropdown in parameter fields or select all. Some days the report fails and I get the follow error: The subscription contains parameter values that are not valid. When I click Edit to review, the parameter field is blank and my selections have been unselected. This report has several parameter fields but only only one or two of the parameter fields are blank, the others are ok.


View 4 Replies View Related

My Data-Driven Subscriptions(DDS) Don't Want To Work Although I Can View My Reports

Nov 28, 2006



Hi

I have been asked to move our entire system over from SQL2000 to SQL2005, i eventually got our database and Cubes over to 2005 and converted all my reports to 2005 and deployed them successfully on RS2005

All the subscriptions have been made and they seem to work when i click to view them. Although when i create a DDS for all my reports to run then they fail. Now i've got over 5000 reports to build and i'm definately not going to create each report manually. My SQL Reports huild successfully, it just seems to be the MDX Reports with a problem, i;ve checked my stored procs and they seem fine, i've even seen the [Clients].&[All.Clients] difference from SQL2005 ---->&<------ that is not in SQL2000.

If someone has, or has had the same problem before would you please help me. Any advice or suggestions would be more than welcome.



Kind Regards

Eagle

View 1 Replies View Related

AnyOne Has Implemented Custom Subscriptions Of Reports(Reporting Services 05) ?

Dec 28, 2007

Any One has implemented Custom Subscriptions of Reports(Reporting Services 05)

View 4 Replies View Related

Reporting Services :: SSRS Subscriptions Resend Reports For Each Subscription?

Sep 1, 2015

I have a Daily Invoice Report that has subscriptions for all customers and is run every night.  The report has parameters of @date & @Customer.  We had issues with invoices not being sent due to the Owner on the reports was disabled in Active Directory.  We do not know how many reports did not get sent out so I am tasked with creating a script to rerun the reports for each day in August.  I have queried the subscriptions, but need to figure out how to rerun the reports using the existing parameters set in the subscription, increment the report parameter date, and execute the emailing of the report.

Here is my query that gives me all the subscription information, but how do I rerun the job for all subscriptions for each date in August?

SELECT USR.UserName AS SubscriptionOwner
      ,SUB.ModifiedDate
      ,SUB.[Description]
      ,SUB.EventType
      ,SUB.DeliveryExtension

[Code] ....

View 2 Replies View Related

One Or More Subscriptions Have Been Marked Inactive. Drop And Re-create All Subscriptions For This Node...

May 16, 2007

Greetings,



We periodically get the following message in our three server peer-to-peer transactional replication environment:



"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."



What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?



Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.



Thanks,

BCB

View 9 Replies View Related

Email Subscriptions: Changing From Address For Different Subscriptions On Same Server

Jan 18, 2007

I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?

It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).

View 1 Replies View Related

LogShipping Cant Make Sec Srvr Operational

Feb 12, 2004

Whenever I am trying to put my secondary server operational as my primary (primary is not available) by running

sp_change_secondary_role

@db_name = 'TX_ACCT',

@final_State = 1,

@access_level = 1,

@terminate = 1



I am repeatedly getting this message



Server: Msg 3021, Level 16, State 1, Procedure sp_change_secondary_role, Line 124

Cannot perform a backup or restore operation within a transaction.

Server: Msg 3013, Level 16, State 1, Procedure sp_change_secondary_role, Line 124

RESTORE DATABASE is terminating abnormally.

Server: Msg 226, Level 16, State 1, Line 1

ALTER DATABASE statement not allowed within multi-statement transaction.



Can you please help? This is urgent

View 6 Replies View Related

SQL 2012 :: CDC Overhead On Operational Database

Nov 6, 2015

We are going to apply CDC on one database.

CDC is creating additional tables under System tables.

What is the performance overhead on the database by creating these tables?

I am going to access the CDC records through one ETL tool. Once read the data I am going to delete the records.

If frequency of changes are more once reading the data there may be few records will be added to the CDC. Is CDC is going to truncate the tables or only read records?

View 0 Replies View Related

Bulk Delete On Operational Data

Nov 23, 2005

Hello,I read several articles of newsgroup about the bulk delete, and I foundone way is to:-create a temporary table with all constraints of original table-insert rows to be retained into that temp table-drop constraints on original table-drop the original table-rename the temporary tableMy purge is a daily job, and my question is how this work on a heavyload operational database? I mean thousand of records are written intomy tables (the same table that I want to purge some rows from) everysecond. While I am doing the copy to temp table and drop the table whathappens to those operational data?I also realized another way of doing the bulk delete is using BCP:1) BCP out rows to be deleted to an archive file2) BCP out rows to be retained3) Drop indexes and truncate table4) BCP in rows to be retained5) Create indexesAgain the same question: When I'm doing the BCP is there any insertionblocking to my original table? What happens to my rows meantime to beinserted?Does BCP acquire an exclusive lock on the table which prevents anyother insertion?Does any one have an experience with a BCP command for querying out 2million records, and how long will it take?I appreciate your help.

View 2 Replies View Related

Scaling SQL Server 2005

Jan 27, 2008

Hi,
assuming a data driven web application was written, and it is used by a huge number of customers, we can set up some load balancing infrastructure, where multiple web servers run to process all requests. In this case, the database might run on its own machine. But what can be done if one machine is not enough for the database anymore? Is it possible to build some kind of database cluster? And if yes, how does the application work with such a cluster? Is there still ONE connection string or is it needed to query multiple SQL servers?
Long story short; Can a SQL Server database cluster be handled as ONE SQL Server instance from outside, even it is hosted on multiple machines?And if yes, what edition of SQL Server 2005 is required?
RegardsMarco Buerckel

View 2 Replies View Related

SQL Database Scaling Inexpensively

Mar 17, 2008

Hi Folks,

I have web application for which I need to come up with a scalability plan. The application right now is running on 2 computers, the front end is running Tomcat with JSP (Java Server Pages). The back end is MS SQL 2000

The database is running fine now, but the file size and CPU utilization is growing and I need to figure out a way to distribute this database over several machines.

I looked at MSCE and setting up a cluster, but I think that the solution will NOT work in an intensive enterprise environment. (Am I wrong?). For one thing, there is a hard limit of 16 nodes in a cluster. Also, it requires a shared RAID or FibreChannel Array. These solutions are very expensive. Also, it's not easy to add additional capacity if you reach the maximum of the RAID array. For example, if you have a FibreChannel Array with 8 Terrabytes of capacity, if your DB uses up all 8 terrabytes, you have to buy a whole new enclosure.

One of the major objectives I have is to minimize cost and support unrestricted database growth.

Here is what I would like to achieve. If any of you know of a solution or something that YOU have USED which might give the functionality I require .. I would greatly appreciate your feedback.

Wish List:

1) Run single SQL Instance on regular PCs, which host 1 or More Tables (Table level partitioning) of a very large database.

2) Distributed JOINs on Tables residing on different physical PCs

3) Multiple Machines hosting same table (Fault Tolerance)

4) Single Virtual IP for database to Application Front End.


-----------

I looked at C-JDBC which is an open source middleware that can do some of the things above. I liked it, but it doesn't seem to support Distributed JOINs which means that our application - which requires quite a few tables JOINs - won't be able to achieve Table level partitioning.

Can anyone recommend any clustering middleware which you can recommend?

View 3 Replies View Related

Question About Considering Scaling While Designing

May 14, 2008

I'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.

Any suggestions to consider? I'd appreciate any opinions.

View 5 Replies View Related

SSB &&amp; Scaling Architecture Question

Apr 26, 2007

Hi,
My application is about to scale up significantly, and it seems that SSB could be very useful to help me scale it right, especially I like the multiple readers feature.

So, here's the deal €“
my application is about to get lots of records (peaks could gets up to €“ 300k records per hour). Each record must be processed separately, but could be processed in parallel €“ no dependency between rows (gut feeling tells me not to overuse parallelism, but I€™ll do my tests).
It takes about 10ms-15ms to process a single row; I don't mind sliding to non peak hours if needed.
Each record is constructed of about 10 columns.



My questions:

1. Is SSB the right solution as a buffer queue? any famous use case is similar to my problem ?

2. What is the fastest easiest way to serialize / deserialize each record?

I would prefer not using CLR integration due to performance issues and stick with tsql for now. I don€™t necessarily prefer XML serialization. If binary serialization works faster, it€™s fine with me.

View 1 Replies View Related

Recovery :: AlwaysOn Secondary Operational State Unknown

Sep 30, 2013

I recently configured SQL Server 2012 AlwaysOn Availability group using two nodes - a primary and one secondary read only replica.  The group is residing on a windows 2012 cluster with an smb file share as the quorum.  I am able to successfully failover through SQL and through the windows 2012 cluster.  When I look at the group dashboard on the primary server and view the Operational state of each node I notice an odd value.  The secondary role server is listed as Unknown.  I also noticed that the Availability replicas node icons in object explorer are displaying the same icon on the primary server but on the secondary server, the primary server is shown as a server with a question mark.

Am I missing a permissions setting or is this normal behavior.

For example:

ServerA is the primary
ServerB is the secondary
ServerA  lists the servers in Object Explorer as:

ServerA (Primary)ServerB (Secondary)
ServerB  lists the servers in Object Explorer as:

ServerA ServerB (Secondary)

The primary is never listed a primary on the secondary server.  Again failovers are working properly, but I want to be sure I am not missing a setting somewhere.

View 5 Replies View Related

Pivot Chart Value Axis Scaling Question

Jul 23, 2005

I have a pivot chart that displays 3 data series.Series 1 values range from 17 to 106.Series 2 values range from 1 to 18.Series 3 values range from 0 to 1.When I display all three series on the chart, the value axis labels run from1 to 120 with major unit lines at intervals of 20.When I remove Series 1, the chart automatically adjusts itself and thevalue axis labels run from 1 to 20 with lines at intervals of 2, thusnicely accommodating the value ranges of Series 1 and Series 2.All this is fine. But when I remove Series 2, leaving only Series 3,the labels run from 1 to 1.2 with interval spacing of .2. Since thesevalues are counts, it makes little sense to show decimal values.Can anyone provide advice on how to keep these axis labels fromshowing decimal values, if a user removes Series 1 and Series 2?Thanks.- Bob

View 2 Replies View Related

Scaling Model For Sales Force Automation

Jun 21, 2006

Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.



This doable? -- I couldn't find a timing/ sizing model any place.

View 1 Replies View Related

Advice On Middleware Products For TRUE Scaling Out Of SQL Server

Apr 14, 2006

We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com

View 17 Replies View Related

For New In SSIS. To Send Data From Operational D/base To Data Warehouse

Oct 6, 2006

Hi Dear All!

I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.

I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.

Please help me if I can look similar packages performing?
Thank you!!

View 5 Replies View Related

Scaling Width Of Report To Fit To Page Width

Apr 9, 2007

Hi,

I've a report projects where each report has a number of columns and the spec is to have all the columns print-out on the same page. Is there a setting that will auto-scale these columns to fit to the page or will I have to edit the font size and widths manually on each report to fit to the page?

Thanks for any comments.

View 4 Replies View Related

Reports Usage Frequency For All The Existing Reports On SSRS 2000

Mar 18, 2008

I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.

Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?

I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.

Any help is appreciated

View 2 Replies View Related

Reports Usage Frequency For All The Existing Reports On SSRS 2000

Mar 18, 2008

I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.

Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?

I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.

Any help is appreciated

View 1 Replies View Related

Remove All Reports ( Not Data Sources) From Reports Server

Jan 23, 2007

Hi does anyone know how to do the above with out going through reportserver url?

Preferably by using a cmd tool ? such rs.exe

or through the backend in the reportserver DB?

Thanks

Dave

View 2 Replies View Related

Migration Of SQL Reports 2000 To SQL Reports 2005

Feb 13, 2008

Hi Friends,

Could I migrate the reports which are developed on SQL 2000 to SQL 2005. If Yes then How ...

Could anyone explain me. How it works.


Thanks
sqlferns

View 2 Replies View Related

Converting Crystal Reports To SSRS Reports

Mar 27, 2008


My issue is with converting multi-value parameters:

In Crystal Reports, you can set a parameter to accept multiple vales (Discrete, Range or Discrete and Range).

As an example:

I have a database table with a column called ID.
I can create a parameter called param_id and set the options of the parameter to "Allow multiple range values".

With this setup, I can limit the result set of the report by comparing the param_id parameter to the ID column in the database. Because param_id is a multi-value range parameter, I can pass it the following data:
1 - 50
60 - 80
150 - 127

This will only return results within those ranges.


Does anyone know if SSRS provides this kind of functionality?

Thanks,

Patrick Conway

View 9 Replies View Related

Copy Reports From Other Users My Reports Folder

Aug 7, 2007

Is there a way to copy reports from other users "My reports" folder? I am logging onto the management studio reporting services using an administrative account and I am able to view the reports from all users' "My Reports" folder. But I am not able to export them as an rdl file. I am able to export reports from other common folders, but not from the "My Reports" folder.

The reason I need to do this is some of the users have created some reports in one environment and the reports are available in their respective "My Reports" folder. I need to move these reports to their corresponding "My Reports" folder in another environment.


Is there a way to do this?

Thanks for your help.

View 3 Replies View Related

Subscriptions

May 15, 2006

We are running Reporting Services on an Application server, side by side with a web application. The server exists outside our corporate domain due to the fact that our external vendors need access to the application (not the reports). Our web app uses a third party mail system to funnel the application email thru our corporate exchange server. Since Reporting Services is not connected to our domain, it can not authenticate to send reports via e-mail subscriptions. I am looking for alternatives to make this work from outside the domain.

View 7 Replies View Related

Subscriptions

Mar 13, 2007

Hi all,

I have created data drvien subscriptions in Report Manager (rs 2005). When the reports are run in Report Manager they give a status ie 5 processed of 5 Total-2 errors. Is it possible to find out what these errors are as no indication is given?

Thanks in advance

View 3 Replies View Related

Need Help With Subscriptions

Feb 1, 2008



We've got a lot of user subscriptions that need to be re-run (data was bad on the initial run). Is there anyway to do this programatically or behind the scenes? We're looking for a way to avoid going into each subscrption and re-running.

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved