MQSeries.net :: View topic - How do I load varchar(max)/varbinary(max) contents into ESQL

rmp · Posted: Sun Sep 22, 2013 9:07 am Post subject:

I have a T-SQL stored procedure in a SQL Server 2012 that I must call using an Integration Service in IIB v9 and return the output in a SOAP message.

I'm able to connect IIB to SQL Server using an ODBC setting (SQL Server Native Client 11.0).

I'm experiencing trouble with columns in the returned result set with varchar(max) and varbinary(max) types. Nonetheless, I can use without problems varbinary(N) or varchar(N), being N a value between 0 and 8000.

I have an ESQL Compute Node in our flow that calls the stored procedure, but to simplify things in the resolution and explanation of this problem, I have the following simple ESQL code to get an image in a varbinary(max) column:

set Environment.Response.row =
select max( T.Picture )
from Database."<name of our ODBC connection>".dbo.OurTableWithPictures as T;

It works if the Picture column is varbinary( N ) but not with varbinary ( max ). If I create a column with a "cropped" version of the original contents (e.g. a varbinary(8000) column with the first 8000 bytes of the original varchar(max) column), it works. So it's probably nothing about contents, but types.

When using varbinary( max ) the error that occurs in IIB is:

Text:CHARACTER:[Microsoft][SQL Server Native Client 11.0]String data, right truncation

When using stored procedure returning varchar( max ) in an output parameter, we get:

Text:CHARACTER:[Microsoft][SQL Server Native Client 11.0]Invalid precision value

So far, I tried many different ways to get this information:
- in a varbinary(max) column in a result set of a stored procedure in SQL Server.
- in an output parameter of type varbinary(max) of a stored procedure in SQL Server.
- in the varbinary(max) return of a user-defined function (UDF) in SQL Server.
- in a direct query to a varbinary(max) column in a table [the example shown in this text].
- all of the above but converting varbinary contents to base64 using T-SQL, so that we returned varchar(max) instead of varbinary(max).
- also tried returning base64 but using 'text' type, but we get error messages telling that this type is deprecated.

Can anyone provide a working example of ESQL code for IIB v9 that calls a SQL Server T-SQL stored procedure, or UDF, or even a direct SQL query to the database, containing either a varchar(max) or varbinary(max) column in a rowset, in a output parameter or in the output of the UDF?

Or, can you tell us how can we get an image from SQL Server stored in varbinary(max) and place it in the output of an Integration Service.

Note that my stored procedure returns more stuff than the (small) images. Everything is working now as I hacked the T-SQL code to return NULL in the Picture output field instead of the Picture column's contents - but it means the Picture is missing.

I'm saying that because I'd like solutions that work with what I already have: an ESQL calling T-SQL stored procedure and returning the results in a XML defined in the schema. The final result for the image must be a base64 string in the SOAP message (XML).