Category Archives: SQL Server

Microsoft SQL Server Related

Basic Understanding of Using JQuery DataTable Server Side And Asp.Net


JQuery DataTable definition taken from that DataTables is a plug-in for the jQuery Javascript library.
It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.

Common example of basic usage on JQuery DataTable is not Server side processing. You can imagine it is a data collection method that retrieve all data represent as Json string and stored in the DataTable.
So paging, searching, ordering are fully client side processing. This is handy if the data is not large enough.

On the other hand, Server Side processing in JQuery DataTable take data partially based on user request.
Pagination queries data in between start & end records in that page.
Searching will search data on fields usualy with Sql Like also Ordering will do Sql Order by.
All data taken from database server dynamically.

However, this server side processing could be confusing for someone who just exploring JQuery DataTable.
So this post explains jQuery DataTable server side basic understanding with a simple example.
We will use Newtonsoft component to convert class object to Json string. You need to install it using Nuget package manager.


First of all, we need to include CSS, jQuery and jQuery DataTable javascript in aspx file.

<link rel="stylesheet" href="" />
    <script type="text/javascript" src=""></script>
    <script type="text/javascript" src=""></script>

And then the html table as DataTable. This table has id example

<table id="example" class="display" cellspacing="0" width="100%">
			<th>Full Name</th>
			<th>Phone Number</th>
			 <th>Fax Number</th>
			<th>Email Address</th>


Continue reading

Prevent A Same SSRS Report From Running Concurrently

Some report could have quite long run more then some minutes. Usually due to heavy query or big content.
This blog will show how to prevent a specific SSRS report to have two or more report instances being run in same time.
So it applies only to each report you want to validate.

Creating Class Library

First, you need to make a class library either using VB.Net or C# and add a Web Reference to SQL Server Reporting Service API.
This class library is intended to list all active SSRS report jobs.

  1. Create a VB.Net Class Library Project and save it as SSRSLib.vb
  2. Add Reporting Service Web Reference.
    To do that, click Add Service Reference, Advanced, Add Web Reference.
    Inside Url text box type http://yourServIpAddr/ReportServer/ReportService2010.asmx for SSRS 2008 R2 version, click ‘go’ arrow icon.
    Edit the Web Reference Name to your prefer i.e ReportService2010 and then click Add Reference button.
  3. Write a class to retrieve list of SSRS Active report jobs
    Public Class SSRSInstance
    Public Sub New()
    End Sub
    Public Function getJobs() As DataTable
    	Dim dt As New DataTable()
    	Dim rs As New ReportingService2010()
    	rs.Credentials = New System.Net.NetworkCredential("yourUserId", "yourPwd")
    	Dim jobs As Job() = Nothing
    	jobs = rs.ListJobs()
    	Dim job As Job
    	Dim dr As DataRow
    	For Each job In jobs
    		dr = dt.NewRow()
    		dr("JobID") = job.JobID
    		dr("Name") = job.Name
    		dr("ComputerName") = job.Machine
    		dr("UserName") = job.User
    		dr("Action") = job.JobActionName
    		dr("StartTime") = job.StartDateTime
    		dr("Status") = job.JobStatusName
    	Next job
    	Return dt
    End Function
    End Class
  4. Build your project. Since my assembly named SSRSLib so that build project will produce two dll files which are SSRSLib.dll & SSRSLib.XmlSerializers.dll
  5. It is better to test your class Lib with Console Application before proceeding any further to know whether it contains error or success

Okay first step has done. The SSRS list jobs class library is created. Usually the class library created for SSRS called custom assembly.
Continue reading

Scheduling Excel Report With SSRS 2008 R2 And ASP.Net

As default SQL Server Reporting Service 2008 R2 has Scheduled Report Generation named Subscription. You can choose what report and when to generate it also delivery method.

However this feature is quite standard. We must supply report’s Parameter manually to Subscription. So that in this blog I will show you how to make more flexible to this kind of schedule using Visual Basic Asp.Net

The generated report will be on Excel format.

Here are step by step:

  1. Prepare the report you want to sent and deploy Report Server
  2. Browse the report via http://Your_serverip/ReportServer and get its exact Url for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render
  3. Continue reading

Get Top Queries By Average CPU Time SQL Server 2008 R2

Database performance plays significant effect in large user application. We need to examine and identify causes that makes system slow.

One step we can do is to identify query which takes high CPU time. Use transact SQL sys.dm_exec_sql_text in order to get top CPU expensive queries.

Taken from Microsoft site,, the code using sys.dm_exec_sql_text is below:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Above T-SQL gets Top five query by Average CPU Time. Open your SQL Server Management Studio, choose your database and run above query.

After getting those Top queries, you can open SQL Server Database Engine Tuning Advisor to analyze them and apply the Tuning Advisor recommendation to get better performance.
You can take a look at my post about How to use Database Engine Tuning Advisor for your reference.

Agung Gugiaji

Basic How To – Using SQL Server Database Engine Tuning Advisor

The function of SQL Server Database Engine Tuning Advisor in MS Web is “examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning”
Refer To:
Query needs to be tuned in order to increase performance.

This post will showing how to basic use Database Engine Tuning Advisor. I use SQL Server 2008 R2 AdventureWorks sample Database.

  1. First create your prefered query. For example
    select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue 
    from Sales.Customer
    inner join Sales.Store on Sales.Customer.StoreID = Sales.Store.BusinessEntityID
    inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
    inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
    where Sales.Customer.StoreID is not null
    group by Sales.Store.Name,Sales.SalesTerritory.Name order by Sales.Store.Name

    Save To your folder and name it StoreSales.sql

  2. Continue reading

How To Do SQL Server 2008 R2 Replication To Distribute Your Data Or Make Mirror

Replication means making exact copy or synchronize from source database to target with interval of time. So if source data get inserted then for short time target data will get inserted with same data from source.
Same as delete operation, if some records in source data deleted then same data in target table will deleted.

From technical point of view, Replication and Mirroring are different. Replication is synchronization process between databases and intended to be used by day to day application. Whereas mirroring is intended for high availability and data protection of database. So if live database corrupted then mirror database will be used automatically.
However from non technical point of view, they as if similar. Both source & target (secondary) server has same data but replication can have sql query in order to replicate for just specific data and/or combined data.

For some reasons, we want to have a replica for our database. In example, you have two servers with almost or same specification, one server is serving OLTP and other intended to reporting server.
The OLTP server will supply or export its data to Reporting Server, so that overall application load will be devided.

This export data from OLTP to Reporting can be done automatically using SQL Server 2008 R2 Replication. Users do transactional data from their application, data saved to OLTP database.
After that replication do insert or update or delete according source data in short time interval to target which is Reporting database. This makes exact state of data between source & target.

Another example is distribute central data to branches or vice versa. Let’s say you have central data that inputted by head office staffs.
You want some of the data to be distribute to branch servers so that branch users can view central data from their own server.
This also can be done using replication.

SQL Server Replication explanation

Copying some definitions from MS technet site =>
Replication uses a publishing industry i.e magazine and/or newspaper metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions.

The Publisher is a database instance that makes data available to other locations through replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.

A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.

A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions: push and pull.
Continue reading

Maintaining Data Integrity Easily With SQL Server Relationship Cascade Delete & Update Rule

Maintaining Data Integrity Easily With SQL Server Relationship Cascade Delete & Update Rule

SQL Server provides relationship functionality that can be used to maintain data integrity.
Data Integrity means that all related tables data must have correct key value.
Take example of Header & Line tables. Header & Line have one to many relation with the key is Header ID. So each Header ID can have one or more records in Line table.
Please see screen shots:
tb_header Design:

tb_line Design:

Every header ID value inside Line table must exist in Header table. If there is header ID in Line table that does not exist in Header table then that data is junk.
Continue reading

SSRS 2008 R2 Programmatically List Running Reports/Jobs & Cancel Examples

SSRS 2008 R2 does not have pre built List Running Reports in Web format that comes from installation. Unlike the older version: SSRS 2005, we have to use SQL Server Management Studio (SSMS) to see SSRS 2008 R2 running reports / jobs .

The lack of this pre built web format is kind of missing in my day to day monitoring because of SSRS 2008 R2 List running jobs view in SSMS shows only the report name in the first look.
I have to look at each jobs properties to see more details which means more clicks are required.

The below screen shot shows the older version: SSRS 2005 list running Reports:

Steps to show SSRS 2008 R2 list running Reports with help of SSMS are:

  1. Open SSMS 2008 and connect to Reporting Service server.
    Set Server Type field to Reporting Service. Server Name set to Url http://<myserveripaddress>/reportserver. Replace the <myserveripaddress> to your report server ip address.
    Authentication, UserName and Passwd depend to your environment. If your localhost is Reporting Service server then you can use Window Auth.
    Otherwise if the server is a remote server then use basic/form auth. Anyway just try it.
  2. Open Jobs folder to see running Reports. Refresh it to see current jobs.
  3. Double click to see more details at each job
  4. Cancel Job(s) by right click job and click ‘Cancel Job(s)’

Alternatively, we can build our own list running reports/jobs tool. SSRS has a built in class to show running Reports programmatically. The class is ReportingService2010 for SSRS 2008 R2. This class is accessed via web service. The Url path of this webservice is http://<myserveripaddress>/ReportServer/ReportService2010.asmx.
In this blog post, I give you examples of programmatic SSRS 2008 R2 List running jobs.
Continue reading

Accessing Database Record in Sql Server Reporting Service (SSRS) Custom Code

SSRS can use Custom Code to have more flexible way to achieve complex tasks like calculation, conditional result, and also accessing records on database.

Custom Code use System.Data Class to work with database which means read a records. This class have to be registered in SSRS config file and also have to be referenced in Report file.

In this blog post I will show you on how to read sql server record using SSRS Custom Code. I use SQL Server 2005 Express Edition and the database is AdventureWorks sample database.
I will list all Product Category with count number of product in specific category. I use Custom Code to calculate how many of product in category.

Here’s how to make Database enabled SSRS Custom Code:
Continue reading

Easy Step by Step SSRS Parameter Validation Using Code & Conditional DataSet

At A Glance

Parameter Validation on web application is almost must all the time. Usually developer use javascript or server side script validation.

However, although SQL Server Reporting Service (SSRS) is a web app but as a default it does not have parameter (user input) validation. We have to write a Code at Report Properties Menu and implement it on Report Parameter.

The SSRS Parameter Validation mechanism must check if parameters do not meet specific criteria then related sql query is not running.

I use SSRS 2005 and AdventureWorks database to demonstrate creating this validation. I will show Purchase Order info based on Order Date.
Parameters are range of Order Date (Order Date From & Order Date To) but the range must not exceed 365 days or 1 year.
Continue reading