Hot answers tagged

18 votes

8000 character limit on OPENQUERY against a linked server

You can bypass the 8000 character limit of OPENQUERY by utilizing EXECUTE AT, as follows: DECLARE @myStatement VARCHAR(MAX) SET @myStatement = 'SELECT * FROM TABLE WHERE CHARACTERS.... ' -- Imagine ...
user avatar
13 votes
Accepted

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? It depends on the sync mode you have used when configuring ...
user avatar
  • 61.1k
11 votes

Will SSRS be extinct soon, and PowerBI the new model?

Does this mean Microsoft will eventually try to deprecate SSRS? Ref: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/ No. ...
user avatar
  • 12.2k
10 votes
Accepted

Get SSRS Datasources from ReportServer

Get SSRS Datasources from ReportServer I would also like to include the 'Connection String' at least How can I display Data Source details with a SQL query? TSQL for getting the Connection ...
user avatar
10 votes
Accepted

SSRS 2016 Report Portal - KPI option no available in drop down

KPIs are an Enterprise feature. They are only available in the Enterprise and Developer Editions. https://msdn.microsoft.com/en-us/library/mt742500.aspx You will also need to do two things in order ...
user avatar
9 votes

SSRS Blocking Processes

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to ...
user avatar
  • 15.4k
9 votes

Get SSRS Datasources from ReportServer

You can't. When you upload data source and report definitions to the report server it does store their information in the dbo.Catalog table which you can see using Convert(Xml, Convert(Varbinary(Max),...
user avatar
  • 3,428
9 votes
Accepted

SSRS error "something went wrong please try again later" in web portal

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 ...
user avatar
  • 463
8 votes

Is it poor practice to aggregate data from different tables into one?

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 ...
user avatar
7 votes
Accepted

How to pass multi-valued characters in SSRS Report?

I found a solution. I did not properly split the values in the stored procedure with a UDF. To pass multi-values correctly in this stored procedure, I would need to add the following code to the ...
user avatar
7 votes
Accepted

Will SSRS be extinct soon, and PowerBI the new model?

About the separate installer for SSRS, here's a good reason: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/05/17/a-closer-look-at-power-bi-report-server/ In summary, SSRS, and PowerBI Report ...
user avatar
6 votes

Pass a parameter from SSRS to MySQL

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead. So for your query it should look like: SELECT DISTINCT CompanyId, Company FROM ...
user avatar
  • 15.4k
5 votes

SSRS (2008 R2 to 2014) Migration Issues Running Reports

I found the solution in case anyone else comes across this. The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the ...
user avatar
  • 71
5 votes

SSRS and Managed Service Account - impossible to configure?

Yes you can run SSRS with a Managed Service Account. I would double check that your MSA is installed on your SSRS server. You can do so with Powershell. Get-ADServiceAccount -identity msaname -...
user avatar
  • 2,765
5 votes
Accepted

Why cannot connect remote report server instance?

If you're using a different version of Reporting Service Configuration Manager, then it wont pick it up. SSRS Config Manager 2016 doesn't see my local SSRS 2012 instance and vice-versa. Hope that ...
user avatar
5 votes
Accepted

Can SSRS used to Update or Modify data tables?

If Madeira Data shows you how to do it, then I think it's safe to say: It's possible. It can be made to work, as evidenced by this paragraph from them: It’s especially good when your organization ...
user avatar
  • 67k
4 votes
Accepted

SQL Server 2012 Reporting Services: "Unable to connect to the remote server"

I had a similar problem when installing SSRS for a SCOM installation. It was configured with SSL but when I removed this I started getting the same error as above. Solution was to change the ...
user avatar
  • 66
4 votes
Accepted

How to set the Tablix outer border width

In your first screenshot, the BorderStyle value is None. In order for the BorderWidth setting to have an effect, the BorderStyle setting must be changed also (e.g. to Solid). The borders in your ...
user avatar
4 votes
Accepted

Prevent Subscription to report from running on a holiday

have built my SSIS packages to check and make sure that the day isn't a holiday by comparing the date to a table that contains holidays. You can have your 1st job step as IF EXISTS (SELECT 1 FROM ...
user avatar
  • 61.1k
4 votes
Accepted

Which is more efficient, filtering the SSRS dataset or filter using a query parameter

TL/DR Yes you are correct, you can prove it by showing the result of ExecutionLog in the SSRS database. Longer Answer I created 2 identical reports based on the Adventureworks database, one with a ...
user avatar
  • 15.4k
4 votes
Accepted

Report Builder CTP 2.3 Installation DLL Error

Simple solution... posting for anyone else testing SQL 2016. Do NOT supply a default SSRS Server URL during the installation process and it completes without error (Windows 2012 Datacenter and ...
user avatar
  • 2,369
4 votes
Accepted

sp_Blitz "Too Much Free Memory" on seldom-used SSRS instance

I logged a bug for it over on the SQL Server First Responder Kit repository on Github. You can check it for status and also add to it as needed.
user avatar
  • 5,766
4 votes
Accepted

Where/How to generate the validation key and decryption key for SSRS View State Validation

You can generate the machine key through IIS Manager. Use Server Manager -> Roles -> Web Server (IIS) -> IIS Manager. Click on the Web Server in the IIS Manager, then in the right pane, double click ...
user avatar
  • 433
4 votes
Accepted

Moving Report Server Databases to Another Drive

There will be minimal downtime, so plan this in a maintenance window: Don't use detach/attach! Have verified backups! Use Alter database...modify file... (do below for mdf and ldf files) use master;...
user avatar
  • 61.1k
4 votes

SQL Server Error - "Cannot execute as the database principal because the principal [name]" does not exist"

It sounds like the login did not get mapped to the database user following the restore. Try running EXEC sp_change_users_login 'Update_One','dbUserName','dbUserName' pointing to the database and then ...
user avatar
  • 445
4 votes

Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it?

Yes, run a Profiler trace while you run the report. It's just plain old T-SQL. (Source: that's how I wrote the Performance Dashboard Reports chapter in the book SQL Server 2008 Internals and ...
user avatar
  • 41.3k
4 votes
Accepted

Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it?

Yes, we can! What you will need is a .NET decompiler such as Telerik JustDecompile. (Any other decompiler will do, probably, but this is what I use) Once that (or another decompiler) is installed, ...
user avatar
  • 3,383
4 votes
Accepted

Standards or Best Practices for Error Handling in SSRS

As billinkc noted in his comment there is no way to propagate the subreport errors to the main reports. As evidenced in this MSDN post you could try to use data driven subscriptions to drive the ...
user avatar
  • 15.4k

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