Unable to script stored procedures in SQL 2017

Question

Post reply

Unable to script stored procedures in SQL 2017

haichells

SSCrazy

Points: 2713

July 25, 2018 at 10:41 pm

#414441

Hi All,

I have a SQL Server 2017 Standard Edition. I am trying to script all the stored procedures for one particular database. But i am getting error. I am trying to do as a separate file for each procedure. Please help whether i need to verify any server settings.

I am able to script users/views etc. But i am not able to script stored procedures. I have around 90000 stored procedures in this database. Assuming the number of procedures may be a problem, I selected 2-3 procedures and tried. But it failed even when i select 2-3 procedures also.

Thanks in Advance

Chelladurai

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.String.CtorCharArrayStartLength(Char[] value, Int32 startIndex, Int32 length) at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName) at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Microsoft.SqlServer.Management.Smo.DataProvider.GetTrigeredValue(Int32 i) at Microsoft.SqlServer.Management.Smo.PostProcessBodyText.GetColumnData(String name, Object data, DataProvider dp) at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess() at Microsoft.SqlServer.Management.Smo.DataProvider.Read() 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, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields) at Microsoft.SqlServer.Management.Smo.Database.PrefetchStoredProcedures(ScriptingPreferences options) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.PrefetchAllObjects(String urnType) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.<PrefetchObjects>d__1.MoveNext() at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns) at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns) 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(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

August 2, 2018 at 7:42 pm

This was removed by the editor as SPAM
August 2, 2018 at 7:42 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

haichells - Wednesday, July 25, 2018 10:41 PM
Hi All,
I have a SQL Server 2017 Standard Edition. I am trying to script all the stored procedures for one particular database. But i am getting error. I am trying to do as a separate file for each procedure. Please help whether i need to verify any server settings.
I am able to script users/views etc. But i am not able to script stored procedures. I have around 90000 stored procedures in this database. Assuming the number of procedures may be a problem, I selected 2-3 procedures and tried. But it failed even when i select 2-3 procedures also.
Thanks in Advance
Chelladurai
Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.String.CtorCharArrayStartLength(Char[] value, Int32 startIndex, Int32 length) at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName) at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Microsoft.SqlServer.Management.Smo.DataProvider.GetTrigeredValue(Int32 i) at Microsoft.SqlServer.Management.Smo.PostProcessBodyText.GetColumnData(String name, Object data, DataProvider dp) at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess() at Microsoft.SqlServer.Management.Smo.DataProvider.Read() 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, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields) at Microsoft.SqlServer.Management.Smo.Database.PrefetchStoredProcedures(ScriptingPreferences options) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.PrefetchAllObjects(String urnType) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.<PrefetchObjects>d__1.MoveNext() at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns) at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns) 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(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

It's SSMS that is running out of memory. You need to restart SSMS when you get the error and then try with a smaller set. You may also want to try another version of SSMS as it can be worse on some versions of SSMS. There were bugs filed in connect about the issues. It was supposed to have been fixed.They never listed what version though, just marked it as "completed January 2018".

Sue

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

try using the wizard in SSMS, connect to Server, RMC on database, select Tasks, select generate scripts

***The first step is always the hardest *******

haichells SSCrazy Points: 2713 More actions · Answer 3

Sue_H - Thursday, July 26, 2018 6:25 AM
haichells - Wednesday, July 25, 2018 10:41 PM
Hi All,
I have a SQL Server 2017 Standard Edition. I am trying to script all the stored procedures for one particular database. But i am getting error. I am trying to do as a separate file for each procedure. Please help whether i need to verify any server settings.
I am able to script users/views etc. But i am not able to script stored procedures. I have around 90000 stored procedures in this database. Assuming the number of procedures may be a problem, I selected 2-3 procedures and tried. But it failed even when i select 2-3 procedures also.
Thanks in Advance
Chelladurai
Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.String.CtorCharArrayStartLength(Char[] value, Int32 startIndex, Int32 length) at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName) at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Microsoft.SqlServer.Management.Smo.DataProvider.GetTrigeredValue(Int32 i) at Microsoft.SqlServer.Management.Smo.PostProcessBodyText.GetColumnData(String name, Object data, DataProvider dp) at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess() at Microsoft.SqlServer.Management.Smo.DataProvider.Read() 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, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields) at Microsoft.SqlServer.Management.Smo.Database.PrefetchStoredProcedures(ScriptingPreferences options) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.PrefetchAllObjects(String urnType) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.<PrefetchObjects>d__1.MoveNext() at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns) at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns) 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(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
It's SSMS that is running out of memory. You need to restart SSMS when you get the error and then try with a smaller set. You may also want to try another version of SSMS as it can be worse on some versions of SSMS. There were bugs filed in connect about the issues. It was supposed to have been fixed.They never listed what version though, just marked it as "completed January 2018".
Sue

Even after restart i am getting the same error. Should i restart the server??? . I tried in SQL 2016 also but no luck.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

haichells - Friday, July 27, 2018 9:51 PM
Sue_H - Thursday, July 26, 2018 6:25 AM
haichells - Wednesday, July 25, 2018 10:41 PM
Hi All,
I have a SQL Server 2017 Standard Edition. I am trying to script all the stored procedures for one particular database. But i am getting error. I am trying to do as a separate file for each procedure. Please help whether i need to verify any server settings.
I am able to script users/views etc. But i am not able to script stored procedures. I have around 90000 stored procedures in this database. Assuming the number of procedures may be a problem, I selected 2-3 procedures and tried. But it failed even when i select 2-3 procedures also.
Thanks in Advance
Chelladurai
Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.String.CtorCharArrayStartLength(Char[] value, Int32 startIndex, Int32 length) at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName) at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Microsoft.SqlServer.Management.Smo.DataProvider.GetTrigeredValue(Int32 i) at Microsoft.SqlServer.Management.Smo.PostProcessBodyText.GetColumnData(String name, Object data, DataProvider dp) at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess() at Microsoft.SqlServer.Management.Smo.DataProvider.Read() 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, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields) at Microsoft.SqlServer.Management.Smo.Database.PrefetchStoredProcedures(ScriptingPreferences options) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.PrefetchAllObjects(String urnType) at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.<PrefetchObjects>d__1.MoveNext() at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns) at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns) 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(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
It's SSMS that is running out of memory. You need to restart SSMS when you get the error and then try with a smaller set. You may also want to try another version of SSMS as it can be worse on some versions of SSMS. There were bugs filed in connect about the issues. It was supposed to have been fixed.They never listed what version though, just marked it as "completed January 2018".
Sue
Even after restart i am getting the same error. Should i restart the server??? . I tried in SQL 2016 also but no luck.

Assuming that you are doing this from a laptop or desktop system, not the server, you may have to restart the system you are using to run SSMS.