Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BIT Barbarian

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.

dynamic sql and a char crash

Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn’t working. Found out that the remote database I was connecting to had a char date instead of a datetime. I found the comparison of CHARDATE > VARCHARDATE failed to error out, but also failed to give a proper result set. Changing the look-up to ensure both dates were converted to date fixed the issue.

During this debugging I was reviewing my dynamically created SQL statement. I learned that SSMS limits the amount of text it will return. In trying to view the single large UNION ALL statement, I was experiencing truncated results. I wanted to ensure the code being executed looked proper, but couldn’t get past the truncation. Enter SSMSBoost to the rescue! SSMSboost is created by developers and very responsive to requests. I’ll do a proper review soon. They offer a visualize data option that goes beyond the usage I employed. For my purpose, I clicked on the cell and selected visual cell as text, and opened the data in notepad++. This showed the full text without truncation. I was able to move on in my debugging then as I knew the dynamic sql statement was not actually truncated except to my SSMS output.

Dynamic SQL is a great tool, but if I had been working with direct queries, the issue would have been much faster to resolve!

 

Comments

Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...