Permissions for Large Variables to Be Sent Via Stored Procedures (SQL Server)

Question

I can't figure out a way to allow more than 4000 bytes to be received at once via a call to a stored procedure. I am storing images in the table that are around 15 - 20 kilobytes each, but upon getting them and displaying them to the page, they are always exactly 3.91 KB in size (or 4000 bytes).

Do stored procedures have a limit on how much data can be sent at once? I double-checked my data, and I am indeed only receiving the first 4000 characters from the varbinary(MAX) field.

Is there a permission setting to allow more than 4k bytes at once?

Answer 1

You need to use different code to access BLOBs in SQL Server. Example here.

Answer 2

There is a data type called "image" in SQL Server 2005. You should use that data type to store large binary object especially images.

Answer 3

You should read Using Large-Value Data Types

The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.

The large-value data types are similar in behavior to their smaller counterparts, varchar, nvarchar and varbinary.

If you can't pass more than 4000 bytes, I'd check in your client application to see if it is limiting or truncating your data. SQL Server can take more than 4000 bytes at one time.

EDIT just found this:

EXECUTE (Transact-SQL)

Using EXECUTE with a Character String

In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.