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