Search code examples
sql-serverpowershellsqlcmd

SQL Server using Powershell - Error: the 'Invoke-SqlCmd' command was found in the module 'SQLPS', but the module could not be loaded

I'm encountering an error when trying to create a database and also to execute other queries in SQL Server using PowerShell's Invoke-SqlCmd command.

Environment:

  • Windows Version: 10.0.19045
  • PowerShell Version: 5.1.19041.4780

Powershell command used:

Invoke-SqlCmd -ServerInstance "localhost\sqlexpress" 
              -Query "CREATE DATABASE dummy_db"

Error:

Invoke-SqlCmd : The 'Invoke-SqlCmd' command was found in the module 'SQLPS', but the module could not be loaded. For more information, run 'Import-Module SQLPS'.

At line:1 char:1

  • Invoke-SqlCmd -ServerInstance "localhost\sqlexpress" -Query "CREATE D ...
  • CategoryInfo : ObjectNotFound: (Invoke-SqlCmd:String) [], CommandNotFoundException
  • FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

Outside Powershell:

  • I can successfully execute queries directly from the command prompt using sqlcmd.
  • I can also manage the database using third-party tools like HeidiSQL with no issues.
  • I attempted to troubleshoot using a similar Stack Overflow thread (URL: Powershell Invoke-SQLCmd and SQLPS) but haven't found a solution.

What additional troubleshooting steps can I take to resolve this error? Is there any step I missed?

Solution

  • As explained in the comments, this error occurs because the PowerShell execution policy is set to a restricted mode, preventing the execution of scripts. So I tried changing the execution policy with the command: Set-ExecutionPolicy RemoteSigned -Scope CurrentUser, and finally the Invoke-SqlCmd command could be executed successfully.

    Thanks

    DeeKey