locked
255 character limit RRS feed

  • Question

  • Hi,

    I’m having a problem with a storedprocedure in SQL Server 2005 Developer Edition: I have declared a varchar(max) variable, but when a try to assign a large string it gets truncated to 255 characters, I get the same result if a declare the variable with varchar(8000), ¿can someone help me fix this?, I don’t need the full 8000 characters, but I need more that just 255.

    Thanks.

    Friday, March 28, 2008 2:20 PM

Answers

  • Thank you very much Jacob for your help, I bitted the bullet and made the change to the storedprocedure to use ROW_NUMBER() for paging and now it works in SQL Server 2005, I guess it had something to do with the temporal table I was using in SQL Server 2000, that’s the good news the bad is that now I have to do the same for all the storedprocedures that use paging.

     

    Friday, March 28, 2008 7:15 PM

All replies

  • Can u post the code you are using? When do u find this problem? Is it when you call the procedure with ADO.NET? If so, did you specify the length of the parameter?

    Friday, March 28, 2008 2:27 PM
  • Hi,

    Jacob, thanks for the promptly response, I get the problem in the storedprocedure itself, I’m debugging it with Visual Studio and that’s how I noticed the line was getting truncated, here’s part of the stored procedure:

    The problem is in @SQLCommand it gets truncated

    ALTER PROCEDURE Incidents_Select_Logic
        @StartRowIndex int = 1,
        @MaximumRows int = 10,
        @Filter         varchar(max) = NULL,
        @OrderBy  varchar(1000) = 'Incidents.IncidentNumber',
    AS
     SET NOCOUNT ON
     
     DECLARE @MaxRow  int 
     DECLARE @SQLCommand varchar(max) 
     
     CREATE TABLE #TempItems
     (
      Pos int IDENTITY,
      Id uniqueidentifier
     ) 

     SET @MaxRow = (@StartRowIndex + @MaximumRows) - 1

     SET ROWCOUNT @MaxRow
     
     SET @SQLCommand = 'INSERT INTO #TempItems (Id) SELECT Incidents.Id FROM IncidentStatusCodes INNER JOIN Incidents ON IncidentStatusCodes.Id = Incidents.IncidentStatusCodeId LEFT OUTER JOIN Users ON Incidents.AssignedAgentContactId = Users.ContactId '
     IF NOT @Filter IS null
      SET @SQLCommand = @SQLCommand + ' WHERE ' + @Filter  
     SET @SQLCommand = @SQLCommand + ' ORDER BY ' + @OrderBy
     EXECUTE(@SQLCommand)

     

    The storedprocedure gets called and returns the result, but since @SQLCommand gets truncated the result is unpredictable, the storedprocedure works well in SQLServer 2000 Developer.

    The stored procedure is used to show a large amount or records using paging, I know this can be done in a more effective way in SQLServer 2005, I’m going to change this to use row_number() but I’m afraid I’m going to get into the same trouble.

    Thanks.

    Friday, March 28, 2008 3:26 PM
  • In SSMS, check the Tools|Options|Query Results|SQL Server|Results to Text/Grid

    In both locations, set the maximum number of characters displayed or retreived to a number that 'makes sense' for your needs.

     

    There may be a similar setting in VS -I just don't know where to tell you to look.

    Friday, March 28, 2008 3:46 PM
  • Thank you very much Jacob for your help, I bitted the bullet and made the change to the storedprocedure to use ROW_NUMBER() for paging and now it works in SQL Server 2005, I guess it had something to do with the temporal table I was using in SQL Server 2000, that’s the good news the bad is that now I have to do the same for all the storedprocedures that use paging.

     

    Friday, March 28, 2008 7:15 PM