Solved

SQL Server Reporting Services, RDL and Windows Environment Variable

Posted on 2016-08-23
4
65 Views
Last Modified: 2016-08-29
If I define a variable in the OS like:

SET MyTest="Hello"

Is it possible and if so, how, to get that MyTest variable value into an RDL of SSRS? It's on SQL Server 2014.

It is possible to do like a variable of %MyTest% from within the RDL?

Thanks in advance for the information
0
Comment
Question by:davism
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 41769701
If you can grant the reporting system the permissions, the code that gathers the data before feeding it to the report (I personally prefer backing reports with stored procedures rather than embedded queries, as they're lots easier to maintain) can use xp_cmdshell to run code to obtain the information you seek. Then run some PowerShell to push it back into SQL Server.
In fact, you should be able to embed PowerShell into your report - see here for some info.

Hope this helps

Mike
0
 
LVL 1

Author Comment

by:davism
ID: 41770240
I prefer NOT allowing xp_cmdshell possibilities from within SQL. SQL should not interact with the DBMS except for maybe DBA need/use.  A CLR is a better option than xp_cmdshell.

Why would the reporting system need permissions to access the environments variables? It would be read-only.
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 41770386
I was referring to the id of the runner of the proc supplying data to the report. However, I also found the CodePlex link that shows something of running SSRS with PowerShell, which might be easier to use to get the environment value variable and produce data accordingly for the report ... I haven't had a chance to try any of this, though (sorry!).

Mike
0
 
LVL 1

Author Closing Comment

by:davism
ID: 41775355
Thanks Mike! Great information.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A procedure for exporting installed hotfix details of remote computers using powershell
In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question