SSMS ISSUE: Connection information does not change in SSMS when a new Query is created from Dedicated Administrator Connection

Description:
Connection information does not change in SQL Server Management Studio for SQL Server 2008 r2 when a new Query is created from Dedicated Administrator Connection.

Steps:
1. Open SQL Server management studio and connect to SQL Server in Dedicated Administrator Connection.
In order to connect to SQL Server in Dedicated Administrator Connection (DAC) you have to add ADMIN: before the SERVER\INSTANCE name.

Dedicated Administrator Connection

2. After connecting the the Server with DAC click on new query.

3. You will get the error that you cannot connect as you are already connected to the server with DAC and only one DAC Connection is allowed.

[text]
TITLE: Connect to Database Engine
——————————

Cannot connect to ADMIN:..

——————————
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 – An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10053&LinkId=20476

——————————
BUTTONS:

OK
——————————
[/text]

4. Now in the error message popup click OK and change the connection to any other SQL Server.

5. Now you will find that the connection details displayed when you keep the mouse pointer over the SQL Server Query are still showing that of the DAC.

BUG SSMS DAC

How To View Data in Hidden System Tables.

SQL Server has System base tables which are hidden cannot be directly queried.

For example:

[sql]
SELECT * FROM sys.sysrscols
[/sql]

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the server\instance in SQL Server Management studio.

Dedicated Administrator Connection

Now you can query all the System tables. 🙂

[sql]

——————————————————————————————————————————————————
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO
[/sql]

Impact of TEMPDB Collations on Other Databases

When you create databases with different collations then you have to be very careful when using the temporary tables.

In the below script let us analyze such a scenario.

Creating Sample database with case sensitive collation
[sql]
CREATE DATABASE TESTCOLLATION
COLLATE SQL_Latin1_General_CP1_CS_AS ;
GO
[/sql]

Let us create a sample table in a database and insert data.
[sql]
USE TESTCOLLATION
GO

CREATE TABLE TestTable ( NAME VARCHAR(100))
GO

INSERT INTO TestTable
SELECT ‘TestName’

GO

[/sql]

In the below queries let us query the table.
[sql]
USE TESTCOLLATION
GO
SELECT * FROM TestTable

SELECT * FROM TestTable where NAME = ‘TestName’

SELECT * FROM TestTable where NAME = ‘testname’

GO
[/sql]

output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)
[/text]

It can be found that the third select statement above does not return any rows as the database and the columns in the table are with case sensitive collation.

Now let us create a temp table with same data as above and check the results.

[sql]
USE TESTCOLLATION
GO

CREATE TABLE #TestTable ( NAME VARCHAR(100))
GO

INSERT INTO #TestTable
SELECT ‘TestName’

GO

SELECT * FROM #TestTable

SELECT * FROM #TestTable where NAME = ‘TestName’

SELECT * FROM #TestTable where NAME = ‘testname’
[/sql]

Output:
[text]

(1 row(s) affected)
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

[/text]

It can be found that the third select statement above gives ‘TestName’ as output.

So when we go with an assumption that the temporary tables have the same collation as the database in which it is created then the code would fail.

Resolution:
[sql]
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘TestName’

SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘testname’
[/sql]

Output:
[text]
NAME
—————————————————————————————————-
TestName

(1 row(s) affected)

NAME
—————————————————————————————————-

(0 row(s) affected)

[/text]

Cleanup:
[sql]
DROP DATABASE TESTCOLLATION
GO
[/sql]