SSRS Report #Error

Often when I go to add a field to an SSRS Report, I see #Error when I first try to display the field. The solution is simple, I just forgot to add .Value to the end of the expression for the field.

I normally wouldn't consider something so insignificant to be blog-worthy, but today I spent about a while trying to figure it out before a co-worker reminded me. I'm putting it here so I can find it in the future! Hopefully it will help someone else.

I discovered today that a search for SSRS Report #Error doesn't bring up much that's helpful, I found a few links that mentioned "divide by zero" issues.

Nailed by BizTalk

I got nailed by what seems to be a BizTalk bug yesterday. I found a resolution on Victor Fehlberg's blog. Thanks, Victor! The issue occurs when deploying an MSI that has a map that uses an external assembly. If the map was created when the external assembly was compiled with Debug configuration, the map won't be able to find the assembly compiled with the Release configuration.

Victor's issue was slightly different, so here is the (very ambiguous) error message that I saw. I'm including it here to make it easy to search with Google, and so that I can find this in the future:

Event Type: Error
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006 
Event ID: 5753
Date:  9/9/2010
Time:  5:00:37 PM
User:  N/A
Computer: ACMEDEV1
Description:
A message received by adapter "FILE" on receive location "Rloc_Recs_FF" 
with URI "C:\acme-inbound\ACME_CT\Inbound Flat File\*.*" is suspended. 
 Error details: The system cannot find the file specified. (Exception from HRESULT:
 0x80070002) 
 MessageId:  {5AFADD6F-6C98-4818-8554-13B17D0B22E9}
 InstanceID: {99470432-7526-4CB1-8FC0-7ECDA8C2F08E}

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I resolved the issue by opening the map in my text editor and replacing all instances of "\obj\Debug\" with "\obj\Release\"

Using Notepad after Schema Changes

Often I want to make changes to a schema that I'm using in BizTalk. Typically I will change a node name (or two), or I'll change the namespace.

That will break any maps that reference the schema, and I'll sometimes get error messages if I compile an orchestration that uses the schema. Even worse, if I have some xpath expression in an orchestration, I may not figure that out that it's broken until run time.

I end up using a simple text editor like notepad to edit the map file(s) and orchestration file(s) directly, so I won't have to track down all of the usages of the changed node (or namespace) in the schema. I can just use simple search and replace (although I do check each replace instance before I actually do it). I don't work much with other BizTalk people these days, so I don't know if my approach is common...

BizTalk Error "...is an invalid XPath expression..." when using Table Looping functoid

I was building a BizTalk map today that uses a Table Looping functoid. It's been a while since I have used that functoid, and I thought I had everything setup fairly well. I went to test the map, and here's an error that I saw. The names have been changed to protect the innocent:

C:\dev\Acme Development\BizTalk\Code\Acme.Customer\Acme.Customer.Maps\TestCases_FF_to_TestCase.btm: 
error btm1050: XSL transform error: Unable to write output instance to the following 
. XSLT compile error at (55,45). See InnerException for details. 
'userCSharp:ConvertToXsDateTime(string($var:))' is an invalid XPath expression. 
userCSharp:ConvertToXsDateTime(string($var:))' has an invalid qualified name.

ConvertToXsDateTime() is a function that I put inside of a scripting functoid, and one of the Table Extractor functoids feeds into it.

The issue was that I forgot to drag the output of the Table Looping functoid directly to the node I want to repeat. The output of the Table Looping functoid tells the map how many of the destination node it should produce.

I figured this out by changing the output of the Table Extractor functoids to feed directly into the destination field (instead of into my Scripting functoid), and observing that running the test didn't produce any useful output.

I want to record this here in my blog, since I've seen this error before, and I likely will again.

System.MissingMethodException was unhandled

I have been creating a "string array" for use in a BizTalk orchestration. My first version was quick and dirty, and I didn't implement the IEnumerator interface.

Later, I decided to implement IEnumerator (and IEnumerable) so that it would be more recognizable to the next person reading my code. Everything compiled and a test program started, but the above exception was thrown when the client tried to call the Reset() method.

One thing that I didn't realize was related was that the debugger wouldn't step into my code. I saw the message below when I tried to do that:

---------------------------
Microsoft Visual Studio
---------------------------
The following module was built either with optimizations enabled or without debug information:

C:\WINDOWS\assembly\GAC_MSIL\AtlasReo.BizTalk.Utils\1.0.0.0__799aa29801fe6d60\AtlasReo.BizTalk.Utils.dll

To debug this module, change its project build configuration to Debug mode. To
suppress this message, disable the 'Warn if no user code on launch' debugger option.
---------------------------
OK
---------------------------

As many people reading this have probably figured out by now, I had an older version of my string array class in the GAC. Oops.

Pulling the assembly out of the GAC solved the first issue, and then I could also run the debugger on the assembly code.

BizTalk "Errors exist for one or more children."

I used to see the error "Errors exist for one or more children." a lot in BizTalk 2004 when I was creating an orchestration. The error wasn't valid, because I could usually get rid of it by saving and then deleting code from an Expression shape, then compiling, then copying the code back, and compiling again.

I had not yet encountered this issue with BizTalk 2006 until today. This time the error was tagged to my Loop shape. I was able to fix the error by deleting the code from an Expression shape inside of the loop, and proceeding as I describe above. I'm just glad that I didn't have a large number of expression shapes inside of my Loop, because there was no way for me to identify which Expression was causing the error.

BizTalk 2006 Envelopes

I've been working on a BizTalk 2006 project lately. I was pleasantly surprised to find out that using an envelope schema for debatching is easier than I remembered from when I worked with 2004.

In my case, I had a schema whose records looked something like this:
<root>
<header>child elements omitted!</header>
<body>child elements omitted!</body>
</root>

All I did was to create 3 schemas, one for the envelope, and one each for the header and the body. The envelope schema had the Envelope property of the Schema node set to "Yes", and the Body Xpath property of the "root" node set to:

/*[local-name()='root' and namespace-uri()='']

I created the schemas for the header and the body directly from samples I made by cutting and pasting from the large message.

I then deployed the schemas, set up a receive port / location, and 2 send ports with filters to grab the messages based on the receive port and the message type. Here are the results:



I remember this being more difficult in BTS 2004, I think I created a pipeline component to make this work. I'd like to test this out, but I don't currently have access to a BTS 2004 installation.

Microsoft Enterprise Library Caching Gotcha

Recently, a developer where I work added caching to some methods that retrieve contacts from a table in our database. Afterwords, a tester complained that when he went to one screen that allows selecting a contact, he saw the text "[Select a contact]" repeated. Not only that, every time he returned to that screen, the problem became worse.

Before caching was added, the method that retrieves the list of contacts got them from the database, then it added the text "[Select a contact]" to the beginning of the list. When caching was added, the list was retrieved from the cache (if available), and then the text "[Select a contact]" was added to the beginning.

It turns out that when a list is added to the cache, and then later the list is modified, that modified list will be returned the next time the cache is accessed. Here is some sample code to make it more clear:

private void btnTestCaching_Click(object sender, EventArgs e)
{
  List<string> testList = TestCaching(1);
  PrintList(testList);
  testList = TestCaching(2);
  PrintList(testList);
}

private void PrintList(List<string> testList)
{
  foreach (string s in testList)
  {
      Debug.WriteLine(s);
  }
}

private List<string> TestCaching(int id)
{
  ICacheManager cache = CacheFactory.GetCacheManager();
  List<string> testList = cache["TestList"] as List<string>;
  if (testList == null)
  {
      testList = new List<string>();
      testList.Add("Test 1");
      testList.Add("Test 2");
      testList.Add("Test 3");

      AbsoluteTime expiry = new AbsoluteTime(new TimeSpan(2, 0, 0, 0));
      cache.Add("TestList", testList, CacheItemPriority.High, null, new ICacheItemExpiration[] { expiry });
  }
  testList.Add("[Select a contact] " + id.ToString());
  return testList;
}

I added a counter to make it slightly more clear what's happening. Here's the output from Visual Studio:

Test 1
Test 2
Test 3
[Select a contact] 1
Test 1
Test 2
Test 3
[Select a contact] 1
[Select a contact] 2

I was surprised by the outcome. I thought that the list would be persisted only when cache.Add() is called, apparently that's not the case.

Viewing GAC Assemblies in an Explorer Window

I saw a neat trick somewhere on the internet...at the moment I can't find the original source. I like to give attributions where possible, but oh well.

As many developers probably know, it's possible to navigate to the GAC by using the command line to go to C:\Windows\Assembly (this is on Windows Server 2003).

However, it's also possible to get Explorer to show items in the GAC. All of the assemblies I have created with Visual Studio end up in the GAC_MSIL folder. I can take the following steps to view them: click Start / Run, then key in "%SYSTEMROOT%\assembly\gac_msil", and click OK.

An Explorer window will pop up showing all of the assemblies in that part of the GAC:



Why would you want to do this? This trick allows you to debug a DLL directly from the GAC. You can now navigate to the exact location of the DLL, and then drop the .PDB in the same folder. If you then have the project for the DLL open in Visual Studio, you can attach to the process calling your DLL and set breakpoints, etc.

This doesn't always work smoothly, but there are some situations where it was the only way to get the job done. In particular, if the client for the DLL is a program like BizTalk or SharePoint, it's the only way I could debug the DLL.

I have encountered at least one time when I had to reboot to be able to set breakpoints, but it beat the hell out of my alternatives.

Remember to remove the PDB from the GAC before you try to UN-install your DLL.

T-SQL UPDATE with FROM

I'm putting the following query here where I can find it again...it basically shows how the T-SQL "UPDATE" statement works with the FROM option. It's from a response to an old newsgroup post of mine -- thanks to David Pendleton!

UPDATE dest
SET accountNumber = src.ACCT
FROM Vendor dest
INNER JOIN zCustomer src on dest.[name] = src.CUNA