Query transform with MSSQL server
Posted by Ronnie on September 15th, 2006
Ever found yourself struggling with a big chuck of embedded SQL failing on you? Oftentimes because of a trivial, but hard to spot, syntax (or semantic) error introduced as part of your dynamic query composition process.
Typically, you’d target your frustration at the debugger and insert a breakpoint around the problematic part of your code to extract the SQL and copy it into the Query Analyzer for investigation.
Unfortunately, pasting the SQL into Query Analyzer causes the statement to appear on one line entirely. You then find yourself inserting new line characters for readability and for making the error message more precise.
Transforming SQL this way is tedious, boring, and crying for automation. Fortunately, Enterprise Manager responds well to these cries. Just drill down to a table view of your database, right click on a table, and select “Open table” followed by “Query”.
Now paste your extracted SQL into the text portion of the Query by Example window and hit “Run”. Although the query might not actually execute, it’s transformed into a more readable one.
Obviously, you could start and stay in the QBE window. Personally, though, I dislike QBE as a working environtment beyond for layout beautification, so I usually copy the transformed query into Query Analyzer.
By the way, the transform also comes in handy if you want to make sure your query doesn’t contain any camouflaged expensive joins, e.g., for a query with inner joins done using where clauses, QBE transforms it into explicit inner joins.
