3

I've run into quite a lot of issues specially when it comes to conversion errors: convert(datetime, [fieldname]) or SELECT CAST([field2] AS int).

Imagine a large select statement with 30+ conversion or cast functions, or a complex view with conversions, or even an insert statement on a field with wrong data type. I get so much headache trying to exclude fields one by one to find the troublemaker.

Is there a way to find out which field and/or value is causing the issue? How do people troubleshoot these without having to go through an arduous process of going field by field? Is there a log or information message that can help?

Improve this question

3 Answers 3

Reset to default
3

How do people troubleshoot these without having to go through an arduous process of going field by field?

Exclude half of them. If the error persists exclude half the remaining. Repeat.

Improve this answer
1
  • 1
    Classic debugging. :)
    – J.D.
    Dec 8, 2020 at 17:08
1

Hmm sounds like the issue is more of your process. The SQL Server Error Log can be found under the installation folder of the SQL Server instance, under a folder called "Log" but that typically won't contain the type of issues you're running into or when it does won't be as particularly helpful.

If you're able to run your query in SQL Server Management Studio, generally if a syntactical or logical error occurs, you can double click the error message itself and a lot of times it'll take you to the correct line of the issue (though not always, unfortunately it's not perfect).

You also can use the debugger in SSMS as well, which is helpful in certain cases.

If you have a specific kind of error you commonly run into, I can give you better specific advice.

Improve this answer
2
  • Thanks JD. No this is not an installation issue. Everything is fine. It's just that I am trying to debug my code, but it's hard to do.
    – TiffanyEl_1995
    Dec 5, 2020 at 23:55
  • Sorry I think you misunderstood what I said, I was just answering your question in the title in regards to the installation folder, not saying you have an issue with the installation itself. As far as debugging your code, hopefully my suggestions help, again if you can provide an example error that seems to be problematic for you to debug, I can provide specific advice that might make debugging that kind of issue easier.
    – J.D.
    Dec 6, 2020 at 0:15
1

Have you tried the TRY_CAST and/or TRY_CONVERT functions? Depending on the context, you could use them in the code or in a view for checking.

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

Improve this answer
1
  • hi @la-mochila - please register your account 🙂 - you may want to add some example code to your answer showing how to use those statements. Thanks!
    – Hannah Vernon ♦
    Dec 8, 2020 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.