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
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))
endASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shaun Kline
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.
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),
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),
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)
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')
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.
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.
ASKER
it was solved