I didn't know that I could do this inside SQL Server

EXEC master..xp_fixeddrives

Executing the procedure onmy server gave me this result set:

drive  MB free
C       5897
E       33334

Man, I think it's about time I upgrade and brush up my SQL skills. I don't know why you would do it inside SQL Server. But then again it's pretty cool to know that something like this exist. You never know, you might need it someday.

I've been working on alot of SQL Server lately due to the current project i'm assigned to and I found myself this morning needing a query that would truncate all the tables in one of my staging database. My initial thought is that I can do this using a cursor that would hold all truncate statements and execute each one of them one at a time so within 5 minutes i was able to build a query that looks like this:

--declare a variable that would hold the query to be executed
DECLARE @TruncateQuery varchar(4000)

-- create a cursor that would hold our truncate statements
DECLARE TruncateQuerries CURSOR LOCAL FAST_FORWARD
FOR SELECT
    N'TRUNCATE TABLE ' +
    QUOTENAME(TABLE_SCHEMA) +
    N'.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
        TABLE_TYPE = 'BASE TABLE'
    AND    OBJECTPROPERTY    (
                            OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
                            N'.' + QUOTENAME(TABLE_NAME)
                        ), 'IsMSShipped') = 0

-- read our truncate statements
OPEN TruncateQuerries
-- loop thru each statement in our truncate statement cursor
FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery
WHILE @@FETCH_STATUS = 0
BEGIN
    --assign the current truncate statement to our @TruncateStatement variable
    FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery 
    --execute the statement
    EXEC(@TruncateQuery)
END
-- close our cursor
CLOSE TruncateQuerries
-- and free up the resources
DEALLOCATE TruncateQuerries

Looks great right? Then I realized what my good friend Jon Galloway told me once that there are hidden stored procedures in SQL Server and 1 of those stored proc is sp_MSforeachtable. It's an undocumented sp so you won't find anything about it in the SQL Books Online. What this stored procedure basically does is that it lets you execute a command or a set of commands against all tables inside a database. Before we go into further detail lets look at the parameters that sp_MSforeachtable expects.

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand

Where:(description taken from [LINK])

• @RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
• @command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
• @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
• @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
• @whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
• @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
• @postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

All other parameters are optional except for @command1 which is first statement that would be executed.

Now, lets rewrite our truncate table query from above to use the sp_MSForeachtable procedure:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

What??? Tha't it? 1 line? Crazy huh? I didn't realized that it was that easy until i tried it. Man, If i knew this when I started writing my query above I could have not wasted 5 minutes of my life in something that can be done in 10 seconds. Thanks for hiding feature this Micrsoft!

But wait! There's more, now i've truncated my tables i need a way to reseed all the identity columns on my database:

EXEC [sp_MSforeachtable] @command1="DBCC CHECKIDENT (?, RESEED, 100)"

What??? Another 1 liner? Whats even better is that I could also REINDEX all the tables in my DB with one line of beautiful SQL code:

EXEC [sp_MSforeachtable] @command1="DBCC DBREINDEX('?')"

Wanna show progress message on each execution? Try this version:

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

Oh, Ma! I can't believe being a programmer could be this easy. ;) I hope i could save someone's precious time by proving this example because i know it would save mine in the future.

New to C#? Need to know whats on C# 3.0? Then download this 500 page book coutersy of Microsoft. It's the most complete C# reference you can find and it is primarily written by the engineers of the C# language

Go download it here!

Thank you Charlie for the link!

Somebody emailed this to me today and i must say that i needed this headbanger to brighten up my day...

A lecturer, when explaining stress management to an audience, raised a glass of water and asked, “How heavy is this glass of water?” Answers called out ranged from 20g to 500g.

The lecturer replied, “The absolute weight doesn’t matter. It depends on how long you try to hold it.

If I hold it for a minute, that’s not a problem. If I hold it for an hour, I’ll have an ache in my right arm. If I hold it for a day, you’ll have to call an ambulance.

In each case, it’s the same weight, but the longer I hold it, the heavier it becomes.”

He continued, “And that’s the way it is with stress management. If we carry our burdens all the time, sooner or later, as the burden becomes increasingly heavy, we won’t be able to carry on.”

“As with the glass of water, you have to put it down for a while and rest before holding it again. When we’re refreshed, we can carry on with the burden.”

“So, before you return home tonight, put the burden of work down. Don’t carry it home. You can pick it up tomorrow. Whatever burdens you’re carrying now, let them down for a moment if you can.”

So, my friend, Put down anything that may be a burden to you right now. Don’t pick it up again until after you’ve rested a while.

Here are some great ways of dealing with the burdens of life:

1. Accept that some days you’re the pigeon, and some days you’re the statue.

2. Always keep your words soft and sweet, just in case you have to eat them.

3. Always read stuff that’ll make you look good if you die in the middle of it.

4. Drive carefully. It’s not only cars that can be recalled by their maker.

5. If you can’t be kind, at least have the decency to be vague.

6. If you lend someone P100 and never see that person again, it was probably worth it.

7. It may be that your sole purpose in life is simply be kind to others.

8. Never put both feet in your mouth at the same time, because then you won’t have a leg to stand on.

9. Nobody cares if you can’t dance well. Just get up and dance.

10. Since it’s the early worm that gets eaten by the bird, sleep late.

11. The second mouse gets the cheese.

12. When everything’s coming your way, you’re in the wrong lane.

13. Birthdays are good for you. The more you have, the longer you live.

14. You may be only one person in the world, but you may also be the world to one person.

15. Some mistakes are too much fun to only make once.

16. We could learn a lot from crayons… Some are sharp, some are pretty and some are dull. Some have weird names, and all are different colors, but they all have to live in the same box.

17. A truly happy person is one who can enjoy the scenery on a detour.

I saw this picture on my machine today while trying to backup my machine. I can't help myself but post it pretty interesting if you ask me ;)

The Call Center industry is a booming market in the Philippines. Answer is, if you live in Manila you probably know atleast 1 or 2 people working on this industry(i think majority of my classmates in college ended working as a Call Center Agent). They even have a song being played on the Philippine radio airwaves.

It's a great job and people who work at this companies often earn more money than entry-level programmers. Yep! that's true. 5 years back when I was looking for a job fresh out of college a friend told me that he was getting paid 17K + bonus as a call center agent. My jaw dropped because a buddy of mine who is a brilliant developer just got his first job as developer for a multi-national company was just getting paid 8.5K even my first paycheck as a full-time developer was only at 12K.

It's a pretty big margin huh? I guess that's the reason why alot of fresh grads go directly to call centers as their first choice of work(or maybe because of the lack of job openings in the Philippines). I do know that alot of people just call centers as a stepping stone for their real career goal. A former colleague of mine was a call center agent for Sykes before joining my former company. He said that he needed a job while reviewing for his SWCD and some cash to payoff the certification exam fee.

I was working on small project today and one of the requirements is to list down all the Mondays in a year and populate a dropdownlist with the values.

It's a funny requirement but still needs to be done since the client wants it that way(Oh well, customer is always right even if it would end up wrong). Below is my 5 minute function that accepts a 4 digit year value and DayOfWeek as its parameter(I added this feature to the function just incase they go kookoo again). The function would then return a generic list of DateTime objects that equals the specified DyOfWeek(eg. DayOfWeek.Monday, DayOfWeek.Tuesday and etc.).

private List<DateTime> GetDatesByDayOfWeek(int selectedYear, DayOfWeek dayOfWeek)
{
   string firstDayOfTheYear = String.Format("January 1, {0}", selectedYear);
   string lastDayOfTheYear = String.Format("December 31, {0}", selectedYear);

   DateTime firstDateTime = DateTime.Parse(firstDayOfTheYear);
   DateTime lastDateTime = DateTime.Parse(lastDayOfTheYear);

   Int32 dayCount = lastDateTime.DayOfYear - 1;

   List<DateTime> selectedDates = new List<DateTime>();

   for (Int32 ctr = 0; ctr <= dayCount; ctr++) {
      DateTime processedDate = firstDateTime.AddDays(ctr);
      if (processedDate.DayOfWeek == dayOfWeek) {
         selectedDates.Add(processedDate);
      }
   }

   return selectedDates;
}

Pretty boring huh?

*update* Justice, the most Metrosexual developer in the face of North America posted a quick tweak to my code(Robert locke and Jokiz have the same comment too). Check his code below:

public static IList<DateTime> GetAllDaysOfWeekForYear(int year, DayOfWeek dw)
{
   List<DateTime > listOfDates = new List<DateTime>();

   DateTime firstDayOfWeekInYear = FindFirstDayOfWeekInYear(year, dw); 

   for (DateTime currentDateTime = firstDayOfWeekInYear; currentDateTime.Year == year;
currentDateTime = currentDateTime.AddDays(7))
   {
      listOfDates.Add(currentDateTime);
   }
   return listOfDates;
}

public static DateTime FindFirstDayOfWeekInYear(int year, DayOfWeek dw)
{ 
   for (int i = 1; i <= 7; ++i)
   {
      DateTime currentDate = new DateTime(year, 1, i);
      if (currentDate.DayOfWeek == dw)
      {
         return currentDate;
      }
   }
   throw new Exception("Impossible!");
}

It's true. Everybody has their own match. Their own weakness. Their own kryptonite that would hold them to their knees and trade their philosophies and senses for a chance to break free.

And for my friend who is a recognized Linux and Java guru(he founded the largest Java User Group in the Philippines) its the skimpy pants and hanging shirts.

The photo was taken on a Linux conference in Manila back in 2005. Just look at how hard Melvin held on to Microsoft. Mercy!

*Disclaimer* According to Melvin, He still preffers Linux over Windows and that all he did that day was touch Microsoft but never had the chance to use it. :P

comments? go here! >> http://devpinoy.org/blogs/keithrull/archive/2007/08/21/even-linux-gurus-have-their-match.aspx

I've been interviewing lately for developers to fill the 2 open positions in my company and I've just realized an interesting stat inside our development team. A year and a half ago there where:

• 4 Caucasians, 2 from India, 2 Chinese, 2 Filipino
• 2 of the 10 Developers are girls. 1 Chinese and 1 from India.

After 1 year 1/2 with people resigning and us hiring a new batch of developers, composition changed into:

• 1 Caucasian, 1 Filipino, 2 Chinese and 5 from India.
• 4 of the 9 Developers are girls. 1 Chinese and 3 from India.

Pretty interesting huh? It's really amazing how the composition of our team changed in a year and a half. I guess its because of the turnover that we have encountered in the past few months..

So how about you? Whats the composition of your IT team?

*Wanna Comment?* Go here!

Some of you might have noticed that I haven't posted anything lately... well the reason is that I've been busy the past few weeks since working on alot of Reporting Services lately for one of the biggest projects that currently in progress in the company that I work for and when I say big I really mean big.

The thing about this current project is that it doesn't involve alot of coding and is mostly data analysis and report writing with Microsoft Reporting Services(just think of it this way, I need to generate and build 30 reports in 30 days. Yikes.). This takes alot of my time in a day and it has been the same way eversince I came back from my wedding. 

But anyhow, I'm back and I'm ready to post my dev thoughts again. I can't wait to start posting about Silverlight and start showing you guys some cool things that I have built with .NET 3.0 lately (No Windows Vista samples thou.. Any sponsors for this? :P).

Just keep readin' guys. I'll have something in the next few days ;) Till then, God speed everyone!

Hmmm... I was browsing CS today when I saw an announcement about the upcoming CSDC (Community Server Developer Conference) and man was I excited! I think its about time that Telligent gets the guys together and show them the stuff that they have been baking since the announcement for CS2008. I've been running CS in the developers community that I lead for two years now and I think going to such events would help us know great things that we can do and implement at our CS install.

Gee! I'm excited.. but.. I can't go unless somebody sponsors my plane ticket and registration :P

Anybody interested? :P Scott? Alex? Rob? Anyone?

Hey guys! Please vote the DevPinoy theme as the best looking theme for Community Server. You can vote for us here. Thanks guys!

Yup! We are finally married ;) We tied the knot on July 4, 2007 on a ceremony at Blue Gardens, Quezon City, Philippines.

It was a blast, a moment we'll forever cherish!

Thanks to everyone who came to our wedding! God bless you all!