Link to home
Start Free TrialLog in
Avatar of Dee M
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.
Avatar of HainKurt
HainKurt
Flag of Canada image

Via SQL Agent JOB: error

how? any code?
Avatar of Dee M
Dee M

ASKER

SL "C:\Policy\Powershell\"
.\EPM_EnterpriseEvaluation_412.ps1 -ConfigurationGroup "prod" -PolicyCategoryFilter "Microsoft Best Practices: Performance" –EvalMode “Check”

Via POWERSHELL: successful
User generated image

Via SQL AGENT JOB
User generated image
Message
Executed as user: prod\svc_dev_aws_sql1. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102). The step failed.
User generated image
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)"
}

Open in new window


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

Powershell.exe -executionpolicy remotesigned -File  C:\Users\SE\Desktop\myPSFile.ps1

Open in new window


then run bat file
Avatar of Dee M

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?
Avatar of Dee M

ASKER

you are not passing any parameters, so create a bat file

Powershell.exe -executionpolicy remotesigned -File  C:\Users\SE\Desktop\myPSFile.ps1


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
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
Avatar of Dee M

ASKER

Is this correct?
User generated image
Executed as user: sa\user1. Incorrect syntax near 'executionpolicy'. [SQLSTATE 42000] (Error 102).  The step failed.

I got an error
try wrapping it by "..."
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Why are you trying to run a Powershell script from SQL Server?
Anyway, you must change the job's step type to Operating System as shown below:
User generated image
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...
Avatar of Dee M

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 :)
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 :)
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.
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...
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 :)
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...

 for example they are against materialized views / triggers as well...
 or scared to implement partitions :)
Wow. And those are not even new things. Maybe it's just lack of knowledge from them?
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...