I have an orchestration that is retrieving data from one SQL Server instance, doing some processing, and inserting the data into another SQL Server instance. One of the fields being moved is stored in an image column in both the source and destination SQL Server instances.
In the stored procedure for the receive location, I am using the BINARY BASE64 option with FOR XML to get base64 encoded data. When I genererate an adapter from this stored procedure, the generated schema has the data type of this field set to "xs:base64Binary", which seems appropriate. On the destination side, I have a stored procedure for inserting the processed data that contains a parameter of type image. When I generate an adapter from this destination procedure, the data type of the image field is again set to "xs:base64Binary" in the generated schema. I was hoping that this meant that the SQL adapter would decode the base64 data back to binary, but that doesn't seem to be the case. Is there any way to get the base64 data decoded back to binary? I checked the BizTalk help and it recommends using a stored procedure for binary data which is what I'm doing.
I'm open to using an updategram with a mapping schema if that will work and someone can point me to some information on doing that from within BizTalk (where would the mapping schema be saved, etc.).
Currently I am looking for somebody who could help you on it. We will reply here with more information as soon as possible. If you have any more concerns on it, please feel free to post here.
Thanks for your understanding!
Best regards,
Peter Huang Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights.
Did you try to send the updategram with base64 encoded field (xs:base64Binary) back with the SQL adapter, and then meet a type convert error?
If so, this should be a SQLXML issue because the decoding isn't done at the biztalk SQL adapter sending message stage. The following is a sample about inserting image via updategram. It uses dt:type="bin.base64" .
Yes, I get a type conversion error from the SQL Server.
As I said, I'm willing to try an updategram with a mapping schema (like the referenced article) if you can direct me how to do this in BizTalk 2004. How do I get BizTalk to generate an updategram with a reference to a mapping schema? And where is the mapping schema saved? Since I'm using BizTalk and the SQL adapter I don't have any virutal directories configured for SQL Server.
Sean Carpenter
""WenJun Zhang[msft]"" <v-wzh...@online.microsoft.com> wrote in message
> Did you try to send the updategram with base64 encoded field > (xs:base64Binary) back with the SQL adapter, and then meet a type > convert error?
> If so, this should be a SQLXML issue because the decoding isn't done > at the biztalk SQL adapter sending message stage. The following is a > sample about inserting image via updategram. It uses > dt:type="bin.base64" .
I understand how to use an updategram. The problem is with the conversion of the base64 encoded data to a SQL Server image column. When I use an updategram, I get the same type conversion error as I do when using a stored procedure. The articles you reference do not contain any information on using a mapping schema so that I could specify the sql:datatype="image" annotation.
Is converting binary data from base64 encoded not supported in BizTalk 2004?
Sean Carpenter
""WenJun Zhang[msft]"" <v-wzh...@online.microsoft.com> wrote in message
SQLXML is able to accept the bin.base64 data type and convert it back to binary before inserting. So you needn't look for the way to perform the convert within Biztalk.
Best regards,
WenJun Zhang Microsoft Online Partner Support
This posting is provided "AS IS" with no warranties, and confers no rights.
I think you're missing the point. The only references I've seen for SQLXML converting from base64 (including the article you referenced) have to do with using a mapping schema with a virtual directory. I don't know how to use a mapping schema from within BizTalk. Can anyone provide any information on doing that? The fact that it works outside of BizTalk is of no use to me since I need to insert the data by using BizTalk and not by executing a template query with a virtual directory.
Is there any way for BizTalk to pass the base64 data and have it converted back to binary (either by BizTalk, the SQL Adapter, SQLXML, or any other means)? If so, can someone please provide some guidance on doing so.
Sean Carpenter
""WenJun Zhang[msft]"" <v-wzh...@online.microsoft.com> wrote in message
> SQLXML is able to accept the bin.base64 data type and convert it back > to binary before inserting. So you needn't look for the way to > perform the convert within Biztalk.
> Best regards,
> WenJun Zhang > Microsoft Online Partner Support
> This posting is provided "AS IS" with no warranties, and confers no > rights.
|
