-
Jul 10th, 2012, 04:09 AM
#1
Thread Starter
Fanatic Member
[RESOLVED] SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
Hi,
I have a stored procedure that checks to see if the passed through data has changed before actually updating the table this works perfectly until the data exceeds 4000 characters despite the table field and the parameter being nvarchar(max) fields, the issue appears to be with the IsNull check, if I comment out the highlighted line (see the code block below) the SP works okay.
Does anyone know how to make "IsNull" play nicely with nvarchar(max)? Otherwise any suggestions for doing this in a better way?
Code:
CREATE PROCEDURE up_pUpdatePage
(
@New_Page_Id numeric(18,0) OutPut
,@Page_Id numeric(18,0)
,@LU_Page_Id numeric(18,0)
,@Jnl_Code varchar(10)
,@Client_Code varchar(10)
,@Name nvarchar(250)
,@Title nvarchar(250)
,@Body nvarchar(max)
,@Status varchar(20)
,@User_Id numeric(18,0)
)
AS
SET NOCOUNT ON
DECLARE @Result int SET @Result=@@Error
DECLARE @DT datetime SET @DT=GetDate()
DECLARE @DateAdded datetime SET @DateAdded=@DT
DECLARE @CurrentStatus varchar(20) SET @CurrentStatus='A'
BEGIN TRANSACTION
IF EXISTS(SELECT NULL
FROM Pages WITH (UPDLOCK)
WHERE IsNull(Jnl_Code,'') COLLATE Latin1_General_CS_AS=IsNull(@Jnl_Code,'')
AND IsNull(Client_Code,'') COLLATE Latin1_General_CS_AS=IsNull(@Client_Code,'')
AND IsNull(Name,'') COLLATE Latin1_General_CS_AS=IsNull(@Name,'')
AND IsNull(Title,'') COLLATE Latin1_General_CS_AS=IsNull(@Title,'')
AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE IsNull(@Body,'')
AND IsNull(Status,'') COLLATE Latin1_General_CS_AS=IsNull(@Status,'')
AND IsNull(User_Id,-1)=IsNull(@User_Id,-1)
AND IsNull(LU_Page_Id,-1)=IsNull(@LU_Page_Id,-1)
) BEGIN
-- This Page already exists
SET @Result=-1
SET @New_Page_Id=@Page_Id
END ELSE BEGIN
-- Work out whether we can update the existing page or if we must create a new instance
....
Regards Al
-
Jul 10th, 2012, 05:53 AM
#2
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
Try writing it out the long way, which I think is this:
Code:
AND ( ( @Body Is Null AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE '')
OR ( @Body Is Not Null AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE @Body) )
(but please check, my brain isn't working quite right today!)
-
Jul 10th, 2012, 06:12 AM
#3
Thread Starter
Fanatic Member
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
Hi Si,
Thanks for your reply I've tried your suggestion but no luck, I've done a little more testing and it seems that it's not IsNull, I've changed the line to -
Code:
AND Body LIKE @Body
the error/warning "String or binary data would be truncated." still appears.
-
Jul 10th, 2012, 08:14 AM
#4
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
I was going to say, I've never had issues with varchar(max) and isnull... are you *sure* it's the Body field and not one of the others? What's the Body field definition on the Pages table look like?
-tg
-
Jul 10th, 2012, 08:43 AM
#5
Thread Starter
Fanatic Member
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
TechGnome,
Thanks for your reply, I've quadripple checked it and it's a nvarchar(max)
Code:
Body nvarchar(max),
As a temporary workaround I'm ignoring the changed check if the Body is longer that 4000 characters, not ideal but doesn't seem to be much else that I can do, other than maybe writing a UDF to split the text up into chunks then compare those.
-
Jul 10th, 2012, 08:52 AM
#6
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
I notice you use a LIKE on the field, rather than an equals...
It looks like it's the like.... look at this page, all the way at the bottom, in the communuity notes....
http://technet.microsoft.com/en-us/l...9859(v=sql.100)
It talks about that 4000 character boundary causing errors to be thrown.
-tg
-
Jul 10th, 2012, 09:10 AM
#7
Thread Starter
Fanatic Member
Re: SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause
TechGnome,
You might have cracked it; I've changed the "like" to "=" and the error has gone away. With SQL2005 or perhaps it was 2000, I was told to use "LIKE" instead of "=" when comparing large (max) fields.
Thanks for your help.
Cheers Al
When I follow -
I get a page not found error.
Last edited by aconybeare; Jul 10th, 2012 at 09:13 AM.
-
Jul 10th, 2012, 11:17 AM
#8
Re: [RESOLVED] SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) L
sorry, I must not have copied it completely....
just for posterity sake... let's try it again...
http://technet.microsoft.com/en-us/l...9859(v=sql.100)
... oh... it looks like the forum flubbed up the ) on the end of the URL. ... bit.ly to the rescue!
http://bit.ly/NgvPOT
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|