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.

2 thoughts on “GetSmoObject(table) throws FailedOperationException

  1. Pingback: SMO and .Net Frameworks and nuget | devioblog

  2. Pingback: Database.EnumObjects() fails on SQL Server 2008 | devioblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.