Powershell: Error trying to script using the Sql Server 2019 SMO object model

[techvslife]

Steps to reproduce

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

Expected behavior

return GAC = True (as Powershell 5 does):
GAC    Version        Location
---    -------        --------
True  v4.0.30319     C:\WINDOWS\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll 

Actual behavior

returns GAC = False:
GAC    Version        Location
---    -------        --------
False  v4.0.30319     C:\WINDOWS\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll 

Environment data

Name                           Value
----                           -----
PSVersion                      7.0.0-rc.2
PSEdition                      Core
GitCommitId                    7.0.0-rc.2
OS                             Microsoft Windows 10.0.18363
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

[mklement0]

A couple of general points:

• LoadWithPartialName() has been declared _obsolete_:

The overloads of the Assembly.LoadWithPartialName method are obsolete and have been retained for backward compatibility. The non-obsolete alternative isAssembly.Load(String).

• .NET Core no longer has a GAC (Global Assembly Cache).

The GAC applies to the .NET Framework (FullClr), and I generally wouldn't expect assemblies located there to be loadable from .NET Core (PowerShell Core) - unless they were explicitly compiled for cross-.NET-edition compatibility (against a .NET Standard version).

What is the exact problem you're experiencing, and what do you _expect_ to happen?

[techvslife]

The error on that line prevents scripting of SQL Server objects in the rest of the script. The script functions fine in Powershell 5, but not at all in Powershell 7. I appreciate that line is now deprecated, but I don't know how to rework it to make it compatible with Powershell 7--I'm lifting standard SQL Server scripting routines without familiarity with Powershell. Thanks.

As for as Assembly.LoadString(), that's not a functional substitute here--gives this error:

Method invocation failed because [System.Reflection.Assembly] does not contain a method named 'LoadString'.

[techvslife]

fyi, here's a fuller excerpt of the code.

#NOTE: DOES NOT WORK WITH POWERSHELL 7, only with ps5.
$server = "."
$dbname = "MyDbs"
$path = "C:\MyPath\ScriptTextOutput\"
function ScriptDbsIntoFolders([string]$server, [string]$dbname, [string] $path) {

    # code from https://blogs.technet.microsoft.com/heyscriptingguy/2010/11/04/use-powershell-to-script-sql-database-objects/ by Aaron Nelson
    # modified to add the path as a variable and remove the drop code.
#[System.Reflection.Assembly]::LoadString("Microsoft.SqlServer.SMO") | out-null  #DOES not work 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlManagementObjects") | out-null #does not work with ps7
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server    
    #$SMOserver = New-Object ('Microsoft.SqlServer.SqlManagementObjects') -argumentlist $server
    $db = $SMOserver.databases[$dbname]

    $Objects += $db.StoredProcedures
    $Objects += $db.Synonyms
    $Objects += $db.Tables
    $Objects += $db.Triggers

    #Build this portion of the directory structure out here in case scripting takes more than one minute.

    $SavePath = $path #+ "\" + $($dbname)


    foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
        #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
        $scriptr.Options.AnsiFile = $true
        $scriptr.Options.AppendToFile = $false
        $scriptr.Options.AllowSystemObjects = $false
        $scriptr.Options.Bindings = $true
        $scriptr.Options.ClusteredIndexes = $true
        $scriptr.Options.ColumnStoreIndexes = $true
        $scriptr.Options.ContinueScriptingOnError = $true
        $scriptr.Options.Default = $true
        $scriptr.Options.DriAll = $true
        $scriptr.Options.DriIncludeSystemNames = $false
        $scriptr.Options.Encoding = [System.Text.Encoding]::UTF8
        $scriptr.Options.ExtendedProperties = $true
        $scriptr.Options.IncludeHeaders = $false
        $scriptr.Options.Indexes = $true
        $scriptr.Options.NoCollation = $true
        $scriptr.Options.NonClusteredIndexes = $true
        $scriptr.Options.OptimizerData = $false
        $scriptr.Options.Permissions = $false
        $scriptr.Options.SchemaQualify = $true
        $scriptr.Options.SchemaQualifyForeignKeysReferences = $true
        $scriptr.Options.ScriptData = $false
        $scriptr.Options.ScriptDrops = $false
        $scriptr.Options.ScriptOwner = $false
        $scriptr.Options.ScriptSchema = $true
        $scriptr.Options.Statistics = $false
        $scriptr.Options.ToFileOnly = $true
        $scriptr.Options.TargetServerVersion = "Version150"
        $scriptr.Options.Triggers = $true
        $scriptr.Options.WithDependencies = $false
        $scriptr.Options.XmlIndexes = $true



        <#This section builds folder structures.  Remove the date folder if you want to overwrite#>

        $TypeFolder = $ScriptThis.GetType().Name

        if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true") {"Scripting Out $TypeFolder $ScriptThis"} 
        else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}
        $ScriptFile = $ScriptThis -replace "\[|\]"
        $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"

        #This is where each object actually gets scripted one at a time.
        $scriptr.Script($ScriptThis)

    } #This ends the loop

} #This completes the function

ScriptDbsIntoFolders -server $server -dbname $dbname -path $path

[mklement0]

The method name is LoadFile, not LoadString, but the best way to load well-known assemblies in PowerShell is to use Add-Type -AssemblyName, and to load assemblies in a given location with Add-Type -LiteralPath.

Both your outputs represent _successful_ loading of the assembly per se, not errors - though as stated, I wouldn't expect the version in the GAC to necessarily work in PowerShell Core, but I have no personal experience.

This blog post suggests that you'll have to install this NuGet package in PowerShell Core.

---

Generally, please note that this repository isn't for general support questions, only for reproducible problems with PowerShell Core itself (/cc @SteveL-MSFT).

See this comment for guidance.

[techvslife]

Thanks, that appears to be the right direction to resolve this, but installation still fails with this error:

Install-Package: Dependency loop detected for package 'Microsoft.SqlServer.SqlManagementObjects'.

Though not a bug, this is a backwards incompatibility with what I'd guess would be a big use case of Powershell: Sql Server admin.

[techvslife]

This seems to be relevant:
https://github.com/microsoft/sqltoolsservice/issues/891

[techvslife]

Directions there (to use "a non-vanilla PS" with the "package manager"), still leave me a dependency loop, but at least I have another place to post about this.....

Install-Package : Dependency loop detected for package 'Microsoft.SqlServer.SqlManagementObjects'.
At line:1 char:1

• Install-Package -Name Microsoft.sqlserver.SqlManagementObjects -Scope ...
• ~~~~~~~~~~~~~~~~~
  
  
  
  • CategoryInfo : Deadlock detected: (Microsoft.SqlServer.SqlManagementObjects:String) [Install-Package],
    
    Exception
  
  
  • FullyQualifiedErrorId : DependencyLoopDetected,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage
  
  

[mklement0]

@techvslife, yes, that dependency-loop issue sounds annoying.

A - cumbersome and possibly impractical - workaround is to download and extract the NuGet package manually.

Looking at the broader picture, a re-architected PowerShellGet module (v3) that no longe depends on the PackageManagement (OneGet) module is being worked on: https://github.com/PowerShell/PowerShell-RFC/pull/237

Now that we know that the solution lies elsewhere, please consider closing this issue.

[msftbot[bot]]

This issue has been marked as answered and has not had any activity for 1 day. It has been closed for housekeeping purposes.

[techvslife]

I'm grateful for your help, and I realize I'll have to get help elsewhere (note: though "msftbot" closed this, the issue is unresolved).
There is no solution that I can see (except I guess to wait for a future PowerShellGet v3 module):
Downloading and extracting the NuGet package locally doesn't work, because there are further dependency loop messages down the line:
Install-Package: Dependency loop detected for package 'Microsoft.Data.SqlClient'.

[mklement0]

According to the https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects,
the dependencies are https://www.nuget.org/packages/Newtonsoft.Json/ (which is part of PowerShell) and https://www.nuget.org/packages/System.Data.SqlClient/, so if you if you download and extract the latter manually too (without Install-Package), it may work.

Microsoft.SqlServer.SqlManagementObjects 150.18208.0

The SQL Server Management Objects (SMO) Framework is a set of objects designed for programmatic management of Microsoft SQL Server and Microsoft Azure SQL Database.

Newtonsoft.Json 12.0.3

Json.NET is a popular high-performance JSON framework for .NET

System.Data.SqlClient 4.8.1

Provides the data provider for SQL Server. These classes provide access to versions of SQL Server and encapsulate database-specific protocols, including tabular data stream (TDS) Commonly Used Types: System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlException System.Data.SqlClient.SqlParameter System.Data.SqlDbType System.Data.SqlClient.SqlDataReader System.Data.SqlClient.SqlCommand System.Data.SqlClient.SqlTransaction System.Data.SqlClient.SqlParameterCollection System.Data.SqlClient.SqlClientFactory When using NuGet 3.x this package requires at least version 3.4.

[techvslife]

Thank you--unfortunately that fails for me, with this error:
Install-Package: Object reference not set to an instance of an object.

[techvslife]

--Let me try it another way.

[techvslife]

How would you recommend installing the extracted package?

[mklement0]

To be clear: I meant _avoiding_ Install-Package in order to _work around_ the dependency-loop issue.

How would you recommend installing the extracted package?

You need to place the relevant DLLs somewhere where your scripts can load them with Add-Type -LiteralPath.

Locating the relevant DLL's _inside the extracted package_ can itself be a challenge - see #6724

As far as I know, there is no established location for where to place helper assemblies _globally_, aside from bundling them with a _module_.

The alternative is therefore to _package the DLLs as a PowerShell module_ and place them in a directory listed in $env:PSModulePath (in short: put them in a folder and create a module manifest with the same name as the folder that loads the DLLs), which then allows the DLLs to either be auto-loaded via their module or loaded Import-Module (by mere name).

[techvslife]

I got pretty far, despite the dependency bug in the powershell package manager with respect to nuget packages. But basically this is unresolved, and the SMO code won't work in Powershell 7.
Now I get this error:

New-Object: Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.10.19324.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

That's after the last line of this code:

$smopath = Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source | Split-Path) (Join-Path lib netstandard2.0);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll);
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll);
$sqldll_path = Join-Path ((Get-Package Microsoft.Data.SqlClient).Source | Split-Path) (Join-Path lib netstandard2.0);
Add-Type -Path (Join-Path $sqldll_path Microsoft.Data.SqlClient.dll);

$SqlSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo(".", "sa", "mypassword")
$SmoServer = New-Object Microsoft.SqlServer.Management.SMO.Server($SqlSrvConn)

Interestingly, the following code works perfectly fine--so I think MS did NOT port the entire server-side SMO code to .net core, or there is a bug in the attempt. Or maybe it's an issue with the buggy powershell install of nuget packages. Have no idea.

$sqlConn = New-Object System.Data.SqlClient.SqlConnection("Server=.;Database=MyDbs;Trusted_Connection=yes;")
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$query = “select FName, LName from Customersorder by LName, FName;”
$sqlcmd.CommandText = $query
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$data.Tables

[techvslife]

Thank you for your help--I did make progress, but not there yet.
To clarify, Powershell really does know where the file Microsoft.Data.SqlClient.dll is (that's correctly given by Add-Type), but it's still not working. Perhaps there's a versioning issue? that's the kind of thing that gets to be a mess when package install fails on you, which is why you want package install to work ....

[techvslife]

If it is a version issue (which it may not be), there are 22 (!) copies of the file Microsoft.Data.SqlClient.dll on my desktop, so not clear which one to use (some are identical, most are not). The one I'm using (to which $sqldll_path points) is at:
C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\lib\netstandard2.0

But fwiw, these are the 22 copies:

C:>dir Microsoft.Data.SqlClient.dll /s

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\lib\net46
02/14/2020 12:39 AM 1,920,472 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\lib\netcoreapp2.1
02/14/2020 12:38 AM 346,584 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\lib\netstandard2.0
02/14/2020 12:38 AM 339,928 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\ref\net46
02/14/2020 12:38 AM 69,592 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\ref\netcoreapp2.1
02/14/2020 12:38 AM 66,008 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\ref\netstandard2.0
02/14/2020 12:38 AM 60,376 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\runtimes\unix\lib\netcoreapp2.1
02/14/2020 12:38 AM 1,155,032 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\runtimes\unix\lib\netstandard2.0
02/14/2020 12:38 AM 1,122,776 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\runtimes\win\lib\net46
02/14/2020 12:39 AM 1,920,472 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\runtimes\win\lib\netcoreapp2.1
02/14/2020 12:38 AM 1,266,648 Microsoft.Data.SqlClient.dll

C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.1.1.1\runtimes\win\lib\netstandard2.0
02/14/2020 12:38 AM 1,217,496 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\lib\net46
11/20/2019 09:58 PM 1,921,496 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\lib\netcoreapp2.1
11/20/2019 09:58 PM 346,584 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\lib\netstandard2.0
11/20/2019 09:58 PM 339,928 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\ref\net46
11/20/2019 09:58 PM 69,592 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\ref\netcoreapp2.1
11/20/2019 09:58 PM 66,008 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\ref\netstandard2.0
11/20/2019 09:58 PM 59,864 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\runtimes\unix\lib\netcoreapp2.1
11/20/2019 09:58 PM 1,158,616 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\runtimes\unix\lib\netstandard2.0
11/20/2019 09:58 PM 1,125,848 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\runtimes\win\lib\net46
11/20/2019 09:58 PM 1,921,496 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\runtimes\win\lib\netcoreapp2.1
11/20/2019 09:58 PM 1,270,232 Microsoft.Data.SqlClient.dll

C:\Users\usr.nuget\packages\microsoft.data.sqlclient\1.1.0\runtimes\win\lib\netstandard2.0
11/20/2019 09:58 PM 1,220,560 Microsoft.Data.SqlClient.dll

[techvslife]

This might be a bug, but I thought I was using the latest version (that should in theory have fixed it).
https://github.com/dotnet/SqlClient/issues/260

[techvslife]

Thank you for your advice -- I finally did get it working. There is a bug (a bug as far as using it in net.core) that's still uncorrected -- at least until v2 preview of microsoft.data.sqlclient. I had to use this beta version:
microsoft.data.sqlclient.2.0.0-preview1.20021.1.nupkg
I'm not sure how to get that to install naturally given the issues with Install-Package and nuget, so all I did was refer to it on disk as you suggested (hope all of this gets fixed soon, whether it's nuget or .netcore or ps or sqldev or some other MS team--no one likes to spend a day or two on installation!):

$sqldll_path = "C:\Users\usr\ps\microsoft.data.sqlclient.2.0.0-preview1.20021.1\runtimes\win\lib\netstandard2.0"
Add-Type -Path (Join-Path $sqldll_path Microsoft.Data.SqlClient.dll);