Defining SqlDbType.Xml SqlParameter needs a size. Why?

[blondebier]

Hi Guys,

I'm calling a stored procedure from my VB.NET application and it passes the following SQLParameters.

sdiParameters(0) = New SqlParameter("@RequestXML", RequestXML.InnerXml)
sdiParameters(0).SqlDbType = SqlDbType.Xml
sdiParameters(0).Size = RequestXML.InnerXml.Length
sdiParameters(1) = New SqlParameter("@UserID", UserID)
sdiParameters(1).SqlDbType = SqlDbType.Int
sdiParameters(2) = New SqlParameter("@FailureMessage", System.DBNull.Value)
sdiParameters(2).Direction = ParameterDirection.InputOutput
sdiParameters(2).SqlDbType = SqlDbType.NVarChar
sdiParameters(2).Size = 255
sdiParameters(3) = New SqlParameter("@SessionID", System.DBNull.Value)
sdiParameters(3).Direction = ParameterDirection.InputOutput
sdiParameters(3).SqlDbType = SqlDbType.Int
sdiParameters(4) = New SqlParameter("@ProcessXML", System.DBNull.Value)
sdiParameters(4).Direction = ParameterDirection.InputOutput
sdiParameters(4).SqlDbType = SqlDbType.Xml
sdiParameters(4).Size = 1 ' * * * Why do I need to specify this? * * *
sdiParameters(5) = New SqlParameter("@RETURN_VALUE", System.DBNull.Value)
sdiParameters(5).Direction = ParameterDirection.ReturnValue

sdiParameters(4) is an Output parameter and the XML will be loaded into a XMLDocument after the SP executes.

I get the following error "System.InvalidOperationException: String[4]: the Size property has an invalid size of 0." if I do not specify the length of sdiParameters(4). As it is returned from the SP I do not know what the length will be before the SP executes.

If I set the size to 1, it works and executes successfully.

Any ideas why?

Cheers,

Francis

 

[chiph]

At the lowest level, OLEDB and ADO.NET will be doing memory copies to transfer contents from their buffers into your variables. By passing it a 0, you're telling it that the destination buffer is zero length.

If the column is a fixed-length string ( CHAR(20) ), then you should use the column width as the size parameter. If the column is variable-width ( VARCHAR() ), then pick a reasonable value.

If your columns are Unicode (NCHAR, NVARCHAR, NTEXT), the size is in characters, so no worries about converting to a byte-count.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[blondebier]

Thanks Chip!