• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

varchar(4000) is not working at sql 2005

i am using stored procedure
it has parameter @idList
which is varchar(4000)
to create update statement dynamically with datatype  @sql varchar(8000)
but the problem @sql truncate the statement after 4000  
is varchar(8000) actual length is 4000 not 8000
???
note :
i am using sql server 2000
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER procedure [dbo].[Transaction_UpdateStatusByIdRange]

( @idList varchar(4000) ,@status char(1), @company int , @FK_ConfirmationUserId nvarchar(5))

as

DECLARE @SQL varchar(8000)






print (len(@idlist))
set @SQL=
'update corp.dbo.EccTransactions set status ='+ @status+',FK_ConfirmationUserId ='+ @FK_ConfirmationUserId+' where TransactionID in ('+ @idList+')'

EXEC(@SQL)	
print ('sql')
print (@sql)
print ('sql length ')
print(len(@sql))
print('id list='+ @idList)
print (len(@idlist))
SET @SQL = 
'update corp.dbo.EccTransactions set CompanyConfirmation=0 where TransactionID in ('+ @idList+')'

EXEC(@SQL)	
print ('sql')
print (@sql)
print ('sql length')
print(len(@sql))
end

Open in new window

0
AhmedHindy
Asked:
AhmedHindy
1 Solution
 
Pratima PharandeCommented:
try varchar(MAX instade of 8000

DECLARE @SQL varchar(MAX)
0
 
Shaun KlineLead Software EngineerCommented:
You have mixed data types in your query, including an nvarchar. In SQL Server 2000, this has a maximum size of 4000. It is possible, since you are concatenating values, the final string has a data type of NVARCHAR, and therefore would result in only 4000 characters. Try casting all of your variables to varchar, if you do not explicitly need the nvarchar data type.
0
 
BartVxCommented:
The max length of VARCHAR is 8000, the max length of NVARCHAR is 4000.

I suspect your concatenation results in a NVARCHAR (due to the hardcoded strings) reducing its size to 4000.

Try converting your strings to varchar and check the result:

set @SQL=CONVERT(varchar(500), 'update corp.dbo.EccTransactions set status =', +  ....
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sameer2010Commented:
That's correct. NVarchar concatenated with Varchar would result in NVarchar and the size would limit to 4K. Try this simple test to verify:

To resolve this, please convert NVarchar to Varchar. E.g. cast(@a+@b as varchar)
declare @a varchar;
declare @b nvarchar;
set @a='abc'
set @b='def'
select SQL_VARIANT_PROPERTY(@a+@b,'BaseType')

Open in new window

0
 
Chris MConsulting - Technology ServicesCommented:
If you declare a variable with length of 4000, then the latter value is the upper limit.
Ensure that you declare it as varchar(8000) and do not append any spaces in the variable.

Use rtrim() and ltrim() to trim any unnecessary spaces if you need to.
All the best.
0
 
AhmedHindyAuthor Commented:
it was solved
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now