It was noticed when I opened one of my SSIS packages that had ForEachLoopContainer used. The exclamation mark was appeared on the ForEachLoopContainer and found out the reason for it was missing Foreach File Enumerator item. Not only that, the Foreach Item Enumerator was missing too. How did it happen? As usual, made a search and found out that it is a known issue, introduced by SP1. The problem has been discussed under below link and some contain possible solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1146061&SiteID=1
support.microsoft.com/default.aspx?scid=kb;en-us;913817
http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!203.entry

Unfortunately, nothing worked for me. I changed the registry, re-installed, etc.. I would have corrected it if have had a backup of the registry because one possible reason for this is, registry changes made by other newly installed software. I suspect that culprit is WindowsDesktopSearchEngine, that I tried to install for my outlook 2007 but failed. So, the whole rescue operation ended up with re-installing the OS. How bad?

So, guys be aware of this issue.

We all know that “SELECT *” is a bad practice and it is expensive. But does this “theory” apply for all scenarios? No it does not. There is one place we should not worry about this. See the code below.

IF EXISTS (SELECT * FROM Person.Address)
BEGIN
   print 'yes'
END

If “STATISTICS IO” sets on and executes the above code, you can see that only few logical or physical pages have been read. But if “SELECT * FROM Person.Address” statement executes without “EXIST”, all the pages for the table will be read. This shows that “SELECT *” is not an expensive query with “EXIST” because SQL Server check the availability of records instead reading the entire table. So, do not worry about “SELECT” statements with “EXIST”, SQL Server completely ignores the statement.

I was going through few notes I had made regarding sql injection couple of years ago and it remembered me the value of some good practices when we write dynamic queries. Using QUOTENAME function for parameter values was one of among. See the below code for brushing up your mind too.

Assume that we wanted to have a stored procedure that returns some records from one of our business tables base on the column name (from a drop-down) and the value for it. You might be writing the SP like this;

CREATE PROCEDURE TestProc @ColumnName varchar(100), @Value varchar(100)
AS
BEGIN

   DECLARE @sql varchar(1000)
   SET @sql = 'SELECT columns FROM table WHERE '
   SET @sql = @sql + @ColumnName + ' = ''' + @Value + ''''

   EXEC (@sql)
END

So the client will execute this with;

DECLARE @ColumnName varchar(100), @Value varchar(100)
SET @ColumnName = 'Column1'
SET @Value = 'value'
EXEC TestProc @ColumnName, @Value

He got the result he wanted. What happen if he/she set the @value like this?

SET @Value = '''; SELECT * FROM sys.sysobject --'

Too bad. He got something that he should not see. Here is the worse case.

SET @Value = '''; DELETE FROM table1 --'

So, where we can use QUOTENAME function? Here is the way. Modify the SP like below.

SET @sql = @sql + @ColumnName + ' = ' + QUOTENAME(@Value, '''')

Keep this in your mind. Even though it is not a good practice writing dynamic SQLs, some situations force you to apply dynamic queries. If so, make sure you use this function too.

I bought few items from the MS store, most interesting items are Sudoku game that I love to play (even though it is difficult than I thought) and the WM USB Powered Speakers.

The speakers do not produce the sound as I expected but it is far more better than the sounds produce by the laptop. Unfortunately the software received for both the PS and the mouse are not compatible with Vista but seems new versions are available for Vista.

If you want the Zune theme on your Windows XP, click on this. Thanks for Keith, publishing this.

Just received an email from MS that I have been awared as MVP again. So it starts my second enjoying MVP year. Thanks for all, specially Microsoft, all Sri Lanka MVPs.

Two new guys have been awared as MVP; Ahmed Fiqri Ismail (Windows – Mobile Devices) and Shaminda Illangantileke (Windows Server System – SMS). Congratulation guys!

Hey guys, this is one of my plans for 2007, new web-site dedicated for SQL Server. It is being constructed and will be launched soon. Stay tuned.....

First of all, Wish you Happy New Year!

After long time, had a chance to do a Microsoft exam again (got a free voucher :) ), yes it is Technolodgy Specialist: Microsoft SQL Server 2005—Implementation and Maintenance (70-431). I had no time to study but was abale to find the correct answer since most of the questions were related to the job I do. Some of the questions were little bit difficult to me (like questions related to Mirroring..) because of no experience on those subjects. but I was lucky enough to got through.

Now Im thinking of doing Technolodgy Specialist: Microsoft SQL Server 2005 Business Intelligence - Implementation and Maintenance (70-445), even though I have not touched BI related stuff for long time. That's one of my 2007 plans anyway.