Dee M
asked on
Powershell error using sql agent job
Hi I am executing
SL "C:\Policy\Powershell\"
.\EPM_EnterpriseEvaluation _412.ps1 -ConfigurationGroup "prod" -PolicyCategoryFilter "Microsoft Best Practices: Performance" –EvalMode “Check”
Via POWERSHELL: successful
Via SQL Agent JOB: error
Message
Executed as user: sa\user1. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
SL "C:\Policy\Powershell\"
.\EPM_EnterpriseEvaluation
Via POWERSHELL: successful
Via SQL Agent JOB: error
Message
Executed as user: sa\user1. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
ASKER
SL "C:\Policy\Powershell\"
.\EPM_EnterpriseEvaluation _412.ps1 -ConfigurationGroup "prod" -PolicyCategoryFilter "Microsoft Best Practices: Performance" –EvalMode “Check”
Via POWERSHELL: successful
Via SQL AGENT JOB
Message
Executed as user: prod\svc_dev_aws_sql1. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
Can anyone help me what's wrong with the syntax???
I have setup policy base management(powershell script and policies) I followed and go from https://epmframework.codeplex.com/releases/view/619917
Refer below to the powershell code I have used:
I'm setting up Policy Based Management on SQL
.\EPM_EnterpriseEvaluation
Via POWERSHELL: successful
Via SQL AGENT JOB
Message
Executed as user: prod\svc_dev_aws_sql1. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
Can anyone help me what's wrong with the syntax???
I have setup policy base management(powershell script and policies) I followed and go from https://epmframework.codeplex.com/releases/view/619917
Refer below to the powershell code I have used:
# Evaluate specific Policies against a Server List
# Uses the Invoke-PolicyEvaluation Cmdlet
# v4.12
param([string]$ConfigurationGroup=$(Throw `
"Parameter missing: -ConfigurationGroup ConfigGroup"),`
[string]$PolicyCategoryFilter=$(Throw "Parameter missing: `
-PolicyCategoryFilter Category"), `
[string]$EvalMode=$(Throw "Parameter missing: -EvalMode EvalMode"))
# Parameter -ConfigurationGroup specifies the
# Central Management Server group to evaluate
# Parameter -PolicyCategoryFilter specifies the
# category of policies to evaluate
# Parameter -EvalMode accepts "Check" to report policy
# results, "Configure" to reconfigure any violations
# Declare variables to define the central warehouse
# in which to write the output, store the policies
$CentralManagementServer = "SERVER1/INSTANCE1 "
$HistoryDatabase = "Policy"
# Define the location to write the results of the policy evaluation
$ResultDir = "C:\Policy\Results\"
# End of variables
# Function to load assemblies and Snapins - http://msdn.microsoft.com/en-us/library/hh245202.aspx
function LoadAssemblies()
{
$Error.Clear()
$assemblylist = "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.SmoExtended", "Microsoft.SqlServer.Dmf", "Microsoft.SqlServer.Facets", "Microsoft.SqlServer.Management.RegisteredServers", "Microsoft.SqlServer.Management.Sdk.Sfc", "Microsoft.SqlServer.Management.Collector", "Microsoft.SqlServer.Management.CollectorEnum"
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Loading Assemblies"
}
foreach ($assembly in $assemblylist)
{
$assembly = [Reflection.Assembly]::LoadWithPartialName($assembly)
}
$sqlVerText = "SELECT CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff)"
$TestVer = (Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Query $sqlVerText -QueryTimeout 65535)
if ($TestVer.ItemArray[0] -eq 10)
{
# Load SqlServerProviderSnapin120
if (-not (Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin120'}))
{
if (Get-PSSnapin -registered | ?{$_.name -ne 'SqlServerProviderSnapin120'})
{
Add-PSSnapin SqlServerProviderSnapin120 | Out-Null
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Loading SqlServerProviderSnapin120 in session"
}
}
}
else
{
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " SqlServerProviderSnapin120 already loaded"
}
}
# Load SqlServerCmdletSnapin100
if (-not (Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'}))
{
if (Get-PSSnapin -registered | ?{$_.name -ne 'SqlServerCmdletSnapin100'})
{
Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Loading SqlServerCmdletSnapin100 in session"
}
}
}
else
{
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " SqlServerCmdletSnapin100 already loaded"
}
}
}
}
# Function to load module
function LoadModule($modname)
{
$Error.Clear()
if (-not(${env:programfiles(x86)}))
{
$PrgFilePath = ${env:programfiles}.ToString()
}
else
{
$PrgFilePath = ${env:programfiles(x86)}.ToString()
}
$env:PSModulePath = "$PrgFilePath\Microsoft SQL Server\120\Tools\PowerShell\Modules\;" + $env:PSModulePath
if (-not(Get-Module -Name $modname))
{
if (Get-Module -ListAvailable | Where-Object { $_.name -eq $modname })
{
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Loading SQLPS module"
}
try
{
Import-Module -Name $modname –DisableNameChecking -Force -WarningAction SilentlyContinue -ErrorAction Stop
}
catch
{
if ($Error -notlike "A drive with the name *SQLSERVER* already exists.")
{
Write-Host $Error
}
}
}
}
else
{
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " SQLPS module already loaded"
}
}
}
#Function to insert policy evaluation results into SQL Server - table policy.PolicyHistory
function PolicyHistoryInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResults)
{
$sqlQueryText = "INSERT INTO policy.PolicyHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES (N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResults')"
try
{
Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -QueryTimeout 65535 -ErrorAction Stop
}
catch
{
$ExceptionText = $_.Exception.Message -replace "'", ""
return $ExceptionText
}
}
#Function to insert policy evaluation errors into SQL Server - table policy.EvaluationErrorHistory
function PolicyErrorInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResultsEscape)
{
$sqlQueryText = "INSERT INTO policy.EvaluationErrorHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResultsEscape')"
try
{
Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -QueryTimeout 65535 -ErrorAction Stop
}
catch
{
$ExceptionText = $_.Exception.Message -replace "'", ""
return $ExceptionText
}
}
#Function to delete files from this policy only
function PolicyFileDelete($File)
{
# Delete evaluation files in the directory.
try
{
Remove-Item -Path $File -Force
}
catch
{
$ExceptionText = $_.Exception.Message -replace "'", ""
return $ExceptionText
continue
}
}
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "EPM Framework v4.1.2"
Write-Host -ForegroundColor green "Starting policy category evaluation - $(Get-Date -Format G)"
}
# Load Assemblies
LoadAssemblies
LoadModule -modname "SQLPS"
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Connecting to the policy store"
}
# Connection to the policy store
$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$CentralManagementServer;Trusted_Connection=true")
$PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn)
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Get list of servers to evaluate"
}
# Create recordset of servers to evaluate
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true")
$q = "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].[pfn_ServerGroupInstances]('$ConfigurationGroup');"
$sconn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn)
$cmd.CommandTimeout = 0
$dr = $cmd.ExecuteReader()
# Handle PS4 or above
if ($PSVersionTable.PSVersion.Major -ge 4)
{
if ($CentralManagementServer -like "*\*")
{
sl "SQLSERVER:\SQLPolicy\$CentralManagementServer\Policies"
}
else
{
sl "SQLSERVER:\SQLPolicy\$CentralManagementServer\DEFAULT\Policies"
}
}
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Starting server loop"
}
# Loop through the servers and then loop through
# the policies. For each server and policy,
# call cmdlet to evaluate policy on server and delete xml file afterwards
while ($dr.Read()) {
$ServerName = $dr.GetValue(0);
foreach ($Policy in $PolicyStore.Policies)
{
if (($Policy.PolicyCategory -eq $PolicyCategoryFilter) -or ($PolicyCategoryFilter -eq ""))
{
# Remove illegal characters in file names
$PolicyName = $Policy.Name
# Done in several lines for v2 compatibility
$PolicyName = $PolicyName -replace "\\", ""
$PolicyName = $PolicyName -replace "\/", ""
$PolicyName = $PolicyName -replace "\?", ""
$PolicyName = $PolicyName -replace "\:", ""
$PolicyName = $PolicyName -replace "\*", ""
$PolicyName = $PolicyName -replace "\<", ""
$PolicyName = $PolicyName -replace "\>", ""
$PolicyName = $PolicyName -replace " ", ""
$OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $ServerName), ($PolicyName))
if (-not ($OutputFile))
{
$ServerName = $ServerName -replace "\\", "_"
$OutputFile = $ResultDir + ("{0}_{1}.xml" -f ($ServerName), ($PolicyName))
}
try
{
if ($PSVersionTable.PSVersion.Major -ge 4)
{
Get-ChildItem | Where-Object {$_.Name -eq $Policy.Name} | Invoke-PolicyEvaluation -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile
}
else
{
Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile
}
$PolicyResult = Get-Content $OutputFile -encoding UTF8
$PolicyResult = $PolicyResult -replace "'", ""
PolicyHistoryInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $PolicyResult
$File = $ResultDir + ("*_{0}.xml" -f ($PolicyName))
PolicyFileDelete $File
}
catch
{
$File = $ResultDir + ("*_{0}.xml" -f ($PolicyName))
PolicyFileDelete $File
$ExceptionText = $_.Exception.Message -replace "'", ""
$ExceptionMessage = $_.Exception.GetType().FullName + ", " + $ExceptionText
PolicyErrorInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $ExceptionMessage
continue
}
}
}
}
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green " Finished server loop"
}
$dr.Close()
$sconn.Close()
#Shred the XML results to PolicyHistoryDetails
Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query "EXEC policy.epm_LoadPolicyHistoryDetail `$(PolicyCategory)" -Variable "PolicyCategory='${PolicyCategoryFilter}'" -QueryTimeout 65535 -Verbose -ErrorAction Stop
if ($host.Name -eq "ConsoleHost")
{
Write-Host -ForegroundColor green "Finished policy category evaluation - $(Get-Date -Format G)"
}
I'm setting up Policy Based Management on SQL
how do you run it via PS?
you are not passing any parameters, so create a bat file
then run bat file
Powershell.exe -executionpolicy remotesigned -File C:\Users\SE\Desktop\myPSFile.ps1
then run bat file
ASKER
It was put on SQL Job so I can schedule it, the agent job should call the powershell, however, i don't know why it's having error to make sure nothing's wrong with the powershell so when I executed it directly it's just working fine but confused why on the agent job it is showing error. Is there something wrong in the syntax?
ASKER
you are not passing any parameters, so create a bat file
Powershell.exe -executionpolicy remotesigned -File C:\Users\SE\Desktop\myPSFi le.ps1
Where should I create this? will it be separate from the powershell script?
Powershell.exe -executionpolicy remotesigned -File C:\Users\SE\Desktop\myPSFi
Where should I create this? will it be separate from the powershell script?
sorry just call this
Powershell.exe -executionpolicy remotesigned -File myPSFile.ps1
or create a bat and put above inside and run
myBatFile.bat
Powershell.exe -executionpolicy remotesigned -File myPSFile.ps1
or create a bat and put above inside and run
myBatFile.bat
I see you have a file @ ID: 42130176
save it as myPSFile.ps1 and then call
Powershell.exe -executionpolicy remotesigned -File myPSFile.ps1
from sql job
save it as myPSFile.ps1 and then call
Powershell.exe -executionpolicy remotesigned -File myPSFile.ps1
from sql job
ASKER
try wrapping it by "..."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why are you trying to run a Powershell script from SQL Server?
why not? PS is like a bat file and SQL agent supports PS commands :)
instead of creating a bat file that calls PS with a PS file supplied to PS, we can just run PS commands this way...
ASKER
It worked changd to powershell
why not? PS is like a bat file and SQL agent supports PS commands :)Isn't about if technically he can do it or not but security issues. SQL Server should be restricted to databases tasks. For example, as a DBA I never let SQL Server to send emails. Applications should do that my connecting to an email server instead of using SQL Server.
The same for CLR code, running OS commands, etc...
what about if we have only one machine and need to do those stuff from sql :)
like our DBA! He hates dblinks and tries to force us to join tables on client memory...
DB1
select * from table1
DB2
select * from table2
do a join in memory to find the required data :)
like our DBA! He hates dblinks and tries to force us to join tables on client memory...
DB1
select * from table1
DB2
select * from table2
do a join in memory to find the required data :)
Do your company only owns a single machine? :O
Nope, but we have lots of DB servers and we need to sync data by running a join query to find differences / new / old records
He refuses to create db link and says do it on your app :)
with db link it is taking 3 seconds a few lines of code... but the other way is taking minutes/hours :)
He refuses to create db link and says do it on your app :)
with db link it is taking 3 seconds a few lines of code... but the other way is taking minutes/hours :)
Understood.
Well, I don't have nothing against Linked Servers. In fact we use them a lot. Only rule we have is to not create linked servers between Production and Development or Test environments and that's for data protection.
Well, I don't have nothing against Linked Servers. In fact we use them a lot. Only rule we have is to not create linked servers between Production and Development or Test environments and that's for data protection.
and for my own project, on amazon / AWS Cloud, I have a single machine for SQL server and I prefer to do send email from db via triggers :)
perfectly fine for me... not everybody has money/resource to separate all things to different servers...
perfectly fine for me... not everybody has money/resource to separate all things to different servers...
in our case dev/uat is separated from prod environment...
these dba's are really scared of trying something new ha ha...
for example they are against materialized views / triggers as well...
or scared to implement partitions :)
these dba's are really scared of trying something new ha ha...
for example they are against materialized views / triggers as well...
or scared to implement partitions :)
In the email case it shouldn't be a money issue as I'm expecting that every company has their own mail server so it should be a matter of using it instead of SQL Server.
these dba's are really scared of trying something new ha ha...Wow. And those are not even new things. Maybe it's just lack of knowledge from them?
for example they are against materialized views / triggers as well...
or scared to implement partitions :)
they are really stubborn :)
they dont want to change any damn thing and forces developers to stick to 10 years of old aging rules ha ha...
they dont want to change any damn thing and forces developers to stick to 10 years of old aging rules ha ha...
how? any code?