GO, Go, go

GO

that’s how we tell SQL Server to  execute query or batch of tsql statements.

However GO is not TSQL statement. it’s just command recognized by SQL server utilities like SSMS, SQLCMD, OSQL that signals it is end of batch and it is time to execute the code.

If you want to execute your batch few times for example when you populate table you can  use GO and integer to execute batch that many times.

GO 5

 

go5

As you can see in picture above it executes everything in the batch, including variable declaration. That’s why we got NULL as result 5 times.

You shouldn’t include GO when executing commands using ODBC or OLE DB drivers because you will get an error during code execution.

 

 

 

MS documentation says to not do that as in result you’re going see  error:

Incorrect syntax near 'GO'

Reason for that is that SQL Server parser doesn’t recognize GO as this is not part of the TSQL syntax.

 

Problems with DAC

Recently I wasn’t able to connect to one of my servers due to high CPU usage on the machine. So I tried to connect using DAC (Dedicated Admin Connection) and here is lesson I knew but forgotten (hence this post): Do not open DAC connection in object explorer or you’ll see (in SQL Server 2008 R2):

 
  ===================================

Cannot connect to ADMIN: mySqlServer

===================================

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
 Program Location:

at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
 at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
 at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
 

Which is not helpful when you know very well that you had it working

Reason for that is that Object Explorer is opening more than one connection and therefore DAC won’t work as it is meant to be lightweight single connection tool with minimal resources use connection for troubleshooting issues when you can’t connect other way.

SQL Server 2016 is much more friendly and  will give you more information:

===================================

Cannot connect to admin: mySqlServer2016.

===================================

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So solution for this issue is really simple and I am going to remember it now. Just connect using File-> New ->Database Engine Query and connect with:

ADMIN: mySQLServer

Another problem that may occur is that connection is already taken. This may happen if you (of course not) or your colleague (of course) connected to DAC and forgot to close the session afterwards.

For such situation I am have scheduled job that runs every 12hrs and kills any session using DAC endpoint

Code snippet:

If exists (
select session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection'))

begin

declare @Session2 int

select @Session2 = session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection')
declare @sqlkill nvarchar (24)

set @sqlkill = N'kill ' + CAST(@Session2 as nvarchar)

execute sp_executesql @sqlkill

end