BizTalk WCF-SQL adapter - avoid passing DBNull to table-valued parameter

1
votes

I have a BizTalk orchestration which is sending a message that is mapped to a stored proc using a generated schema with the WCF-SQL adapter. One of the parameters to the stored proc is a table value parameter. This is working fine when there are records that BT can translate into the table value. However, if there are a zero records, I still need the proc to be fired off, because there are other parameters supplied that can still be acted upon. But the orchestration crashes with 

System.NotSupportedException: DBNull value for parameter '@DetailRecords' is not supported. Table-valued parameters cannot be DBNull.

Apparently you can get around this error and execute the stored proc without supplying that parameter, and SQL Server will generate an empty table automatically.

Is there any way to get BizTalk to fire off a stored proc that contains a table value parameter, without trying to pass the parameter as DBNull?

1
sql-serverbiztalk
Can you not create an empty TVP yourself? – Damien_The_Unbeliever
Well I'm not sure. Right now I have a BizTalk map that translates my internal records into the WCF-SQL records. So if there are zero records for the mapping to work with, it seems like BizTalk is trying to pass DBNull. If there is a way to modify my mapping to avoid the DBNull, I'm not sure how to do it. That's my question really. – RationalGeek

1 Answers

2
votes

First, connect a Record Count functoid to the repeating element on your source schema. Take the output of that and connect it to a Greater Than functoid to check that the row count is greater than zero. Finally, hook the output side of the Record Count functoid to the table valued parameter on the destination side.Example