TSQL : Which protocols are being used by active connections?

-- what protocols are being used by active connections

SELECT DISTINCT net_library FROM sysprocesses

SELECT DISTINCT net_transport FROM sys.dm_exec_connections

TSQL : Fetch Service Account name

TSQL to get the SQL Server service account.

DECLARE @MSSqlService VARCHAR(50)
EXECUTE master.dbo.xp_instance_regread
 N'HKEY_LOCAL_MACHINE'
,N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
,N'ObjectName'
,@MSSqlService OUTPUT
,N'no_output'
SELECT @MSSqlService AS SQL_Service_Account

TSQL : TOP N PER GROUP using RANK()

An Adventureworks quick script to demonstrate top items in a group. It isn't the query I wrote today, but it is one I can publish here!

-- Sales Order ID, Name and Price for the 3 most expensive items in each order
-- We join to a derived table utilising the RANK function to order the items in each order by expense.
-- The WHERE clause then restricts the results according to the output of the RANK function
SELECT 
  S.SalesOrderID
 ,P.Name
 ,D.UnitPrice
FROM Sales.SalesOrderHeader S
INNER JOIN
 (SELECT 
  RANK() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) ItemCount
  ,* 
  FROM Sales.SalesOrderDetail)  D
ON S.SalesOrderID = D.SalesOrderID
INNER JOIN Production.Product P
ON P.ProductId = D.ProductId
WHERE ItemCount <= 3
ORDER BY SalesOrderID, Name, UnitPrice 

Solace : Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

Luke Hayler : Rediscovering RANK

Windows Scheduled Tasks - Command Line Options

Delete that annoying task that keeps being recreated -

SCHTASKS /Delete /TN "Annoying Scheduled Application" /F

The rest of the options can be displayed like this -

H:\>SCHTASKS /?

SCHTASKS /parameter [arguments]

Description:
    Enables an administrator to create, delete, query, change, run and
    end scheduled tasks on a local or remote system. Replaces AT.exe.

Parameter List:
    /Create         Creates a new scheduled task.

    /Delete         Deletes the scheduled task(s).

    /Query          Displays all scheduled tasks.

    /Change         Changes the properties of scheduled task.

    /Run            Runs the scheduled task immediately.

    /End            Stops the currently running scheduled task.

    /?              Displays this help/usage.

Examples:
    SCHTASKS
    SCHTASKS /?
    SCHTASKS /Run /?
    SCHTASKS /End /?
    SCHTASKS /Create /?
    SCHTASKS /Delete /?
    SCHTASKS /Query  /?
    SCHTASKS /Change /?

Wait Stats (Quick Summary)

In SQL 2000 - 

DBCC SQLPERF(waitstats)

In SQL 2005+ 

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms  
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

To clear Wait Stats -

dbcc sqlperf(waitstats,clear)

Some you can safely ignore -

CLR_SEMAPHORE
LAZYWRITER_SLEEP
RESOURCE_QUEUE
SLEEP_TASK
SLEEP_SYSTEMTASK
SQLTRACE_BUFFER_FLUSH
WAITFOR
LOGMGR_QUEUE
CHECKPOINT_QUEUE
REQUEST_FOR_DEADLOCK_SEARCH
XE_TIMER_EVENT
BROKER_TO_FLUSH
BROKER_TASK_STOP
CLR_MANUAL_EVENT
CLR_AUTO_EVENT
DISPATCHER_QUEUE_SEMAPHORE
FT_IFTS_SCHEDULER_IDLE_WAIT
XE_DISPATCHER_WAIT
XE_DISPATCHER_JOIN
BROKER_EVENTHANDLER
TRACEWRITE
FT_IFTSHC_MUTEX
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SLEEP

Management Studio Error : mscorlib.tlb could not be loaded

For testing purposes, I've got a VM with SQL 2000, 2005 & 2008 all as named instances. Yes I know it sucks performance wise but it is only for testing deployment scripts!

Anyway, after installing it and applying the various service packs, I got this error :

mscorlib.tlb could not be loaded

The 'Microsoft Fix it 50701' hotfix downloadble here did not work for me, but scroll down that page and you'll find instructions to modify the registry. It points Management Studio at the correct version of the .NET framework. These did work, the only problem being I'm reportedly going to have to do this each time I patch SQL :(