Results 1 to 8 of 8

Thread: [RESOLVED] SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause

  1. #1

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Resolved [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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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!)

  3. #3

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    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.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    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.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width