Generate Script on SQL 2008 DB causes a "Collection cannot be modified when TextMode has been set" error

Question

Hi,

When exporting a database that we've inherited, we kept on getting the "Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)" error on a few of the tables.  I can only find one instance of this error on the web, referring to a stored proc needing to turn off TextMode on SQL 2005, doesn't seems related.  Any help would be greatly appreciated, here's the full error:

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

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.4064.0+((Katmai_SP2_GDR).110225-1353+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.CheckCollectionLock()
   at Microsoft.SqlServer.Management.Smo.SortedListCollectionBase.ImplAddExisting(SqlSmoObject obj)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateNewObjectFromRow(AbstractCollectionBase childColl, Type childType, IDataReader reader, Int32 columnIdx, Boolean isOrderedByID)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResultsRec(SqlSmoObject currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader reader, Int32 columnIdx, Object[] parentRow, Boolean forScripting, List`1 urnList, Int32 startLeafIdx)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AdvanceInitRec(SqlSmoObject currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader reader, Int32 columnIdx, Int32 columnOffset, Object[] parentRow, Boolean forScripting, List`1 urnList, Int32 startLeafIdx)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResultsRec(SqlSmoObject currentSmoObject, XPathExpression levelFilter, Int32 filterIdx, IDataReader reader, Int32 columnIdx, Object[] parentRow, Boolean forScripting, List`1 urnList, Int32 startLeafIdx)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResults(Urn levelFilter, IDataReader reader, Boolean forScripting, List`1 urnList, Int32 startLeafIdx, Boolean skipServer)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingOptions so, Boolean forScripting)
   at Microsoft.SqlServer.Management.Smo.Database.PrefetchUserDefinedFunctions(ScriptingOptions options)
   at Microsoft.SqlServer.Management.Smo.Prefetch.<CreateBatchBlock>b__7(BatchBlock batchBlock)
   at Microsoft.SqlServer.Management.Smo.BatchBlock.PrefetchObjects()
   at Microsoft.SqlServer.Management.Smo.Prefetch.UrnIterator.Enumerator.get_Current()
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptProcessor.GenerateScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingEngine.GenerateScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.GenerateScriptMaker.DoScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.GenScriptWizForm.Script()

Thanks!

William

Answer 1

Hi William,

Based on your description, this issue only happens to several tables. Can you provide a sample table definition here? Also, it would be better if you want provide us reproduce steps.

---

Best Regards
Alex Feng | Forum Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].