Store procedure "GenerateDropScript" returns error message
Closed this issue · 5 comments
On both the test and production versions of the online modeler with a new model and default settings for a CRT model, I receive the following error when executing the stored procedure [dbo].[_GenerateDropScript]. I believe I was able to properly execute this before so perhaps a recent update broke the procedure somehow. The error message receive is as follows:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Many thanks ahead of time for your help.
After looking at this issue further, I noticed that in my simple model with one anchor and two historized attributes under a CRT model for SQL Server, if you comment out lines 377 to 382 of /SQL/SQLServer/CreateSchemaTracking.js then the script will run. It appears something related to the attribute annex tables is giving the error. That's all I have time for now. Hope this helps point anyone in the right direction. Cheers!
I think I found the issue causing the infinite recursion. I've pushed a new version to test. Can you check if the _GenerateDropScript generated by the test version now completes? If so I will retrofit the fix onto v0.98 as well. Please note that the stored procedure is slightly smarter than it was a few commits ago, so you are correct that there was a recent update. The old behavior corresponds to calling the procedure as follows:
EXEC _GenerateDropScript DEFAULT, DEFAULT, 'Upwards', DEFAULT
Even so, it should never enter infinite recursions, so the following should also work:
EXEC _GenerateDropScript
There may still be some ordering issues, forcing you to run the resulting DROPs more than once.
Thanks for the update. The _GenerateDropScript using the new calling method works now.
You are right as I am seeing some issues with the drop order. In my case, I receive an error where the script tries to drop one of the attribute restatement finder functions (with prefix rf...) prior to dropping the restatement constraint (with prefix rc...). And so once I run the script one more time, I see the final anchor table correctly dropped but the original rf... function still remains and is not picked up by running the _GenerateDropScript a third time. This was all tested using a CRT model with one anchor and two historized attributes with default settings. Here is the XML for the model:
<schema format="0.99" date="2015-10-09" time="09:41:08">
<metadata changingRange="datetime" encapsulation="dbo" identity="int" metadataPrefix="Metadata" metadataType="int" metadataUsage="false" changingSuffix="ChangedAt" identitySuffix="ID" positIdentity="int" positGenerator="true" positingRange="datetime" positingSuffix="PositedAt" positorRange="tinyint" positorSuffix="Positor" reliabilityRange="tinyint" reliabilitySuffix="Reliability" deleteReliability="0" assertionSuffix="Assertion" partitioning="false" entityIntegrity="true" restatability="false" idempotency="true" assertiveness="true" naming="improved" positSuffix="Posit" annexSuffix="Annex" chronon="datetime2(7)" now="sysdatetime()" dummySuffix="Dummy" versionSuffix="Version" statementTypeSuffix="StatementType" checksumSuffix="Checksum" businessViews="false" decisiveness="true" equivalence="false" equivalentSuffix="EQ" equivalentRange="tinyint" databaseTarget="SQLServer" temporalization="crt"/>
<anchor mnemonic="CO" descriptor="Company" identity="int">
<metadata capsule="dbo" generator="true"/>
<attribute mnemonic="NAM" descriptor="Name" identity="int" timeRange="datetime" dataRange="varchar(42)">
<metadata capsule="dbo" generator="true" assertive="true" restatable="false" idempotent="true"/>
<layout x="1012.65" y="581.27" fixed="false"/>
</attribute>
<attribute mnemonic="PRI" descriptor="Price" identity="bigint" timeRange="datetime2(7)" dataRange="decimal(17,7)">
<metadata capsule="dbo" generator="true" assertive="true" restatable="true" idempotent="false"/>
<layout x="992.42" y="602.99" fixed="false"/>
</attribute>
<layout x="960.00" y="552.50" fixed="false"/>
</anchor>
</schema>I have made some more changes to _GenerateDropScript and I believe it should work as expected now. I have tried it on a couple of models and it ran without errors. Can you also test before closing the issue?
The _GenerateDropScript works well now using DEFAULT parameter values. Many thanks! I believe this issue can now be closed, but I would be interested in seeing some use cases or examples of using the _GenerateDropScript with non-default or other values for the input parameters to accomplish perhaps common tasks if you can suggest any. Thanks again!