8 Replies Latest reply on May 24, 2006 2:18 PM by paross1

    Process works fine... how to track which column is causing error..

    AjasHadi
      hi, i have a big sp which does lets say tasks A, B, C, D... After every task i.e A, B, C, D I have an update or insert statement. The update & insert both work fine, except that I have coldfusion catch code for sp execution and it returns String or binary data would be truncated error. Normally this means inserting something into a column whose lenght is less than whats being inserted. I understand this , but how can i determine which column is causing problem since after tasks A,B,C,D the insert or update i have is working fine. It would be have easy if i couldnt have seen inserted or updated records, but in this case the functionality is working fine except that coldfusin gives an error which i want to avoid.

      Thanks in advance.
        • 1. Re: Process works fine... how to track which column is causing error..
          MikerRoo Level 1
          This is not a coldfusion problem.

          The SP needs to catch its own errors at each step A, B, etc. and warn if data is going to truncated.

          It should also be obvious from looking at the table and the SP design which columns are at risk.
          • 2. Re: Process works fine... how to track which column is causing error..
            AjasHadi Level 1
            quote:

            It should also be obvious from looking at the table and the SP design which columns are at risk.


            I agree but as I said, the data inserted and updated doesnt show any truncation. Secondly, if there is an error in sp, the insert and update shouldnt go thru right, but in this case I can see the insert or update which is making things difficult.

            I have to find exception handling in sql server sp's..I havent tried it before... Plus I am gonna print the sp and match variables column by column once again to see if theres any lenght issues... I remember having checked this before, for ex variable is defined as x varchar(20) and the column being populated is x' varchar(50) which i think is fine since the var is not crossing 50 length which causes the truncation problem... do you think this might have any role to play in this error?

            thanks in advance..
            • 3. Re: Process works fine... how to track which column is causing error..
              MikerRoo Level 1
              SQL server will generate this warning even if actual data truncation did not occur.
              Although I would not be so sure that some of your data did not get truncated.

              Rewrite the SP to use @@ERROR checking at all key steps. Consider adding Transaction processing too.

              Having an SP varchar (20) stuff a varchar (50) column would not cause this error but it is poor practice.

              You must check: (1) the CF to SP data types and sizes, (2) the SP to table types and sizes, and (3) the total maximum possible row size.

              Obviously, CF sending 25 chars to an SP varchar (20) variable can trigger the warning.
              So can having a varchar (50) variable stuffed into a varchar (49, or less) column.

              You also get this when the sum of all insert statement variables is greater than the max row size (8096) or if the maximum possible sum of the table's column sizes exceeds 8096.

              Also, You can get this when stuffing nvarchar into varchar or bigint into int, etc..

              If you send a number or date as a string and it gets automatically stuffed into a non-varchar variable, this warning could get triggered. So keep the types consistent and use the CFSQLType attribute well.
              • 4. Re: Process works fine... how to track which column is causing error..
                AjasHadi Level 1
                quote:

                Originally posted by: MikerRoo
                Also, You can get this when stuffing nvarchar into varchar or bigint into int, etc..

                If you send a number or date as a string and it gets automatically stuffed into a non-varchar variable, this warning could get triggered. So keep the types consistent and use the CFSQLType attribute well.


                Ok, its a date thing which is causing problems.
                I have a column called startdate which is type datetime field in table. In sp i have startdate as varchar(20) and the value startdate has in sp is 2006-05-22 00:00:00.000 which is 23 chars. So heres what I did
                a)made the sp startdate as varchar(50) but still didnt work
                b)made the sp startdate as datetime but still didnt work
                c)I tried this SET @start_date = CAST(convert(varchar(10),GetDate(),101) as datetime) still gives probs
                d) tried SET @start_date = CAST(convert(varchar(10),GetDate(),101) as smalldatetime) but didnt work...

                I am not able to solve this mess... The reason Iam sure about the date issue is that if i remove that column from insert statement, everything works fine.. the moment i add start_date to insert statemetn, i get that error.

                do you think I should have start_date declared as smalldatetime in SP ??? the reason i had to stick to this bad method of varchar datatype is because client was sending all kinds of junk data and i could only proceed further with varchar datatype, & other datatype like int,date etc would have caused serious troubles....

                Thanks for ur time and advise...
                • 5. Re: Process works fine... how to track which column is causing error..
                  paross1 Level 2
                  I'm confused. Why are you CASTing AND CONVERTing?????? GetDate() itself returns a datetime object, so

                  SET @start_date = CAST(convert(varchar(10),GetDate(),101) as datetime) should just be SET @start_date = GetDate()

                  Also, if you did this SET @start_date = CAST('2006-05-22 00:00:00.000' AS datetime) you would get a valid datetime in @start_date.

                  Phil
                  • 6. Re: Process works fine... how to track which column is causing error..
                    MikerRoo Level 1
                    quote:

                    Originally posted by: AjasHadi
                    do you think I should have start_date declared as smalldatetime in SP ??? the reason i had to stick to this bad method of varchar datatype is because client was sending all kinds of junk data and i could only proceed further with varchar datatype, & other datatype like int,date etc would have caused serious troubles....



                    Yes, if the database column is smalldatetime then then @start_date should be smalldatetime.
                    However, the SP's input variable (call it say, "RawStartDate") can still be varchar for the reasons you stated. Just be sure to return the appropriate error and/or insert a valid smalldatetime always.


                    • 7. Process works fine... how to track which column is causing error..
                      AjasHadi Level 1
                      quote:

                      Originally posted by: MikerRoo
                      Yes, if the database column is smalldatetime then then @start_date should be smalldatetime.
                      However, the SP's input variable (call it say, "RawStartDate") can still be varchar for the reasons you stated. Just be sure to return the appropriate error and/or insert a valid smalldatetime always.


                      Sorry for confusion... GetDate was just an example , it could another variable @end_date which is varchar(20).
                      so we could have SET @start_date = CAST(convert(varchar(20),@enddate,101) as datetime)

                      Now coming back to main problem.. I am not calling the SP from coldfusion. I am directly running it thru Query Analyser and here is what iam getting for a date value passed as '2006-05-24 00:00:00.000'
                      Server: Msg 295, Level 16, State 3, Procedure WS_AUTO_INTAKE, Line 263
                      Syntax error converting character string to smalldatetime data type.

                      here is the query starting at line 263
                      INSERT INTO intake_seq (intake_id, intake_seq, absence_type, start_date, start_time, end_date, end_time)
                      VALUES (@intake_id, 1, @absence_type, cast(@start_date as smalldatetime), DATEADD ( hh , 8, cast(@start_date as smalldatetime)), cast(@end_date as smalldatetime), DATEADD ( hh , 16, cast(@start_date as smalldatetime)) )

                      You would say, ok try just datetime instead of smalldatetime...tried that also .. it gives this error
                      Server: Msg 241, Level 16, State 1, Procedure WS_AUTO_INTAKE, Line 263
                      Syntax error converting datetime from character string.

                      Its all got to do with this value '2006-05-24 00:00:00.000' which is passed as one of the parameters to sp call... I tried with this and gives error... when i removed the last 3 zeroes with . , it works fine ...example '2006-05-24 00:00:00'


                      What is wrong here? Iam I missing something????

                      by the way , this SP is working fine for 99% of time... its just that on very few occassions iam seeing the error... thats the reason i persisted with SP INPUT VARIABLES declared as VARCHAR rather than int or datetime, ....

                      any ideas????

                      I know I can have convert(varchar(20), @start_date,101) to get the format dd/mm/yy but then I have to cast it to datetime which will again make it in format dd/mm/yy hh:mi:ss... any other way to just keep it dd:mm:yy ???
                      • 8. Re: Process works fine... how to track which column is causing error..
                        paross1 Level 2
                        I guess that you could check to see if the @start_date contains a '.' and only use the date value up to the .000 if it does. Gets pretty convoluted though!

                        CASE
                        WHEN CHARINDEX('.',@start_date) = 0 THEN CAST(@start_date AS smalldatetime)
                        ELSE CAST(LEFT(@start_date, CHARINDEX('.',@start_date)-1) AS smalldatetime)
                        END

                        Phil