Session Announcement: New functions in SQL Server 2012


Come and join me on 29th June, 2013 [Saturday] @ C# Corner Chapter Meet. This is the first ever C# Corner Chapter event being organized at Chandigarh “The City Beautiful”.

I shall be taking a session on New Functions in SQL Server 2012. There would be interesting session on jQuery and WCF conducted by my fellow speakers Anil Kumar and Raghavendra respectively.



Date: 29th June,2013 [Saturday]

Hotel Windsor
Mansa Devi Complex, Sector-4,
(Near Rajiv Gandhi IT Park, Chandigarh)
Panchkula 134114
Locate on Google Map

This event is sponsored by:
SSS & Educational Society ,Gorakhpur and C#Corner.


SQL Server 2014 CTP1: Download available

Finally!! SQL Server 2014 has been released after the recent announcements earlier this month. Community Technology Preview 1 [CTP1] is available for download, link below.

Download >> SQL Server 2014 CTP1

This release has been targeted around In-Memory computing capabilities along with Cloud Computing.

Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) installation limitations:

  • Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
  • To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
  • The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
  • The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
  • The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
  • The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
  • The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
  • The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.

Upgrade to SQL 2012 using detach and attach commands

Today we shall try to see how to upgrade database from SQL Server 2008 R2 to SQL Server 2012 using Detach and Attach T-SQL commands.

Let’s first create a sample database say “DB_SQL2008R2” using SQL 2008 and create a test table (with some values)

USE master

-- creating sample datbase


-- creating test table with values
CREATE TABLE Test_table (col1 VARCHAR(10), col2 VARCHAR(10));
INSERT INTO dbo.Test_table ( col1, col2 )

Take note of the data file and log file created and the location (this is required as we are going to attach these files to SQL Server 2012 later). In my test server, they are created at:
Data File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf
Log File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf

How do I get the data and log file name?

Go to “Object Explorer” window in Sql Server management studio. Expand the database folder and look for “DB_SQL2008R2”. Right-click and choose “Properties”. Database properties window will open. Select the “Files” page on the left. On the right, you will find two files DB_SQL2008R2 and DB_SQL2008R2_log. The location of these files is mentioned under the “Path” column header.

So, we are all set for upgrade!

First, step is to detach the database. Open SSMS for SQL Server 2008R2 and execute the following code snippet to detach the database

USE master;

EXEC sp_detach_db @dbname = N'SQLTraining';

Now login to SSMS for SQL Server 2012 and create the database using the data and log files we have taken not of earlier.

USE master;

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf'),
   (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf')

So finally, we have been successful in upgrading a database from SQL Server 2008R2 to SQL Server 2012

For further reading please refer MSDN: Upgrade a Database Using Detach and Attach (Transact-SQL)

On the way to “SQL Server 2014”

Whilst, we are still exploring SQL Server 2012, Microsoft has announced features of SQL Server 2014.  With most fingers in Cloud (Cloud computing), the the new product combines together to deliver “In-the-box” in-memory capabilities, hybrid cloud capabilities for mission critical applications and permits faster data insights.

One of the core components of SQL Server 2014 is built to make most of “Cloud computing”. This helps in scalability (nearly infinite) and also reduces the computational and storage cost by many folds.

In-memory computing has been in the market for some time now, “In-memory in the box” takes the game to next level. Unlike other in-memory products which require separate dedicated software and hardware; SQL Server 2014 comes with Hekaton’s in-memory OLTP which is delivered in the box with the product.

Here are few links to explore more on the product..

ERROR:17892 Database login issue due to logon-trigger

I was working with Logon triggers and was stuck-up in a very interesting issue. Tried to create one trigger named “Logon_trigger” which writes auditing information in audit table in DB named “TestDB”. Once I finished my testing, I dropped the database [TestDB] without dropping the trigger. Unfortunately when I tried to connect to SSMS after that, I got the following error:


Thought of connecting thru DAC to drop the trigger and rectify the issue. Interestingly, was not able to connect thru DAC and got the following error:

DAC error

Here is how I rectified the issue:
Connected to the SQL Server Configuration Manger: Start >> All Programs >> SQL Server 2008 R2 >> Configuration Tools >> SQL Server Configuration Manager
Added a trace flag to enable DAC (-T7806), as shown in the screen below and then re-started the service:


Tried to connect using DAC again and execute below commands:


Connected using SSMS successfully this time:-)

Length of replicated LOB data exceeds configured maximum

We had set-up peer-to-peer replication on three geographically distinct nodes with around 800 articles being published with data type of lot many columns as varchar(max), image and text data. Once the replication was up and running, users reported this error “ Length of LOB data (70199) to be replicated exceeds configured maximum 65536”.

Reason for this issue: Default SQL Server configuration supports “Max text replication size” of 65536 bytes (2 power 16 bytes) for text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data columns. We get this exception when in a single insert or update statement data size for any of the replicated text column violates this limit.

Solution: There are two ways to check and rectify this issue. Once using T-SQL and another using SSMS

Using T-SQL
Check the server configuration for current max text replication size

EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size'; 

-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536

Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.

-- For SQL Server 2005
EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size', 2147483647 ; 

-- For SQL Server 2008 (and 2008 R2)
EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size', -1 ; 

-1 indicates that there is no limit other than imposed by the data type itself. Furthermore, max data replication option applies to transactional replication and CDC (Change Data Capture) and is ignored for snapshot replication and merge replication.

Using SSMS

  1. Open SSMS and connect to object explorer
  2. Right-click on the server name (in explorer) and choose properties
  3. Select “Advanced” options on the properties page.
  4. Max text replication Size is available under “Miscellaneous” header.
  5. Change the value from 65536 to -1 or 2147483647 (depending on the version on SQL Server) and press OK.


List jobs residing on a particular database

Below query become very handy when it is required to list all the jobs residing on a specific database.

DECLARE @db_name VARCHAR(100);

SET @db_name = 'My_Database'; -- Change your database name here

SELECT database_name [Database Name],
 name [Job Name],
 js.job_id [Job ID]
FROM msdb.dbo.sysjobsteps js
 INNER JOIN msdb.dbo.sysjobs_view jv
 ON js.job_id = jv.job_id
WHERE database_name = @db_name;

If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.

SELECT DISTINCT database_name [Database Name],
                name          [Job Name],
                js.job_id     [Job ID]
FROM   msdb.dbo.sysjobsteps js
       INNER JOIN msdb.dbo.sysjobs_view jv
               ON js.job_id = jv.job_id
ORDER  BY database_name;