Weird error "Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies"

Article
2020-05-12

Question

_{Tuesday, May 12, 2020 5:25 AM}

Hi,

I am running the following script:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.BatchParser')
....
Invoke-Sqlcmd -InputFile $InputFile -ServerInstance $LocalSQLInstance -Variable $StringArray -ErrorAction 'Stop' -Verbose -QueryTimeout 1800

As a result I get the following output:

GAC    Version        Location                                                                                                                                                                                                     *
*                                                                                                                                                                                                                 *
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Microsoft.SqlServer.BatchParser\v4.0_14.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll                                                                 *
Invoke-Sqlcmd : Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
At C:\TMP\MS-14-1.ps1:109 char:5
+     Invoke-Sqlcmd -InputFile $InputFile -ServerInstance $LocalSQLInstance -Varia ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : NotSpecified: (:) [Invoke-Sqlcmd], FileNotFoundException
*    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

What's wrong with my script? Why does it successfully find the right Microsoft.SqlServer.BatchParser.dll and then can't use it?

All replies (13)

_{Tuesday, May 12, 2020 5:35 AM}

There is no need to load that file to use Invoke-SqlCmd. The SqlServer module will load the correct file for you.

Be sure you have installed the latest version of the SqlServer Module

Update-Module SqlServer

The required assemblies will be autoloaded when the command is executed the first time.

\(ツ)_/

_{Tuesday, May 12, 2020 1:56 PM}

Ok, tried to issue Update-Module SqlServer in PowerShell ISE, got this:

PS C:\tmp> Update-Module SqlServer
Update-Module : The term 'Update-Module' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At line:1 char:1
+ Update-Module SqlServer
+ ~~~~~~~~~~~~~
* + CategoryInfo : ObjectNotFound: (Update-Module:String) [], CommandNotFoundException*
* + FullyQualifiedErrorId : CommandNotFoundException*

What did I do wrong?

_{Tuesday, May 12, 2020 1:58 PM}

You need to be running the current version of PowerShell. The SQLServer components won't work on old versions of Windows and PowerShell.

\(ツ)_/

_{Tuesday, May 12, 2020 6:07 PM}

Ok, I downloaded and installed PowerShell-6.2.4-win-x64.msi, ran PowerShell cmd line and got this:

Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.

PS C:\Users\adm> Update-Module SqlServer

NuGet provider is required to continue
PowerShellGet requires NuGet provider version '2.8.5.201' or newer to interact with NuGet-based repositories. The NuGet
* provider must be available in 'C:\Program Files\PackageManagement\ProviderAssemblies' or*
'C:\Users\adm\AppData\Local\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider by running
'Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want PowerShellGet to install and import
* the NuGet provider now?*
[Y] Yes [N] No [S] Suspend [?] Help (default is "Y"): Y
WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
WARNING: Unable to download the list of available providers. Check your internet connection.
PackageManagement\Install-PackageProvider : No match was found for the specified search criteria for the provider
'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package
has the tags.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7405 char:21
+ ...     $null = PackageManagement\Install-PackageProvider -Name $script:N ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : InvalidArgument: (Microsoft.Power...PackageProvider:InstallPackageProvider) [Install-Pac*
*   kageProvider], Exception*
*    + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackagePro*
*   vider*

PackageManagement\Import-PackageProvider : No match was found for the specified search criteria and provider name
'NuGet'. Try 'Get-PackageProvider -ListAvailable' to see if the provider exists on the system.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7411 char:21
+ ...     $null = PackageManagement\Import-PackageProvider -Name $script:Nu ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : InvalidData: (NuGet:String) [Import-PackageProvider], Exception*
*    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.ImportPackageProv*
*   ider*

WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
WARNING: Unable to download the list of available providers. Check your internet connection.
PackageManagement\Get-PackageProvider : Unable to find package provider 'NuGet'. It may not be imported yet. Try
'Get-PackageProvider -ListAvailable'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7415 char:30
+ ... tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : ObjectNotFound: (Microsoft.Power...PackageProvider:GetPackageProvider) [Get-PackageProvi*
*   der], Exception*
*    + FullyQualifiedErrorId : UnknownProviderFromActivatedList,Microsoft.PowerShell.PackageManagement.Cmdlets.GetPacka*
*   geProvider*

Update-Module : NuGet provider is required to interact with NuGet-based repositories. Please ensure that '2.8.5.201'
or newer version of NuGet provider is installed.
At line:1 char:1
+ Update-Module SqlServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
* + CategoryInfo : InvalidOperation: (:) [Update-Module], InvalidOperationException*
* + FullyQualifiedErrorId : CouldNotInstallNuGetProvider,Update-Module*

PS C:\Users\adm>

What's wrong with it now? I can visit https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409 and download its content w/o any issues. Firewall is turned off.

_{Tuesday, May 12, 2020 9:07 PM}

The latest version of PowerShell that supports SQLServer is PS 5.1. PS 6 is a non-Windows version that is made available for cross platform use. It will not work with SQLServer.

PS 5.1 is installed on all versions of Windows from Windows 10 an later.

\(ツ)_/

_{Wednesday, May 13, 2020 10:38 AM}

Per this command I don't have PS 6 installed:

PS C:\TMP> Get-Host | Select-Object Version

*Version *
* *
5.1.14409.1005

Looks like I am still using 5.1. What else can I do to make Update-Module SqlServer working?

_{Wednesday, May 13, 2020 10:40 AM}

Install-Module SQLServer

\(ツ)_/

_{Wednesday, May 13, 2020 11:03 AM}

PS C:\TMP> Install-Module SQLServer
WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
WARNING: Unable to download the list of available providers. Check your internet connection.
PackageManagement\Install-PackageProvider : No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified
package has the tags.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7405 char:21
+ ...     $null = PackageManagement\Install-PackageProvider -Name $script:N ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : InvalidArgument: (Microsoft.Power...PackageProvider:InstallPackageProvider) [Install-PackageProvider], Exception*
*    + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackageProvider*

PackageManagement\Import-PackageProvider : No match was found for the specified search criteria and provider name 'NuGet'. Try 'Get-PackageProvider -ListAvailable' to see if the provider exists on the system.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7411 char:21
+ ...     $null = PackageManagement\Import-PackageProvider -Name $script:Nu ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : InvalidData: (NuGet:String) [Import-PackageProvider], Exception*
*    + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.ImportPackageProvider*

WARNING: Unable to download from URI 'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.
WARNING: Unable to download the list of available providers. Check your internet connection.
PackageManagement\Get-PackageProvider : Unable to find package provider 'NuGet'. It may not be imported yet. Try 'Get-PackageProvider -ListAvailable'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7415 char:30
+ ... tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : ObjectNotFound: (Microsoft.Power...PackageProvider:GetPackageProvider) [Get-PackageProvider], Exception*
*    + FullyQualifiedErrorId : UnknownProviderFromActivatedList,Microsoft.PowerShell.PackageManagement.Cmdlets.GetPackageProvider*

Install-Module : NuGet provider is required to interact with NuGet-based repositories. Please ensure that '2.8.5.201' or newer version of NuGet provider is installed.
At line:1 char:1
+ Install-Module SQLServer
+ ~~~~~~~~~~~~~~~~~~~~~~~~
*    + CategoryInfo          : InvalidOperation: (:) [Install-Module], InvalidOperationException*
*    + FullyQualifiedErrorId : CouldNotInstallNuGetProvider,Install-Module*

PS C:\TMP>

_{Wednesday, May 13, 2020 11:11 AM}

You have to have an Internet connection to download and install the module. Read the complete error carefully.

\(ツ)_/

_{Wednesday, May 13, 2020 12:24 PM}

I do have an Internet connection, and no firewall is turned on. Moreover, by visiting the link https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409 I can get an XML from that site w/o any issues. Here's the response from the url:

<?xml version="1.0" encoding="utf-8"?>
<SoftwareIdentity xmlns="http://standards.iso.org/iso/19770/-2/2015/schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:discovery="http://packagemanagement.org/discovery" patch="false" media="(OS:windows)" name="OneGet.Providers" tagVersion="1" uniqueId="OneGet.Providers.1" version="1.15.194.0" versionScheme="multipartnumeric">

    <!--
        This swidtag is a Discovery Feed that has pointers to the SWIDTAGs for
        the providers that the bootstrapper can download.
    -->

     <Link href="https://onegetcdn.azureedge.net/providers/nuget-2.8.5.208.package.swidtag" type="application/swid-tag+xml" rel="package" discovery:name="nuget" discovery:latest="true" discovery:version="2.8.5.208" media="(OS:windows)" />

   <Link href="https://onegetcdn.azureedge.net/providers/psl-1.0.0.210.package.swidtag" type="application/swid-tag+xml" rel="package" discovery:name="psl" discovery:latest="true" discovery:version="1.0.0.210" media="(OS:windows)" />
  
    <Link href="https://onegetcdn.azureedge.net/providers/ChocolateyPrototype-2.8.5.130.package.swidtag" type="application/swid-tag+xml" rel="package" discovery:name="chocolatey" discovery:latest="true" discovery:version="2.8.5.130" media="(OS:windows)" />

    <Link href="https://onegetcdn.azureedge.net/providers/nugetv2.feed.swidtag" type="application/swid-tag+xml" rel="feed" discovery:name="nuget" media="(OS:windows)" />

    <Link href="https://onegetcdn.azureedge.net/providers/psl.feed.swidtag" type="application/swid-tag+xml" rel="feed" discovery:name="nuget" media="(OS:windows)" />

    <Link href="https://onegetcdn.azureedge.net/providers/chocolateyprototype.feed.swidtag" type="application/swid-tag+xml" rel="feed" discovery:name="chocolatey" media="(OS:windows)" />
</SoftwareIdentity>

_{Wednesday, May 13, 2020 12:29 PM}

Then you may have a proxy server in the way or your company is blocking downloads.

\(ツ)_/

_{Wednesday, May 13, 2020 1:16 PM}

I don't have any proxy, and download works for any file from MS.

_{Wednesday, May 13, 2020 1:20 PM}

Then something is blocking PowerShell from accessing the Internet.

\(ツ)_/

Share via

Weird error "Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies"

Question

All replies (13)

Additional resources