2

I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the same code it fails.

And the reason it fails is because it doesn't manage to add a few snap-ins, like the one below or include some assemblies.

Add-PSSnapin SqlServerCmdletSnapin100

This leads me to believe that SQL Server has a internal version of Powershell installed with which it runs the Powershell jobs. And that specific internal version of Powershell is older and does not have the required assemblies / snap-ins and thus fails.

Now, is there any way I can use the Powershell version installed on my machine and not the one that's built into SQL Server?

I've looked over "Proxies" in SQL Server but I don't think they're what I'm looking for.

Any help is appreciated!

  • 4
    You are correct, SQL uses an internal version of powershell. You will need to use a command line job step and run the powershell.exe and point to the .ps1 file you want to execute. Make sure to set your execution policy correctly. – Jonathan Fite Aug 30 '16 at 14:14
  • Why not use the scheduler in Windows instead of using SQL Server Agent to do operating system stuff? – Aaron Bertrand Aug 30 '16 at 14:51
  • @AaronBertrand I'd prefer to and also suggested to use the Windows scheduler, but it's a... "requirement", which is sometimes harder to fight than magically building a solution. – Radu Gheorghiu Aug 30 '16 at 15:17
  • 2
    @RaduGheorghiu, You want to use a SQL Agent job, but specify a cmdexec job step. Then the command is going to be "Powershell -noprofile -executionpolicy bypass -file "C:\scripts\script.ps1". Where you have saved the powershell script you want to run in "C:\scripts\script.ps1". msdn.microsoft.com/en-us/library/ms190264.aspx – Jonathan Fite Aug 30 '16 at 15:29
  • 2
    @JonathanFite, consider posting your cmdexec solution as an answer along with a job create script example. – Dan Guzman Aug 31 '16 at 10:36
2

As requested, here is an example of the process. The step should look like this, though

Job Step Command Set the step to be a cmdexec job and then run powershell through that. It lets you use the latest version of powershell as well as configure the powershell environment for that user (if you want). The step command will look like the one below, complete with arguments for the example. Remember to put quotes around strings that contain spaces.

Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance 'localhost\jdf2016' -SourceDatabaseName 'AdventureWorks' -TargetSQLInstance 'localhost\jdf2016' -TargetDatabaseName 'AdventureWorks2'

Complete Job

USE [msdb]
GO

/****** Object:  Job [DailyRestore_ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/16/2017 9:00:51 AM ******/
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'DailyRestore_ADWorks', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Daily restore AD Works from backup', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name='XXXXX\JFite', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore ADWorks', 
        @step_id=1, 
        @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'CmdExec', 
        @command=N'Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance ''localhost\jdf2016'' -SourceDatabaseName ''AdventureWorks'' -TargetSQLInstance ''localhost\jdf2016'' -TargetDatabaseName ''AdventureWorks2''', 
        @flags=0
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_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
  • Wow, this is way more than I expected! Excelent! I was using the interface from SSMS, but this is much better. Thank you once again! – Radu Gheorghiu Feb 16 '17 at 15:05
  • and thanks for the prompting to finally make this a real answer. – Jonathan Fite Feb 16 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.