SSMS - Shrink Database Log Files

Microsoft SQL Server 2005

Two simple steps on how to shrink database log files using SQL Server Management Studio (SSMS):-

1. Open SSMS > login Database Server > Right Click Database that you would like to shrink > select Task > Shrink > Files
as shown in figure below.

2. Do a full backup first!. Select "Log" in File Types and check carefully before you click "OK".

Please be remind that to restart your database server to refresh, otherwise you will be facing a problem where you couldn't find your tables data on the next day!

Crystal Report - Group Weekly

In Crystal Report, when you group Date or DateTime data types by weekly, first day of the week will be Sunday by default.

You can set the first day of the week to Monday, Saturday, ... etc.

Create a formula e.g. WeeklyMonday, and insert the following formula:-
Datevalue(RecordsDateTimeColumn) - WeekDay (RecordsDateTimeColumn, crMonday) + 1
Then Create Group Weekly by using the formula that we created: WeeklyMonday and select "by Daily" at the "Group Option".

*Remember to replace the [RecordsDateTimeColumn] with your database field

WeekDayName(WeekDay(RecordsDateTimeColumn), True, CrSunday) will return Mon, Tue, Wed .. Sun
WeekDay will output the number that represent Sun, Mon, ... Sat start with 1.00, 2.00, ... 7.00 by default.

SSIS - Mapping Parameter with Variables

Thanks to Rafael Salas, I able to solve my problem for using the variables in SSIS Execute SQL Task. If you having the same problem with me, here I attached some graphical guidelines on "How to use variable as Source Type in Execute SQL Task".

Wondering what is SSIS?
SSIS stand for SQL Server Integration Services. It's the new ETL tool for Microsoft SQL Server 2005


1.
Let's start with creating a new SSIS Project, define the data source. Rename the desire SSIS package name and double click to open. Now, define the connection manager by right click the area and select "New Connection From Data Source". Grab a "Execute SQL Task" from ToolBox


2.
Create a variable with string data types to store the sql query. If your query accept parameter(s), please create the parameter variable(s) too. For example, datefrom and dateto, and remember to key in the default values. Select your query variables, at the properties box, set the "evaluateAsExpr" to True.

3.
At the same properties box, select "Expression" and click on the "ellipsis" button. You will see a pop up windows for expression builder. Key in your sql statement and click on "Evaluate Expression" to check if the query parse successfully or not.

4.
Back to "Execute SQL Task editor", choose "Variable" at the SQLSourceType. Drop down the Source Variable and select your sql variables. Press "OK" button and you are done! Congratulation!