While scripting, get "Cannot access properties or methods for the Microsoft.SqlServer.Management.Smo.Table '[dbo].[MyTable]', because it has been dropped." #202
Description
I'm trying to script a table such that it is dropped if it exists and is then recreated.
I know that this table exists on the server, yet I keep getting this exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.MyTable'.
---> Microsoft.SqlServer.Management.Smo.SmoException: Cannot access properties or methods for the Microsoft.SqlServer.Management.Smo.Table '[dbo].[ItemList]', because it has been dropped.
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckObjectStateImpl(Boolean throwIfNotCreated)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckObjectState(Boolean throwIfNotCreated)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckObjectState()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_IgnoreForScripting()
at Microsoft.SqlServer.Management.Smo.ScriptMaker.IsFiltered(SqlSmoObject obj, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptDropObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptDrop(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptDropObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScript(Urn[] urns)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns)
at SqlExtractor.Lib.FileSaver.ScriptObject(SqlTreeItem item) in C:\Users\Christian\RiderProjects\SqlExtractor\SqlExtractor\Lib\FileSaver.cs:line 132
at SqlExtractor.Lib.FileSaver.OutputOneFilePerType() in C:\Users\Christian\RiderProjects\SqlExtractor\SqlExtractor\Lib\FileSaver.cs:line 395
at SqlExtractor.OutputToFolderWindow.<ExportButton_OnClick>b__11_0() in C:\Users\Christian\RiderProjects\SqlExtractor\SqlExtractor\OutputToFolderWindow.xaml.cs:line 54
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
This is the code that is doing the scripting:
private string ScriptObject(SqlTreeItem item)
{
// Retrieves the Server from a dictionary of servers, based off of item.OriginalServer, which is a ServerConnection.
// If no Server exists for this ServerConnection; a new Server is created, added to the dictionary, and returned.
// Since ScriptObject is called many times in succession, doing it this way prevents us from having to construct
// and destroy identical Server objects over and over again.
Server server = GetServer(item.OriginalServer);
// Retrieves the Scripter from a dictionary of scripters, based off of server, which is a Server.
// If no Scripter exists for this Server; a new Scripter is created, added to the dictionary, and returned.
// Since ScriptObject is called many times in succession, doing it this way prevents us from having to construct
// and destroy identical Scripter objects over and over again.
Scripter scripter = GetScripter(server);
scripter.Options.IncludeDatabaseContext = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
var builder = new StringBuilder();
scripter.Options.ScriptDrops = true;
scripter.Options.IncludeIfNotExists = true;
builder.AppendLine(string.Join("\nGO\n", scripter.EnumScript(new[] { item.Urn })));
scripter.Options.ScriptDrops = false;
scripter.Options.IncludeIfNotExists = false;
builder.AppendLine(string.Join("\nGO\n", scripter.EnumScript(new[] { item.Urn })));
return builder.ToString();
}Interestingly, this happens for all objects other than the first object I use ScriptObject() on. The first time I run ScriptObject(), it works fine. But all subsequent runs fail.
I am not running the result of ScriptObject(), it is just being written to a file. This program does not modify the source database server in any way at runtime.
I have tried changing ScriptObject() to create a new Server and Scripter instance, like so:
private string ScriptObject(SqlTreeItem item)
{
Server server = new(item.OriginalServer);
Scripter scripter = new(server);
scripter.Options.IncludeDatabaseContext = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = false;
var builder = new StringBuilder();
scripter.Options.ScriptDrops = true;
scripter.Options.IncludeIfNotExists = true;
builder.AppendLine(string.Join("\nGO\n", scripter.EnumScript(new[] { item.Urn })));
scripter.Options.ScriptDrops = false;
scripter.Options.IncludeIfNotExists = false;
builder.AppendLine(string.Join("\nGO\n", scripter.EnumScript(new[] { item.Urn })));
return builder.ToString();
}but run into the same problem.
If you need to see more of my code, ask, and I will send it in private.