Error trying to script using the Sql Server 2019 SMO object model #11860
Description
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
Activity
mklement0 commentedon Feb 17, 2020
A couple of general points:
LoadWithPartialName()has been declared obsolete: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 commentedon Feb 19, 2020
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:
techvslife commentedon Feb 19, 2020
fyi, here's a fuller excerpt of the code.
mklement0 commentedon Feb 19, 2020
The method name is
LoadFile, notLoadString, but the best way to load well-known assemblies in PowerShell is to useAdd-Type -AssemblyName, and to load assemblies in a given location withAdd-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 Steve Lee (@SteveL-MSFT)).
See this comment for guidance.
techvslife commentedon Feb 19, 2020
Thanks, that appears to be the right direction to resolve this, but installation still fails with this error:
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 commentedon Feb 19, 2020
This seems to be relevant:
microsoft/sqltoolsservice#891
techvslife commentedon Feb 19, 2020
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.....
mklement0 commentedon Feb 19, 2020
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: PowerShell/PowerShell-RFC#237
Now that we know that the solution lies elsewhere, please consider closing this issue.
techvslife commentedon Feb 25, 2020
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 commentedon Feb 25, 2020
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.techvslife commentedon Feb 28, 2020
Thank you--unfortunately that fails for me, with this error:
Install-Package: Object reference not set to an instance of an object.
techvslife commentedon Feb 28, 2020
--Let me try it another way.
techvslife commentedon Feb 28, 2020
How would you recommend installing the extracted package?
mklement0 commentedon Feb 29, 2020
To be clear: I meant avoiding
Install-Packagein order to work around the dependency-loop issue.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 loadedImport-Module(by mere name).techvslife commentedon Feb 29, 2020
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:
That's after the last line of this code:
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.
techvslife commentedon Feb 29, 2020
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 commentedon Feb 29, 2020
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.0But fwiw, these are the 22 copies:
techvslife commentedon Feb 29, 2020
This might be a bug, but I thought I was using the latest version (that should in theory have fixed it).
dotnet/SqlClient#260
techvslife commentedon Feb 29, 2020
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!):