Archive for November, 2015

Check if you have a disconnected remote desktop session

November 16, 2015

1. open up powershell
2. QUERY USER username /server:servername

“No user exists for username”

will displayed if you do not have a disconnected rdp

Error enumerating session names, the rpc server is unavailable will be displayed if the servername you entered doesnt exist

create partition scheme strings

November 13, 2015

SELECT
i.object_id,OBJECT_NAME(i.object_id) table_name,
QUOTENAME(s.name) + ‘(‘ + z1.name + ‘)’ partition_scheme
from sys.indexes i
join sys.partition_schemes s on i.data_space_id = s.data_space_id
JOIN sys.index_columns z ON z.index_id = i.index_id AND z.object_id = i.object_id
JOIN sys.columns z1 ON z1.object_id = z.object_id AND z1.column_id = z.column_id

Another example of importing extended event data

November 4, 2015

SET QUOTED_IDENTIFIER ON

IF (OBJECT_ID(‘tempdb..#staging’)) IS NOT NULL DROP TABLE #staging

SELECT q.statement_text,q.client_hostname,q.client_app_name,timestamp
INTO #staging
FROM (
SELECT statement_text=e.event_data_XML.value(‘(//data[@name=”statement”]/value)[1]’,’varchar(max)’)
, client_hostname=e.event_data_XML.value(‘(//action[@name=”client_hostname”]/value)[1]’,’varchar(max)’)
, client_app_name=e.event_data_XML.value(‘(//action[@name=”client_app_name”]/value)[1]’,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]’,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘Z:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\trace*.xel’, NULL, NULL, NULL)

)e
)q

IF OBJECT_ID(‘database_name.dbo.readonly_trace’) IS NULL

SELECT TOP 0 *
INTO database_name.dbo.readonly_trace
FROM #staging

INSERT INTO database_name.dbo.readonly_trace
SELECT b.* FROM database_name.dbo.readonly_trace a
RIGHT JOIN #staging b ON a.timestamp = b.TIMESTAMP
WHERE a.timestamp IS NULL