Hot answers tagged

77

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012. A Longer Answer on SQL Server Upgrades/Migrations So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...


14

In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' ...


12

... was hoping I could get ... a rough rough estimate of what we should be running. Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate. Database: sql server 2008 r2 enterprise database Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running ...


10

Unless I'm missing some nuance, you will simply set the Row Visibility based on the value. Right click on the detail line and select Row Visiblity... In the window that pops up, select Show or hide based on an expression Within that expression, you'll use logic like =Fields!Total_Allocation.Value = 0 And Fields!Total_Actual_Cost.Value = 0


10

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once. USE msdb; GO CREATE TABLE dbo.ReportServerJob_FailQueue ( job_id UNIQUEIDENTIFIER, run_date INT, run_time INT, -- horrible ...


9

This is account 'bean counter' insanity. The $1100-$2500 you would spend on RAM could possible pay for itself back within a week! They are getting time outs for 20 employees and 5 of them are doing 'resource intensive' work. I'd imagine their time isn't cheap, and some of those reports are the ones the boss not signing off on the paycheck would love. ...


9

Okay, I got it. Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/ I distilled that information to this query: select Catalog.name, cat1.Name datasource from Catalog join DataSource on Catalog.ItemID = DataSource.ItemID join Catalog cat1 on ...


8

Why are you running a log backup job on [ReportServerQA5TempDB]? It should be in simple mode. From MSDN: ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports. and Use the simple recovery model to backup the reportservertempdb database. Now the real ...


8

If you're not using transaction log backups, then you don't need FULL recovery mode. It's that simple.


7

There are a couple of courses of action for you: If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have). From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties. ...


7

There are two places you can control this visibility. The first is on the row in the tablix; the second is on the row in the Row Groups*. If you change visibility by right-clicking the row in the tablix then on the final report the row will be invisible, but still there. If you change visibility by right-clicking the Row Groups line then on the final ...


7

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying. Your options are, in no particular order: Mirroring with snapshots : effective but has management overhead. Backup and restore : Are you restoring from full backups rather than ...


7

You actually just put a SUM around the outside: =Sum(IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100, Fields!actualwork.Value * 77)) It will evaluate that row by row and will provide the correct sum.


7

If I understood you correctly, you have a large third-party system, you don't have much control over it, you make complex reports that read data directly from this third-party database, your queries depend on the internal structure of the third-party database. I would approach it like this: Set up my own separate database, which I have full control ...


6

Use this syntax: ORDER BY name,CASE WHEN country='UK' THEN 0 ELSE 1 END,country


6

The book "The Data Warehouse Toolkit" by Kimball is a must for any fledging BI developer, IMO.


6

You mentioned that you have the table (technically clustered index) partitioned. Does your query use the clustered key(s) as part of the search predicate? If not, you are going to get scans, as opposed to seeks, against the partitioned index. Do you have any nonclustered indexes? Are they also partition-aligned? Have you investigated your query ...


6

Once you're ready to start building reports on that data an alternative (or supplemental) approach would be to pre-aggregate your data. If the reports don't need the same level of detail as the table then it would be a good idea to do periodic loads to some snapshot tables or an SSAS cube that the reports would directly connect to. Directly reporting off ...


6

Have you considered READ_COMMITTED_SNAPSHOT row versioning for the database? Kim Tripp has a good article about it at http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx READ_COMMITTED_SNAPSHOT allows better functionality than WITH (NOLOCK) in that it provides absolute point-in-time consistency for long-running aggregations or queries ...


6

Quick answers: Works, as you noted. Works, as you noted. This does not appear to work. I didn't see an option off-hand and whenever that question gets asked, the answer always comes back to setting the isolation level in the stored procedure. I wouldn't believe so. SSRS is at a higher abstraction layer than the database engine, so in some sense, it ...


6

We actually ran into a similar issue at StackOverflow and Kyle blogged about it: http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/ The problem can be a number of things: Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields) Client-side apps processing data ...


5

Converted to answer, per OP. Check the SET settings for her session (sys.dm_exec_sessions). I wonder if she has different SET ARITHABORT or other settings compared to the other users, in which case she might be getting a different plan for the same underlying query.


5

Mirroring is for individual database. For reporting services, you can user web server farms (AKA scale out deployment). It will allow multiple servers in a cluster. You will need SQL Server Enterprise Edition for this feature, though, as well as a load balancer (Windows has this or NW hardware like BigIP, etc.). See here for more info: ...


5

You can, as stated by Joel, at the report level change te language field (in the properties) but to always get the language used by the user, you can fill in =User!Language So the date-format will change to the one matching the user locale.


4

You can still install through ClickOnce deployment via (assuming you have configured SSRS with ReportServer as the installation path): http://<servername>/reportserver/reportbuilder/ReportBuilder_3_0_0_0.application It's still functional, but it's not full-featured. Source For example, Report Models are not supported and neither are ad-hoc queries ...


4

You can add a row outside of the outermost group right under the tablix header row. Set the row visibility to Show or hide based on an expression. The expression would like like: =iif(CountRows("DataSet1") > 0, true, false) When there is no data, the table will show the headers and empty row. When there is data, the empty row will be hidden.


4

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started. CREATE EVENT SESSION query_check ON SERVER ADD EVENT sqlserver.sql_statement_starting( ACTION (sqlserver.database_id, sqlserver.sql_text)), ADD ...


4

Open "Reporting Services Configuration Manager" --> Database, verify the login under [Current Report Server Database Credential] is one of users in RSExecRole or not, if not, click "Change Credentials" button to change to an user in RSExecRole. You can also use commnadline utility rsconfig as well http://technet.microsoft.com/en-us/library/ms162837.aspx ...


4

Why not change permissions so that folk can't deploy datasources to anything other than this folder? So, remove "Manage data sources" from all folders except for /Data Sources. This can be done at root level and then set custom permissions on /Data Sources You may need to setup a custom role for this if you can't change the existing ones.


4

I don't have 2008 to look at but do you have access to the RDL files that were published to the server? I can't recall if those are plain old XML files, but I think if you have those, it's probably a convenient way - you could parse them with Linq to XML, or XSLT. The schema is documented at: http://msdn.microsoft.com/library/dd297486%28SQL.100%29.aspx



Only top voted, non community-wiki answers of a minimum length are eligible