Hi

I am using BizTalk 2004 against Sql Server 2000.
I have an orchestration which is calling the Sql adapter (using a
Request-Response send port). The Request consists of an updategram that
simply inserts a row in a sql table.
The updategram works correctly if I do not attempt to set a null value for a
datetime column in the row I am inserting. Unfortunately I get a sql
exception of "Syntax error converting datetime from character string" if I
try to set the datetime column to a null value.
Using Sql Profiler it is immediately apparent why this is happening: a
string is being passed through for the datetime column instead of null for
the DatePaid and VatRate columns.

Here is my updategram:

<?xml version="1.0" ?>
<ns0:SaveDocumentRequest
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:ns0="http://Expenses.BizTalk.Schemas.SaveDocumentUpdategram">
<updg:sync updg:nullvalue="ValueIsNULL">
<updg:before></updg:before>
<updg:after>
<ExpenseItem DocId="24" ReceiptNo="54321" Date="2005-09-12"
Description="test description" Customer="thecustomer" Project="theproject"
CategoryId="2" VatRate="ValueIsNULL" Gross="$20.55" Status="0"
DatePaid="ValueIsNULL" LastUpdatedBy="tak"
updg:id="ExpenseItem1"></ExpenseItem>
</updg:after>
</updg:sync>
</ns0:SaveDocumentRequest>

And here is what shows up in Sql Profiler:

SET XACT_ABORT ON
BEGIN TRAN
DECLARE @eip INT, @r__ int, @e__ int
SET @eip = 0
INSERT ExpenseItem (DocId, ReceiptNo, Date, Description, Customer, Project,
CategoryId, VatRate, Gross, Status, DatePaid, LastUpdatedBy) VALUES (N'24',
N'54321', N'2005-09-12', N'test description', N'thecustomer', N'theproject',
N'2',  N'ValueIsNULL', $20.55, N'0',  N'ValueIsNULL', N'tak');
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
IF (@eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF


This sql exception only happens if I use the Sql adapter. I have a written a
test .Net program that uses the same updategram and calls sql server using a
SqlXmlCommand - this works fine, as shown below in the Sql Profiler snippet:

SET XACT_ABORT ON
BEGIN TRAN
DECLARE @eip INT, @r__ int, @e__ int
SET @eip = 0
INSERT ExpenseItem (DocId, ReceiptNo, Date, Description, Customer, Project,
CategoryId, VatRate, Gross, Status, DatePaid, LastUpdatedBy) VALUES (N'24',
N'54321', N'2005-09-12', N'test description', N'thecustomer', N'theproject',
N'2',  NULL, $20.55, N'0',  NULL, N'tak');
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
IF (@eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF

So this verifies my updategram is correct. So the question is why does the
Sql Adapter for BizTalk not handle this updategram containing a null value??
I  have wasted a couple of days on this and would be grateful for any
suggestions.

Thanks
Tak

[ Post a follow-up to this message ]

I've not managed to resolve this particular problem as I think the problem
lies with the Sql Adapter for BizTalk not handling updategrams terribly well
.

As a workaround I ditched the Sql Adapter, and instead wrote a custom .Net
class to fire the updategram at Sql Server (using SqlXmlCommand). I modified
my orchestration to call this custom class (inside an Expression shape) and
everything worked as it should.

As a side note I wanted to also return the Id of the row I was inserting (as
this was being autogenerated by the database). I modified the updategram to
add the "returnid" attribute and tested it using the Sql Adapter. As I
reluctantly expected the response message came back with no returnid.. just 
a
"success" node. Fortunately using the custom class fixes this issue too.

Looks like more work needs doing on the Sql Adapter before its ready for
real-world use.

Tak

[ Post a follow-up to this message ]

On Fri, 16 Sep 2005 03:44:17 -0700, Tak
<Tak@discussions.microsoft.com> wrote:

>I am using BizTalk 2004 against Sql Server 2000.
>I have an orchestration which is calling the Sql adapter (using a
>Request-Response send port). The Request consists of an updategram that
>simply inserts a row in a sql table.
>The updategram works correctly if I do not attempt to set a null value for 
a
>datetime column in the row I am inserting. Unfortunately I get a sql
>exception of "Syntax error converting datetime from character string" if I
>try to set the datetime column to a null value.
>Using Sql Profiler it is immediately apparent why this is happening: a
>string is being passed through for the datetime column instead of null for
>the DatePaid and VatRate columns.
Hi

In BizTalk 2002, I would just omit the element that is to be null. So
I would just not have the attribute for the field. Does that work?

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]

Hi Jan

This does work for inserts, but would not for updates. In the end i
went down the route of not using the Sql Adapter, as mentioned in an
earlier post (this is not showing up in Google so is posted below):

I've not managed to resolve this particular problem as I think the
problem
lies with the Sql Adapter for BizTalk not handling updategrams terribly
well.

As a workaround I ditched the Sql Adapter, and instead wrote a custom
.Net
class to fire the updategram at Sql Server (using SqlXmlCommand). I
modified
my orchestration to call this custom class (inside an Expression shape)
and
everything worked as it should.

As a side note I wanted to also return the Id of the row I was
inserting (as
this was being autogenerated by the database). I modified the
updategram to
add the "returnid" attribute and tested it using the Sql Adapter. As I
reluctantly expected the response message came back with no returnid..
just a
"success" node. Fortunately using the custom class fixes this issue
too.

Looks like more work needs doing on the Sql Adapter before its ready
for
real-world use.

Tak


> Hi
>
> In BizTalk 2002, I would just omit the element that is to be null. So
> I would just not have the attribute for the field. Does that work?
>
> --
> Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]