Post reply

Datatype coversion error

  • December 4, 2019 at 4:27 am

    #3704117

    Hi,

    In my application I am fetching data from a View which internally having multiple select qurries combined using Union.  It's near about 500 lines of query.

    When i fire Select on view it takes some time and then throws Datatype coversion error. To find the source of the error I execute available queries on View one by one. After that only I come to know which select statement giving me the error.

    Could you please tell me best feasible solution to handle such a type of issue?

    Thanks in advance.

  • December 4, 2019 at 6:17 am

    #3704144

    Tracing the error :

    1. Checkout this Microsoft Fix if you have SQL Server 2016 and compatibility level set to 130 .
    2. You can take help of trace flag. In this case you will have to enable Trace Flag 460.
    3. Checkout a simple and very useful article written by BrentOzar.
    4. Always implement the error handling using TRY..CATCH block and log/return the error message, line number, source stored procedure etc. along with the error message. This will let you know exactly at which line of which object you have encountered the error. Please note, you cannot use the TRY...CATCH block in VIEW and TABLE VALUED FUNCTIONS.

    Recently I wrote an article on How to Implement the Batch Processing using WHILE LOOP. I've also depicted the use of TRY..CATCH which you can refer as a sample/example

     

    Avoiding the error :

    1. Always create the tables (Table Variables, Temp Tables, Static Tables etc.) and Variables of the same data type and length as of the Source Data.
    2. Columns used in UNION, INTERSECT, EXCEPT etc. should be of the same data type. If you suspect, then go ahead and make them of same data type using CAST or CONVERT etc.
    3. Columns used in WHERE clause, JOINS etc. should be of the same data type. If you suspect, then go ahead and make them of same data type using CAST or CONVERT etc.
    4. One short-cut is to use SELECT.. INTO statement with 1<>1 in WHERE clause. This solution can be useful if you have complex query as in your case. It will create the structure and will not copy any data. But I do not recommend it. I believe in spend some additional time in development than wasting lot of efforts in Production.

    Data Modelling is very important and is occasionally ignored. I've started a series of article Does it matter ? that talks about most important but least focused areas in Database Design and Development. You can spend few minutes reading it. I hope it will give you some insights.

    SQL Server Carpenter - Consultant & Trainer
    Author of Learn T-SQL from Scratch

  • December 4, 2019 at 9:33 am

    #3704180

    At table level:

    The data kind for this discipline need to enable decimals. So: Single, Double or (not sure) Currency.

    If you desire you also can set the Format property to Percent but this is now not important.

    At structure level:

    Set the Format property for the bounded control to Percent.

  • December 4, 2019 at 10:21 am

    #3704198

    hi - is this a datetime conversion error?

    2nd question - if it is, is it stored in a varchar field

    MVDBA

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

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