Hi all,

I need to execute PowerShell script on a server via command from a remote server running CA7 job. The ExecutionPolicy is set to RemoteSigned and I can execute script locally just fine, but when I try executing with CA7 job I get this error:

File X:\MyFolder\MyScript.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" fo r more details. At line:1 char:39 + &{D:\MyFolder\MyScript.ps1 <<<< param1 param2} + CategoryInfo : NotSpecified: (:) [], PSSecurityException + FullyQualifiedErrorId :


RuntimeException param1 param2 are parameters CA7 job is passing to my script. I even tried setting ExecutionPolicy to Unrestricted with no success.

Any suggestions are appresiated.

If you have a 64-bit server, make sure to set the execution policy for both the 32-bit and 64-bit instances of PowerShell.

Marco, it is x64 bit, do you mean in the PowerShell.exe on C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe ?

Yes, that's the 32-bit instance. Change the exec policy there also and retry/retest.

I think I should be able to specify to run the powershell from C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe instead of C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe , correct?

invoke ot like so:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe X:\MyFolder\MyScript.ps1 "param1" "param2"

another starnge thing I am expiencing is it looks like 2 shells are not the same: 32bit in C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe doesn't understand TrimEnd method

I get [System.Object[]] doesn't contain a method named 'TrimEnd'. error

64bit has no problem with that

Can you be more specific? This works with both for me:
"foo".trimend()

This doesn't, as expected:
("foo","bar").trimend()

yes, so I have a script that calls the SQL procedure to back up sql databases through Sqlcmd. I executed it just fine manually from the 64bit shell C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

It just seems that things that work in x64 do not work in 32 bit. When trying to call it via CA7 job from the reomote server I initially got ExecutionPolicy errors. Then you advised to set the ExecutionPolicy in 32 bit C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe, and that got rid of the access errors, but other errors appeared.

1. Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version
So I commented out 2 SQL snap-ins
#add-pssnapin SqlServerProviderSnapin100
#add-pssnapin SqlServerCmdletSnapin100

2. Method invocation failed because [System.Object[]] doesn't contain a method named 'TrimEnd'.

I did not have issues with it in x64 shell, but here I needed to prefix [string] before the variable like this:


[string]$P= Sqlcmd -E -d"MyDB" -S "$Servername" -h-1 -Q "set nocount on; select value from dbo.BackupDefaults where name = 'DataPath';"
$Path = $p.TrimEnd()



I am pasting the whole script here for you:

-------------------


#if (!(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin100'}))
#{
#add-pssnapin SqlServerProviderSnapin100
#add-pssnapin SqlServerCmdletSnapin100
#}

$Servername=$args[0]
$BackupGroup=$args[1]

if (!$args[0])
{
write-host "Invalid or missing sql server (Argument 1)"
exit
}

if (!$args[1])
{
write-host "Invalid or missing BackupGroup (Argument 2)"
exit
}


# QUERY 1 FOR DataPath

[string]$P= Sqlcmd -E -d"MyDB" -S "$Servername" -h-1 -Q "set nocount on; select value from dbo.MyTable where name = 'DataPath';"
$Path = $p.TrimEnd()



# QUERY 2 TO EXECUTE BACKUP

[string]$networkpath="\\" + $Servername + "\" + $Path.Replace(":\","$\")


# Create Log file name using Server instance
[string]$logFile = $networkpath + "DBBKP_" + $Servername.Replace("\","_") + ".log"


# Create backup timestamp, using PowerShell Get-Date to format a datetime string
[string]$backupTS = Get-Date -Format "yyyy-MM-dd hh:mm:ss"
"Backing up files on $Servername at $backupTS" | Out-File -filepath $logFile -encoding oem



Sqlcmd -E -d"DBAUtils" -S "$Servername" -Q "EXEC dbo.usp_BackupProc '$BackupGroup';" -OutputSqlErrors $true | Out-File -filepath $logFile -encoding oem -append

Sorry, I can't look at this in detail. Some snapins were built as 32-bit only, like Exchange 2007, for example. SQL (sqlps.exe) may also be 32-bit only.

Now, you may need to understand the different between a string object and a collection of strings.

When you get an error referring to [string[]] not having a Trim() method, you're variable or command is actually returning a collection of strings, and that method is not supported with a colleciton.

You'd have to look at the contents of the variable and also see what type it is.

Posted By BikeBoy on 09 Feb 2010 10:34 AM
another starnge thing I am expiencing is it looks like 2 shells are not the same: 32bit in C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe doesn't understand TrimEnd method

I get [System.Object[]] doesn't contain a method named 'TrimEnd'. error

64bit has no problem with that

As Marco pointed out you have a command that is returning a single string value on some cases and returning an array of strings in others. If you can find that command you can enclose it with

@()

to always get an array. Then you can iterate over the array and call the TrimEnd() method on the elements in the array.
My guess is that the Sqlcmd command returned a single string during testing and is now returning multiple strings. If you're goal is to process each string separately then you should force an array out of the command:

[string[]]$P= @(Sqlcmd -E -d"MyDB" -S "$Servername" -h-1 -Q "set nocount on; select value from dbo.BackupDefaults where name = 'DataPath';")
$Path = @($p | where {$_} | foreach { $_.TrimEnd() })

This will give you an array in $Path. If you are truly expecting only one result then you need to determine why you are getting multiple lines of output from Sqlcmd.