INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Defining SqlDbType.Xml SqlParameter needs a size. Why?

Defining SqlDbType.Xml SqlParameter needs a size. Why?

(OP)
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


RE: Defining SqlDbType.Xml SqlParameter needs a size. Why?

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

RE: Defining SqlDbType.Xml SqlParameter needs a size. Why?

(OP)
Thanks Chip!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close