I am using a SQL Send port to call a stored procedure. The parameters
include varchar, smalldatetime, and money data types. The SQL Transport
Schema Generator seems to have no problem generating a schema. It correctly
pulls the parameter names and data types from the database into the wizard,
and it creates a schema.
I first noticed a problem when all the data types in the schema were
xs:string. Even if I change the datatypes that are supposed to be datetime
to xs:datetime and the ones that are supposed to be money to xs:float, they
are passed as nvarchar. I can view the exec command in SQL profiler as it
runs, and every parameter is passed in as varchar. So of course SQL returns
an error to BizTalk that ends up in the event log that says it cannot convert
nvarchar to money datatype (or float datatype, as I later attempted).
I can get it to *work* by changing all the parameters in the stored
procedure to varchar and then doing some casts or converts before performing
the insert or update into the tables. But this just does not feel like the
right way to do it.
Can anyone help me figure out why the SQL Adapter will not pass parameters
other than nvarchar?