Wednesday, January 30, 2013

How to check NULL in SSRS Formula Expression

The problem that literally takes a week to find solution is that I can't find any way to handle NULL values in my SSRS report.

Suppose Field1 returns NULL value, i need to check if Field1 is null then 0 else Field1

Solution:

Right now you can do it by building the following expression:
if(Field1 = Empty, 0, Field1)

Tuesday, January 8, 2013

Customization of CDC Cleanup Job

Change data capture uses a retention based cleanup strategy to manage change table size. you can check default retention in minutes using below syntax

select *,retention from msdb.dbo.cdc_jobs

Cleanup Job start cleaning process every night, by default it will clean data of three days older.

You can update this retention value and cleanup job as below.


USE [msdb]
GO

Declare @Job_Id UniqueIdentifier
SELECT @Job_id = job_id From msdb.dbo.sysjobs where name = 'cdc.DatabaseName_cleanup'

Declare @Command Varchar(MAX)
Set @Command = N'Declare @EndLSN as Binary(10)
SELECT @EndLSN = GetDate()

if @EndLSN is not null
UPDATE msdb.dbo.cdc_jobs Set retention = isnull(DateDiff(MI, sys.fn_cdc_map_lsn_to_time(@EndLSN), GetDate()),0)+1440 where job_id = ''' + CONVERT(Varchar(100), @Job_Id) + '''
GO

sys.sp_MScdc_cleanup_job'

EXEC msdb.dbo.sp_update_jobstep @job_id=@Job_id, @step_id=1 ,
        @command = @Command
GO

Monday, January 7, 2013

Enable Change Data Capture on Table

Microsoft SQL Server 2008 has introduces new feature for data change tracking on tables called CDC (Change Data Capture). below are steps to enable change tracking on specific table "dbo.Users"

1) First enable CDC on database using below syntax:

    EXEC sys.sp_cdc_disable_db

it will create two jobs
- cdc.DatabaseName_capture
- cdc.DatabaseName_cleanup

2) Enable CDC on table "dbo.Users"

    IF NOT EXISTS (SELECT Name FROM sys.tables WHERE Name = 'Users' AND is_tracked_by_cdc = 1)
    BEGIN
        EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name   = N'Users',  
        -- @captured_column_list = N'[UserID], [Name], [IsDeleted]',
        @role_name     = NULL
    END


it will create below objects in database

- Tracking table cdc.dbo_Users_CT
- Table-valued Functions
    cdc.fn_cdc_get_all_changes_dbo_Users - Returns one row for each change applied to the source table within the specified log sequence number (LSN) range. If a source row had multiple changes during the interval, each change is represented in the returned result set.

    cdc.fn_cdc_get_net_changes_dbo_Users - Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.

3) Finish CDC Enabled on dbo.Users table. Now, You can get changes on "dbo.Users" table using below syntax
  
    select * from cdc.dbo_Users_CT
  

    DECLARE @Begin_lsn BINARY(10);
    DECLARE @End_lsn BINARY(10);
    DECLARE @Operation_Time DATETIME=Getdate();
    DECLARE @LastUpdatedDate DATETIME=Getdate()-2
  
    SET @Begin_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@LastUpdatedDate);
    SET @End_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@Operation_Time);

    SELECT * FROM Cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_lsn,@End_lsn,'all')

Aggregation of running value in SSRS

The usual way to do aggregate concatenation in SSRS is with custom code. See here for an example:

http://blogs.msdn.com/suryaj/archive/2007/08/11/string-aggregation.aspx

Here's the custom code in basic form:

Private CurrGroupBy As String = String.Empty
Private ConcatVal As String = String.Empty
Public Function AggConcat(GroupBy as String, ElementVal as String) as String
    If CurrGroupBy = GroupBy Then
        ConcatVal = ConcatVal & ", " & ElementVal 
    Else
        CurrGroupBy = GroupBy 
        ConcatVal = ElementVal 
    End If
    Return ConcatVal 
End Function
 
 
Followed by this expression at the grouping level you want to display:

=RunningValue(
     Code.AggConcat(
         Fields!YourFieldToGroupBy.Value
       , Fields!YourFieldToConcat.Value
       )
   , Last
   , "GroupName" 
   )
 
 

MS SQL Server Tracking Data Changes

An important requirement for some applications to be efficient is the ability to query for data that has changed in a database. Typically, to determine data changes, application developers had to implement a custom tracking method in their applications by using a combination of triggers, timestamp columns, and additional tables. Creating these applications usually involved a lot of work to implement, led to schema updates, and often carried a high performance overhead.

SQL Server 2008 provides the following new technologies that application developers can use to track data changes and enable applications to query for only the data that has changed:


Change Data Capture
Describes the change data capture process, how to configure, administer, and use change data capture.
Change Tracking
Describes the advantages of using change tracking, how to configure, manage, and use change tracking.

Popular Emulator / Simulator for Mobile Devices Testing

You can download emulators for popular mobile devices and browsers by following these links

Device or Browser Emulator / Simulator
Windows Phone Windows Phone SDK Downloads
The Windows Phone Software Development Kit (SDK) includes all of the tools that you need to develop apps and games for Windows Phone
iPhone / iPod / iPad Electric Plum
iPhone and iPad Simulators for Windows, as well as a Responsive design tool. Can integrate with VS 2012 "Browse With.." option.
iBBDemo2 iPad / iPhone Simulator
There's no true iOS emulator for Windows. iBBDemo2 is an unofficial 3rd-party utility that simulates Mobile Safari's screen dimensions and user agent string.
Android Android SDK homepage
Opera Mobile / Opera Mini Latest versions: Opera Developer Tools home
Opera Mini 4.2: Online Java-based simulator
Windows Mobile 6.5.3 Windows Mobile 6.5.3 Developer Tool Kit
Note that to give the phone network access, you also need the VPC Network Adaptor included in Virtual PC 2007 . To connect IE on the phone to your Visual Studio development server, see Kiran Patil's blog post .
Windows Mobile 6.1 Emulator images for Visual Studio 2005/2008

What is SignalR

SignalR is an open-source .NET library for building web applications that require live user interaction or real-time data updates. Examples include social applications, multiuser games, business collaboration, and news, weather, or financial update applications. These are often called real-time applications.

SignalR simplifies the process of building real-time applications. It combines an ASP.NET server library and a JavaScript client library to make it easier to manage client-server connections and push content updates to clients. You can add the SignalR library to an existing ASP.NET application to gain real-time functionality.

Friday, January 4, 2013

New Features in ASP.NET MVC 4

This section describes features that have been introduced in the ASP.NET MVC 4 release.

ASP.NET Web API

ASP.NET MVC 4 includes ASP.NET Web API, a new framework for creating HTTP services that can reach a broad range of clients including browsers and mobile devices. ASP.NET Web API is also an ideal platform for building RESTful services.
ASP.NET Web API includes support for the following features:
  • Modern HTTP programming model: Directly access and manipulate HTTP requests and responses in your Web APIs using a new, strongly typed HTTP object model. The same programming model and HTTP pipeline is symmetrically available on the client through the new HttpClient type.
  • Full support for routes: ASP.NET Web API supports the full set of route capabilities of ASP.NET Routing, including route parameters and constraints. Additionally, use simple conventions to map actions to HTTP methods.
  • Content negotiation: The client and server can work together to determine the right format for data being returned from a web API. ASP.NET Web API provides default support for XML, JSON, and Form URL-encoded formats and you can extend this support by adding your own formatters, or even replace the default content negotiation strategy.
  • Model binding and validation: Model binders provide an easy way to extract data from various parts of an HTTP request and convert those message parts into .NET objects which can be used by the Web API actions. Validation is also performed on action parameters based on data annotations.
  • Filters: ASP.NET Web API supports filters including well-known filters such as the [Authorize] attribute. You can author and plug in your own filters for actions, authorization and exception handling.
  • Query composition: Use the [Queryable] filter attribute on an action that returns IQueryable to enable support for querying your web API via the OData query conventions.
  • Improved testability: Rather than setting HTTP details in static context objects, web API actions work with instances of HttpRequestMessage and HttpResponseMessage. Create a unit test project along with your Web API project to get started quickly writing unit tests for your Web API functionality.
  • Code-based configuration: ASP.NET Web API configuration is accomplished solely through code, leaving your config files clean. Use the provide service locator pattern to configure extensibility points.
  • Improved support for Inversion of Control (IoC) containers: ASP.NET Web API provides great support for IoC containers through an improved dependency resolver abstraction
  • Self-host: Web APIs can be hosted in your own process in addition to IIS while still using the full power of routes and other features of Web API.
  • Create custom help and test pages: You now can easily build custom help and test pages for your web APIs by using the new IApiExplorer service to get a complete runtime description of your web APIs.
  • Monitoring and diagnostics: ASP.NET Web API now provides light weight tracing infrastructure that makes it easy to integrate with existing logging solutions such as System.Diagnostics, ETW and third party logging frameworks. You can enable tracing by providing an ITraceWriter implementation and adding it to your web API configuration.
  • Link generation: Use the ASP.NET Web API UrlHelper to generate links to related resources in the same application.
  • Web API project template: Select the new Web API project form the New MVC 4 Project wizard to quickly get up and running with ASP.NET Web API.
  • Scaffolding: Use the Add Controller dialog to quickly scaffold a web API controller based on an Entity Framework based model type.