Character limit for variables inside a stocked procedure

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)

I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.

I am looking for a "How to" to put this limit to 8000.

Thank you!

Try Varchar(MAX) . It should help you.|||

Hi , see this link

http://www.sqlmag.com/Articles/ArticleID/26654/pg/2/2.html

|||

How you insert data to your field? Are you use stored procedure or any type of parameter? check the size of your parameter if it is not limited to 1000 chars, I never had problems with varchar(8000) like you so check the way how you insert value to your cell.

Thanks

|||

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters. And I need to fix that and cannot seem to find were to do so. 1000 character is fine for quite simple task, but we had some stocked procedure that would have required over 10K characters in order to do what we wanted to do.

If you have any idea on how to change the limitation on the number of character a variable within a stored procedure can hold, I am looking for it since it is quite limitating.

|||

Veritek:

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

I believe you are mistaken.

Try this from Query Analyzer:

DECLARE @.test varchar(2000)
SELECT @.test = REPLICATE('1',1000) + REPLICATE('2',700)
PRINT LEN(@.test)
PRINT @.test

You will see that the length returned is 1700. And that the string printed contains both 1's and 2's.

Your problem lies elsewhere. Something else is truncating your data at 1000 characters.

|||Affirmative, length is indeed 1700... But then I do not know where I could look ...|||

Veritek:

Affirmative, length is indeed 1700... But then I do not know where I could look ...

Well, either do we since we haven't seen any code...

jpazgier has suggested that you review your parameters to make sure you are not truncating data before it gets to your stored procedure.

|||

Well, still unresolved, and wont be anytime soon now since I would seem to have a new problem with the server.

Since the stocked procedure is receiving data from an aspx/vb set of files. Even when the .vb is of size 0.

Reinstalling the softwares seem in order now...

Thank you tho for the help!

|||

Well, we actually had to disable the SP causing the problem since during the weekend it simply stoped working and kept returning an error which we fail to see where it comes from.

I think we need to upgrade our software :p

|||

Veritek:

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters.

How are you determining that @.SQL will not hold more than 1000 characters? Again, you've really not shown us any of your code so it's difficult for us to help. I strongly doubt that reinstalling software is the answer.