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.

Strang Right() Function Behavior

I have a query that has been working for 6 months. All of a sudden it started giving me the error "Invalid length parameter passed to the RIGHT function". That query contains serveral select queries that are UNIONed together. A RIGHT() function is in one of those select queries. When I ran the select queries seperately, they all worked fine. But when I ran them together with the UNION, I got the error. This is very confusing.

The error went away after I put additional filtered in the query with Right() function. The additional filter is not necessary. But it got rid of the error.

My theory is that when SQL server parses the query, the Right() was verified against data that will be filtered out by the WHERE clause. I hope there is a way to verify that.