October 3, 2007 at 12:44 pm
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
October 3, 2007 at 1:10 pm
October 3, 2007 at 1:16 pm
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 @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
Thanks for looking at this.
Mattie
October 3, 2007 at 1:25 pm
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
October 3, 2007 at 1:43 pm
October 4, 2007 at 6:39 am
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 @PersonnelType CHAR(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.
October 4, 2007 at 9:03 am
October 4, 2007 at 9:09 am
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
October 4, 2007 at 9:17 am
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
October 4, 2007 at 9:25 am
October 4, 2007 at 9:29 am
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
October 4, 2007 at 9:33 am
October 4, 2007 at 9:36 am
Yup, just output variables. No truncation whatsoever when returned as part of a recordset.
Mattie
October 4, 2007 at 9:36 am
October 4, 2007 at 9:43 am
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