Feeds:
Posts
Comments

Archive for the ‘Breaking Changes’ Category

sys.dm_os_memory_cache_counters is one of the dynmaic management views shipped with SQL Server 2005. The major benefits of this dynamic management view is to provide the list of summary stating how cache is allocated against each request.

Given below is a simple query that will give the details about cache.

--This query is compatibale with SQL Server 2005 and 2008 only.
Select [name],[type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

dm_os_memory_cache_counters1.1

A few days ago I tried using the same query in SQL Server 2012 to check out the summary of cache but the above query did not work. Given below is the error detail.

Select [name],[type]
,SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb] 
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘single_pages_kb’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘multi_pages_kb’.
sys.dm_os_memory_cache_counter1.2

Oooops……………
It generated an error. The first impression I got was that these two fields have been eliminated from sys.dm_os_memory_cache_counters in SQL Server 2012 ?

But the answer is No, these field are not eliminated but renamed. Given below are the details.


S.No


Previous Column Name


New Column Name

1

single_pages_kb

pages_kb

2

multi_pages_kb

pages_in_use_kb

Lets rename the column in the above query and execute it again in SQL Server 2012.

--This query is compatible with SQL Server 2012 and above.
Select [name],[type]
,SUM([pages_kb]) As [pages_kb]
,SUM([pages_in_use_kb]) As [pages_in_use_kb]
from sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

sys.dm_os_memory_cache_counter1.3

Conclusion :
In SQL Server 2012, sys.dm_os_memory_cache_counters has renamed the two columns single_pages_kb, multi_pages_kb to pages_kb, pages_in_use_kb respectively. Remember to implement this renamed column change effects to avoid such errors.

Reference : MSDN

Read Full Post »

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

sys.dm_exec_requests1.1

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

Read Full Post »

sys.dm_os_sys_info is one of the handy dynamic management views when it comes to query about the miscellaneous information of computer, resources & consumption by SQL Server. sys.dm_os_sys_info is with SQL Server family since SQL Server 2005, but whenever there is a new version of SQL Server, it always brings modification / additional helpful information in this view. But this modification / addition may  break your code if you don’t consider these changes with respect to versions. To avoid any such errors, I have prepared a comparison sheet of different versions of sys.dm_os_sys_info along with the changes to make life far easier.

Given below is the screen image of the comparison sheet. (Also attached Excel file for version 2007 & above):

sys.dm_os_sys_info.1.1

Reference : MSDN

Read Full Post »

Mostly, there are multiple ways to write a script to perform a single task. It varies from Developers to Developers, who normally try to follow the best practice. But sometimes some standards discard from time to time (version to version). In this article, I will go through some standard formats that we used in the prior version of SQL server but does not work in SQL Server 2012. It may break your script.

Let me create a sample in SQL Server 2005/ 2008 to explain it.

USE [test]
GO
CREATE TABLE [dbo].[t_Student](
	[Student ID] [int] IDENTITY(1,1) NOT NULL,
	[Student Name] [nvarchar](50) NULL
)

After creating the sample table lets alter this table. But there are three different formats that you can use to alter the table in earlier version of SQL server (2005/2008)


Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Command(s) completed successfully

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Command(s) completed successfully

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

All of the above methods executed successfully in SQL Server 2005/ 2008.

Lets browse the table.

Select * from t_Student

alter table 1.1

Now, lets do the same exercise in SQL Server 2012.

Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘.test.dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘..dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

Conclusion :
If you are planning to upgrade your SQL Server earlier version to SQL Server 2012 or you are using SQL Server 2012, remember, it will not allow you to use methods 1 & 2. In case your are using above methods 1 & 2 in Dynamic SQL or Static SQL, it may break your script.

Reference : MSDN

Read Full Post »