How to run Powershell script as job in SQL Server

May 24, 2010 at 9:02 PM

I'm new in Powershell. Recently I installed Powershell 2.0 and then CodePlex SQL Server Powershell extension. Everything works fine when I ran interactively. But when I schedule a DTS as SQL Sever job it fails with the following error:

Executed as user: MHPNT\mhpsqlservice. File J:\eventviewer\GetSystemLog.ps1 cannot be loaded because the execution of   scripts is disabled on this system. Please see "get-help about_signing" for mor  e details.  At line:1 char:2  + & <<<<  'j:\eventviewer\GetSystemLog.ps1'      + CategoryInfo          : NotSpecified: (:) [], PSSecurityException      + FullyQualifiedErrorId : RuntimeExceptionDTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_4   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_4   DTSRun OnStart:  DTSStep_DTSCreateProcessTask_1   DTSRun OnError:  DTSStep_DTSCreateProcessTask_1, Error = -2147220330 (80040496)      Error string:  CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code 1, which does not match the specified SuccessReturnCode of 0.      Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts80.hlp      Help context:  4900      Error Detail Records:      Error:  -2147220330 (80040496); Provider Error:  0 (0)      Error string:  CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code 1, which does not match the specified SuccessReturnCode of 0.      Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts80.hlp      Help context:  4900      DTSRun OnFinish:  DTSStep_DTSCreateProcessTask_1   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed.

Within the DTS, one of the task was "Execute Process Task" which executes: powershell.exe -command "& { J:\eventviewer\GetSystemLog.ps1}"

Based on the error, I thought I need to add "set-executionpolicy unrestricted" to the profile, so I added it to the profile which is located at C:\WINDOWS\system32\windowspowershell\v1.0. But it didn't help and I still get the error "....File J:\eventviewer\GetSystemLog.ps1 cannot be loaded because the execution of   scripts is disabled on this system. Please see "get-help about_signing" for mor  e details." in the job history.

I also checked execution policy using get-executionPolicy and it returns "unrestricted".

Can someone help me: why the script fails when I run as SQL Server job ?

FYI, I'm running on Windows Server 2003 64-bit SP2 and SQL Server 2008 Enterprise Edition (64-bit) RTM.

Thanks in advance,

Jimmy

 

May 28, 2010 at 9:23 PM

I found out why it was failing:

when the job is running, it runs in the context of the account that starts the SQL Server Agent Service. So I decided to remote desktop to the server where the job is located and run it from there with -noexit just to check the execution policy. When it comes to the task that runs Powershell script, it kicks off a command-line window with the error that I mentioned. Then, I checked the execution policy, it was "resctricted". So, while still in the command-line window, I changed the execution policy to "RemoteSigned" and then exit it. After this change, I re-ran the job from Management Studio from my PC (instead of remote desktop to the server) and it ran successfully.

Jimmy