Solved

SQL Server Reporting Services, RDL and Windows Environment Variable

Posted on 2016-08-23
4
45 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Mike! Great information.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
A procedure for exporting installed hotfix details of remote computers using powershell
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

772 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

11 Experts available now in Live!

Get 1:1 Help Now