locked
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" RRS feed

  • Question

  • 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?

    Tuesday, May 12, 2020 5:25 AM

All replies

  • 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 5:35 AM
  • 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:56 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 1:58 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 6: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.


    \_(ツ)_/

    Tuesday, May 12, 2020 9:07 PM
  • 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:38 AM
  • Install-Module SQLServer


    \_(ツ)_/

    Wednesday, May 13, 2020 10:40 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>
    • Edited by Senglory Wednesday, May 13, 2020 11:05 AM
    Wednesday, May 13, 2020 11:03 AM
  • You have to have an Internet connection to download and install the module.  Read the complete error carefully.


    \_(ツ)_/

    Wednesday, May 13, 2020 11:11 AM
  • 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>

    • Edited by Senglory Wednesday, May 13, 2020 12:26 PM
    Wednesday, May 13, 2020 12:24 PM
  • Then you may have a proxy server in the way or your company is blocking downloads.


    \_(ツ)_/

    Wednesday, May 13, 2020 12:29 PM
  • I don't have any proxy, and download works for any file from MS.
    Wednesday, May 13, 2020 1:16 PM
  • Then something is blocking PowerShell from accessing the Internet.  


    \_(ツ)_/

    Wednesday, May 13, 2020 1:20 PM