Truncation of Output Variable

  • I have a stored procedure that returns a number of output variables. It's shown below in its entirety, and I apologize for the formatting, or lack thereof.

    The problem is that the stored procedure is truncating the results of @PersonnelTypeDesc. What it should return is

    Retired Supreme Court Associate Justice, but here's what I get when I run the stored procedure through QA:

    Stored Procedure: AOC_Applications.dbo.stp_JudicialServicesRelatedGet

    Return Code = 0

    Output Parameter(s):

    @PayPeriodCheckDate =

    @CompletedStatus = 40

    @JudicialServicesDataWhenAuthorizedReturn = 0

    @AuthorizedSSN =

    @AuthorizedCourt =

    @AuthorizedServicePeriod =

    @AuthorizedServiceReason =

    @AuthorizedServiceType =

    @AuthorizedServiceDate =

    @AuthorizedSSNUIDHighest =

    @AuthorizedSSNUIDLowest =

    @AuthorizedSSNName =

    @AuthorizedCourtName =

    @AuthorizedServicePeriodDesc =

    @AuthorizedServiceReasonDesc =

    @AuthorizedServiceTypeDesc =

    @PersonnelTypeDesc = Retired Supreme Court Associat

    It truncates after the 30th position, even though the field length is varchar(50).

    I thought I could fool it by setting it to an intermediate variable (@Temp), but no such luck. And when I tried to debug it by putting the following statement in

    SELECT@Temp AS TempValue, @PersonnelTypeDesc AS PermValue

    Both values were returned as the complete string, Retired Supreme Court Associate Justice.

    Please feel free to tell me this is something obvious, or stupid. I'd rather feel embarrassed than baffled.

    Thanks,

    Mattie

    ALTER PROCEDURE dbo.stp_JudicialServicesRelatedGet

    @JSUID INT,

    @PayPeriodCheckDate datetime output,

    @CompletedStatus char(2) output,

    @JudicialServicesDataWhenAuthorizedReturn integer output,

    @AuthorizedSSN char(9) output,

    @AuthorizedCourt char(4) output,

    @AuthorizedServicePeriod char(3) output,

    @AuthorizedServiceReason char(2) output,

    @AuthorizedServiceType char(1) output,

    @AuthorizedServiceDate datetime output,

    @AuthorizedSSNUIDHighest int output,

    @AuthorizedSSNUIDLowest int output,

    @AuthorizedSSNName varchar(40) output,

    @AuthorizedCourtName varchar(35) output,

    @AuthorizedServicePeriodDesc varchar(35) output,

    @AuthorizedServiceReasonDesc varchar(35) output,

    @AuthorizedServiceTypeDesc varchar(35) OUTPUT,

    @PersonnelTypeDesc varchar(50)OUTPUTAS

    SET NOCOUNT ON

    DECLARE @AsOfDate varchar(20)

    DECLARE @CurrPeriodFY char(04)

    DECLARE @CurrPeriodPP char(02)

    DECLARE @CurrPeriodStart datetime

    DECLARE @CurrPeriodEnd datetime

    DECLARE @CurrPeriodPaid datetime

    DECLARE @CurrPeriodPaidDay varchar(09)

    DECLARE @CurrPeriodSubDate datetime

    DECLARE @CurrPeriodSubDay varchar(09)

    DECLARE @PrevPeriodFY char(04)

    DECLARE @PrevPeriodPP char(02)

    DECLARE @PrevPeriodStart datetime

    DECLARE @NextPeriodFY char(04)

    DECLARE @NextPeriodPP char(02)

    DECLARE @NextPeriodStart datetime

    DECLARE @AbsNextSubPeriodFY char(04)

    DECLARE @AbsNextSubPeriodPP char(02)

    DECLARE @AbsNextSubPeriodStart datetime

    DECLARE @RelNextSubPeriodFY char(04)

    DECLARE @RelNextSubPeriodPP char(02)

    DECLARE @RelNextSubPeriodStart datetime

    DECLARE @ReturnError INT

    DECLARE @HoldError INT

    DECLARE @FY CHAR(4)

    DECLARE @PayPeriod CHAR(2)

    DECLARE @ServiceReason CHAR(2)

    DECLARE @PersonnelTypeCHAR(2)

    DECLARE@TempVARCHAR(50)

    SET @ReturnError = 0

    SELECT @FY= FY,

    @PayPeriod = PayPeriod,

    @ServiceReason = ServiceReason,

    @PersonnelType = PersonnelType

    FROM JudicialServices

    WHERE UID= @JSUID

    SET @HoldError = @@Error

    IF @ReturnError = 0

    SET @ReturnError = @HoldError

    IF @FY IS NOT NULL

    AND @PayPeriod IS NOT NULL

    BEGIN

    Exec stp_PayPeriodDates@AsOfDate, @FY, @PayPeriod,

    @CurrPeriodFY output, @CurrPeriodPP output, @CurrPeriodStart output, @CurrPeriodEnd output,

    @CurrPeriodPaid output, @CurrPeriodPaidDay output, @CurrPeriodSubDate output, @CurrPeriodSubDay output,

    @PrevPeriodFY output, @PrevPeriodPP output, @PrevPeriodStart output,@NextPeriodFY output,

    @NextPeriodPP output, @NextPeriodStart output, @AbsNextSubPeriodFY output, @AbsNextSubPeriodPP output,

    @AbsNextSubPeriodStart output, @RelNextSubPeriodFY output, @RelNextSubPeriodPP output, @RelNextSubPeriodStart output

    SET @HoldError = @@Error

    IF@ReturnError = 0

    BEGIN

    SET @ReturnError = @HoldError

    END

    SET @PayPeriodCheckDate = @CurrPeriodPaid

    END

    SELECT @CompletedStatus = CompletedStatus

    FROM JudicialServicesReasons

    WHERE ServiceReason = @ServiceReason

    SET @HoldError = @@Error

    IF@ReturnError = 0

    SET @ReturnError = @HoldError

    SELECT @Temp = ISNULL(Description, '')

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    SET @HoldError = @@Error

    IF@ReturnError = 0

    SET @ReturnError = @HoldError

    SET@PersonnelTypeDesc = @Temp

    SELECT@Temp AS TempValue, @PersonnelTypeDesc AS PermValue

    exec stp_JudicialServicesDataWhenAuthorized@JSUID, @JudicialServicesDataWhenAuthorizedReturn output,

    @AuthorizedSSN output, @AuthorizedCourt output, @AuthorizedServicePeriod output,

    @AuthorizedServiceReason output, @AuthorizedServiceType output,@AuthorizedServiceDate output,

    @AuthorizedSSNUIDHighest output, @AuthorizedSSNUIDLowest output, @AuthorizedSSNName output, @AuthorizedCourtName output,

    @AuthorizedServicePeriodDesc output, @AuthorizedServiceReasonDesc output,

    @AuthorizedServiceTypeDesc output

    SET@HoldError = @@Error

    IF @ReturnError = 0

    SET @ReturnError = @HoldError

    RETURN @ReturnError

    Thanks,

    Mattie

  • What is the datatype of JudicialServicesPersonnelTypes.Description?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • John,

    Thanks for responding. It's varchar(50), just like the output variable. I've tried changing both the column and the output variable to char(50), with no luck. I posted the entire SP because I thought it might be all the other variables, but even this produces the truncation.

    ALTER PROCEDURE dbo.stp_TestReturn

    @JSUID INT,

    @PersonnelTypeDesc char(50)OUTPUT

    AS

    SET NOCOUNT ON

    DECLARE @ReturnError INT

    DECLARE @HoldError INT

    DECLARE @PersonnelTypeCHAR(2)

    SET @ReturnError = 0

    SELECT @PersonnelType = PersonnelType

    FROM JudicialServices

    WHERE UID= @JSUID

    SELECT @PersonnelTypeDesc = Description

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    SELECT @PersonnelTypeDesc

    RETURN @ReturnError

    Thanks for looking at this.

    Mattie

  • There's gotta be something goofy going on here. Can you post the table DDL? This works just fine for me here on a test DB:

    CREATE TABLE JudicialServicesPersonnelTypes (Description varchar(50))

    INSERT INTO JudicialServicesPersonnelTypes

    SELECT 'Retired Supreme Court Associate Justice'

    CREATE PROCEDURE dbo.stp_TestReturn

    @JSUID INT,

    @PersonnelTypeDesc char(50)OUTPUT

    AS

    SET NOCOUNT ON

    DECLARE @ReturnError INT

    DECLARE @HoldError INT

    DECLARE @PersonnelType CHAR(2)

    SET @ReturnError = 0

    --

    -- SELECT @PersonnelType = PersonnelType

    -- FROM JudicialServices

    -- WHERE UID= @JSUID

    SELECT @PersonnelTypeDesc = Description

    FROM JudicialServicesPersonnelTypes

    --WHERE PersonnelType = @PersonnelType

    SELECT @PersonnelTypeDesc

    RETURN @ReturnError

    DECLARE @UID int, @PersonnelTypeDesc varchar(50)

    exec stp_TestReturn NULL, @PersonnelTypeDesc output

    SELECT @PersonnelTypeDesc

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • In QA under Tools-ptions-results, what is your max characters per column set at?

    If you run

    select 'Retired Supreme Court Associate Justice'

    what do you get?


  • Thanks to everyone for taking a look at this. Here's the DDL for the table

    CREATE TABLE [dbo].[JudicialServicesPersonnelTypes] (

    [PersonnelType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [InactiveInd] [bit] NOT NULL ,

    [CreatedBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [CreatedOn] [datetime] NOT NULL ,

    [ModifiedBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [ModifiedOn] [datetime] NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[JudicialServicesPersonnelTypes] WITH NOCHECK ADD

    CONSTRAINT [PK_JudicialServicesPersonnelTypes] PRIMARY KEY CLUSTERED

    (

    [PersonnelType]

    ) WITH FILLFACTOR = 90 ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[JudicialServicesPersonnelTypes] ADD

    CONSTRAINT [DF_JudicialServicesPersonnelTypes_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]

    GO

    setuser

    GO

    EXEC sp_bindefault N'[dbo].[Empty String]', N'[JudicialServicesPersonnelTypes].[Description]'

    GO

    EXEC sp_bindefault N'[dbo].[Empty Number]', N'[JudicialServicesPersonnelTypes].[InactiveInd]'

    GO

    setuser

    GO

    Here's what I'm working with to test.

    ALTER PROCEDURE dbo.stp_TestReturn

    @JSUID INT,

    @PersonnelTypeDesc char(50)OUTPUT

    AS

    SET NOCOUNT ON

    DECLARE @ReturnError INT

    DECLARE @HoldError INT

    DECLARE @PersonnelTypeCHAR(2)

    SET @ReturnError = 0

    SET @JSUID = 43556

    SELECT @PersonnelType = PersonnelType

    FROM JudicialServices

    WHERE UID= @JSUID

    SELECT @PersonnelTypeDesc = ISNULL(Description, '')

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    SELECT ISNULL(Description, '') AS Description

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    RETURN @ReturnError

    The results from the

    SELECT @PersonnelTypeDesc = ISNULL(Description, '')statement return

    Stored Procedure: AOC_Applications.dbo.stp_TestReturn

    Return Code = 0

    Output Parameter(s):

    @PersonnelTypeDesc = Retired Supreme Court Associat

    The results from the

    SELECT ISNULL(Description, '') AS Descriptionstatement return

    Retired Supreme Court Associate Justice

    The result of

    SELECT'Retired Supreme Court Associate Justice' AS Textreturns

    Retired Supreme Court Associate Justice

    I may try dropping the table and recreating it, but I'd welcome some other suggestions.

    Thanks,

    Mattie

    My maximum characters per column is 5000. But I don't think this is a QA issue, because running the stored procedure through my ASP application produces the same truncation.

  • Just for S&G what happends if you declare the output as char(100) instead of char(50)?


  • I've created your tables/defaults/sp's here on my testDB with some sample data and I cannot reproduce what you are seeing. You say you get this whether you run your SP from your app or QA. What happens when you run the SP logic outside of the SP in QA? What do you get when you run this in QA?

    DECLARE @PersonnelTypeDesc varchar(50),

    @PersonnelType CHAR(2),

    @JSUID int

    SET @JSUID = 43556

    SELECT @PersonnelType = PersonnelType

    FROM JudicialServices

    WHERE UID= @JSUID

    SELECT @PersonnelTypeDesc = ISNULL(Description, '')

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    SELECT ISNULL(Description, '') AS Description

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    SELECT @PersonnelTypeDesc

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Just so you know, there is no grinning going on here:crying:

    It makes no difference. I've tried char(50), varchar(50), nvarchar(50), char(100), varchar(100), and nvarchar(100). Not only did deleting and recreating the table make no difference, it's not limited to that table. It's not limited to that database. It's not limited to my machine. It's not limited to databases on that server. It's not limited to databases I've designed. When I run any SP that attempts to bring back a string in excess of 30 characters, only the first 30 characters are returned.

    I tried googling the issue (no, there aren't a billion posts in the world that combine SQL Server and truncate, but it's close), and the closest I could find were two issues. Once had something to do with output parameters being truncated at 4000 characters if you were using a JDBC driver, and another that caused a truncation message to appear because some buffer wasn't big enough. I can't believe there's a setting out there that someone from this site hasn't heard of.

    Thanks so much for responding. Tell your friends!:rolleyes:

    Mattie

  • ok so what version of SQL (7 or 2000) what is the OS and waht are the SPs for both?


  • Hi John,

    I ran your code, and it does just what it's supposed to, return Retired Supreme Court Associate Justice. Returning the data as a selected value in a recordset returns the whole string; returning it as an output parameter truncates it at thirty characters. And as you can see from my ranting above, it's a systemwide issue. I'm going to send the stored procedure to someone with similar data and see what they get. The JDBC driver issue has me thinking it's specific to something we've got installed (or not).

    I'm running this on a Windows 2000 machine with SQL Server 2000 version 8.00.2040, SP4, Personal Edition.

    I really appreciate the responses. Misery may not have any company, but it appreciates an audience.

    Mattie

  • wasn't this part of the SP

    SELECT ISNULL(Description, '') AS Description

    FROM JudicialServicesPersonnelTypes

    WHERE PersonnelType = @PersonnelType

    and returned the whole value? Is this just happenning with output variables?


  • Yup, just output variables. No truncation whatsoever when returned as part of a recordset.

    Mattie

  • This is surely odd. This may be a good time to get big brother Microsoft involved.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • John and Mr. Polecat,

    Thanks so much for your efforts on this. I'll definitely post whatever else I find out about this. Do either of you have a web site I can report this to Microsoft on, or a phone number that's a good starting point?

    Thanks,

    Mattie

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply