To change your execution policy, type:
Set-ExecutionPolicy -ExecutionPolicy <PolicyName>
For example:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted
Use case scenario:
As a DBA you need to routinely check databases in your SQLServer, name, size, and spaceavailable, indexspaceusage. Enter the following in powershell
Set-ExecutionPolicy -ExecutionPolicy <PolicyName>
For example:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted
Use case scenario:
As a DBA you need to routinely check databases in your SQLServer, name, size, and spaceavailable, indexspaceusage. Enter the following in powershell
Set-ExecutionPolicy -ExecutionPolicy Unrestricted
import-module sqlps -DisableNameChecking
Set-Location SQLSERVER:\SQL\GBI-7696M12\DEFAULT\DATABASES
Get-ChildItem | SELECT NAME, SIZE, SPACEAVAILABLE, INDEXSPACEUSAGE | Out-FILE 'C:\Users\petera\Documents\DB_SCRIPT\database3.txt'
notepad 'C:\Users\petera\Documents\DB_SCRIPT\database3.txt'
Windows Client
import-module sqlps -DisableNameChecking
Set-Location SQLSERVER:\SQL\GBI-7696M12\DEFAULT\DATABASES
Get-ChildItem | SELECT NAME, SIZE, SPACEAVAILABLE, INDEXSPACEUSAGE | Out-FILE 'C:\Users\petera\Documents\DB_SCRIPT\database3.txt'
notepad 'C:\Users\petera\Documents\DB_SCRIPT\database3.txt'
Windows Client
SQLPS used to work before sql update
- I have a few .ps1 files being called from sql jobs that used to work and now i'm getting an error when the SQLPS module tries to load. The error specifically says: "...job step recieved an error at line 1 in Powershell script. The corresponding line is 'import module SQLPS -DisableNameChecking."
The job step is running like this -
Start-Process PowerShell -ArgumentList "& 'C:\NewInstanceScripts\SPLA_Users.ps1'"
The contents of the .ps1 file are as follows. This and others like it work fine in ps direct. just not when called from a sql job.
#Pull all group members into csv file
Get-ADGroupMember “CRDS Users” | where {$_.objectclass-eq “user”} | Select SamAccountName | Export-csv -path C:\SPLA_Users.csv -NoTypeInformation
#Insert contents of csv into SQL table
$sqlsvr = 'SQL03'
$database = 'db_SPLA'
$table = 'tbl_CRDSUsers'
#Create SQL Connection
Write-Verbose "Creating SQL Connection"
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=$SQLSvr;Database=$Database;Integrated Security=True")
$conn.Open()
$cmd = $conn.CreateCommand()
#Create a delete statement if want to replace contents each time
#$cmd2 = $conn.CreateCommand()
#$cmd2.commandtext = "Delete From $table"
#Write-Host -Fore Green "Clean Table Contents"
#$cmd2.ExecuteNonQuery()
Import-Csv C:\SPLA_Users.csv | % {
#$_.Displayname
#$_.Version
#Create query string
#Must matching the table layout (SoftwareName, Version)
Write-Host -Fore Green "Inserting $_.SamAccountName into Table"
$cmd.CommandText = "INSERT INTO $table (SamAccountName,UploadDate) VALUES ('$($_.SamAccountName)', '$(get-date -f MM-dd-yyyy)')"
#Execute Query
$cmd.ExecuteNonQuery() | Out-Null
}
The only thing i changed was the version of SQL Server 2012- Edited by Dave_Odom Tuesday, June 03, 2014 1:52 PM
Tuesday, June 03, 2014 1:47 PM
(Partner)5 Points
Question
All replies
- Hi Dave,
Do you mean errors occured when calling a powershell script in SQL agent job?
I'm also a little confused about the error, because I can not find the cmdlet "import-module SQLPS -DisableNameChecking" in the .ps1 file you posted above.
Would you please post the hard code and its error information in detail, which will help us to troubleshoot?
I recommend you can run the .ps1 file in powershell before calling in SQL agent job, and check if the script has any error.
In addition, the script would get error, if there has UNC file path after running "import-Module 'sqlps'", for more detailed information, please check this thread:
Powershell from a SQL Agent Job (SQL Server 2012)
Best Regards,AnnaTechNet Community SupportWednesday, June 04, 2014 2:11 PM
18,205 PointsModerator - Yes, i have a sql agent job and that is what fails. the import-module is implicit in that ps tries to import the cmdlet without my intervention. I've tried adding to the .ps1 and get the same result.
I have run in ps direct and it works perfectly, no errors.
The next part of the error if i look at the job history details is "
Message
Executed as user: CLIENT\svc-sql03. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'File C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. File C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. '. Process Exit Code -1. The step failed."
I've run set-executionpolicy remotesigned on the SQL server on both the 64(C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe) and 32(C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe) bit powershell.exe (run as admin)Wednesday, June 04, 2014 7:07 PM (Partner)5 Points - Try this ... I think this will be the issue ..Check the registry for :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy=Unrestricted
for me the registry value wasn't there and i created the key and string value Microsoft.SqlServer.Management.PowerShell.sqlps110 and ExecutionPolicy=Unrestricted and it worked .
You may wanna check this link as well: http://sqlandy.com/2014/01/uninstalling-ssms-on-server-breaks-sql-powershell/Monday, October 13, 2014 10:09 AM
210 Points
No comments:
Post a Comment