A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database else the changes will be rolled back. As we all know we use the following blocks to specify the start and end of the transaction.

Begin Tran –> Starting point
Commit Tran –> Ending point

An example for the same is given below.

Begin Tran
insert into Employee Values(1,’Roopesh’,’PL’,’IT’)
insert into Employee Values(2,’Lokesh’,’PM’,’IT’)
Commit Tran

In the above statement the TWO insert queries will be considered as ONE unit of work i.e transaction. Now let’s see what is Explicit Transactions.

Marking the boundaries of a transaction explicitly is called as Explicit transactions. You can skip the Begin block by setting the below given property to ON and these are called as Implicit transactions.

set IMPLICIT_TRANSACTIONS on

So, to write the Explicit transactions we need to set Implicit_Transactions property to OFF using the below given query.

set IMPLICIT_TRANSACTIONS off

When this option is off, you do have to specify the BEGIN TRAN statement to mark the beginning of a transaction and mark the transaction’s end with a COMMIT TRAN or a ROLLBACK TRAN statement. This is called as Explicit Transactions. An example for the same is given below.

Begin Tran
insert into Employee Values(3,’Roopesh’,’PL’,’IT’)
insert into Employee Values(4,’Lokesh’,’PM’,’IT’)
Commit Transaction

You can see in the above query the BEGIN TRAN is used to tell the starting point of transaction. PFB the image showing the same.

In short, Explicit transactions are the ones for which the starting point of the transaction need to be mentioned. To activate the same you have to set Implicit_Transactions property to OFF as shown below.

set IMPLICIT_TRANSACTIONS off

That’s it guys !! Happie coding !!

Regards,
Roopesh Babu V

Implicit Transactions in Sql Server

By

roopesh.valluru

-

February 19, 2013

68

Friends,

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database else the changes will be rolled back. As we all know we use the following blocks to specify the start and end of the transaction.

Begin Tran –> Starting point
Commit Tran –> Ending point

An example for the same is given below.
Begin Tran
insert into Employee Values(1,’Roopesh’,’PL’,’IT’)
insert into Employee Values(2,’Lokesh’,’PM’,’IT’)
Commit Tran

In the above statement the TWO insert queries will be considered as ONE unit of work i.e transaction. Now let’s see what is Implicit Transactions.

If you do not mark the boundaries of a transaction explicitly, by default, SQL Server treats each individual statement as a transaction; in other words, by default, SQL Server automatically commits the transaction at the end of each individual statement. You can change the way SQL Server handles implicit transactions with a session option called IMPLICIT_TRANSACTIONS. This option will be set to OFF by default and we can change it to ON by using the following query.

set IMPLICIT_TRANSACTIONS on

When this option is on, you do not have to specify the BEGIN TRAN statement to mark the beginning of a transaction, but you have to mark the transaction’s end with a COMMIT TRAN or a ROLLBACK TRAN statement. This is called as Implicit Transactions. An example for the same is given below.

insert into Employee Values(3,’Roopesh’,’PL’,’IT’)
insert into Employee Values(4,’Lokesh’,’PM’,’IT’)
Commit Transaction

You can see in the above query the BEGIN TRAN is not used to tell the starting point of transaction and if you execute the same with out Begin Tran command it works just as fine as normal query with Begin Tran command. PFB the image showing the same.

In short, Implicit transactions are the ones for which the starting point of the transaction need not to be mentioned. To activate the same you have to set Implicit_Transactions property to ON as shown below.

set IMPLICIT_TRANSACTIONS on

That’s it guys !! Happie coding !!

Regards,
Roopesh Babu V

How to find active open transactions in SQL Server 2008R2

By

roopesh.valluru

-

February 19, 2013

39

Friends,

In this post we are gonna discuss about the way to find the OPEN and ACTIVE Transactions in Sql Server. To explain the same I created a table with the below given script.

Create table Employee
(
Emp_ID int,
Emp_Name Varchar(50),
Dept Varchar(50),
Desig Varchar(50)
)

To check the Open Transactions you can follow any one of the following ways.

select @@TRANCOUNT
DBCC OPENTRAN

Both the above Sql statements returns the Open transactions details. The first one gives you the count of OPEN transactions where as the second gives you the details of the open transaction. First let me execute the same and see whether any open transactions are available.

I am getting the following message when I execute DBCC OpenTran query.

“No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

and when I execute the second one i.e “select @@TRANCOUNT” statement.

From the above Pic and the Error Message, it is clear that there are NO ACTIVE OPEN transactions. Now let me execute the following query which OPENS the transaction but not Close.

Begin Tran
insert into Employee Values(1,’Roopesh’,’PL’,’IT’)
insert into Employee Values(2,’Lokesh’,’PM’,’IT’)

The query executed successfully and now lets execute the above commands to check the status of the open transactions.

You can see that the command is proving the open transaction details. Now let me execute the following command to Commit the transaction i.e Active.

“Commit tran”

Now if I execute the DBCC command again, you can see the following message which says that there are NO OPEN TRANSACTIONS.

“No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

This is how you can get the details about the open transactions and there is one more way i.e by using the following SYSTEM tables.

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_database_transactions
select * from sys.dm_tran_session_transactions

The above tables stores the information about ACTIVE Transactions,Database Transactions and Session Transactions respectively. You can make use of these queries to get MORE details about OPEN and ACTIVE transactions.

SELECT
trans.session_id as [Session ID],
trans.transaction_id as [Transaction ID],
tas.name as [Transaction Name],
tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas
INNER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
WHERE trans.is_user_transaction = 1 — user
AND tas.transaction_state = 2 — active
AND tds.database_transaction_begin_time IS NOT NULL

So .. That’s it and now you can track the open transactions easily .. 🙂

All the Best !!

Regards,
Roopesh Babu V

MSBI Training Part 45

By

roopesh.valluru

-

February 15, 2013

170

Friends,

This is the 45th video from the series of videos. In this video we are gonna discuss about the following topics –

Intersect
Except
Table Expressions
Derived Tables
Common Table Expressions
Non Recursive CTEs
Recursive CTEs

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

How to use XML Configuration file in SSIS

By

roopesh.valluru

-

February 14, 2013

5

Friends,

In the video attached, we are gonna discuss about “How to use XML Configuration file in SSIS” . If you are not able to view the Video then that means you don’t have ACCESS to YOUTUBE. Ask your manager to provide the same .. 😉

Regards,
Roopesh Babu V

MSBI Training Part 44

By

roopesh.valluru

-

February 14, 2013

1793

Friends,

This is the 44th video from the series of videos. In this video we are gonna discuss about the following topics –

Union
Union All
Select * into
Subqueries

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

MSBI Training Part 43

By

roopesh.valluru

-

February 12, 2013

14

Friends,

This is the 43th video from the series of videos. In this video we are gonna discuss about the following topics –

Select Query Logical Execution
Schemas
Joins

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

Using OFFSET N Rows Fetch Next N Rows in Sql server

By

roopesh.valluru

-

February 11, 2013

58

Friends,

Don’t get worried that you have never used or seen these functions in SQL SERVER. This is newly introduced in 2012 version i.e Denali. The major advantage of this function is PAGINATION. We can implement pagination at Sql Server level. Let’s See a simple example to demonstrate these functions.

Let’s say my requirement here is to get 25th record to 50th record from the table order by date. That means I have to skip first 25 rows and all the rows after 50th row. To achieve the same we generally use CTE(Common table expression) in earlier versions of Sql Server and see the sample query below.

WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY date) AS Row_Count,*
FROM tablename)
SELECT * FROM cte
WHERE Row_Count between 25 and 50

The above query Orders the data by DATE and creates a ROW_NUMBER for all the rows from which we are selecting ROWS with number between 25 and 50 which will fulfill the requirement. But, if the see the query, it’s bit complex and not a normal select statement. If you are not good in Sql Server CTE’s then you are gone case. PFB the query which achieves the same result with out using CTEs and by using OFFSET and FETCH in Sql Server Denali.

SELECT *
FROM tablename
ORDER BY date
OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;

The above query looks simple and easy to understand. The OFFSET function skips the # of rows specified and the FETCH next pulls the # of rows provided. If you wish to pull all the records except the top 25 then you can use the query given below.

SELECT *
FROM tablename
ORDER BY date
OFFSET 25 ROWS ;

If you wish to have a dynamic value to the query then you can do it by defining variables.

DECLARE @value int = 25
SELECT *
FROM tablename
ORDER BY date
OFFSET @Value ROWS FETCH NEXT @Value ROWS ONLY;

The advantage of the above method is that we can write Select Statement as per our wish and apply pagination with JUST one extra line of code.

Hope you understood the concept and don’t forget to use OFFSET and FETCH when you work on Denali to apply pagination.

All the Best !!

Regards,
Roopesh Babu V

MSBI Training Part 42

By

roopesh.valluru

-

February 10, 2013

14

Friends,

This is the 42th video from the series of videos. In this video we are gonna discuss about the following topics –

Deploying reports
Report Configuration Manager
Report Server
Report Manager
Snapshot Reports
Cached Reports
Schedules
Subscriptions
Data Driven Subscriptions
Site Setting
Query Timeout options

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

MSBI Training Part 41

By

roopesh.valluru

-

February 4, 2013

98

Friends,

This is the 41th video from the series of videos. In this video we are gonna discuss about the following topics –

Guages
Data Bar
Sparkle Line
Indicators

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

MSBI Training Part 40

By

roopesh.valluru

-

February 2, 2013

10

Friends,

This is the 40th video from the series of videos. In this video we are gonna discuss about the following topics –

List
Rectangle
Image
Drillthrough reports
Sub reports
Charts

Hope you understood the concepts and start browsing the next one ..

Regards,
Roopesh Babu V

Drill down in SSRS reports

By

roopesh.valluru

-

February 2, 2013

37

Friends,

In the video attached, we are gonna discuss about “Drill down feature to Matrix Report in SSRS” . If you are not able to view the Video then that means you don’t have ACCESS to YOUTUBE. Ask your manager to provide the same .. 😉

Regards,

Roopesh Babu V

Add alternating row color to Matrix Report in SSRS

By

roopesh.valluru

-

February 2, 2013

520

Friends,

In the video attached, we are gonna discuss about “Add alternating row color to Matrix Report in SSRS” . If you are not able to view the Video then that means you don’t have ACCESS to YOUTUBE. Ask your manager to provide the same .. 😉

Regards,
Roopesh Babu V

Add alternating row color to Tabular Report in SSRS

By

roopesh.valluru

-

February 2, 2013

7

Friends,

In the video attached, we are gonna discuss about “Add alternating row color to Tabular Report in SSRS” . If you are not able to view the Video then that means you don’t have ACCESS to YOUTUBE. Ask your manager to provide the same .. 😉

Regards,
Roopesh Babu V

Parent child hierarchy in SSRS with Sql Server Data Source

By

roopesh.valluru

-

February 2, 2013

31

Friends,

In the video attached, we are gonna discuss about “Implementing Parent child hierarchy in SSRS with Sql Server Data Source” . If you are not able to view the Video then that means you don’t have ACCESS to YOUTUBE. Ask your manager to provide the same .. 😉

Regards,
Roopesh Babu V