unlimited size for varbinary Sql Server 2005 parameter in C#

[=?Utf-8?B?ZHZhcm1h?=]

Hi

I am executing a Sql Server SP from my C# app
One of the parameters is of type varbinary. Since the content length of this
parameter is variable I cannot set it to a pre-defined length. What do I set
the parameter size to ?
I can set it to -1 but does -1 means variable upto maximum size of Varbinary
type ?

Regards
Dilip

[Marc Gravell]

Random thought; you could set it to the size of the (runtime) data you
are adding? or alternaviely int.MaxValue?

If the data is huge, and you don't want to allocate a large byte[] in
your C#, then you may wish to chunk the transfer. For SELECT this is
easy enough using one of the DataReader overloads, however, for saves
you need (unless I have missed this option) to do it manually:

http://groups.google.com/group/micro...4e7e3782e59a93

Marc

[=?UTF-8?B?QXJuZSBWYWpow7hq?=]

dvarma wrote:

I am executing a Sql Server SP from my C# app
One of the parameters is of type varbinary. Since the content length of this
parameter is variable I cannot set it to a pre-defined length. What do I set
the parameter size to ?
I can set it to -1 but does -1 means variable upto maximum size of Varbinary
type ?

2000 : 8000
2005 : int.MaxValues

was obvious suggestions.

Arne

[Marc Gravell]

Actually, I believe that varbinary maps most closely to "image", so
was never subject to the 8000 page limit.

And (for the OP) another solution: simply don't set one! Not sure it
would be used for this data-type anyhows.

Marc

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Marc Gravell wrote:

Actually, I believe that varbinary maps most closely to "image", so
was never subject to the 8000 page limit.

2000 BOL says:

varbinary [ ( n ) ]

Variable-length binary data of n bytes. n must be a value from 1 through
8,000.
2005 BOL says:

varbinary [ ( n | max) ]
Variable-length binary data. n can be a value from 1 through 8,000. max
indicates that the maximum storage size is 2^31-1 bytes.

So if the documentation can be trusted it was subject to the 8000 bytes
limit.

Arne

[Marc Gravell]

You are quite correct. I must have done a few too many translations
when reading it! So yes : if it is varbinary then you'd need to follow
the varbinary rules for that environment. I read it as "image" (hence
my comment), which can be added to SqlParamater using SqlDbType.Image,
but is a pain to do through IDbDataParameter and DbType.

Marc