Create windows service using powershell

Shamelessly pilfered from The Powershell Guy.  I’ve used it before with no issue, but that doesn’t mean you won’t have one.  Use at your own risk.

$computer = "." # this computer
$class = "Win32_Service"
$method = "Create"
$mc = [wmiclass]"\\$computer\ROOT\CIMV2:$class"
$inparams = $mc.PSBase.GetMethodParameters($method)
$inparams.DesktopInteract = $false
$inparams.DisplayName = "WebDirs Trigger Command"
$inparams.ErrorControl = 0
$inparams.LoadOrderGroup = $null
$inparams.LoadOrderGroupDependencies = $null
$inparams.Name = "Service Name"  #
$inparams.PathName = "Path to .exe"
$inparams.ServiceDependencies = $null
$inparams.ServiceType = 16
$inparams.StartMode = "Automatic"
$inparams.StartName = $null # will start as localsystem builtin if null
$inparams.StartPassword = $null

$result = $mc.PSBase.InvokeMethod($method,$inparams,$null)
$result | Format-List

Extended Event to Capture Error Messages

Ripped with wonton abandon from Jay.  

CREATE EVENT SESSION CaptureErrors
ON SERVER
ADD EVENT sqlserver.error_reported(
	ACTION (
		sqlserver.sql_text,
		sqlserver.database_id,
		sqlserver.client_hostname,
		sqlserver.username,
		sqlserver.nt_username,
		sqlserver.tsql_stack
	)
WHERE 
severity > 10 and severity < 20  /*anything over20 is captured by System Health Event*/ )
ADD TARGET package0.asynchronous_file_target(
	SET FILENAME = N'U:\CaptureErrors.xel', 
	METADATAFILE = N'U:\CaptureErrors.xem'
)
WITH (max_dispatch_latency = 5 seconds);
GO

ALTER EVENT SESSION CaptureErrors ON SERVER
STATE = START
GO

DROP EVENT SESSION CaptureErrors ON SERVER
GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER EVENT SESSION CaptureErrors ON SERVER
STATE = STOP
GO

IF OBJECT_ID('tempdb..#ErrorData') IS NOT NULL
BEGIN
	DROP TABLE #ErrorData
END

CREATE TABLE #ErrorData(
	event_data xml
)

INSERT INTO #ErrorData (event_data)
SELECT  CAST(event_data as XML) 
FROM sys.fn_xe_file_target_read_file('U:\CaptureErrors*.xel', 'U:\CaptureErrors*.xem', NULL, NULL);

SELECT
	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
	event_data.value('(event/@package)[1]', 'varchar(50)') AS package_name,
	event_data.value('(event/@id)[1]', 'varchar(50)') AS ID,
	event_data.value('(event/@version)[1]', 'varchar(50)') AS Version,
	DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
	event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
	event_data.value('(event/data[@name="error"]/value)[1]', 'int') as ErrorNumber,
	event_data.value('(event/data[@name="severity"]/value)[1]', 'smallint')  AS Severity,
	event_data.value('(event/data[@name="state"]/value)[1]', 'tinyint')  AS [State],
	event_data.value('(event/data[@name="user_defined"]/value)[1]', 'nvarchar(100)') as User_Defined,
	event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(250)') as [Message],
	event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') as sql_text,
	event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') as DatabaseID,
	event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(4000)') as client_hostname,
	event_data.value('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(4000)') as nt_username,
	event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(4000)') as username,
	event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') as tsql_stack
FROM #ErrorData
ORDER BY [timestamp]