SQL Server Express Connectivity

If you are facing problem to connect to your SQL Server Express 2005 from SQL Server Management Studio, try the follow codes:-

• [IP Address|Domain Name]\SQLEXPRESS,[PORT]
• PORT number by default = 1433

Make sure your UserName and Password are correct.

BOE - Web Services

Having hard time to look for web services url provided by BO?
Example of business objects XI Release 2 Web Service URL:-

http://localhost:8080/dswsbobje/services/session

Crystal Report - Alternate Row Color

Simple way to color row alternately
IF RecordNumber Mod 2 = 0 THEN crSilver ELSE crNoColor

Crystal Report - Auto Schedule by Weekly or Monthly

Human nowadays is getting lazier and lazier. Most of the time we will let "Robot" to do our daily jobs. In crystal report, it is possible to schedule daily, weekly, monthly, or quarterly report based on the parameter with auto scheduling using Job Scheduling function provided by Microsoft SQL Server.

Create a schedulebyparameter to accept values Weekly or Monthly,
Create a crystal report formula

Filter From Date (Last 7 Days)

IF schedulebyparameter = "Weekly" THEN
Datadate - 7
ELSE IF schedulebyparameter = "Monthly" THEN
Date(DateAdd ("m", -1, Datadate - day(Datadate)+1))

Filter To Date (Yesterday)

IF schedulebyparameter = "Weekly" THEN
DataDate - 1
ELSE IF schedulebyparameter = "Monthly" THEN
Datadate - day(Datadate)

T-SQL - Check all collations in SQL Server 2005

Simple T-SQL to show all available Collations in Microsoft SQL Server 2005:-

SELECT * FROM sys.fn_HelpCollations()
GO

SSIS - System Null Reference Exception

If you facing the error message in SSIS like below:

Error: System.NullReferenceException:
Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

Possibilities are:-
- You might be using "Select * " statement in Data Flow Source, and the source has added new columns.
- There's an empty row data exists in Data Flow Source result.

Solutions:-
- Open the SSIS package, refresh your Data Flow Source Query. Check the added field in the external/output column. If the new column is required, you can create the field in the destination database, and map it with the new created source column.
- To avoid this type of error message in daily scheduling, try to use "Select specific fields" rather than using "select * statement". Which might be dangerous and will cause the package failure.
- Try to split empty rows with using the conditional split provided in SSIS.

T-SQL - Check if Table Exists in Database System

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[TableName]
END
GO

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!