Cannot execute .PS1 file from Powershell within SSMS

Question

I found a script in another post in StackOverflow which appears like it will do exactly what I need, however, I'm getting an error when attempting to execute the .ps1 file type from PowerShell within SSMS.

The environment is SQL Server 2014.

My FindBrokenObjectsInSQLServer.ps1 file is defined as:

$server = "APPDEV2014"
cls
Import-Module "sqlps" -DisableNameChecking

$databases = Invoke-Sqlcmd -Query "select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')" -ServerInstance $server
foreach ($db in $databases) {
    $dbName = $db.name
    $procedures = Invoke-Sqlcmd -Query "select SCHEMA_NAME(schema_id) as [schema], name from $dbName.sys.procedures" -ServerInstance $server
    foreach ($proc in $procedures) {
        if ($schema) {
            $shortName = $proc.schema + "." + $proc.name
            $procName =  $db.name + "." + $shortName
            try {
                $result = Invoke-Sqlcmd -Database $dbName -Query "sys.sp_refreshsqlmodule '$shortName'" -ServerInstance $server -ErrorAction Stop
                Write-Host "SUCCESS|$procName"
            }
            catch {
                $msg = $_.Exception.Message.Replace([Environment]::NewLine, ",")
                Write-Host "FAILED|$procName|$msg" -ForegroundColor Yellow
            }
        }
    }
}

The command I'm executing is:

PS SQLSERVER:\SQL\APPDEV2014\DEFAULT> PowerShell -command 
"H:\SQLScripts\FindBrokenObjectsInSQLServer.ps1"

Finally the error I'm getting is:

Import-Module : A positional parameter cannot be found that accepts argument
'?'.
At H:\SQLScripts\FindBrokenObjectsInSQLServer.ps1:3 char:1
+ Import-Module "sqlps"? -DisableNameChecking
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Import-Module], ParameterB
   indingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell
   .Commands.ImportModuleCommand

Any suggestions/direction would be appreciated. This is my first attempt at working with PowerShell. Thanks.

You are already at a PowerShell prompt (see the PS at the beginning of the prompt), so you don't need to type PowerShell -command .... Just type the name of the script you want to run, followed by its parameters, and press Enter. — Bill_Stewart
– Bill_Stewart, Commented Mar 24, 2016 at 18:55

JamesQMurphy · Accepted Answer · 2016-03-26 14:14:19Z

1

Check that you saved the script correctly. It looks like there's an extra question mark on the line that contains Import-Module. Look carefully at the line that it is complaining about:

+ Import-Module "sqlps"? -DisableNameChecking

Notice the extra question mark after the "sqlps" parameter? Delete it.

answered Mar 26, 2016 at 14:14

JamesQMurphy

4,5172 gold badges42 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Kuperstein Over a year ago

From what the OP posted in the original script, that extra '?' isn't there. Its thrown in the exception handler annotating the positional parameter that it doesn't accept. Once I read into it, it looks like it can't resolve the name of the module, which means its probably not on the machine.

JamesQMurphy Over a year ago

Not true for three reasons: 1. The error explicitly states that it cannot find positional parameter '?'. 2. If the module were not present, the error would be "The specified module was not loaded." 3. The OP's PowerShell command shows the current drive as SQLSERVER:, which is a dead giveaway that the SQLPS module is, in fact, installed.

Chris Kuperstein Over a year ago

good catch. I was continuing forward with what was only provided in the original example, from what you found in the exact error does show the OP may have put the character in.

Chris Kuperstein · Accepted Answer · 2016-03-24 23:15:40Z

0

You're trying to import a module that more than likely does not exist on your machine because its native to SQL Server 2016, but not native to SQL server 2014.

You may need to install SQL Server 2014 Feature Pack SP1, which can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=46696

The same errors occur when trying to do things like

Import-Module ActiveDirectory

when RSAT tools are not installed and enabled under windows-features.

answered Mar 24, 2016 at 23:15

Chris Kuperstein

6764 silver badges8 bronze badges

1 Comment

Melinda Over a year ago

Thanks for the additional information Chris. It's appreciated.

Collectives™ on Stack Overflow

Cannot execute .PS1 file from Powershell within SSMS

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related