Execution Policy Issue When Running a PowerShell Script from Team Build

I was trying to run a Powershell script as a step in a Team Build. To do that, I created the Powershell script, and called it in Team Build via an EXEC task. The execution policy was set to Unrestricted on the build server manully. I kept running into the permission error when the build ran ("cannot be loaded because the execution of scripts is disabled on this system. ").

After strugging with it for two days, I decided to put the set-executionpolicy in the Team Build to set the execution policy on the fly. And that fixed the problem.

Why did that work? After some digging (basically adding another powershell command to find out what the current user is.), the Team Build uses the same service account as the one we remote desktop in and set the policy manually. So the only theory that can explain that is the same account has different security settings in team build and remote desktop in. I still need to prove that.

PowerShell Stuff

Ping a server and get results:
***************
$ping = new-object System.Net.Networkinformation.Ping
$result = $ping.send("ServerName")
write-host $result.status
***************
Or
***************
$ip =”ServerName”
$qry = ('select statuscode from win32_pingstatus where address="' + $ip + '"')
$rslt = gwmi –query “$qry”
if ($rslt.StatusCode –eq 0) {
write-host “ping worked” -BackgroundColor "Green"
}
else {
write-host “ping failed” -BackgroundColor "Red"
}
***************

Using Custom Assembly in the Report

I was working on a report that requires getting information from a txt file. To do that, I planed to use the custom assembly reference feature in SSRS 2008. And I found a realy simple but useful post that walks through how to get thing setup(http://geekswithblogs.net/shervin/archive/2008/04/28/121712.aspx).

--Update--
1. The rssvPolicy.config file is not located in the Bin folder but one level up. The above post was wrong.
2. Here is a better post.
http://www.c-sharpcorner.com/UploadFile/balajiintel/CustomAssemblyinRS06302005081435AM/CustomAssemblyinRS.aspx

So I followed the steps in the post and create a Class Library C# solution. The custom assembly worked fine within its own solution. But after I copied the dll to the Report Designer bin folder and granted FullTrust access in the rssvPolicy.config file, the report couldn't find the file. This was frastrating because there is no error or exception that provides more information for debugging. After hours of digging on the Internet with no progress, I created a VB code snippet that does the same thing as the C# custom assembly. This time I embed the code in the report, hoping to get some different results. And indeed, I got an error:
********************
Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Preview complete -- 0 errors, 1 warnings
********************

The above error led me to another Microsoft post (http://support.microsoft.com/kb/842419), which solved my problem.

Solution:
Add the following code to the class
********************
FileIOPermission filePerm = new FileIOPermission(FileIOPermissionAccess.Read, "C:\TestFile[Put in the actual location of the file.]");
filePerm.Assert();
********************

split().GetValue()

The GetValue() method of the Split() function is cool.
E.g.
If the Version number is 5.4.6. I can get the release number by using:
Split(Fields!Version.Value, ".").GetValue(0)
and iteration number by using:
Split(Fields!Version.Value, ".").GetValue(1)

This is very neat.

Adding Line Feed in A Text Box

If you need to have multiple lines in a SSRS text box, VbCrLf is what you need.

Example:
="adbc" + VbCrLf + "xyz"
Result:
adbc
xyz

More details at:
http://stackoverflow.com/questions/26567/how-do-i-set-a-textbox-to-multi-line-in-ssrs

Solving Blockings with Dirty Read

A very nice article about dirty read.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx

Linked Server Name Trick

I created a report that pulls data from Oracle databases via OpenQuery() agained SQL Server linked server. I initially set the linked server name with periods (.), i.e. Environment.Schemaname.abc. Then my SQL query failed because OpenQuery() doesn't like period in the linked server name. After replacing the (.) with (_) everying worked just fine.