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.
Pingback: SMO and .Net Frameworks and nuget | devioblog
Pingback: Database.EnumObjects() fails on SQL Server 2008 | devioblog