20 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 ...
John Eisbrener's user avatar
13 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 ...
IT Thug Ninja's user avatar
13 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. ...
SqlWorldWide's user avatar
  • 13.2k
12 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 ...
Kin Shah's user avatar
  • 62.2k
11 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 ...
Bartosz X's user avatar
  • 503
10 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),...
Cody Konior's user avatar
  • 3,508
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 ...
Anthony Genovese's user avatar
9 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 ...
RoyceBautista's 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 ...
Tom V's user avatar
  • 15.7k
5 votes
Accepted

Multi values for SSRS parameter with Like statement

You would need to change your query in an expression and concatenate the condition using the JOIN expression on your parameter values. For instance, if you create a report based on the Adventureworks ...
Tom V's user avatar
  • 15.7k
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 -...
Bob Klimes's user avatar
  • 3,224
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 ...
thundercougarfalcon's 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 ...
Hannah Vernon's user avatar
  • 70k
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 ...
Tom V's user avatar
  • 15.7k
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.
Tara Kizer's user avatar
  • 5,956
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 ...
SS_DBA's user avatar
  • 453
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;...
Kin Shah's user avatar
  • 62.2k
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 ...
rvsc48's user avatar
  • 553
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 ...
Brent Ozar's user avatar
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, ...
Mister Magoo's user avatar
  • 3,543
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 ...
Tom V's user avatar
  • 15.7k
4 votes
Accepted

Migrating SSRS 2008 to 2016 - ReportSync cannot see Data Sources

Thanks to McNets for the heads up on the map DataSources option. To solve the problem I had to: Load up both servers as per the image in the question Select Tools > Map DataSources I was getting a ...
SE1986's user avatar
  • 1,960
4 votes
Accepted

SSRS 2016 - Change connection string in all reports

You can use the replace value of XML DML command to modify the value stored in the connection string. I have put together an example below for you as a guide but please note that it is untested as I ...
Mr.Brownstone's user avatar
4 votes

Moving SSRS reports from one Server to other

Just migrate the objects with the "ssrs_migration.rss" script. Much easier than database keys and restores rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -...
gbn's user avatar
  • 69.8k
4 votes
Accepted

SSRS 2016 report manager using m/d/y date parameter format in Chrome

I'm not sure if this will work for you but Chrome uses it own language configuration. Go to: Settings Show Advanced Settings Languages Language and Input Settings And then select the ...
McNets's user avatar
  • 23.7k
4 votes
Accepted

Can we download SSRS 2017 to use with SQL Server 2016?

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 ...
David Browne - Microsoft's user avatar
4 votes
Accepted

Microsoft SSRS - Can users view a report based on a database they have no permissions to?

It's up to the report author whether to store credentials for the data source or to run the report using the current user's identity. If you select "Use current Windows user" then the user ...
David Browne - Microsoft's user avatar
4 votes
Accepted

How to limit which NUMA nodes are used by SQL Server / SSRS?

I have SSRS and SQL Server running on same physical server (2 NUMA nodes). How do you configure SQL Server and SSRS to use separate NUMA nodes? You install a hypervisor, like Hyper-V, and run SQL ...
David Browne - Microsoft's user avatar
3 votes
Accepted

How to determine if SQL Server Reporting Services database is being used?

ExecutionLog databases seems to be the way to go: SELECT COUNT(*) AS RptExecutions FROM ReportServer.dbo.ExecutionLog ,ReportServer.dbo.ExecutionLog2 ,ReportServer.dbo....
graybeam's user avatar
3 votes

Keep original column names in SSRS report designer 3.0?

Find and Replace one of the desired characters, such as an Underscore, in the dataset query SQL with a non-sense string, such as "3145234jhasdf" ..... (minus the quotes). In Report Builder, create a ...
solutionist's user avatar

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