|
Home > Archive > BizTalk Server Tools > May 2004 > Biztalk SQL Adapter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Biztalk SQL Adapter
|
|
| Linda Comstock 2004-05-30, 10:47 am |
| I have a SQLServer 2000 database with three tables.
I would like to create one map to insert / update data in all three tables.
Can this be done?
I used the stored procedure wizard on the SQLServer2000. I specified that
all three tables are used for insert and update. The wizard created 6
procedures, not one.
In Biztalk 2004, I tried using the SQL Transport Schema Generation wizard.
I specified the stored procedure and used the generate button to generate a
script. When I tried to go to the next screen, I got the error message:
"Failed to execute SQL statement. Please ensure the supplied syntax is
correct. implicit conversion from datatype nvarchar to money is not
allowed. Use convert function to run this query". I can scroll through the
generated script. I noticed that the parms for the two money fields were
set to " ". I would think that money fields should be set to 0.00. I do
not see from this screen, how to edit the generated script.
Thanks,
Linda Comstock
| |
| Linda Comstock 2004-05-30, 10:47 am |
| An addenda to the message listed below.
I found how to set the values on the SQL Transport Schema Generation Wizard.
I set the two money fields equal to the value 0.00. However, I still got
the implicit conversion error message as described below.
Thanks,
Linda Comstock
"Linda Comstock" <ecc@oh.com> wrote in message
news:ey2dXxAREHA.1048@tk2msftngp13.phx.gbl...
> I have a SQLServer 2000 database with three tables.
> I would like to create one map to insert / update data in all three
tables.
> Can this be done?
>
> I used the stored procedure wizard on the SQLServer2000. I specified that
> all three tables are used for insert and update. The wizard created 6
> procedures, not one.
>
> In Biztalk 2004, I tried using the SQL Transport Schema Generation
wizard.
> I specified the stored procedure and used the generate button to generate
a
> script. When I tried to go to the next screen, I got the error message:
> "Failed to execute SQL statement. Please ensure the supplied syntax is
> correct. implicit conversion from datatype nvarchar to money is not
> allowed. Use convert function to run this query". I can scroll through
the
> generated script. I noticed that the parms for the two money fields were
> set to " ". I would think that money fields should be set to 0.00. I do
> not see from this screen, how to edit the generated script.
>
> Thanks,
> Linda Comstock
>
>
| |
| Matt Milner 2004-05-30, 10:47 am |
| It is possible to update multiple tables. The easiest way is to create
schemas for updating each individual table separately, and then combine
those schemas into one updategram schema. Essentially, within the synch
block of the schema, you'll want three before/after pairs that align with
each table. Having them all in the synch block gives you a transaction to
make sure all updates happen or fail.
When I did this before, I think I might have also needed to use the ID
attribute from the updategram schema to tie the before to the after. To do
this I created a schema and added the node I needed specifying the
updategram namespace and imported this into my SQL Adapter schema.
I haven't quite figured out the money issue, but I think you need to put a
convert statement in your SQL to convert the money to nvarchar or vice
versa.
Matt
"Linda Comstock" <ecc@oh.com> wrote in message
news:%23tBVI4AREHA.252@TK2MSFTNGP10.phx.gbl...
> An addenda to the message listed below.
> I found how to set the values on the SQL Transport Schema Generation
Wizard.
> I set the two money fields equal to the value 0.00. However, I still got
> the implicit conversion error message as described below.
>
> Thanks,
> Linda Comstock
> "Linda Comstock" <ecc@oh.com> wrote in message
> news:ey2dXxAREHA.1048@tk2msftngp13.phx.gbl...
> tables.
that[vbcol=seagreen]
> wizard.
generate[vbcol=seagreen]
> a
> the
were[vbcol=seagreen]
do[vbcol=seagreen]
>
>
|
|
|
|
|