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

Advertisements

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