question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked MrFlinstone-1451 answered

Error with powershell workflows and invoke-sqlcmd

I am testing a powershell script when executed from a deployment server. The deployment server is ansible and this calls powershell. The powershell script is as follows.

workflow Test-ParallelForeachSQL
{
param
(
[String[]]
$instances
)

foreach -parallel -throttlelimit 8 ($instance in $instances)
{
"Begin $instance"
Invoke-SQLCMD -ServerInstance $instance -database "master" -query "select @@servername"
"End $instance"
}
}


Test-ParallelForeachSQL -instance "SQLServer,1433"


I get the error

Cannot find drive. A drive with the name 'SQLSERVER' does not exists
+ CategoryInfo: ObjectNotFound: (SQLSERVER:String){} ParentContainsErrorRecordException
+ FullyQualifiedErrorID: DriveNotFound
+ PSComputerName: Localhost

Placing the invoke-sqlcmd command in an inline script simply makes the process stall. It starts and never completes within ansible tower.

Any ideas for this behaviour.




windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered

Hi,

It seems the SQLSERVER PSDrive cannot be found. You can try importing the SQLPS module manually. Please see if this works.

 Push-Location
 Import-Module SQLPS
 Pop-Location
 workflow Test-ParallelForeachSQL
 {
     param
     (
     [String[]]
     $instances
     )
    
     foreach -parallel -throttlelimit 8 ($instance in $instances)
     {
         "Begin $instance"
         Invoke-SQLCMD -ServerInstance $instance -database "master" -query "select @@servername"
         "End $instance"
     }
 }
 Test-ParallelForeachSQL -instance "SQLServer,1433"

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 answered IanXue-MSFT commented

Tried this, its still thesame error.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you see the SQLSERVER drive when you run Get-PSDrive?

0 Votes 0 ·

when i run Get-PSDrive, _I get the below.

117387-image.png


0 Votes 0 ·
image.png (28.3 KiB)

So the SQLSERVER drive still cannot be found after importing the SQLPS module? Is there any error message when you run the below lines?

  Push-Location
  Import-Module SQLPS
  Pop-Location



0 Votes 0 ·
MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 answered

when I run the below from PS.
Push-Location
Import-Module SQLPS
Pop-Location

I get an error saying that SQLPS cannot be loaded because running scripts is disabled on the system.

What It hen did was to store contents in a file and use the following powershell.exe -ep Bypass .\script.ps1

Then I can see the SQLSERVER drive.

When I go back to the script, add the Import-module SQLPS inside an inline block within the workflow. I can get it to work on a PS console screen. however when I run it from the batch scheduling tool, it just runs forever without exiting.

I went ahead to place print statements to see where it was getting stuck, I found that it was getting stuck as soon as it got to invoke-sqlcmd if executed from the automation tool (ansible). IF executed from the console, it works fine.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered

Hi,

The error message means the execution policy is Restricted. You can try setting it to RemoteSigned/Bypass using Set-ExecutionPolicy.

 Set-ExecutionPolicy RemoteSigned

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 answered

Hello.

I tried this and still made no difference, as the script file was being called with the bypass option, this will override an execution policy of remote signed. Its a really strange one because I do not know of any other way to run scripts in parallel in the manner in which I am running it, i.e pass a list of SQL instances to a script and let it loop through the list.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.