Hot answers tagged

7

After some digging I found plenty of unconnected issues, but finally managed to solve them and wanted to share them all in one place so nobody else waste that much time if hits the same issue (or some of them). First you want to access is the Reporting Services Error Catalog, depending on your installation directory, it will be something like this: ?:\...


4

You can download and install SSRS 2017, and use an existing SQL Server to store the Report Server database. The databases can be on any supported version of SQL Server, as documented here. The server running SSRS 2017 must, of course, be licensed per the licensing guide here. Alternatively you can get this functionality (and more), with Power BI Report ...


3

I guess because it cant have a string value with a column formatted for Date and time? You are correct. CASE Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. Data type precedence comes into play here. You'd have to convert your column to a varchar. DB FIDDLE case when ...


3

Report Server executes the query and stores a copy of it within its cache. This is outside of the SQL Server process and so you will need to account for it when architecting your installation. Generally, the biggest winner I have noticed when I have done installations myself is the number of CPU's. RAM helps of course, but I have found additional CPU's to ...


2

The reason the documentation states that automatic failover is not supported is that there are post-failover tasks that must be carried out to ensure the Report Server is completely online. See this section for information on exactly what tasks need to be completed. Essentially it boils down to needing to stop the SQL Agent on the old primary and restart the ...


2

When you setup a data source, you can specify components of the connection string directly. Simply add the following to the connection string: Encrypt=True;TrustServerCertificate=True That will force encryption between the SQL Server and the SSRS reporting server for the given data source. Any report that uses that data source will obtain its data via an ...


1

The log in question I believe is Report Server Service Trace Log and it turned ON by default https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-http-log?view=sql-server-ver15 I figured this by checking where the log is stored in the question. Trace log behavior is managed in the configuration file ...


1

Not a direct answer to this question, but as an alternative and certainly very useful, I have followed the advise and link provided by Peter Vandivier in the comments above and doing some changes in the original script, like how to convert to VARBINARY(MAX) and even considered master.dbo.fn_varbintohexstr but it was too slow. Here is the modified script, ...


1

Because the ReportServer and ReportServerTempDB databases need to be able to communicate with each other, SQL Database is not a valid option. However, if you migrate to SQL Server 2008 in an Azure VM and have Software Assurance then you may be able to get extended support and continue to use the 2008 version for an extended period.


1

I assume you are talking about migrating ReportServer and ReportServerTempDB. As per this document, Azure SQL Database is not supported. Below are the support databases Azure SQL Managed Instance SQL Server 2019 SQL Server 2017 SQL Server 2016 (13.x) SQL Server 2014 (12.x) SQL Server 2012 (11.x) SQL Server 2008 R2 I have done one Reporting Server ...


1

why would that be the case only with SSRS? As addition note to the Handy's answer, one of the main reason, SSRS dependencies are not only in ReportServer and ReportServerTempDB databases, but also on SQL Server Agent which host all the subscription configuration of the SSRS and which would not be a scope of AG replication. So, you got covered ReportServer ...


1

This db<>fiddle shows a mock-up of two ways to solve this (with some highly simplified tables). First, you can create a function that calculates the days between the dates with the conditional check against weekends (and holidays in my example). The function simply takes the order and shipment dates as parameters, counts the number of weekends and ...


1

Did you try looking in the Reporting Services Configuration on the server in question? https://social.msdn.microsoft.com/Forums/en-US/a856488b-fbcd-479e-9167-34ddf7ba3e83/how-do-i-find-out-if-ssrs-is-intregrated-or-native?forum=sqlreportingservices


1

You're running into this problem because of the difference in how SSRS passes multi-value parameters when using a text query vs. using a stored procedure for your dataset. When using a text query, the parameter is actually replaced with a literal list of strings before executing the query, e.g. PDPOID IN (@PDPOID) is translated to PDPOID IN ('aaa', 'bbb', '...


1

You could achieve this by changing your dataset query to an expression and use the Join operator on the multi-valued parameter, for example, your expression would look like = "Select * " & "from table t " & "where a11.PDPOID LIKE '%" & Join(Parameters!TestParm.Label, "%' OR a11.PDPOID LIKE '%") & "%'" Replace the Select * from table ...


1

Following on from @McNets, the problem is how to educate all of the users. I plan to create an SSRS report that will be posted on the root of the server. It will have one date parameter and the text of the report will describe what to do, with embedded images of the configuration changes (to be updated with subsequent versions of Chrome) Users can then ...


1

It appears that you just want a copy of all your databases centrally located, which could then be used to run the same report for different regions, just by changing the datasource? You could acheive this with a single Server and a single instance. While I wouldn't call this a data warehouse (per se), but it might be the simplest solution to solve your ...


1

I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this: Yes, but the named instance ports are dynamic and managed internally by SQL browser service which use the port number 1434. In your case port# 8000 should not be used ...


1

You can use AlwaysOn Availability Groups to replicate the SSRS Databases to other servers, and reference the Availability Group Listener in place of the server names to allow for failover between servers. Be aware that Availability Groups do not replicate server level objects such as logins or jobs. If you have any subscriptions in SSRS these will be ...


1

SSRS isn't doing anything special. It's actually the query that's making the distinction to either pull all fuel types or just the one specified, though I suspect the book has a typo because it looks to be missing an extra pair of parenthesis. The real magic is supposed to be happening in the predicate, here: WHERE (YEAR(FuelPrice.PriceStartDate) = @year)...


1

SSRS just needs a SQL Connection to a database that supports all of the features it requires. The connection type that it uses should be forward compatible to SQL 2016 (and even 2019). I want to ask, why not upgrade SSRS as well though?


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