how to spot which column cause the error

Question

how to spot which column cause the error

Frances L

SSCarpal Tunnel

Points: 4727
More actions
December 13, 2006 at 8:18 am

#174053

CREATE     PROCEDURE [dbo].[CreateFile] AS
DECLARE @strInsert     nvarchar(4000), @strDelete nvarchar(4000), @strUpdate nvarchar(4000)
set @strDelete='Delete from InpatientMedStat '
exec(@strDelete)
--inpatient
set @strInsert='insert into InpatientMedStat (PatientAccountNumber, MedicalRecordNumber, AdmissionDate,AdmissionSourceCode, AdmissionTypeCode,DischargeDate, DischargeStatusCode, PatientSex, PatientDOB,PatientZipCode, PatientTypeCode,AttendingPhysicianID,PrincipalDiagnosisCode, PatientEthnicity,FinancialClassCode,PatientName)
        SELECT   distinct rptPXA1.PNO AS PNO, rptPXA1.MRNO AS MRNO, rptPXA1.AdmDate AS AdmDate,rptPXA1.AdmSource, rptPXA1.AdmType, rptPXA2.DischargeDate, rptPXA2.Disposition, rptPXA1.Sex AS Sex, rptPXA1.DOB AS DOB,
      rptPXA1.ZipCode AS ZipCode, rptPXA1.PType, rptPXA2.ResponsibleAttedning AS ResponsibleAttedning, rptPXA1.AdmitDCode1 + rptPXA1.AdmDode2 AS DCode, rptPXA1.Race AS Race , rptPXA1.FClass   , rptPXA1.PName
FROM      rptPXA1 LEFT OUTER JOIN rptPXA3 ON rptPXA1.PNO = rptPXA3.PNO LEFT OUTER JOIN rptPXA2 ON rptPXA1.PNO = rptPXA2.PNO WHERE     (NOT (rptPXA2.ProcedureCode IS NULL)) AND (NOT (DCode IS NULL))'
exec(@strInsert)

GO
I run the above stored procedur. it gave me below error. I do not want test one by one field.
Server: Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Do you know how can spot which column cause the error ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Frances

First off, why are you using dynamic SQL? You can do away with the string variables and the EXEC statements and achieve exactly the same effect. Because your code won't all appear red, it'll make it much easier to read and debug.

To find out which is the offending column, run the following script:

SELECT column_name, data_type

FROM information_schema.columns

WHERE data_type LIKE '%char'

OR data_type LIKE '%text'

AND column_name IN (PatientAccountNumber, MedicalRecordNumber, AdmissionDate,AdmissionSourceCode, AdmissionTypeCode,DischargeDate, DischargeStatusCode, PatientSex, PatientDOB,PatientZipCode, PatientTypeCode,AttendingPhysicianID,PrincipalDiagnosisCode, PatientEthnicity,FinancialClassCode,PatientName)

(You will need to put all those column names in quotation marks, but I haven't got the patience (patients?) to do that here.) The query will tell you which column(s) it could be. You will now have to compare this with the columns you are inserting into. Since they're not all in the same table, the script isn't quite so straightforward to write, so it's probably easier to do them one by one. From your column names, I'd say there are only three or four that should hold character data, if your database is well designed.

Good luck

John

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

I do not what you imply here " why are you using dynamic SQL? "

Since I need to extract the fields from five tables and export the table out as txt file. I set that up as char. I do not know whether there are better way to do that.

Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

No variables, no quotation marks, no red text. Simple:

CREATE PROCEDURE [dbo].[CreateFile] AS

Delete from InpatientMedStat

insert into InpatientMedStat (PatientAccountNumber, MedicalRecordNumber, AdmissionDate,AdmissionSourceCode, AdmissionTypeCode,DischargeDate, DischargeStatusCode, PatientSex, PatientDOB,PatientZipCode, PatientTypeCode,AttendingPhysicianID,PrincipalDiagnosisCode, PatientEthnicity,FinancialClassCode,PatientName)

SELECT distinct rptPXA1.PNO AS PNO, rptPXA1.MRNO AS MRNO, rptPXA1.AdmDate AS AdmDate,rptPXA1.AdmSource, rptPXA1.AdmType, rptPXA2.DischargeDate, rptPXA2.Disposition, rptPXA1.Sex AS Sex, rptPXA1.DOB AS DOB,

rptPXA1.ZipCode AS ZipCode, rptPXA1.PType, rptPXA2.ResponsibleAttedning AS ResponsibleAttedning, rptPXA1.AdmitDCode1 + rptPXA1.AdmDode2 AS DCode, rptPXA1.Race AS Race , rptPXA1.FClass , rptPXA1.PName

FROM rptPXA1 LEFT OUTER JOIN rptPXA3 ON rptPXA1.PNO = rptPXA3.PNO LEFT OUTER JOIN rptPXA2 ON rptPXA1.PNO = rptPXA2.PNO WHERE (NOT (rptPXA2.ProcedureCode IS NULL)) AND (NOT (DCode IS NULL))

GO

John

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

Frances L

SSCarpal Tunnel

Points: 4727

December 13, 2006 at 9:24 am

#677826

Thanks.