To recap, this is what I do for all of my conversions... This one is the only one that has been a problem and thankfully it is on my own production server and not a clients.
It has happened twice since deployment, and this is the most recent section of code that caused the deletion of the Constraints in the SQL Table.
I was adding 2 new columns "BILLED" and "BILLINGSOURCE" (25 and 26) to an existing table that had 24 other columns. After the end, all of the 24 pre-existing constraints were gone from the SQL table but there were 2 new constraints for the new fields:
Code:
Close THREADS
Open THREADS mode DF_EXCLUSIVE
If (Found) Begin
Send update_StatusPanel to (Status_Panel(Self)) 'Opening THREADS in Exclusive Mode'
End
Else Move 1 to iReady
If (iReady=1) Begin
Send info_box 'Your computer is unable to open the files in EXCLUSIVE mode at this time. Conversion cannot continue.'
Send ImmedAbort to (Client_Area(main(current_object)))
End
Send Initialize_StatusPanel to (Status_Panel(Self)) "Please Wait... Converting Data v29" "Converting Data Files" "" "-c0"
Send Start_StatusPanel to (Status_Panel(Self))
Send Update_StatusPanel to (Status_Panel(Self)) 'Updating file THREADS'
Move THREADS.File_Number to iHdle
Structure_Start iHdle sDriver
Move (fNewField(iHdle,'BILLED','A',1,0)) to iRet
Move (fNewField(iHdle,'BILLINGSEQUENCE','N',12,0)) to iRet
Move 0 to iIndex
Create_Index iHdle at iIndex
Set_Attribute DF_INDEX_NUMBER_SEGMENTS of iHdle iIndex to 4
Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 1 to 25
Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 2 to 4
Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 3 to 26
Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 4 to 11
Set_Attribute DF_FIELD_INDEX of iHdle 25 to 6
Set_Attribute DF_FIELD_INDEX of iHdle 26 to 6
Structure_End iHdle DF_STRUCTEND_OPT_NONE
If you are wondering "sDriver" is parsed out of the INT file earlier in the process as "MSSQLDRV"
And the "fNewField" looks like this:
Code:
Function fNewField Global Integer iHdle String sName String sType Integer iLength Integer iDecimals Returns Integer
Move (Trim(sName)) to sName
If (sName='') Function_Return 0
Integer iNewField
Move 0 to iNewField
Create_Field iHdle at iNewField
Set_Attribute DF_FIELD_NAME of iHdle iNewField to sName
If (sType='A') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_ASCII
If (sType='D') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_DATE
If (sType='T') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_TEXT
If (sType='N') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_BCD
Set_Attribute DF_FIELD_LENGTH of iHdle iNewField to iLength
If (sType='N') Set_Attribute DF_FIELD_PRECISION of iHdle iNewField to iDecimals
Function_Return 1
End_Function
The 2 new fields "BILLED" and "BILLINGSEQUENCE" got created fine and there were 2 new "Constraints" showing up in the SQL Database Table BUT the Constraints for the previous 24 columns where now missing from the SQL Table and I had to recreate each one of them separately in SQL like this:
Code:
USE [RM10]
GO
ALTER TABLE [dbo].[THREADS] ADD DEFAULT ((0)) FOR [SEGMENTS]
GO
So, of course, this could be devastating if we were modifying several tables which could have hundreds of existing columns. Thankfully I've never seen this before (and have been using basically the same process for at least 10 years at 100s of customer sites. So what would cause this?