Solved

SQL Server Reporting Services, RDL and Windows Environment Variable

Posted on 2016-08-23
4
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

There are many software programs on offer that will claim to magically speed up your computer. The best advice I can give you is to avoid them like the plague, because they will often cause far more problems than they solve. Try some of these "do it…
This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
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…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

752 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