Monday, January 30, 2006

Be more careful when handling dynamically generated SQL queries

Last Friday I encountered an issue in a system which we provide support. It was caused by a dynamic SQL query.
A dynamic sql query is a query which is generated in the run time, stored usually in a variable, and executed usually using
sp_executesql to return the results.


I found comments placed by the developer who has developed it inside the sql query string it self, which was executed after building it dynamically.
But that query string is appended more dynamic parameters before execution (ORDER BY Field and ASC/DESC). After executing the query the sproc returned data but the sorting field and the ascending descending parameters did not seem to be working.
The reason was a comment started right after the last statement has made all other appended code as part of the comment.

I give below an example query (@sortedByField and @sortType are passed in to the sproc as parameters)

DECLARE @SqlStatement nvarchar(1000)

SET @SqlStatement =
'SELECT * FROM SomeTable
WHERE SomeField = 6 --POD => Proff Of Delivery'

(Please notice the comments given by the developer inside the string are in bold)

SET @SqlStatement = @SqlStatement + ' ORDER BY [' + @sortedBy + '] ' + @sortType

(Appending code after the comment line, results appended code to be treated as comments)


'SELECT * FROM SomeTable WHERE SomeField = 6 --POD => Proff Of Delivery ORDER BY Field1 DESC'
('SELECT * FROM SomeTable WHERE SomeField = 6' is the only effective part of the dynamically generated query)

So we better be more careful when we are working with dynamically generated sql in the future. And if possible reduce the use of the as much as possible.

Tuesday, January 10, 2006

SysAdmins becomes a Microsoft Gold Certified Partner

I just got the news from Nick (our managing director) that SysAdmins (the company I work for) has been awarded the Microsoft Gold Certified Partnership. It's a wonderful news to hear at the beginning of the year. If I'm correct we are the second company in Sri Lanka to achieve the Gold Certified Partnership. Great effort team! Let's keep it up.

Tuesday, January 03, 2006

Sigiriya will remain as the 8th wonder of the world forever














While browsing google news I came across an important link http://www.new7wonders.com, There is a process going on to choose the new 7 wonders of the world. And the best thing is we can call up and vote to choose the new 7 wonders of the world. And the bad thing is that since ages we were calling "Sigiriya" as the 8th wonder of the world, and we can call it the 8th wonder of the world rest of our lives, because huge cement structures are also amongst the 21 finalists. For people who don't know what sigiriya is you can get a brief idea from this link http://www.sritravel.8m.net/sigiriya.htm