SMOscript 0.50

I added a couple of new options to my scripting tool SMOscript:

  • The new -td command-line switch generates two files for tables: a [table].create.sql file containing the CREATE TABLE statement, and a [table].details.sql file containing the CREATE statements all table details (foreign keys, indexes, triggers), thus allowing recreating of a database schema without referential integrity problems
  • The -o (object name) switch semantics has been extended to other commands than script
  • The new -ol command-line switch implements a filter on object names similar to T-SQL LIKE. The filter is applied on the object name, and if not matching, on the combination “schema.objectname”.

The new command dd operates on data diagrams (see my previous post on SQL Server Data Diagrams):

  • Using dd without parameters will list all data diagrams stored in the database.
  • Using dd -o [diagram name] will list all tables contained in the data diagram, even tables that have been DROPped.
  • Using dd -o [diagram name] -dt lists the DROPped tables only.

The latest version of SMOscript is available for download on my download website.

SMOscript for Windows 10

I recently got notified that my tool SMOscript raises an error message when run on Windows 10.

I had a look at the problem, and indeed, as soon as the enumeration of database objects (e.g. using commands l (list) or s (script)) is to process a table, the program stop. In case of the script command, an error message is issued

Error: iterating objects: Attempt to retrieve data for object failed for Server ”.

The most recent version of SMOscript was still targeted on .Net 2.0, and used the SMO 100 assemblies. Time to upgrade! 😉

So I changed the .Net version to 4.5 and added the nuget SMO package and changed the .Net version to 4.7 to make the compiler happy.

Tests were performed successfully against SQL Server 2014 and 2019, but now SQL Server 2008 caused an error that I could work-around.

The tool now also supports options to sort database objects by schema and name, or by type and schema and name, rather than relying on the order resulting from the SMO method Database.EnumObjects().

Additionally, the -st (Sort Types) parameter allows to specify a custom sort order for database object types (rather than alphabetically), so that the parameter

smoscript -st Table,View

will process objects of types table and view first, and everything else afterwards. The parameter -st without a list of types will display the available types.

The latest version of SMOscript is available for download here.

Note that there is a regular build “V 0.40”, but also a build using the original SMO 100 assemblies.

smoscript 0.40 command-line parameters

smoscript 0.40 sort SMO Types

Database.EnumObjects() fails on SQL Server 2008

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

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

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

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.

Handling SMO Errors in SSMS View Dependencies

I tried to retrieve the dependencies of a T-SQL function in SQL Management Studio 2008, when SSMS – after some database querying – displayed the following error message:

SSMS: Discover dependencies failed. (Microsoft.SqlServer.SMO)

SSMS: Discover dependencies failed. (Microsoft.SqlServer.SMO)

The message reads

TITLE: Microsoft SQL Server Management Studio
——————————

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (Microsoft SQL Server, Error: 515)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

Clicking on the “Additional Details” button, the SMO exception tree is displayed

===================================

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)
at Microsoft.SqlServer.Management.SqlManagerUI.ObjectDependencies.DiscoverDependenciesAsync()

===================================

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)

===================================

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (.Net SqlClient Data Provider)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

——————————
Server Name: NIIGATA
Error Number: 515
Severity: 16
State: 2
Line Number: 311

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

Off to the internets, I found this Script to fix dependencies in the MSSQLWIKI and gave it a try. It soon pointed to a couple of views and stored procedures that the Dependency Viewer (or the underlying SQL statements) thought contained invalid dependencies, however I opened them and re-compiled them without errors. Only when I extended the script to also include triggers, I found an obsolete dependency and fixed it.

Probably re-compilation fixed internal dependency tracking as the script completed without errors in the end.

What the script actually does is iterate through all T-SQL modules, i.e. stored procedures, functions, view definitions (and, after editing, also triggers), and run sys.sp_refreshsqlmodule on each of them. The script’s magic lies in finding the correct order to process the modules.

Fixing the script

These are my changes to the script:

First, all temporary tables are DROPped, since they stay in the database in case the script (or rather, the call to sp_refreshsqlmodule) causes an error:

if (OBJECT_ID('tempdb..#t_excluded_modules') is not null)
    drop table #t_excluded_modules
    
if (OBJECT_ID('tempdb..#t_modules_refreshed_in_end') is not null)
    drop table #t_modules_refreshed_in_end
    
if (OBJECT_ID('tempdb..#t_user_views_or_tables') is not null)
    drop table #t_user_views_or_tables

if (OBJECT_ID('tempdb..#t_dependency_table') is not null)
    drop table #t_dependency_table

Second, I extended to SELECT statement which retrieves the modules to be checked to also include triggers

        select object_id from sys.objects where
            type in ('V', 'FN', 'IF', 'TF', 'TR')

After a couple of runs, I was able to invoke the View Dependencies dialog again without errors.