updating ps

I like to update PoweShell at regular intervals (on my Servers/Laptop), so that I have the latest updated files.  I add a couple of steps to a SQL server job that runs each quarter.

Just like a lot of people; I like Star Trek.  I thought it would be fun to incorporate a couple of fun wav files from the original series (I was a big fan).  I do not do this on my Servers (Production or otherwise), there is no need for sound.  This is strictly for my Laptop.  For Servers, I omit the Powershell PlaySync function.  Download a small wav file of your favorite sound, and Geek-out whenever this job runs.

I am sure there are better/different ways but, this is how I like to do it:

---

STEP 1:  Plays a wav file letting you know the process is about to begin

powershell.exe -c (New-Object media.soundplayer "C:\some_path\st_init_upd.WAV").PlaySync();

 

STEP 2:  Updates PowerShell file(s)

Update-Help -Force -ea 0

 

STEP 3:  Writes results out to txt file, then plays a wav file stating the process is complete

 Get-ChildItem -Path C:\Windows\System32\WindowsPowerShell\v1.0 -Recurse | Where-Object { $_.LastWriteTime.ToShortDateString() -eq (Get-Date).ToShortDateString() } powershell.exe -c (New-Object media.soundplayer "C:\Users\knowu\Documents\Visual Studio 2015\Projects\data_flow\data_flow\st_prog_comp.WAV").PlaySync();

---

Here is the t-sql script for the whole job…

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’dm_upd_powershell’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’init_upd_sound’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’PowerShell’,
@command=N’powershell.exe -c (New-Object media.soundplayer “C:\some_path\st_init_upd.WAV”).PlaySync();’,
@database_name=N’master’,
@flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’update_help’,
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’PowerShell’,
@command=N’Update-Help -Force -ea 0′,
@database_name=N’master’,
@flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’get_last_mod_date’,
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’PowerShell’,
@command=N’Get-ChildItem -Path C:\Windows\System32\WindowsPowerShell\v1.0 -Recurse | Where-Object {
$_.LastWriteTime.ToShortDateString() -eq (Get-Date).ToShortDateString()
}
powershell.exe -c (New-Object media.soundplayer “C:\some_path\st_prog_comp.WAV”).PlaySync();’,
@database_name=N’master’,
@output_file_name=N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ps_date_last_mod_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt’,
@flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’weekly’,
@enabled=1,
@freq_type=8,
@freq_interval=8,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=1,
@freq_recurrence_factor=1,
@active_start_date=20170101,
@active_end_date=99991231,
@active_start_time=72000,
@active_end_time=235959,
@schedule_uid=N’29af86ee-8471-48a7-8fda-023184d5afc5′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO