Beware of .CS Files

I just had the joy of debugging a thorny issue in BizTalk 2010. I spent quite a while wondering why a property schema I had changed was not showing up in the Admin Console with the changes. I even went to the trouble to delete everything from the GAC using gacutil (the BizTalk Assembly Viewer isn't supported on my 64 bit operating system). Still the schema was unchanged.

It finally occurred to me to look at the .CS file corresponding to the problem XSD file. The .CS file had not changed at all, and the attribute on it was read-only. Deleting it from the file system just caused me to get other errors when I compiled.

Then I looked in TFS, and I realized that a co-worker had added the .CS file there. When I deleted the .CS file from TFS, the issue was resolved. Now the schema changes show up in the Admin Console.

BizTalk 2010 SQL Adapter

I'm finally working with BizTalk 2010, after hearing about it for quite a while. I love the new mapper.

I was using the new WCF-SQL Adapter. I like the new way of doing things, it seems much easier than to use than the old one. In order to invoke it, I right clicked the project and then choose Add / Add Generated Items. Then I clicked Consumer Adapter Service and clicked Add.

On the new dialog box, under Select a binding I chose sqlBinding, and then clicked Configure. On the popup, I chose Windows security. On the URI Properties tab, I entered values for Server and InitialCatalog, and then clicked OK. Back to the Consume Adapter Service dialog, where I clicked Connect, which refreshed some metadata below. Using the tree under Select a Category, I navigated to Strongly-Typed Procedures. I then clicked on my stored proc under Available categories and operations and clicked Add. I entered a filename prefix for the new schemas, and clicked OK.

Two new schemas were created, along with a binding file for the WCF port. The schema file that I use for creating messages, mapping, etc. ends with ...TypedProcedure.dbo.xsd. There's another schema that appears to create a schema that has the types for records to be returned from the proc. One is for stored procs returning one record, and another for stored procs returning multiple records. By default, the ...TypedProcedure.dbo.xsd uses the type for single records, but it looks as though it would be pretty easy to set it up for multiple records.

So I created my messages and imported the binding file WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml using BizTalk Admin Console. When I first tried sending a message through the port, I was surprised to see the following message:

The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom" with URL "mssql://localhost//StevesTest?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Operation Name="GetByAccountNo" Action="TypedProcedure/dbo/GetByAccountNo" />
</BtsActionMapping>" was not understood.

Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

I don't remember where I found the answer to this, but it turns out that in the send port, in the SOAP action header, you have to specify the Operation Name as the same value that the Operation is inside of the Orchestration send port. By default in the Orchestration, it is Operation_1. By default when the binding for the port was created, it was GetByAccountNo, the name of the stored proc. When I made the name of the Operation on the port inside the orchestration the same as the Operation Name in the binding, all was well.

This is actually the first time I can remember that the name of the Operation inside of an Orchestration in BizTalk had significance. Until now I have always left the Operation name as the default.

SSR Report Fun

I was working on an SSRS Report recently that needed to select a bunch of, shall we say, Widgets, and the filter criterion was to select one or more Widget Managers. Each widget manager ID is a GUID. Fairly easy to do in SSRS. Here's the basic query:

SELECT * FROM Widget W
WHERE WM.WidgetManagerId IN (@WidgetManagers)

And here's the query to select the Widget Managers:

SELECT FirstName + ' ' + LastName AS WidgetManagerName, WidgetManagerId
FROM WidgetManager
ORDER BY WidgetManagerName

Here's what the parameter definition looks like:


This works okay, but for my purposes there were 2 issues. First of all, my client wanted all items to be selected by default. That's an easy problem to handle, all I need to do is to set the Default values for the WidgetManagers report parameter to the same dataset I use for Available values.

Unfortunately, that query ran really slowly. When I looked at the query using SQL Profiler, it looked like this:

SELECT * FROM Widget W
INNER JOIN WidgetManager WM ON
W.WidgetManagerId = WM.WidgetManagerId
WHERE WM.WidgetManagerId IN
(N'0fa07056-8e50-43a0-b72d-000a68d17be1',
N'8eff8f59-ca6a-4eed-a434-016a8831c7ec',
N'2a3a885e-8fb2-4e8b-b55a-02a7225a1143',
...)

I only showed 3 of the GUIDS, but the query sent to SQL had all 1000+ of them inline in the query. No wonder it was slow.

I'll skip to the final solution. I changed the query for the Widget Managers to look like this:

SELECT 'All Widget Managers' AS WidgetManagerName,
'00000000-0000-0000-0000-000000000000' AS WidgetManagerId, 1 AS OrderBy
UNION
SELECT FirstName + ' ' + LastName AS WidgetManagerName,
WidgetManagerId, 2 AS OrderBy
FROM WidgetManager
ORDER BY OrderBy, WidgetManagerName

Then I changed the query for the Widgets themselves to look like this:

DECLARE @WidgetManagerCount AS INT
SET @WidgetManagerCount = (SELECT COUNT(*) FROM WidgetManager
WHERE WidgetManagerId IN (@WidgetManagers))

SELECT * FROM Widget W
WHERE
(1 =
CASE
WHEN @WidgetManagerCount = 0 THEN 1
ELSE
  CASE
  WHEN W.WidgetManagerId IN (@WidgetManagers) THEN 1
  ELSE 0
  END
END)

And finally, I changed the report parameter to look like this:


The basic idea is that when the report first comes up, "All Widget Managers" is chosen, and all other Widget Managers are unchecked. When the Widget query runs, @WidgetManagerCount will be set to 0, and only the top part of the outer case statement will be evaluated, and all Widgets will display on the report.

If any other Widget Managers are checked, @WidgetManagerCount will then be greater than zero, which will cause the inner case statement to be evaluated for each Widget. If a Widget Manager has been checked, all related Widgets will be displayed on the report.

This version of the report ran much, much faster than the original. What I'm showing above is a stripped down version of the report, but the real one had 4 filter criteria, and a couple of those filter criteria had thousands of choices.

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.