One of the Jr. DBA at in my Team Member asked me question the other day when he was replacing TEXT field with VARCHAR(MAX) : How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowing 12500 characters.
Traditionally thinking we only create the field as long as we need. SQL Server 2005 does support VARCHAR(MAX) but does not support VARCHAR(12500). If we try to create database field with VARCHAR(12500) it gives following error.
Server: Msg 131, Level 15, State 3, Line 1
The size (12500) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).
The solution we came up was to put constraint on the VARCHAR(MAX). SQL SERVER 2005 supports VARCHAR(MAX) and Constraint.
CREATE TABLE [dbo].[MyTestTable] ( [VarChar12500] VARCHAR(MAX) )
GO
ALTER TABLE [dbo].[MyTestTable]
ADD CONSTRAINT [MaxLengthConstraint]
CHECK (DATALENGTH([VarChar12500]) <= 12500)
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
Hi,
I’m Constructing a Dynamic Query in a Stored Procedure with Variable as
DECLARE @Query VARCHAR(MAX)
But when i try to print the constructed Query (Print @Query)
it shows only around or less than 8000 chars, rest got truncated.
Please provide me some solution.
thanks,
Print statement will display first 8000 characters only
Hi,
I really need some help~
I am trying to use a batch file with fmt file to insert data into a table which I had declared as varchar (max), but somehow I keep encounter some problem as below:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid field size for datatype
My BCP statement:
bcp [Database].dbo.[table] in D:\20110815.ASC -fD:\fedline.fmt -F2 -Uuid -Ppwd -Ssvr -e ERROR.LOG
this is my fmt file.
7.0
1
1 SQLCHAR 0 14000 “\r\n” 1 data_str
I am using SQL Server 2005, when I tried to change the fmt file to version 9.0 which is same as SQL Server 2005, I get the following error:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file
Could you please help me out? Thanks.
Hi
I am using SQL server 2005; i created a store procedure to put query result into XML into String.
declare @returntext varchar(max)
set @ReturnText = CAST ((
select A, B, C, D from tableE for xml path , TYPE) AS VARCHAR(MAX))
Total Length from the result should be 20000 characters; but it only return 8000 characters.
Anything I can improve it and make it work.
Thanks
Great..!!!!! It worked for me… Thanks a lot my friend..
the problem lies with some result output setting of SQL Server Management Studio..i run such queries on 3rd party database client – Toad for Data Analysts, it all worked well and displayed the complete string that exceeded 15000..
hi i am new to sql server 2005 can we restrict the user login for certain table in sql
thanks for the valuable guidance,this article helped me a lot.
Can the ADD CONSTRAINT [MaxLengthConstraint]
CHECK (DATALENGTH([VarChar12500]) <= 12500)
be applied on a SQl Variable declared in a Storedprocedure?.
I am really new to sql – I need to look at a database and determine the number of occurances of names with only 4 characters and then sort them in order.
LEN and DATALENGTH keep telling me that they do not exist?
select [name],count(name) from tablename where len(name)=4 group by [name] order by name asc
how to set compatibility of the database ?