CLR UDTs bigger than 8000 bytes

Hi there,
Say I wanted to create a "compound" type, purely to pass as a parameter
to a stored procedure, but one of the components was NTEXT or
NVARCHAR(MAX). Say it was something like:
- Author NVARCHAR(256)
- IP address VARCHAR(16)
- Comment NVARCHAR(MAX)
How would I go about doing this? My code works fine if the UDT package
is less than 8000 bytes, but fails if it's greater than that (which is
understandable since I had to put MaxByteSize = 8000 into the UDT
definition). If the package is too big, I get an
IndexOutOfRangeException in
System.Data.SqlClient.TdsParser.TdsExecuteRPC().
Is the 8000-byte limit a "hard" limit that I can't work around? As I
mentioned, this is purely to pass as a parameter to a stored procedure,
it'll never be used as a column in a table.
Or am I misunderstanding something and I don't even need to make this a
real UDT since it won't ever be stored as a column? Is there a way for
me to pass a regular .NET object to a CLR stored procedure?
(This example is very simplistic, I know - I could just pass three
individual parameters instead of one compound one in this case. The
actual situation is more complicated though.)
Many thanks,
GeoffGeoff (opinionatedg@.gmail.com) writes:
> Say I wanted to create a "compound" type, purely to pass as a parameter
> to a stored procedure, but one of the components was NTEXT or
> NVARCHAR(MAX). Say it was something like:
> - Author NVARCHAR(256)
> - IP address VARCHAR(16)
> - Comment NVARCHAR(MAX)
> How would I go about doing this? My code works fine if the UDT package
> is less than 8000 bytes, but fails if it's greater than that (which is
> understandable since I had to put MaxByteSize = 8000 into the UDT
> definition). If the package is too big, I get an
> IndexOutOfRangeException in
> System.Data.SqlClient.TdsParser.TdsExecuteRPC().
> Is the 8000-byte limit a "hard" limit that I can't work around?
That's right.

> Or am I misunderstanding something and I don't even need to make this a
> real UDT since it won't ever be stored as a column? Is there a way for
> me to pass a regular .NET object to a CLR stored procedure?
Check out the current thread "DataRow in a CLR Stored Procedure". There
are some suggestions on binary serialization.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
thanks for that - at least I know I'm not missing something simple at
the UDT/parameter level.
I've checked out the thread "DataRow in a CLR Stored Procedure" as you
suggested - interesting stuff. It would just mean serializing the
values manually before the call, rather than the serialization
happening as part of the call... The objects themselves wouldn't then
need to be UDTs, so there'd be no limit. Cool.
Many thanks,
Geoff