Solved

SQL Server Reporting Services, RDL and Windows Environment Variable

Posted on 2016-08-23
4
53 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
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 single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now