SQL Server Portal

I have been using sys.dm_exec_requests for quite a long time and I have developed some tools to store certain request information using this dynamic management view. In the SQL Server 2012, sys.dm_exec_requests has a breaking change, which I realized once I deployed my tools in SQL Server 2012 and I was unsuccessful. Let me explain why and how did I fixed it.
Given below is the script taken from one of the tools that is developed using sys.dm_exec_requests.

Let me explain it step by step.
Step 1 :
Lets create a table (On SQL Server 2008 and 2012) to demonstrate it.

use tempdb
GO
CREATE TABLE [dbo].[dm_exec_requests_temp](
[session_id] [smallint] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[blocking_session_id] [smallint] NULL,
[command] [nvarchar](16) NOT NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[transaction_id] [bigint] NOT NULL
) ON [PRIMARY]
GO
--OUTPUT

Command(s) completed successfully.

Step 2 :
Insert / Select records in the above temporary table in SQL Server 2005/2008.

Insert into dm_exec_requests_temp
SELECT session_id ,status ,blocking_session_id, command
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
GO
Select * from dm_exec_requests_temp
--OUTPUT

Step 3 :
Insert / Select records in the above temporary table in SQL Server 2012.

Insert into dm_exec_requests_temp
SELECT session_id ,status ,blocking_session_id, command
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
GO
Select * from dm_exec_requests_temp
--OUTPUT

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

Oooops……………
It generated an error.

Step 4:
The above script executed successfully in SQL Server 2005/2008 but failed in SQL Server 2012. The reason behind above error is sys.dm_exec_requests has a column namely command. In SQL Server 2005/2008 the data type of command column was nvarchar(16) but it has been changed to nvarchar(32) in SQL Server 2012. As you can see, I created the column command in temporary table with nvarchar(16) datatype.

Step 5:
To resolve above error, you need to change the data type of command column in temporary table (Step 1) from nvarchar(16) to nvarchar(32) and insert records again.

Alter table dbo.dm_exec_requests_temp Alter column [command] nvarchar(32)

Conclusion :
In SQL Server 2012, sys.dm_exec_requests has changed the data type of column “Command” from nvarchar(16) to nvarchar(32). Remember to implement this data type change effects to avoid such errors.

Reference : MSDN