Database.EnumObjects() fails on SQL Server 2008

February 7, 2020

After replacing the SMO 100 assemblies (=SQL 2008) with the SMO 150 (=SQL 2019) nuget package, things worked for 2014 and 2019 databases.

But not for 2008 anymore: Calling Database.EnumObjects() now raised a FailedOperationException:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Enum objects failed for Database ‘myDatabase’.
—> Microsoft.SqlServer.Management.Sdk.Sfc.InvalidVersionEnumeratorException: Operation not supported on version 10.0.5538.
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadInternal()
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadFile(Assembly a, String strFile)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitDataFromAssemblyInternal(Assembly assemblyObject, String file, ServerVersion ver, String alias, StringCollection requestedFields, Boolean store, StringCollection roAfterCreation, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Stream configXml)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitData(String file, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.LoadElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetAllElements(Urn urn, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectsFromCache(Urn urn, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects()

However, iterating over the various SMO collections (Database.Tables, Database.Views, etc.) still works!

SMOscript tries to deal with this situation by handling the exception and calling the original enumeration code from back-in-the-old-days to retrieve the selected SMO objects.


SMO and .Net Frameworks and nuget

February 7, 2020

As I upgraded my tool SMOscript to .Net version 4.5, things compiled and ran nicely again.

Only after a while I noticed the Build warnings

1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Assessment, Version=1.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.1” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3275: The primary reference “Microsoft.SqlServer.Management.Assessment, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the assembly “Microsoft.SqlServer.Assessment, Version=1.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” which was built against the “.NETFramework,Version=v4.6.1” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Management.Assessment, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.2” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Management.SqlParser, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.2” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.

So whatever functionality is implemented in the Management and Assessment assemblies, they come with the nuget package (latest version 150.18208.0 in my case) and are also copied to the project output.

Apparently the package definition contains the requirement for Framework “>= 4.6.0”, allows installation in a 4.5 project, but really requires 4.6.2. Reminds me of the dependency inconsistencies back in 2013.

Anyway, upgrading the project to .Net 4.7 got rid of these warnings.

 


Evolution of SMO DatabaseObjectTypes

February 7, 2020

The SMO library mirrors the enhancing features and functionality of SQL Server over time.

As a consequence, one can “reverse engineer” the evolution of SQL Server features by comparing the classes and enums residing in different versions Microsoft.SqlServer.Smo.dll and related assemblies.

Let’s have a look at the enumeration DatabaseObjectTypes, which is defined as

[Flags]
public enum DatabaseObjectTypes : long

in Microsoft.SqlServer.Smo.dll:

enum from to
ApplicationRole = 0x1,
ServiceBroker = 0x2,
Default = 0x4,
ExtendedStoredProcedure = 0x8,
FullTextCatalog = 0x10,
MessageType = 0x20,
PartitionFunction = 0x40,
PartitionScheme = 0x80,
DatabaseRole = 0x100,
RemoteServiceBinding = 0x200,
Rule = 0x400,
Schema = 0x800,
ServiceContract = 0x1000,
ServiceQueue = 0x2000,
ServiceRoute = 0x4000,
SqlAssembly = 0x8000,
StoredProcedure = 0x10000,
Synonym = 0x20000,
Table = 0x40000,
User = 0x80000,
UserDefinedAggregate = 0x100000,
UserDefinedDataType = 0x200000,
UserDefinedFunction = 0x400000,
UserDefinedType = 0x800000,
View = 0x1000000,
XmlSchemaCollection = 0x2000000,
SymmetricKey = 0x4000000,
Certificate = 0x8000000,
AsymmetricKey = 0x10000000,
UserDefinedTableTypes = 0x20000000,
PlanGuide = 0x40000000,
DatabaseEncryptionKey = 0x80000000,
DatabaseAuditSpecification = 0x100000000,
FullTextStopList = 0x200000000,
SearchPropertyList = 0x400000000, 110
Sequence = 0x800000000, 110

Everything stable since SMO 2005, except for SEQUENCES in SQL Server 2012 (110).

Note that the enum’s symbols match the names of the corresponding SMO classes, exception for UserDefinedTableTypes, which is represented by the class UserDefinedTableType.

SQL Server 2012 Azure (110) also introduced a feature called Federation:

enum from to
Federation = 0x1000000000, 110 130
SecurityPolicy = 0x2000000000, 130 130
ExternalDataSource = 0x4000000000, 130 130
ExternalFileFormat = 0x8000000000, 130 130
ColumnMasterKey = 0x10000000000, 130 130
ColumnEncryptionKey = 0x20000000000, 130 130
QueryStoreOptions = 0x40000000000, 130 130
DatabaseScopedCredential = 0x80000000000, 130 130

The Federation features has been dropped in SQL Server 2017 (140), causing all subsequent enumeration values to right-shift by 1 bit:

enum from to
SecurityPolicy = 0x1000000000, 140
ExternalDataSource = 0x2000000000, 140
ExternalFileFormat = 0x4000000000, 140
ColumnMasterKey = 0x8000000000, 140
ColumnEncryptionKey = 0x10000000000, 140
QueryStoreOptions = 0x20000000000, 140
DatabaseScopedCredential = 0x40000000000, 140
DatabaseScopedConfiguration = 0x80000000000, 150
ExternalLibrary = 0x100000000000, 150

Finally, there is also the version-specific value All, which is the OR’ed value of all enum values.


GetSmoObject(table) throws FailedOperationException

February 5, 2020

For internal testing, I upgraded by tool SMOscript to .Net 4.5, and referenced the required SMO assemblies from the VS 2017 installation directory

C:\Program Files\Microsoft Visual Studio\2017\Community\Common7\IDE\Extensions\Microsoft\SQLCommon\150\

The most basic operation is to list all objects inside the database (command line parameter l), which worked in principle, except not for tables. GetSmoObject() raises an exception:

ERROR smoscript.SmoBuilder:105 - - Server[@Name='NIIGATA\SQL2014']/Database[@Name='devio.u8']/Table[@Name='cmsDictionary' and @Schema='dbo']
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attempt to retrieve data for object failed for Server 'localhost\sql2014'. 
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. 
---> System.Data.SqlClient.SqlException: Invalid object name 'dbo.cmsDictionary'.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, String dbName, Boolean poolConnection)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con, String database, Boolean poolConnection)
at Microsoft.SqlServer.Management.Smo.PostProcessTable.GetRowResults(DataProvider dp)
at Microsoft.SqlServer.Management.Smo.PostProcessTable.GetColumnData(String name, Object data, DataProvider dp)
at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildObject(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)

The internets were not much of a help, so I replaced the explicit assembly references with the SMO Nuget package and things worked out fine.

My guess is that reading table information via SMO requires more assemblies than immediately obvious, and you need the full package available.


VS Solution Dependency Visualizer supports .NET Core Project Files

February 5, 2020

When I tried to analyze an unknown Visual Studio solution in my tool VS Solution Dependency Visualizer, I noticed that the project dependencies were not analyzed correctly. In fact, the dependencies were completely ignored.

A bit of research turned up that the solution in question was partly migrated to .NET Core, and the .csproj project files have a slightly different structure than good old .Net project files.

First, the classic .Net project files use a namespace

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

whereas .NET Core project files come without XML namespace, and have the root attribute Sdk:

<Project Sdk="Microsoft.NET.Sdk">

Also, files are not compiled if they are explicitly contained in the .csproj file, but rather by a rule-based Default Compilation mechanism which decides whether files in or below the project directory are compiled, embedded, or ignored.

For a detailed documentation of .NET Core project files, see MS docs or the dotnet GitHub docs.

The latest version of VS Solution Dependency Visualizer with support for .NET Core projects is available for download here.

BTW: In previous versions of this tool, I tried to register VS Solution Dependency Visualizer as an External Tool in all installed versions of Visual Studio.

However, things change all the time, and VS 2013 was the first version that did not support registration via registry, and with VS 2017 things got even more different, and finally I had to give up.

Instructions on how to add VS Solution Dependency Visualizer as an External Tool in your version of Visual Studio can be found in the readme file that comes with the installer.


Some useful Web Applications

January 29, 2020

Link clearance, and closing some browser tabs 😉

google-webfonts-helper

A Hassle-Free Way to Self-Host Google Fonts

TABLEIZER!

A Quick Spreadsheets-to-HTML <Table> Tool

.Net Regex Tester

Test regular expressions with real-time highlighting.

Port Scanner

Port scanner shows which ports are open on your (or someone else’s) computer and what they are accountable for.
Verification is performed via the scanner nmap, and shows the extend to which your PC is open to the outside world.

 


Which version of SQL Server and SSMS can be installed on which Windows version?

January 17, 2020

As my Windows installation ages, it collects various versions of development tools, such as SQL Server, SQL Server Management Studio (SSMS), or Visual Studio.

But for every version of Windows the question is: will the next version of product X be installable on Windows Y?

So here is the answer for SQL Server and SSMS on Windows versions 7 (32-bit), 8.1 (64-bit) and 10 (64-bit)

SQL/SSMS Version Win7-32 Win8.1-64 Win10-64
2008 10 Y ? ?
2012 11 Y Y Y
2014 12 Y Y Y
SSMS 2016 13 Y Y Y
SQL 2016 13 Y Y
SSMS 17 17 Y Y
SQL 2017 14 Y Y
SSMS 18 18 Y Y
SQL 2019 15 Y