1

I'm trying to execute SQL Query from Powershell.

My Script:

Invoke-Sqlcmd -Query "select 'Y' from sys.databases" -ServerInstance .

Error :

Invoke-Sqlcmd : The type initializer for 'Microsoft.Data.SqlClient.InOutOfProcHelper' threw an exception.
At C:\temp\Untitled5.ps1:2 char:1
+ Invoke-Sqlcmd -Query "select 'Y' from sys.databases" -ServerInstance  ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidResult: (.:PSObject) [Invoke-Sqlcmd], TypeInitializationException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Thom A
  • 88,727
  • 11
  • 45
  • 75
Prakash
  • 23
  • 4
  • https://stackoverflow.com/q/6922879/5894421 – J4GD33P 51NGH Apr 24 '23 at 08:10
  • Check log files. See my response here : https://stackoverflow.com/questions/76090242/sql-collection-working-in-ise-but-not-with-powershell-exe – jdweng Apr 24 '23 at 09:16
  • Could be any number of things from client authentication to server authentication. A more detailed exception with stack trace would be helpful. Given you seem to be in PowerShell 7+ as the exception mentions `Microsoft.Data.SqlClient` does adding `-Encrypt None` to command change things? What if you change the query to `select 'Y' as [SomeColumnNameHere] from sys.databases`? – AlwaysLearning Apr 24 '23 at 12:19

1 Answers1

0

For me, it was because the version of the module installed was certainly buggy.

Install-Module sqlserver installed the 21.1.1 ; I had to uninstall it and reinstall the 21.1.18256 :

Uninstall-Module -Name SqlServer
Install-Module SqlServer -RequiredVersion 21.1.18256 -Force

All is OK now...

(to know the actual version of your installed modules, use Get-InstalledModule)

fred727
  • 2,644
  • 1
  • 20
  • 16