SQL Send Adapter - Null values - Peter Bradley

Hi,

I have a SQL send adapter that calls a stored procedure that expects 32
parameters, all of which can be null.  When I defined the metadata, I
checked the check box in the SQL Transport Schema Generation Wizard,
Statement Information page against every parameter.  This created the
following script:

exec [usp_bts_writetocimis] @ayr_code=NULL, @ecl_code=NULL,
@elv_code_char=NULL, @esb_code=NULL, @fc_costcentre1=NULL,
@fc_costcentre2=NULL, @fee_profile=NULL, @length=NULL, @mav_cred_char=NULL,
@mav_glhr=NULL, @mav_nper_char=NULL, @mav_wper=NULL, @mod_code=NULL,
@mod_name=NULL, @mot_code=NULL, @other_inst=NULL, @percent_sub1=NULL,
@percent_sub2=NULL, @prog_not_tght=NULL, @psl_code=NULL,
@psl_endw_char=NULL, @sch_cats=NULL, @sch_eccf_char=NULL,
@sch_fte_char=NULL, @smr_actg=NULL, @smr_rslt=NULL, @spr_code=NULL,
@spr_eref=NULL, @subject_1=NULL, @subject_2=NULL, @table_type=NULL,
@transaction_type=NULL

When I run my orchestration, I get the following error:

The adapter "SQL" raised an error message. Details "HRESULT="0x80040e10"
Description="Procedure 'usp_bts_writetocimis' expects parameter '@spr_code',
which was not supplied."

You will see that this parameter is contained in the script, so I was
expecting all to be well.  Certainly when I run the script from SQL
Analyser, substituting the actual values for NULL in all the fields except
spr_code and the other parameter that are null, it runs fine: but not from
my BizTalk orchestration.

Can anyone put me right on this.  I'm obviously doing something wrong.

Cheers


Peter

SQL Send Adapter - Null values - Jan Eliasen

On Thu, 16 Aug 2007 16:14:48 +0100, "Peter Bradley"

If you need to supply "Null" for a parameter, the attribute shouldn't
be in the xm,l instance you send to SQL Server.

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

Private blog: http://blog.eliasen.dk

Private email: jan@eliasen.dk

SQL Send Adapter - Null values - Peter Bradley

Ysgrifennodd Jan Eliasen:

Hi again, Jan, and thanks again for replying.

The situation is that some (actually most) parameters are sometimes
null.  It depends on the type of transaction that is being passed to
BTS.  What happens, therefore, is that the incoming XML does not have
those attributes when the receive stored procedure returns null in those
columns.  This in turn means that the outgoing XML doesn't have them
either, causing the above error to occur.

I experimented just before leaving work with specifying the "required"
option for the spr_code attribute in the incoming XML.  This appears to
have worked and moved the problem to the next null column.  So I'll play
about with that a bit more tomorrow, and post the results.

Cheers


Peter

SQL Send Adapter - Null values - Jan Eliasen

On Thu, 16 Aug 2007 19:56:54 +0100, Peter Bradley

In your SP, do the parameter have a default value? Try setting that.

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

Private blog: http://blog.eliasen.dk

Private email: jan@eliasen.dk

SQL Send Adapter - Null values - Peter Bradley

Ysgrifennodd Jan Eliasen:

If you mean by that, "Is there a default value set for the parameter
within the SP?" then the answer is that I don't immediately know, but I
doubt it very much.  I didn't write the SP, so I'll have to check
tomorrow.  I will do that, and set a default if there isn't one already.
To be honest, I hadn't realised that you could do that in an SP, but
I've now found this:

http://forums.aspfree.com/development-articles-42/using-default-parameter-values-for-t-sql-stored-procedures-40921.html

... so I should be able to sort myself out with that, and give it a go.

If you mean did I set default values in the generated metadata, that's
what I thought I'd done by checking the check box.  Really, the only
values that make sense in this context as default values are NULL or the
empty string (or a space because the SP will treat that as an empty
string).  Looking at the xsd, it seems to me that the default default -
if you see what I mean - is a single space, which should be OK.  However
neither specifying NULL or leaving the default of " " appears to work.

I think it's because the the attribute is not present in the incoming
XML.  I map the incoming XML to the outgoing XML in a transform shape in
the orchestration.  It's just a straightforward map without any
between 'being present but null' and 'not being there', although I can't
really see why.

Anyway, when I make the spr_code attribute in the incoming xsd a
required attribute, it appears (I think, although I'll have to check
this tomorrow) as an empty string in the XML output.  This then appears
to correct the problem for that field.

So, in summary, tomorrow I'll try the default values first - because I
think that's a more elegant solution.  If that doesn't work I'll keep
trying with the "required" usage and see if that works.  Either way I'll
post back to the group: even if it's only for closure.



Peter

SQL Send Adapter - Null values - Peter Bradley

Yes.  That works, Jan.  So that's one for my personal knowledge base.

Thanks for your help.  It's a bit lonely for me here, since I'm the only one
dealing with BizTalk projects and therefore I don't have any colleagues I
can turn to for ideas when problems arise.  I do document the solutions,
though, so I shouldn't ask the same question twice.

;)


Peter

SQL Send Adapter - Null values - Jan Eliasen

On Fri, 17 Aug 2007 09:03:32 +0100, "Peter Bradley"

Excellent - and yes... we all learn something new every single day :-)

Yeah - it is nice to more than one, so you can share ideas and learn
from eachother...

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

Private blog: http://blog.eliasen.dk

Private email: jan@eliasen.dk

SQL Send Adapter - Null values (Now OT) - Peter Bradley

Ysgrifennodd Jan Eliasen:

The first book I ever read on programming, about 25 years ago, listed
the following steps for solving programming problems that were proving
to be intractable:

*  Explain the problem to a colleague.  The colleague doesn't have to
understand what you're talking about.  Just the act of explaining may
cause the solution to appear.

*  Sleep on it

*  If all else fails, go out, get drunk, forget all about it.  You'll be
surprised how quickly the solution presents itself once the hangover's
worn off.

I wish I could remember the title of the book.

Cheers and thanks again for all the help.



Peter

SQL Send Adapter - Null values (Now OT) - Jan Eliasen

On Fri, 17 Aug 2007 20:09:52 +0100, Peter Bradley

[CUT]
So do I :-)

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

Private blog: http://blog.eliasen.dk

Private email: jan@eliasen.dk