Msg 8152, Level 16, State 9

[Marryp]

Any thoughts why this happened?

I have a stored procedure that works well in my development db but not in production copy of db.

I noticed that the problem occurs when the stored procedure does not accept null value.

The procedure goes like this:
spmary_weekly_task '5/29/2003 11:59:00 PM', '', 8529851

 

[JohnDTampaBay]

If I'm reading this correctly, the associated error message reads "String or binary data would be truncated." It is possible that somewhere in the procedure some statement is trying to put more characters into a char or varchar field than the maximum length.

Can you post the code for the stored procedure and perhaps we can find the problem?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Marryp]

Create procedure spjvrpt_fiar_weekly_status (@revision_date smalldatetime, @company int, @program1 varchar(4), @program2 varchar(4), @userid varchar(20))
AS

set nocount on

CREATE TABLE #fiar_weekly_status(
userid int not null,
dahpkp int not null,
institution_name varchar (50) not null,
dajo_program_code varchar (4) not null,
program_name varchar (50) not null,
document_code varchar (60) not null,
revision_number varchar (10) null,
revision_created_date datetime not null,
problem_title varchar (255) null,
estimated_closure_date datetime null,
part_number varchar (40) not null,
part_description varchar (255) null,
document_status_name varchar (10) not null,
revision_status_name varchar (20) not null,
signed1 varchar (10) null,
signed2 varchar (10) null,
ISOD_status varchar (10) null)


if @company = '' select @company = null
if @program1 = '' select @program1 = null
if @program2 = '' select @program2 = null

INSERT INTO #fiar_weekly_status
SELECT @userid as userid,
fiar_revision_dawl.dahpkp,
institution_daif.institution_name,
document_dahf.dajo_program_code,
cd_program_dajo.program_name,
document_dahf.document_code,
document_revision_dahp.revision_number,
document_revision_dahp.revision_created_date,
fiar_revision_dawl.problem_title,
fiar_revision_dawl.estimated_closure_date,
part_number_dakb.part_number,
part_dajf.part_description,
cd_document_status_dajn.document_status_name,
cd_fiar_rev_status_dazc.revision_status_name,

( SELECT min(dajp.dakj_signature_stat_cd)
FROM object_function_daob daob
JOIN signature_block_daia daia ON daob.daobkp = daia.daob_signs_off_object_func
JOIN required_signature_dajp dajp ON daia.daiakp = dajp.daia_within_signature_block
WHERE dajp.optional_signature_flag = 0
AND daob.dahz_function_code = "ISODAPPRV"
AND daob.dalw_object_code = "FIAR"
AND daia.signs_off_object_instance = document_revision_dahp.dahpkp
) as signed1,

( SELECT max(dajp.dakj_signature_stat_cd)
FROM object_function_daob daob
JOIN signature_block_daia daia ON daob.daobkp = daia.daob_signs_off_object_func
JOIN required_signature_dajp dajp ON daia.daiakp = dajp.daia_within_signature_block
WHERE dajp.optional_signature_flag = 0
AND daob.dahz_function_code = "ISODAPPRV"
AND daob.dalw_object_code = "FIAR"
AND daia.signs_off_object_instance = document_revision_dahp.dahpkp
) as signed2,

''

FROM asbuilt_part_nc_dagn (NOLOCK),
vw_user_security_profile (NOLOCK),
document_dahf (NOLOCK),
document_revision_dahp (NOLOCK),
fiar_revision_dawl (NOLOCK),
non_conformance_daja (NOLOCK),
document_dahf AS document_dahf_b (NOLOCK),
cd_fiar_rev_status_dazc (NOLOCK),
institution_daif (NOLOCK),
cd_document_status_dajn (NOLOCK),
cd_program_dajo (NOLOCK),
asbuilt_part_dagm (NOLOCK),
part_number_dakb (NOLOCK),
part_dajf (NOLOCK)
WHERE (document_revision_dahp.dahpkp = fiar_revision_dawl.dahpkp)
AND (fiar_revision_dawl.dahf_non_conformance = non_conformance_daja.dahfkp)
AND (document_dahf.dahfkp = document_revision_dahp.dahf_document)
AND (non_conformance_daja.dahfkp = document_dahf_b.dahfkp)
AND (fiar_revision_dawl.dazc_fiar_rev_status_cd = cd_fiar_rev_status_dazc.dazckp_revision_status_cd)
AND (document_dahf.daif_institution = institution_daif.daifkp)
AND (document_dahf.dajn_document_stat_cd = cd_document_status_dajn.dajnkp_document_stat_cd)
AND (document_dahf.dajo_program_code = cd_program_dajo.dajokp_program_code)
AND (vw_user_security_profile.dajo_program_code = document_dahf.dajo_program_code)
AND (vw_user_security_profile.daif_institution = document_dahf.daif_institution)
AND (asbuilt_part_nc_dagn.dahf_non_conformance = non_conformance_daja.dahfkp)
AND (asbuilt_part_nc_dagn.dagm_asbuilt_part = asbuilt_part_dagm.dagmkp)
AND (part_number_dakb.daql_part_parent = part_dajf.daql_part_parent)
AND (asbuilt_part_dagm.dajf_part = part_dajf.dajfkp)
AND (((document_dahf.dajo_program_code = @program1) Or (@program1 is null))
Or ((document_dahf.dajo_program_code = @program2) Or (@program2 is null)))
AND (cd_fiar_rev_status_dazc.revision_status_name <>'Draft')
AND ((document_dahf.current_status_date Between DateAdd(d,-7,@revision_date) And @revision_date)
OR(cd_document_status_dajn.document_status_name='Open'))
AND ((institution_daif.daifkp = @company) Or (@company is null))
AND (vw_user_security_profile.daid_user= @userid)
AND (vw_user_security_profile.dalw_object_code= 'FIAR')
AND ((vw_user_security_profile.dahz_function_code ='VIEW') Or (vw_user_security_profile.dahz_function_code='VIEWCUST'))
AND (part_number_dakb.primary_part_number_flag=1)
AND (fiar_revision_dawl.latest_revision_flag = 1)


/* Set the valu for ISOD status from depending on the value of columns signed1 and signed2 */
UPDATE #fiar_weekly_status
SET ISOD_status = 'Approved'
WHERE signed1 = 'Signed' AND signed2 = 'Signed'

UPDATE #fiar_weekly_status
SET ISOD_status = 'Draft'
WHERE signed1 = 'Unsign' AND signed2 = 'Signed'
OR signed1 = 'Signed' AND signed2 = 'Unsign'
OR signed1 = 'Unsign' AND signed2 = 'Unsign'

INSERT INTO wrk_fiar_temp01_dehf (
userid,
dahpkp,
institution_name,
dajo_program_code,
program_name,
document_code,
revision_number,
revision_created_date,
problem_title,
estimated_closure_date,
part_number,
part_description,
document_status_name,
revision_status_name,
ISOD_status
)
SELECT userid,
dahpkp,
institution_name,
dajo_program_code,
program_name,
document_code,
revision_number,
revision_created_date,
problem_title,
estimated_closure_date,
part_number,
part_description,
document_status_name,
revision_status_name,
ISOD_status
FROM #fiar_weekly_status

 

[JohnDTampaBay]

Your original post references spmary_weekly_task, but this is the code for spjvrpt_fiar_weekly_status. Also, your original post shows three parameters being passed ('5/29/2003 11:59:00 PM', '', 8529851) and this procedure accepts five parameters with no default values. Is there something I missed?

Anyway, my guess is that one of these char or varchar columns in wrk_fiar_temp01_dehf is smaller than the corresponding column in #fiar_weekly_status:

institution_name
dajo_program_code
program_name
document_code
revision_number
problem_title
part_number
part_description
document_status_name
revision_status_name
signed1
signed2
ISOD_status

It is also possible that the insertion into the temp table is failing for the same reason: the source columns are larger than the destination columns.

How far does the procedure get before it fails? Is the temp table being populated, but not the live table? Is the temp tamp being populated at all? Run the procedure through the Query Analyzer Debugger and step through the code, or place SELECT @@ROWCOUNT after each INSERT and UPDATE statement to see exactly which statement is failing.

Good luck.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Marryp]

The first post was just a dummy.

spjvrpt_fiar_weekly_status '5/29/2003 11:59:00 PM', 0, 'SRMS','' , 8529851

Actually, I notice that it fails if the value is 0 or I put null. The code below will work because I change it to 123

spjvrpt_fiar_weekly_status '5/29/2003 11:59:00 PM', 123, 'SRMS','' , 8529851

It is really strange because on one db it works but not on the other.

Thanks