Biztalk 2009: SQL to WCF-SQL adapter migration; orchestration not receiving message?

Question

From topic, I have a receive location that currently uses sql adapter(receive port) to call(poll?) a stored procedure. The stored proc returns a FOR XML result. The receiver then activates an orchestration which takes the message and populate the data from the message and into some variables (expression shape).

Orchestration looks like:

LongScope[ AtomicScope[ Receive location -> Expression ] ][Error handling]

I tried a direct migration to wcf-sql with XMLpolling as InboundOperationType, but it throws a null exception during the variable assignation(I assume).

Additional detail: I caught the message from the receiver by filtering pipelineName using a send port. There is a slight different in the message retrieved by sql and wcf-sql adapter

sql:

<rootNode xmlns="namespace"><row data1="data1" data2 = "data2" /></rootnode>

wcf-sql:

<rootNode xmlns="namespace"><row data1="data1" data2 = "data2" xmlns=""/></rootnode>

Which should do nothing, if this msdn post is correct

I also went into orchestration debugger. Weird thing is, when using sql adapter, the message is still = null, but the varibles are assigned without problem. I also tried adding a send port directly after the receive port to dump the message. Nothing came out

I would appreciate any info/suggestion/solution Do tell me if im missing any info.

Irrelevant Info: As of this post the receive port doesnt even trigger anymore. I dont know why. Rebooting PC. Also I suspect Biztalk gave my bruxism and lead to me requiring 6 teeth fillings

Answer 1

The difference between XML in SQL en WCF-SQL has nothing to do with the MSDN post you are linking to.

In the 2nd XML (WCF-SQL adapter), the row node does not have a namespace. In the 1st XML (SQL adapter), the row node inherits the default namespace "namespace" from its parent: 'root'.

Regarding the Receive Port not triggering anymore: Are you sure your Host Instance(s) are still running?

Answer 2

My solution: I added "xmlns = 'namespace'" as a 'data' in the stored procedure. The adapter recognized it and removed it(since it was the same as parent node), allowing me to use the old schema.

Filler: So I generated a schema using the output from WCF-SQL adapter, however I couldnt replace my old one with it, since the expression shape will not recognize its child elements (var = messageObject.childElement) I created a map to map the new one back to the old one. But that didnt work, because they both shared the same namespace, and biztalk complained during runtime that it couldnt decide which schema to use.