D2_99_MSSQL3.TPW

#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! File Name:   D2MsSql3.tpw
#! Purpose:     DCT to MS SQL creation script
#! Author:      Copyright © 1999-2013 by Roberto Artigas Jr
#!              All rights reserved world wide.
#! Changes:
#! 2012.05.03   Roberto Artigas
#!  * Correction to processing picture for REAL (containing $)
#!  * Additional logic to convert KEY and KEY COMPONENTS to BIGINT.
#!  * Improved messaging to be more explicit in the reporting.
#! 2012.05.02   Roberto Artigas
#!  * Options to convert CHAR to VARCHAR and BIGINT not being IDENTITY.
#!    All neatly packaged under the Joe Hall specials
#!  * Added handling for 'PICTURE' type fields
#!  * Changed WARNING to correctly say no AUTO INCREMENT KEY
#! 2012.03.12   Roberto Artigas
#!  * Added support for the BINARY type field.
#! 2012.03.07   Roberto Artigas
#!  * Begin the comment generation for MSSQL databases.
#!    Since I am doing these as a separate script it will take a bit more logic.
#!    Table and field comments handled. More research, see if other items posible.
#! 2012.03.03   Roberto Artigas
#!  * Cleanup up Key Field not obeying the %IgnoredReserved option.
#!  * Cleaned up keywords on PRIMARY keys do not need options that are on other indexes.
#!  * Corrected BLOB/MEMO field problem with no length comming thru. Might need to check for text.
#! 2012.03.02   Roberto Artigas
#!  * Added COLLATE option and variable. Defaulted to Latin1_General_CI_AS.
#!  * Added NONCLUSTERED for UNIQUE indexes.
#!  * Added CLUSTERED for PRIMARY KEY indexes.
#! 2012.02.27   Roberto Artigas
#!  * Added back '[', ']' to relation logic. Will add to all areas as the brackets [] are prefered.
#!  * Added keywords to the primary key and index area.
#!  * Added option to ignore the reserved words and not add '_'. We have brackets for this.
#! 2012.02.22   Roberto Artigas
#!  * Remove '[', ']' from relation logic.
#!  * Changes to the code generated by the 'CLEAR' area of the relation logic.
#!  * Made WARNING message a bit clearer.
#! 2012.02.20   Roberto Artigas
#!  * Check at the field level for IDENTITY [ DECIMAL(19,0) ] fields.
#!    If you get a WARNING that an IDENTITY is NOT defined, you have no PRIMARY key on the Clarion side.
#!    You will get a message before every IDENTITY or IDENTITY REFERENCE to check what is bellow.
#! 2012.02.17   Roberto Artigas - Version for Joe Hall
#!  * Unique requirements - Create variant with Jor Hall's name
#!  * Changed template to process the tables in name order.
#!  * Changed to IDENTITY(0, 1) for the zero blank record requirement.
#!  * Change the auto key generation logic for the requirements needed.
#!  * Added DATE and TIME to the field generation since MSSQL 2008 supports it.
#!------------------------------------------------------------------------------------------------------
#! 2005.01.26   Russell B. Eggen (regeen@radfusion.com) (search RBE)
#!  * added new defaults in hidden #BOXED group to distinguish using file prefix only on index names.
#!    This is so you can define the non-populating "PrimaryKey" for each table defined in the dct, which
#!    is illegal in SQL as index names are considered unique labels.
#!  * Changed default ALIAS contraint checks to be on by default.
#!  * Changed default to include comments.
#!  * Put back the "02.sql" file as the dct makes no distinction between a table and a view.
#!  * Added BLOB,BINARY support.
#!  * Added option to show completed scripts after generation.  Default is to not do this.
#!
#! 2004.01.21   Richard Bryce(richard@northshoreit.com.au) (search on RAB thru this file)
#!  * changed so that all CStrings over 7999 are treated as Text instead of Varchar
#!  * will now generate template errors based on SQL reserved words (to screen and a disk file, named
#!    dct2mssqlraberrors.txt in the directory you specify)
#!  * No "Alias" Constraints! check box added
#!  * output tables renamed, and yes I skipped "02.sql" on purpose. At this point I run my own scripts
#!    that will create the server defined view structures, and establish data in certain files, etc. As part of this
#!    I must delete from the "01.sql" file the scripts that are generated to build these Views
#!
#!  Possible Problems that are outstanding maybe
#!    * code to not generate certain clarion dct defined file structures that exist in the dictionary to give access to
#!      server defined view structures
#!    * possible problem with the constraints script, not using the external key name if it is specified (I THINK)
#!
#! 2002.11.14   Roberto Artigas Jr - Per Vadim Berman <vadim@power10.co.il>
#!  *   The correct syntax is: DROP INDEX People.KeyNR, but it won't work in your
#!      case (primary key).
#!      AFAIK, there is no need to DROP any of the indices in MSSQL to clean up (I
#!      understand this is the purpose) - MSSQL does it alone. As for DROPping a
#!      PRIMARY KEY, it is simply impossible - it will die only with the whole
#!      table.
#! 2002.11.13   Roberto Artigas Jr -
#!  *   Changed DROP PRIMARY KEY to DROP KEY in key logic area.
#! 2002.10.14   Roberto Artigas Jr - Gary R. Duchesneau
#!  *   Remove '[' and ']', they are not necessary.
#!      As long as you do not use any reserved words this is true.
#!      And if you use reserved words the name check logic will get it.
#!  *   Corrected SYSINDEXES by prefixing.
#!  *   Changed ALTER ADD INDEX to CREATE INDEX.
#!  *   Scripts when thru the MSSQL script executer.
#! 2002.05.31   Roberto Artigas Jr
#!  *   Made extra checking for DATE/TIME edit mask an option.
#!      This allows setting the backend fields as INTEGERs.
#!  *   Set default for delete of items before create to TRUE.
#!  *   Changed deletes for table and relations.
#!  *   Added '[' and ']' around identifiers and other items.
#!      This matches the scripts that do not use double quotes.
#! 2002.05.30   Roberto Artigas Jr
#!  *   Changed all individual DATE and TIME to combined DATETIME.
#!  *   Added option tab for script generation modifiers.
#!  *   Added option for domain prefix. Some folks are just not happy.
#!  *   Added options to turn off (default) the delete of the item
#!      before creation. Since I have not gotten anyone to tell me the
#!      correct items for the deletes I must as well make sure that they
#!      do not inhibit a correct script from being generated.
#! 2002.05.03   Roberto Artigas Jr
#!  *   Removed 'Standarized' options tab. They are not functional.
#! 2002.05.02   Roberto Artigas Jr
#!  *   Preliminary release sent to Didier (TABLE, GRANT, INDEX, RELATION)
#! 2002.04.27   Roberto Artigas Jr
#!  *   Preliminary release sent to Didier (TABLE, GRANT, INDEX)
#! 2002.04.25   Roberto Artigas Jr
#!  *   Created skeleton for MS SQL variant. (Rush job for Didier Le Duc)
#!---------------------------------------------------------------------
#UTILITY(Dct2MSSQL_Joe_Hall, '2016.02.27: Dictionary to MSSQL script')
#! Fixed some minor bugs in the template, some exposed in Clarion 8.
#! True/False value defaults changed to numeric, false prompt is no longer required.
#! Used with MSSQL Server 10 (2008).
#!---------------------------------------------------------------------
#BOXED('Hidden Variables'),HIDE
  #PROMPT('DictName: '    ,@S40 ),%DictName
  #PROMPT('DictPath: '    ,@S80 ),%DictPath
  #PROMPT('nLoc1: '       ,@N4  ),%nLoc1
  #PROMPT('nLoc2: '       ,@N4  ),%nLoc2
  #PROMPT('OutTableName: ',@S120),%OutTableName
  #PROMPT('OutGrantName: ',@S120),%OutGrantName
  #PROMPT('OutIndexName: ',@S120),%OutIndexName
  #PROMPT('OutRelatName: ',@S120),%OutRelatName
  #PROMPT('OutCmntsName: ',@S120),%OutCmntsName
#ENDBOXED
#!
#SHEET,HSCROLL
  #TAB('General')
    #BOXED('')
      #DISPLAY('The MSSQL Script Creator.')
      #DISPLAY('Copyright 2002-2013 © by Roberto Artigas Jr.')
      #DISPLAY('All Rights Reserved World Wide.')
      #DISPLAY('')
      #PROMPT('Directory:'  ,@S80 ),%SQLDirectory,AT(52,,130)
      #DISPLAY('')
      #PROMPT('Generate CREATE?'   ,CHECK),%SQLTable,AT(10),DEFAULT(%TRUE)
      #PROMPT('CREATE Script:'     ,@S40 ),%SQLTableName,AT(82,,100)
      #DISPLAY('')
      #PROMPT('Generate GRANT? '   ,CHECK),%SQLGrant,AT(10),DEFAULT(%TRUE)
      #PROMPT('GRANT Script:'      ,@S40 ),%SQLGrantName,AT(82,,100)
      #DISPLAY('')
      #PROMPT('Generate INDEX?'    ,CHECK),%SQLIndex,AT(10),DEFAULT(%TRUE)
      #PROMPT('INDEX Script:'      ,@S40 ),%SQLIndexName,AT(82,,100)
      #DISPLAY('')
      #PROMPT('Generate RELATION?' ,CHECK),%SQLRelat,AT(10),DEFAULT(%TRUE)
      #PROMPT('RELATION Script:'   ,@S40 ),%SQLRelatName,AT(82,,100)
      #DISPLAY('')
      #PROMPT('Generate Comments? ',CHECK),%SQLCmnts,AT(10),DEFAULT(%TRUE)
      #PROMPT('Comments Script:'   ,@S40 ),%SQLCmntsName,AT(82,,100)
    #ENDBOXED
  #ENDTAB
  #TAB('Options')
    #BOXED('')
      #PROMPT('Domain Prefix:'   ,@S20 ),%SQLPrefix,AT(82,,100),DEFAULT('dbo')
      #DISPLAY('')
      #PROMPT('Delete tables before creation?'    ,CHECK),%DelTable,DEFAULT(%TRUE),AT(10)
      #PROMPT('Delete indexes before creation?'   ,CHECK),%DelIndex,DEFAULT(%TRUE),AT(10)
      #PROMPT('Delete relations before creation?' ,CHECK),%DelRelat,DEFAULT(%TRUE),AT(10)
      #PROMPT('Delete comments before creation?'  ,CHECK),%DelCmnts,DEFAULT(%TRUE),AT(10)
      #DISPLAY('')
      #BOXED('Date and Time checks')                                              #!RBE - 20050128 - Extra dialog added
        #DISPLAY('')
        #DISPLAY('The following does some extra date and time ')
        #DISPLAY('checking.  If you add a TIMESTAMP option in ')
        #DISPLAY('your date and time fields and they are turned on,')
        #DISPLAY('these fields will use TIMESTAMP instead of ')
        #DISPLAY('DATETIME.')
        #DISPLAY('')
        #DISPLAY('CAUTION: MSSQL allows only one instance per file.')
        #DISPLAY('')
        #DISPLAY('Use the field option only for audit purposes ')
        #DISPLAY('and NEVER as part of a Primary key!')
        #DISPLAY('')
        #PROMPT('Extra checking for DATE and TIME?' ,CHECK),%ExtraDTTM,DEFAULT(%FALSE),WHENACCEPTED(%TimeStampGroup3()),AT(10)
        #ENABLE(%ExtraDTTM=%True)
          #PROMPT('Timestamp user option checking?',CHECK),%TimeStampOption,DEFAULT(%False),AT(10)
        #ENDENABLE
      #ENDBOXED
    #ENDBOXED
  #ENDTAB
  #TAB('Arrays')
    #BOXED('')
      #DISPLAY('There are two ways to handle arrays:')
      #DISPLAY('')
      #DISPLAY('1) You defined a group with all the fields of the array')
      #DISPLAY('inside this group. Then you put the array OVER the')
      #DISPLAY('group. If you set up your arrays in this manner, then')
      #DISPLAY('LEAVE the checkbox bellow OFF.')
      #DISPLAY('')
      #DISPLAY('2) You define your arrays as actual fields using no')
      #DISPLAY('groups whatsoever. If you set up your arrays this way,')
      #DISPLAY('then TURN ON the checkbox bellow.')
      #DISPLAY('')
      #DISPLAY('')
      #PROMPT('I DO NOT HAVE ARRAYS OVER GROUPS. ',CHECK),%NoArrayOver,AT(10),DEFAULT(%False)
      #DISPLAY('')
    #ENDBOXED
  #ENDTAB
  #TAB('Misc stuff')
    #PROMPT('No "Alias" Constraints!',CHECK),%NoAliasConstraints,AT(10),DEFAULT(%False)
    #PROMPT('Force BOOLEAN validation to [Y,N] choices? ',CHECK),%ForceBoolYN,AT(10),DEFAULT(%True)
    #PROMPT('Prefix tables and rows with file prefix? ',CHECK),%UsePrefix,AT(10),DEFAULT(%False)
    #! The following allows you to have the same key name in the dct for each file.  In Clarion, this is allowed
    #! since the prefix makes it unique.  However, this is not allowed in MSSQL.  For example, in every file you have
    #! a RecID or SysID field.  You make a key labelled "PrimaryKey" with this unique ID as a component.  From a Clarion
    #! viewpoint, this makes perfect sense as you know relationships are built from this key and you don't have to remember or
    #! decipher cryptic PK_Keyname type constructs.
    #PROMPT('Prefix index with file prefix? ',CHECK),%UsePrefixIndex,AT(10),DEFAULT(%True)           #!RBE 20050126
    #PROMPT('Do KEYFIELD-FILE list to create relations? ',CHECK),%DoFieldFile,AT(10),DEFAULT(%False)
    #PROMPT('True Value (Y,T,1): ',@S1),%TrueValue,REQ,DEFAULT(1)
    #PROMPT('False Value (N,F,0): ',@S1),%FalseValue,DEFAULT(0)
    #PROMPT('Show scripts after generation?',CHECK),%ShowScripts,AT(10),DEFAULT(%True)
    #PROMPT('Ignore RESERVED words (Do NOT check):',CHECK),%IgnoredReserved,AT(10),DEFAULT(%TRUE)    #! RA.2012.02.27
    #DISPLAY('')
    #BOXED('Joe Hall Specials')                                                                      #! RA.2012.05.02
      #PROMPT('Using COLLATION on strings?',CHECK),%HaveCollation,AT(10),DEFAULT(%TRUE)              #! RA.2012.03.02
      #ENABLE(%HaveCollation=%True)                                                                  #! RA.2012.03.02
        #PROMPT('Collation Value',@S40),%CollationValue,DEFAULT('Latin1_General_CI_AS')              #! RA.2012.03.02
      #ENDENABLE                                                                                     #! RA.2012.03.02
      #PROMPT('Convert CHAR to VARCHAR?',CHECK),%DoVarcharConvert,AT(10),DEFAULT(%TRUE)              #! RA.2012.05.02
      #PROMPT('Automovedmatic BIGINT is not an IDENTITY',CHECK),%BigintNotIdentity,AT(10),DEFAULT(%TRUE)  #! RA.2012.05.02
    #ENDBOXED                                                                                        #! RA.2012.05.02
  #ENDTAB
#ENDSHEET
#! RA.2016.02.27 - Moved outside the sheet for C10
#!  #BOXED('Hidden Variables'),HIDE
#!  #PROMPT('Force BOOLEAN validation to [Y,N] choices? ',CHECK),%ForceBoolYN,AT(10),DEFAULT(%True)
#!  #PROMPT('Prefix tables and rows with file prefix? ',CHECK),%UsePrefix,AT(10),DEFAULT(%False)
  #! The following allows you to have the same key name in the dct for each file.  In Clarion, this is allowed
  #! since the prefix makes it unique.  However, this is not allowed in MSSQL.  For example, in every file you have
  #! a RecID or SysID field.  You make a key labelled "PrimaryKey" with this unique ID as a component.  From a Clarion
  #! viewpoint, this makes perfect sense as you know relationships are built from this key and you don't have to remember or
  #! decipher cryptic PK_Keyname type constructs.
#!  #PROMPT('Prefix index with file prefix? ',CHECK),%UsePrefixIndex,AT(10),DEFAULT(%True)      #!RBE 20050126
#!  #PROMPT('Do KEYFIELD-FILE list to create relations? ',CHECK),%DoFieldFile,AT(10),DEFAULT(%False)
#!  #PROMPT('True Value (Y,T,1): ',@S1),%TrueValue,REQ,DEFAULT('Y')
#!  #PROMPT('False Value (N,F,0): ',@S1),%FalseValue,REQ,DEFAULT('N')
 #! #ENDBOXED
#!---------------------------------------------------------------------
#PREPARE
#!
#SET(%nLoc1,INSTRING('\',%DictionaryFile,1,1))
#LOOP,WHILE(%nLoc1)
  #SET(%nLoc2,%nLoc1)
  #SET(%nLoc1,INSTRING('\',%DictionaryFile,1,%nLoc1+1))
#ENDLOOP
#SET(%DictName,UPPER(SUB(%DictionaryFile,%nLoc2+1,LEN(%DictionaryFile)-%nLoc2-4)))
#SET(%DictPath,UPPER(SUB(%DictionaryFile,1,%nLoc2)))
#!
#SET(%SQLDirectory,%DictPath)
#SET(%SQLTableName,%DictName & '_JH1.SQL')    #! RBE 20050125
#SET(%SQLGrantName,%DictName & '_JH2.SQL')    #! RBE 20050125
#SET(%SQLIndexName,%DictName & '_JH3.SQL')    #! RBE 20050125
#SET(%SQLRelatName,%DictName & '_JH4.SQL')    #! RBE 20050125
#SET(%SQLCmntsName,%DictName & '_JH9.SQL')    #! RA.2012.03.07
#!
#ENDPREPARE
#!---------------------------------------------------------------------
#DECLARE(%FileSorted),MULTI,UNIQUE	#! RA.2012.02.17
#DECLARE(%TemplateErrorMessage)         #! RAB 20040121
#DECLARE(%TheComment)                   #! RA.2012.02.17
#!
#DECLARE(%NFileUser)                    #! File user options [RASQL]
#DECLARE(%NFileOpt1)                    #! File option 1 [NO]
#DECLARE(%NFileOpt2)                    #! File option 2
#DECLARE(%NFileOpt3)                    #! File option 3
#DECLARE(%NFileOpt4)                    #! File option 4
#!
#DECLARE(%NFieldUser)                   #! Field user options [RASQL]
#DECLARE(%NFieldOpt1)                   #! Field option 1 [NO]
#DECLARE(%NFieldOpt2)                   #! Field option 2
#DECLARE(%NFieldOpt3)                   #! Field option 3
#DECLARE(%NFieldOpt4)                   #! Field option 4
#!
#DECLARE(%NKeyUser)                     #! Key user options [RASQL]
#DECLARE(%NKeyOpt1)                     #! Key option 1 [NO]
#DECLARE(%NKeyOpt2)                     #! Key option 2
#DECLARE(%NKeyOpt3)                     #! Key option 3
#DECLARE(%NKeyOpt4)                     #! Key option 4
#!
#DECLARE(%NRelationUser)                #! Relation user options [RASQL]
#DECLARE(%NRelationOpt1)                #! Relation option 1 [NO]
#DECLARE(%NRelationOpt2)                #! Relation option 2
#DECLARE(%NRelationOpt3)                #! Relation option 3
#DECLARE(%NRelationOpt4)                #! Relation option 4
#!
#DECLARE(%NFileName)                    #! File Name
#DECLARE(%NFileType)                    #! TYPE=???
#DECLARE(%NFileRowFormat)               #! ROW_FORMAT=???
#!
#DECLARE(%NFieldPKey)                   #! Field is Primary Key?
#DECLARE(%NFieldLine)                   #! Field line
#DECLARE(%NFieldName)                   #! Field Name
#DECLARE(%NFieldTSql)                   #! Field SQL type
#DECLARE(%NFieldTDef)                   #! Field SQL default
#DECLARE(%NFieldType)                   #! Field type
#DECLARE(%NFieldDef)                    #! Field default
#DECLARE(%NFieldCheck)                  #! Field CHECK(...) constraint
#DECLARE(%NFieldCheckOver)              #! Field CHECK(...) constraint override
#DECLARE(%NFieldAuto)                   #! Field AUTO_INCREMENT
#DECLARE(%NFieldCollate)		#! Field COLLATE sequence                #! RA.2012.03.02
#DECLARE(%NFieldNN)                     #! Field NOT NULL
#DECLARE(%NFieldSize  ,LONG)            #! Field decimal size
#DECLARE(%NFieldDec   ,LONG)            #! Field decimal places
#DECLARE(%NLeadZero   ,LONG)            #! Have a leading zero
#DECLARE(%NFNameAuto)                   #! Field Name Auto Increment
#!
#DECLARE(%NKeyName)                     #! Key name
#DECLARE(%NKeyFields)                   #! Key building line
#DECLARE(%NKeyField)                    #! Key field current
#DECLARE(%NKeyFieldCount)               #! Number of key fields
#DECLARE(%PrimaryCount)                 #! Primary key exists
#DECLARE(%SecondaryCount)               #! Secondary key exists
#DECLARE(%AutoIncrementCount)           #! Auto-Increment key exists
#!
#DECLARE(%NRelateItem)                  #! Working item number
#DECLARE(%NRelateCount)                 #! Total items to work
#DECLARE(%NRelateComma)                 #! Number of commas
#DECLARE(%NRelateFile)                  #! Related file
#DECLARE(%NRelateField1)                #! Related origin field
#DECLARE(%NRelateField2)                #! Related relate field
#DECLARE(%RelationField1Count)          #! Count of origin file key items
#DECLARE(%RelationField2Count)          #! Count of relate file key items
#DECLARE(%RelationField1)               #! The origin file keys
#DECLARE(%RelationField2)               #! The relate file keys
#DECLARE(%LastComponent)                #! The last component of a compound key. Added by RBE 2005.4.18
#DECLARE(%FirstComponent)               #! The first component of a compund key. Added by RBE 2005.4.18
#DECLARE(%RelationUpdate)               #! ON UPDATE ?
#DECLARE(%RelationDelete)               #! ON DELETE ?
#!
#!#DECLARE(%nLoc1       ,LONG)            #! Location 1
#!#DECLARE(%nLoc2       ,LONG)            #! Location 2
#DECLARE(%TheCount    ,LONG)            #! Count posible relations
#DECLARE(%CStringLen)                   #! Length of types shortened by one
#DECLARE(%LastField)                    #! Last field processed
#DECLARE(%InnerGroup)                   #! Inner group level processed
#DECLARE(%FieldComma)                   #! Number of fields - 1
#!
#DECLARE(%ChoiceLine)                   #! Build ENUM field type line
#DECLARE(%ChoiceComma)                  #! Count ENUM values - 1
#!
#DECLARE(%DocVar)                       #! Documentation Variable
#DECLARE(%TableCount, 0)                #! Count of tables
#DECLARE(%AutoCount, 0)                 #! Count of Auto-Increment Keys
#DECLARE(%PKeyCount, 0)                 #! Count of primary keys (From Keys)
#DECLARE(%PKeyOption, 0)                #! Count of primary keys (From Options)
#DECLARE(%KeyOthers, 0)                 #! Count of secondary keys
#DECLARE(%KeyCount, 0)                  #! Count of all keys combined
#DECLARE(%FieldCount, 0)                #! Count of fields
#DECLARE(%RelCount, 0)                  #! Relationship Counts
#DECLARE(%FieldTable),MULTI,UNIQUE      #! List of unique fields
#DECLARE(%FieldUnique, 0)               #! Count of unique fields
#!
#DECLARE(%TheDate)                      #! Display current date
#DECLARE(%TheTime)                      #! Display current time
#SET(%TheDate,FORMAT(TODAY(),@D010))    #! Set current date
#SET(%TheTime,FORMAT(CLOCK(),@T06))     #! Set current time
#!
#DECLARE (%DimCount   ,LONG)            #! Number of dimensions
#DECLARE (%FldArray   ,STRING)          #! Created field name from array
#DECLARE (%FD1S       ,STRING)          #! Number for dimension 1
#DECLARE (%FD2S       ,STRING)          #! Number for dimension 2
#DECLARE (%FD3S       ,STRING)          #! Number for dimension 3
#DECLARE (%FD4S       ,STRING)          #! Number for dimension 4
#DECLARE (%FD1I       ,LONG)            #! Looping in Dimension 1
#DECLARE (%FD2I       ,LONG)            #! Looping in Dimension 2
#DECLARE (%FD3I       ,LONG)            #! Looping in Dimension 3
#DECLARE (%FD4I       ,LONG)            #! Looping in Dimension 4
#DECLARE (%FD1L       ,LONG)            #! Set to %FieldDimension1
#DECLARE (%FD2L       ,LONG)            #! Set to %FieldDimension2
#DECLARE (%FD3L       ,LONG)            #! Set to %FieldDimension3
#DECLARE (%FD4L       ,LONG)            #! Set to %fieldDimension4
#!--------------------------------------------------------------!
#! List of table, key and rows names found in reserved list.    !
#!--------------------------------------------------------------!
#DECLARE(%ReservedFieldList),MULTI,UNIQUE
#EQUATE (%FoundFileName     ,'File Name:          ')
#EQUATE (%FoundFileField    ,'File Field Name:    ')
#EQUATE (%FoundAIKeyName    ,'AI Key Name:        ')
#EQUATE (%FoundKeyName      ,'Key Name:           ')
#EQUATE (%FoundKeyField     ,'Key Field Name:     ')
#EQUATE (%FoundRelatedName  ,'Related File Name:  ')
#EQUATE (%FoundRelatedField ,'Related Field Name: ')
#!--------------------------------------------------------------------------!
#! RAS.2001.12.15 - Table of field-table to be able to create relations.    !
#!--------------------------------------------------------------------------!
#DECLARE(%FieldTableList),MULTI,UNIQUE
#!---------------------------------------------------------------------
#DECLARE(%LFieldInKey),MULTI,UNIQUE     #! Field list that are used in keys
#DECLARE(%IFieldInKey)                  #! Field item to search for in list
#DECLARE(%LFieldInKeyCount)             #! How many fields are in keys?
#FREE(%LFieldInKey)
#FOR(%File)
  #IF(ITEMS(%Key))
    #FOR(%Key)
      #FOR(%KeyField)
        #ADD(%LFieldInKey,%KeyField)
      #ENDFOR
    #ENDFOR
  #ENDIF
#ENDFOR
#SET(%LFieldInKeyCount,ITEMS(%LFieldInKey))
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#INSERT(%ClarionReservedWords)
#INSERT(%SqlReservedWords)
#FOR(%ClarionWordList)
  #ADD(%SqlWordList,%ClarionWordList)
#ENDFOR
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#SET(%TableCount, 0)                    #! Count of tables
#SET(%AutoCount, 0)                     #! Count of Auto-Increment Keys
#SET(%PKeyCount, 0)                     #! Count of primary keys (From Keys)
#SET(%PKeyOption, 0)                    #! Count of primary keys (From Options)
#SET(%KeyOthers, 0)                     #! Count of secondary keys
#SET(%KeyCount, 0)                      #! Count of all keys combined
#SET(%FieldCount, 0)                    #! Count of fields
#SET(%RelCount, 0)                      #! Relationship Counts
#SET(%FieldUnique, 0)                   #! Count of unique fields
#!---------------------------------------------------------------------
#!
#! this entire block of code by RAB 20040121
#SET(%OutTableName,%SQLDirectory & 'dct2mssqlerrors.txt')
#CREATE(%OutTableName)
#FOR(%File)                                                 #! RAB 20040121
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
  #OROF('TOPSPEED')
      #CYCLE
  #ENDCASE
  #!
  #SET(%SqlWordItem, UPPER(%File))
  #FIND(%SqlWordList, %SqlWordItem)
  #IF(%SqlWordList)
    #SET(%TemplateErrorMessage, 'FILE: ' & %File & '  (' & %FileDriver & ')' )
    #ERROR(%TemplateErrorMessage)
%TemplateErrorMessage
  #ENDIF
  #FOR(%Field)
    #SET(%SqlWordItem, UPPER(%FieldID))
    #FIND(%SqlWordList, %SqlWordItem)
    #IF(%SqlWordList)
      #SET(%TemplateErrorMessage, 'FILE: ' & %File & ' >===> FIELD: ' & %SqlWordItem)
      #ERROR(%TemplateErrorMessage)
%TemplateErrorMessage
    #ENDIF
  #ENDFOR
#ENDFOR
#CLOSE(%OutTableName)
#! this entire block of code by RAB 20040121
#!
#!---------------------------------------------------------------------
#IF(%SQLTable)
  #SET(%OutTableName,%SQLDirectory & %SQLTableName)
  #CREATE(%OutTableName)
  #MESSAGE('The MSSQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLTableName,1)
--
-- BOF: %OutTableName
--
-- SQL script creator for MSSQL database [TABLE]
-- Created: %TheDate - %TheTime
--

#!---------------------------------------------------!
#! This block added to show the tables in name order !
#!---------------------------------------------------!
#FOR(%File)                                                     #! RAS.2012.02.17
  #ADD(%FileSorted, %File)                                      #! RAS.2012.02.17
#ENDFOR                                                         #! RAS.2012.02.17
#FOR(%FileSorted)                                               #! RAS.2012.02.17
#FIX(%File, %FileSorted)                                        #! RAS.2012.02.17
  #!----------------------------------------------------------------!
  #! RAS.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.   !
  #!----------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RAS.2001.12.01
    #CYCLE                                                      #! RAS.2001.12.01
  #ENDIF                                                        #! RAS.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '')                     #! RA.2012.02.22
#!
  #SET(%NFileName,%File)
  #IF(%UsePrefix)
    #SET(%NFileName,%FilePrefix & '_' & %File)
  #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
  #IF(%IgnoredReserved = %FALSE)                                     #! RA.2012.02.27
    #SET(%SqlWordItem, UPPER(%NFileName))
    #FIND(%SqlWordList, %SqlWordItem)
    #IF(%SqlWordList)
      #ADD(%ReservedFieldList, %FoundFileName & %NFileName)
      #SET(%NFileName, %NFileName & '_')
    #ENDIF
  #ENDIF                                                        #! RA.2012.02.27
#!
  #!------------------------------------------------------------------------!
  #! IF it IS a primary key, AND it is an integer, AND NOT auto-increment,  !
  #! AND there is one item in the key (only thing I can be sure about),     !
  #! THEN assume that it IS an auto-increment handled by the MSSQL backend. !
  #!------------------------------------------------------------------------!
  #SET(%NFNameAuto,'')
  #SET(%AutoIncrementCount,0)
  #IF(ITEMS(%Key))
    #FOR(%Key)
#!---- Key        = %Key
      #IF(%KeyPrimary <> 1)                             #!Contains 1 if the key is the file's primary key
        #CYCLE
      #ENDIF
#!---- KeyPrimary = %KeyPrimary
      #IF(%KeyAuto <> '')
        #SET(%NFNameAuto, SUB(%KeyAuto, INSTRING(':',%KeyAuto,1,1) + 1 , 50))
        #ERROR('Auto increment name: "' & %KeyAuto & '" name exits?' )
        #CYCLE
      #ENDIF
#!---- KeyAuto    = %KeyAuto
#!
      #IF(ITEMS(%KeyField) > 1)
        #CYCLE
      #ENDIF
#!
      #FOR(%KeyField)
#!---- KeyField   = %KeyField
        #FIX(%Field,%KeyField)
#!---- Field      = %Field
#!---- FieldType  = %FieldType
        #CASE(%FieldType)
        #OF  ('LONG')
        #OROF('DECIMAL')                                                       #! RAS.2012.02.17
          #IF(%KeyAuto)
            #SET(%NFNameAuto, SUB(%KeyAuto, INSTRING(':',%KeyAuto,1,1) + 1 , 50))
          #ELSE                                                                #! RAS.2012.02.17
            #SET(%NFNameAuto, SUB(%Field, INSTRING(':',%Field,1,1) + 1 , 50))  #! RAS.2012.02.17
          #ENDIF
        #ENDCASE
      #ENDFOR
#!
#!---- NFNameAuto = %NFNameAuto
      #IF(%NFNameAuto)
        #SET(%AutoIncrementCount,%AutoIncrementCount + 1)
        #SET(%AutoCount, %AutoCount + 1)
#!        #SET(%NFNameAuto, SUB(%KeyAuto, INSTRING(':',%KeyAuto,1,1) + 1 , 50))
        #IF(%UsePrefix)                                                        #!RBE 20050126
          #SET(%NFNameAuto, %FilePrefix & '_' & %NFNameAuto)
        #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
        #IF(%IgnoredReserved = %FALSE)                                              #! RA.2012.02.27
          #SET(%SqlWordItem, UPPER(%NFNameAuto))
          #FIND(%SqlWordList, %SqlWordItem)
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundAIKeyName & %FilePrefix & ':' & %NFNameAuto)
            #SET(%NFNameAuto, %NFNameAuto & '_')
          #ENDIF
        #ENDIF                                                                 #! RA.2012.02.27
      #ENDIF
    #ENDFOR
    #IF(%AutoIncrementCount = 0)
---- WARNING: %File(%FilePrefix) HAS NO AUTO-INCREMENT KEY FROM THE CLARION SIDE DEFINED (IS THIS CORRECT?).
    #ENDIF
  #ENDIF
#!
------------------------------------------------------------
PRINT '[%SQLPrefix].[%NFileName]'
  #IF(%DelTable)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('[%SQLPrefix].[%NFileName]')
AND OBJECTPROPERTY(ID,'IsUserTable') = 1)
    DROP TABLE [%SQLPrefix].[%NFileName]
GO

  #ENDIF
CREATE TABLE [%SQLPrefix].[%NFileName] (
#INSERT(%MSSQLField3)                                           #! Process fields
)
GO

#ENDFOR
-- Create needed views

-- This view is for getting that last unique ID added to any table
-- in the proper scope.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'IDView')
   DROP VIEW IDView
GO
CREATE VIEW [IDView]
AS SELECT scope_identity() as LastID

--
-- EOF: %OutTableName
--
  #CLOSE(%OutTableName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLGrant)
  #SET(%OutGrantName,%SQLDirectory & %SQLGrantName)
  #CREATE(%OutGrantName)
  #MESSAGE('The MSSQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLGrantName,1)
--
-- BOF: %OutGrantName
--
-- SQL script creator for MSSQL database [GRANT]
-- Created: %TheDate - %TheTime
--

#!---------------------------------------------------!
#! This block added to show the tables in name order !
#!---------------------------------------------------!
#FOR(%File)                                                     #! RAS.2012.02.17
  #ADD(%FileSorted, %File)                                      #! RAS.2012.02.17
#ENDFOR                                                         #! RAS.2012.02.17
#FOR(%FileSorted)                                               #! RAS.2012.02.17
#FIX(%File, %FileSorted)                                        #! RAS.2012.02.17
  #!----------------------------------------------------------------!
  #! RAS.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.   !
  #!----------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RAS.2001.12.01
    #CYCLE                                                      #! RAS.2001.12.01
  #ENDIF                                                        #! RAS.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '')                     #! RA.2012.02.22
#!
  #SET(%NFileName,%File)
  #IF(%UsePrefix)
    #SET(%NFileName,%FilePrefix & '_' & %File)
  #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
  #IF(%IgnoredReserved = %FALSE)                                     #! RA.2012.02.27
    #SET(%SqlWordItem, UPPER(%NFileName))
    #FIND(%SqlWordList, %SqlWordItem)
    #IF(%SqlWordList)
      #ADD(%ReservedFieldList, %FoundFileName & %NFileName)
      #SET(%NFileName, %NFileName & '_')
    #ENDIF
  #ENDIF                                                        #! RA.2017.02.27
#!
GRANT SELECT, INSERT, DELETE, UPDATE ON [%SQLPrefix].[%NFileName]
   TO PUBLIC;
GO

#ENDFOR
--
-- EOF: %OutGrantName
--
  #CLOSE(%OutGrantName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLIndex)
  #SET(%OutIndexName,%SQLDirectory & %SQLIndexName)
  #CREATE(%OutIndexName)
  #MESSAGE('The MSSQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLIndexName,1)
--
-- BOF: %OutIndexName
--
-- SQL script creator for MSSQL database [INDEX]
-- Created: %TheDate - %TheTime
--

#!---------------------------------------------------!
#! This block added to show the tables in name order !
#!---------------------------------------------------!
#FOR(%File)                                                     #! RAS.2012.02.17
  #ADD(%FileSorted, %File)                                      #! RAS.2012.02.17
#ENDFOR                                                         #! RAS.2012.02.17
#FOR(%FileSorted)                                               #! RAS.2012.02.17
#FIX(%File, %FileSorted)                                        #! RAS.2012.02.17
  #!----------------------------------------------------------------!
  #! RAS.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.   !
  #!----------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RAS.2001.12.01
    #CYCLE                                                      #! RAS.2001.12.01
  #ENDIF                                                        #! RAS.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '')                     #! RA.2012.02.22
#!
  #SET(%NFileName,%File)
  #IF(%UsePrefix)
    #SET(%NFileName,%FilePrefix & '_' & %File)
  #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
  #IF(%IgnoredReserved = %FALSE)                                     #! RA.2012.02.27
    #SET(%SqlWordItem, UPPER(%NFileName))
    #FIND(%SqlWordList, %SqlWordItem)
    #IF(%SqlWordList)
      #ADD(%ReservedFieldList, %FoundFileName & %NFileName)
      #SET(%NFileName, %NFileName & '_')
    #ENDIF
  #ENDIF                                                        #! RA.2012.02.27
#!
------------------------------------------------------------
PRINT '[%SQLPrefix].[%NFileName]'
  #SET(%PrimaryCount,0)                                         #! Primary key exists
  #SET(%SecondaryCount,0)                                       #! Secondary key exists
  #IF(ITEMS(%Key))
    #FOR(%Key)
      #IF(NOT ITEMS(%KeyField))                                 #! Key has fields? No!
        #CYCLE                                                  #! Then go to the next
      #ENDIF                                                    #! key to be processed
#!
      #SET(%nLoc1,INSTRING('RASQL(',%KeyUserOptions,1,1))
      #SET(%nLoc2,INSTRING(')',%KeyUserOptions,1,%nLoc1))
      #SET(%nKeyUser,UPPER(SUB(%KeyUserOptions,%nLoc1,%nLoc2)))
      #!SET(%NKeyUser,UPPER(EXTRACT(%KeyUserOptions,'RASQL')))
      #SET(%NKeyOpt1,EXTRACT(%NKeyUser,'RASQL',1))
      #SET(%NKeyOpt2,EXTRACT(%NKeyUser,'RASQL',2))
      #SET(%NKeyOpt3,EXTRACT(%NKeyUser,'RASQL',3))
      #SET(%NKeyOpt4,EXTRACT(%NKeyUser,'RASQL',4))
#!%NKeyUser, %NKeyOpt1, %NKeyOpt2, %NKeyOpt3, %NKeyOpt4
      #!------------------------------------------------------------!
      #! The first option RASQL(NO) controls if a key is processed. !
      #!------------------------------------------------------------!
      #IF(SUB(%NKeyOpt1,1,1)='N')
        #CYCLE
      #ENDIF
#!
      #SET(%NKeyName,%KeyID)
      #IF(%UsePrefixIndex)                                                           #!RBE 20050126
        #SET(%NKeyName,%FilePrefix & '_' & %KeyID)
      #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
      #IF(%IgnoredReserved = %FALSE)                                                      #! RA.2012.02.27
        #SET(%SqlWordItem, UPPER(%NKeyName))
        #FIND(%SqlWordList, %SqlWordItem)
        #IF(%SqlWordList)
          #ADD(%ReservedFieldList, %FoundKeyName & %FilePrefix & ':' & %NKeyName)
          #SET(%NKeyName, %NKeyName & '_')
        #ENDIF
      #ENDIF                                                                         #! RA.2012.02.27
#!
      #SET(%NKeyFields,'')
      #SET(%NKeyFieldCount,0)
      #FOR(%KeyField)
        #SET(%NKeyField, SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1 , 50))
        #!--------------------------------------------------------------------------!
        #! RAS.2001.12.15 - Table of keyfield-table to be able to create relations. !
        #!--------------------------------------------------------------------------!
        #IF(%DoFieldFile)
          #ADD(%FieldTableList, %NKeyField & ' ==> ' & %File)
        #ENDIF
        #!
        #IF(%UsePrefix)
          #SET(%NKeyField, %FilePrefix & '_' & %NKeyField)
        #ENDIF
        #IF(%IgnoredReserved = %FALSE)                                                    #! RA.2012.03.02
          #SET(%SqlWordItem, UPPER(%NKeyField))
#!-- SqlWordItem = '%SqlWordItem'
          #FIND(%SqlWordList, %SqlWordItem)
#!-- SqlWordList = '%SqlWordList'
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundKeyField & %FilePrefix & ':' & %NKeyField)
            #SET(%NKeyField, %NKeyField & '_')
          #ENDIF
	#ENDIF                                                                            #! RA.2012.03.02
        #SET(%NKeyFieldCount,%NKeyFieldCount + 1 )
        #IF(%NKeyFieldCount = 1)
          #SET(%NKeyFields,'[' & %NKeyField & ']')
        #ELSE
          #SET(%NKeyFields, %NKeyFields & ', [' & %NKeyField & ']')
        #ENDIF
      #ENDFOR
      #IF(%KeyPrimary)
        #SET(%PrimaryCount, 1)
        #SET(%PKeyCount, %PKeyCount + 1)
        #SET(%KeyCount, %KeyCount + 1)
        #IF(%DelIndex)
IF EXISTS (SELECT 1 FROM SYSINDEXES
           WHERE NAME = '[%NKeyName]'
             AND SYSINDEXES.ID   = OBJECT_ID ('[%SQLPrefix].[%NFileName]')
             AND SYSINDEXES.INDID BETWEEN 1 AND 254)
  DROP INDEX [%SQLPrefix].[%NFileName].[%NKeyName]
GO

        #ENDIF
ALTER TABLE [%SQLPrefix].[%NFileName] ADD
  CONSTRAINT [%NKeyName]
  PRIMARY KEY CLUSTERED (%NKeyFields)
GO

      #ELSIF(NOT %KeyPrimary)
        #SET(%KeyOthers, %KeyOthers + 1)
        #SET(%KeyCount, %KeyCount + 1)
        #IF(%KeyDuplicate)
          #CASE(%KeyIndex)
          #OF  ('KEY')
            #IF(%DelIndex)
IF EXISTS (SELECT 1 FROM SYSINDEXES
           WHERE NAME = '%NKeyName'
             AND SYSINDEXES.ID   = OBJECT_ID ('[%SQLPrefix].[%NFileName]')
             AND SYSINDEXES.INDID BETWEEN 1 AND 254)
  DROP INDEX [%SQLPrefix].[%NFileName].[%NKeyName]
GO

            #ENDIF
#!ALTER TABLE %SQLPrefix.%NFileName ADD
#!   INDEX %NKeyName
#!  (%NKeyFields)
#!GO
CREATE NONCLUSTERED INDEX [%NKeyName] ON [%SQLPrefix].[%NFileName]
  (%NKeyFields)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

          #OF  ('INDEX')
          #OROF('DYNAMIC')
            #IF(%DelIndex)
IF EXISTS (SELECT 1 FROM SYSINDEXES
           WHERE NAME = '%NKeyName'
             AND SYSINDEXES.ID   = OBJECT_ID ('[%SQLPrefix].[%NFileName]')
             AND SYSINDEXES.INDID BETWEEN 1 AND 254)
  DROP INDEX [%SQLPrefix].[%NFileName].[%NKeyName]
GO

            #ENDIF
#!ALTER TABLE %SQLPrefix.%NFileName ADD
#!  INDEX %NKeyName
#!  (%NKeyFields)
#!GO
CREATE NONCLUSTERED INDEX [%NKeyName] ON [%SQLPrefix].[%NFileName]
  (%NKeyFields)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

          #ENDCASE
        #ELSE
          #CASE(%KeyIndex)
          #OF  ('KEY')
            #IF(%DelIndex)
IF EXISTS (SELECT 1 FROM SYSINDEXES
           WHERE NAME = '[%NKeyName]'
             AND SYSINDEXES.ID   = OBJECT_ID ('[%SQLPrefix].[%NFileName]')
             AND SYSINDEXES.INDID BETWEEN 1 AND 254)
  DROP INDEX %SQLPrefix.%NFileName.%NKeyName
GO

            #ENDIF
#!ALTER TABLE %SQLPrefix.%NFileName ADD
#!  UNIQUE INDEX %NKeyName
#!  (%NKeyFields)
#!GO
CREATE UNIQUE NONCLUSTERED INDEX [%NKeyName] ON [%SQLPrefix].[%NFileName]
  (%NKeyFields)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

          #OF  ('INDEX')
          #OROF('DYNAMIC')
            #IF(%DelIndex)
IF EXISTS (SELECT 1 FROM SYSINDEXES
           WHERE NAME = '[%NKeyName]'
             AND SYSINDEXES.ID   = OBJECT_ID ('[%SQLPrefix].[%NFileName]')
             AND SYSINDEXES.INDID BETWEEN 1 AND 254)
  DROP INDEX %SQLPrefix.%NFileName.%NKeyName
GO

            #ENDIF
#!ALTER TABLE %SQLPrefix.%NFileName ADD
#!  UNIQUE INDEX %NKeyName
#!  (%NKeyFields)
#!GO
CREATE INDEX [%NKeyName] ON [%SQLPrefix].[%NFileName]
  (%NKeyFields)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

          #ENDCASE
        #ENDIF
      #ENDIF
    #ENDFOR
    #IF(NOT %PrimaryCount)
--
-- WARNING: %File(%FilePrefix) has no PRIMARY KEY defined.
--
    #ENDIF
  #ELSE
--
-- WARNING: %File(%FilePrefix) has no KEYS defined.
--
  #ENDIF
#ENDFOR
--
-- EOF: %OutIndexName
--
  #CLOSE(%OutIndexName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLRelat)
  #SET(%OutRelatName,%SQLDirectory & %SQLRelatName)
  #CREATE(%OutRelatName)
  #MESSAGE('The MSSQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLRelatName,1)
--
-- BOF: %OutRelatName
--
-- SQL script creator for MSSQL database [RELATIONS]
-- Created: %TheDate - %TheTime
--

#!
  #SET(%RelCount, 0)
  #!---------------------------------------------------!
  #! This block added to show the tables in name order !
  #!---------------------------------------------------!
  #FOR(%File)                                                   #! RAS.2012.02.17
    #ADD(%FileSorted, %File)                                    #! RAS.2012.02.17
  #ENDFOR                                                       #! RAS.2012.02.17
  #FOR(%FileSorted)                                             #! RAS.2012.02.17
  #FIX(%File, %FileSorted)                                      #! RAS.2012.02.17
    #CASE(%FileDriver)
      #OF('ASCII')
    #OROF('BASIC')
    #OROF('DOS')
        #CYCLE
    #ENDCASE
#!
    #CASE(%FileType)
    #OF  ('VIEW')                                               #! RBE 20050127 - why is this check here?
#!------------------------------------------------------------!
#! RAS.2001.07.22 - Need to deal with aliases for the purpose !
#! of discovering additional key relationships that can not   !
#! be done with a single file from Clarion. In specific       !
#!                                                            !
#! Tables                                                     !
#!  Party_Table         Party_Relationship_Table              !
#!  Party_Id            Party_Id_From                         !
#!                      Party_Id_To                           !
#! Relations                                                  !
#!  Party_Id    <->>    Party_Id_From                         !
#!  Party_Id    <->>    Party_Id_To                           !
#!                                                            !
#! Clarion would need an alias of Party_Relationship_Table to !
#! deal with the second relationship. There would be no way   !
#! to use the second table again since Clarion restricts that !
#! specific operation.                                        !
#!                                                            !
#! SQL handles these type of relationships correctly and they !
#! need to be generated as an original file relationship.     !
#!------------------------------------------------------------!
    #OROF('ALIAS')                                              #!RBE 20050127 - this should never have been commented
      #CYCLE
    #ENDCASE
#!
    #SET(%NFileName,%File)
    #IF(%UsePrefix)
      #SET(%NFileName,%FilePrefix & '_' & %File)
    #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
    #IF(%IgnoredReserved = %FALSE)                                   #! RA.2012.02.27
      #SET(%SqlWordItem, UPPER(%NFileName))
      #FIND(%SqlWordList, %SqlWordItem)
      #IF(%SqlWordList)
        #ADD(%ReservedFieldList, %FoundFileName & %NFileName)
        #SET(%NFileName, %NFileName & '_')
      #ENDIF
    #ENDIF                                                      #! RA.2012.02.27
#!
    #SET(%TheCount, 0)
    #IF(ITEMS(%Relation))
      #FOR(%Relation)                                           #!RBE - user options?
        #SET(%nLoc1,INSTRING('RASQL(',%RelationUserOptions,1,1))
        #SET(%nLoc2,INSTRING(')',%RelationUserOptions,1,%nLoc1))
        #SET(%nRelationUser,UPPER(SUB(%RelationUserOptions,%nLoc1,%nLoc2)))
        #!SET(%NRelationUser,UPPER(EXTRACT(%RelationUserOptions,'RASQL')))
        #SET(%NRelationOpt1,EXTRACT(%NRelationUser,'RASQL',1))
        #SET(%NRelationOpt2,EXTRACT(%NRelationUser,'RASQL',2))
        #SET(%NRelationOpt3,EXTRACT(%NRelationUser,'RASQL',3))
        #SET(%NRelationOpt4,EXTRACT(%NRelationUser,'RASQL',4))
#!%NRelationUser, %NRelationOpt1, %NRelationOpt2, %NRelationOpt3, %NRelationOpt4
        #!-----------------------------------------------------------------!
        #! The first option RASQL(NO) controls if a relation is processed. !
        #!-----------------------------------------------------------------!
        #IF(SUB(%NRelationOpt1,1,1)='N')
          #CYCLE
        #ENDIF
#!
        #IF(%FileRelationType = 'MANY:1')
          #SET(%Relationfield1, '')
          #SET(%Relationfield2, '')
          #SET(%NKeyField, SUB(%FileKey, INSTRING(':',%FileKey,1,1) + 1 , 50))
          #IF(NOT %NKeyField)
            #SET(%NKeyField, SUB(%RelationKey, INSTRING(':',%RelationKey,1,1) + 1 , 50))
          #ENDIF
          #IF(%UsePrefixIndex)                                                          #!RBE 20050126
            #SET(%NKeyField, %FilePrefix & '_' & %NKeyField)
          #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
          #IF(%IgnoredReserved = %FALSE)                                                     #! RA.2012.02.27
            #SET(%SqlWordItem, UPPER(%NKeyField))
            #FIND(%SqlWordList, %SqlWordItem)
            #IF(%SqlWordList)
              #ADD(%ReservedFieldList, %FoundKeyField & %FilePrefix & ':' & %NKeyField)
              #SET(%NKeyField, %NKeyField & '_')
            #ENDIF
	  #ENDIF                                                                        #! RA.2012.02.27
          #!
          #SET(%NRelateFile, %Relation)
          #IF(%UsePrefix)
            #SET(%NRelateFile, %RelationPrefix & '_' & %Relation)
          #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
          #IF(%IgnoredReserved = %FALSE)                                                     #! RA.2012.02.27
            #SET(%SqlWordItem, UPPER(%NRelateFile))
            #FIND(%SqlWordList, %SqlWordItem)
            #IF(%SqlWordList)
              #ADD(%ReservedFieldList, %FoundRelatedName & %NRelateFile)
              #SET(%NRelateFile, %NRelateFile & '_')
            #ENDIF
          #ENDIF                                                                        #! RA.2012.02.07
          #!
          #SET(%RelationField1Count, ITEMS(%FileKeyField))
          #SET(%RelationField2Count, ITEMS(%RelationKeyField))
          #!
          #SET(%NRelateCount, %RelationField1Count)
          #SET(%NRelateComma, %NRelateCount - 1)
          #SET(%NRelateItem, 0)
          #FOR(%FileKeyField)
            #IF(%NRelateItem > %NRelateCount)
              #BREAK
            #ENDIF
            #SET(%NRelateItem, %NRelateItem + 1)
            #SET(%NRelateField1, SUB(%FileKeyField,INSTRING(':',%FileKeyField,1,1)+1,50))
            #SET(%NRelateField2, SUB(%FileKeyFieldLink,INSTRING(':',%FileKeyFieldLink,1,1)+1,50))
            #!
            #IF(%UsePrefix)
              #SET(%NRelateField1, %FilePrefix & '_' & %NRelateField1)
            #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
	    #IF(%IgnoredReserved = %FALSE)                                                   #! RA.2012.02.27
              #SET(%SqlWordItem, UPPER(%NRelateField1))
              #FIND(%SqlWordList, %SqlWordItem)
              #IF(%SqlWordList)
                #ADD(%ReservedFieldList, %FoundRelatedField & %FilePrefix & ':' & %NRelateField1)
                #SET(%NRelateField1, %NRelateField1 & '_')
              #ENDIF
            #ENDIF                                                                      #! RA.2012.02.07
            #!
            #IF(%UsePrefix)
              #SET(%NRelateField2, %RelationPrefix & '_' & %NRelateField2)
            #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
            #IF(%IgnoredReserved = %FALSE)                                                   #! RA.2012.02.27
              #SET(%SqlWordItem, UPPER(%NRelateField2))
              #FIND(%SqlWordList, %SqlWordItem)
              #IF(%SqlWordList)
                #ADD(%ReservedFieldList, %FoundRelatedField & %RelationPrefix & ':' & %NRelateField2)
                #SET(%NRelateField2, %NRelateField2 & '_')
              #ENDIF
            #ENDIF                                                                      #! RA.2012.02.27
            #!----------------------------------------------------!
	    #! RA.2012.02.27 - Added back. It is preferred.       !
	    #! RA.2012.02.22 - Remove '[' and ']' from generation !
	    #!----------------------------------------------------!
            #SET(%RelationField1, %RelationField1 & '[' & %NRelateField1 & ']')
            #SET(%RelationField2, %RelationField2 & '[' & %NRelateField2 & ']')
            #IF(%NRelateComma)
              #SET(%NRelateComma, %NRelateComma - 1)
              #SET(%RelationField1, %RelationField1 & ', ')
              #SET(%RelationField2, %RelationField2 & ', ')
            #ENDIF
          #ENDFOR
          #!------------------------------------------------!
          #! RAS.2001.01.20 - Added this feature.           !
          #! Has syntax errors, so commented out.           !
          #! RBE.2005.04.14 - Syntax errors fixed           !
          #!  It can generate corrrect syntax for dct       !
          #!  options Cascade(Server) and Restrict(Server)  !
          #!  But MSSQL has no provision for Clear(Server)  !
          #!  This requires a stored procedure/trigger.     !
          #!================================================!
          #! NOTE: This template expects the developer      !
          #! correctly used the (Server) options!           !
          #!================================================!
          #!-------------------------------------------!
          #! Added to the comments so programmer is at !
          #! aware that other relationships may exist. !
          #!-------------------------------------------!
          #SET(%RelationUpdate,SUB(%RelationConstraintUpdate,1,INSTRING('_',%RelationConstraintUpdate,1,1)-1))       #! ON UPDATE ? #RBE 2005.04.13
          #SET(%RelationDelete,SUB(%RelationConstraintDelete,1,INSTRING('_',%RelationConstraintDelete,1,1)-1))       #! ON DELETE ?
          #!-------------------------------------------!
          #IF(%Relationfield1)
            #MESSAGE('Relation of: ' & %File,2)
            #SET(%RelCount, %RelCount + 1)
            #!----------------------------------------------------------!
            #! RAS.2001.07.22 - The alias name gets changed to the real !
            #! name to generate the SQL statement for the relationship. !
            #!----------------------------------------------------------!
            #IF(%FileType = 'ALIAS')
              #IF(%NoAliasConstraints = 1)          #! RAB 20040121
                #CYCLE                              #! RAB 20040121
              #ENDIF                                #! RAB 20040121
              #SET(%NFileName,%AliasFile)
              #FIX(%File, %AliasFile)
              #IF(%UsePrefix)
                #SET(%NFileName,%FilePrefix & '_' & %File)
              #ENDIF
            #ENDIF
            #IF(%TheCount = 0)
------------------------------------------------------------
PRINT '[%SQLPrefix].[%NFileName]'
            #ENDIF
            #SET(%TheCount,%TheCount+1)
            #IF(%DelRelat)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('[%SQLPrefix].[Rel_%NKeyField]')
AND OBJECTPROPERTY(ID,'IsForeignKey') = 1)
    ALTER TABLE [%SQLPrefix].[%NFileName]
    DROP CONSTRAINT [Rel_%NKeyField]
GO

            #ENDIF
ALTER TABLE [%SQLPrefix].[%NFileName] ADD
   CONSTRAINT [Rel_%NKeyField]
   FOREIGN KEY (%Relationfield1)
   REFERENCES [%SQLPrefix].[%NRelateFile] (%Relationfield2)
            #IF(%RelationUpdate='CASCADE')                              #!RBE 2005.05.14 Added code to handle Cascades and Restricts
   ON UPDATE %RelationUpdate
            #ELSIF(%RelationUpdate='RESTRICT')
   ON UPDATE NO ACTION
            #ENDIF
            #IF(%RelationDelete='CASCADE')
   ON DELETE %RelationDelete
            #ELSIF(%RelationDelete='RESTRICT')
   ON DELETE NO ACTION
            #ENDIF
GO

            #IF(%RelationUpdate='CLEAR')                                #!RBE 2005.4.15 Added to address relation option of Clear(Server)
            #ELSIF(%RelationDelete='CLEAR')
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = '[TR_CLEAR_%NFileName]' AND type = 'TR')
  DROP TRIGGER [TR_CLEAR_%NFileName]
GO

CREATE TRIGGER [TR_CLEAR_%NFileName] ON [%SQLPrefix].[%NRelateFile]
FOR DELETE
AS
/* Test that child column is not null and set to NULL if it is not */
UPDATE [%NFileName]
              #IF(INSTRING(',',%Relationfield1,1,1))                    #!RBE 2005.4.18 There are multiple components to the relation, clear last one
                #SET(%LastComponent,SUB(%Relationfield1,INSTRING(',',%Relationfield1,1,1)+1))
                #SET(%FirstComponent,SUB(%Relationfield1,1,INSTRING(']',%Relationfield1,1,1)))
SET %LastComponent = NULL
WHERE %LastComponent = ANY(Select %LastComponent FROM DELETED) AND %FirstComponent = ANY(Select %FirstComponent FROM Deleted)
GO

              #ELSE
SET %Relationfield1 = NULL
WHERE %Relationfield1 = ANY(Select %Relationfield1 FROM DELETED)
GO

              #ENDIF
            #ENDIF
          #ENDIF
        #ENDIF
      #ENDFOR
    #ENDIF
  #ENDFOR
--
-- EOF: %OutRelatName
--
  #CLOSE(%OutRelatName)
#ENDIF
!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLCmnts)
  #SET(%OutCmntsName,%SQLDirectory & %SQLCmntsName)
  #CREATE(%OutCmntsName)
  #MESSAGE('The MSSQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLCmntsName,1)
--
-- BOF: %OutCmntsName
--
-- SQL script creator for MSSQL database [COMMENTS]
-- Created: %TheDate - %TheTime
--

#!---------------------------------------------------!
#! This block added to show the tables in name order !
#!---------------------------------------------------!
#FOR(%File)                                                     #! RAS.2012.02.17
  #ADD(%FileSorted, %File)                                      #! RAS.2012.02.17
#ENDFOR                                                         #! RAS.2012.02.17
#FOR(%FileSorted)                                               #! RAS.2012.02.17
#FIX(%File, %FileSorted)                                        #! RAS.2012.02.17
  #!----------------------------------------------------------------!
  #! RAS.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.   !
  #!----------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RAS.2001.12.01
    #CYCLE                                                      #! RAS.2001.12.01
  #ENDIF                                                        #! RAS.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '')                     #! RA.2012.02.22
#!
  #SET(%NFileName,%File)
  #IF(%UsePrefix)
    #SET(%NFileName,%FilePrefix & '_' & %File)
  #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
  #IF(%IgnoredReserved = %FALSE)                                #! RA.2012.02.27
    #SET(%SqlWordItem, UPPER(%NFileName))
    #FIND(%SqlWordList, %SqlWordItem)
    #IF(%SqlWordList)
      #ADD(%ReservedFieldList, %FoundFileName & %NFileName)
      #SET(%NFileName, %NFileName & '_')
    #ENDIF
  #ENDIF                                                        #! RA.2017.02.27
#!
------------------------------------------------------------
PRINT '[%SQLPrefix].[%NFileName]'
  #SET(%TheComment, %FileDescription)
  #IF(~%TheComment)
      #SET(%TheComment, 'DCT: NO FILE DESCRIPTION')
  #ENDIF
#!---- [%SQLPrefix].[%NFileName]   FILE COMMENT HERE   "%TheComment"
   #IF(%DelCmnts)

IF EXISTS (
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'%SQLPrefix', N'TABLE', N'%NFileName', NULL, NULL)
  )
  EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA', @level0name=N'%SQLPrefix', @level1type=N'TABLE', @level1name=N'%NFileName'
GO
   #ENDIF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'%TheComment', @level0type=N'SCHEMA', @level0name=N'%SQLPrefix', @level1type=N'TABLE', @level1name=N'%NFileName'
GO
  #SET(%InnerGroup, 0)
  #SET(%FieldComma, ITEMS(%Field) - 1)
  #!
  #FOR(%Field)
#! FieldComma='%FieldComma'
    #SET(%nLoc1,INSTRING('RASQL(',%FieldUserOptions,1,1))
    #SET(%nLoc2,INSTRING(')',%FieldUserOptions,1,%nLoc1))
    #SET(%nFieldUser,UPPER(SUB(%FieldUserOptions,%nLoc1,%nLoc2)))
    #!SET(%NFieldUser,UPPER(EXTRACT(%FieldUserOptions,'RASQL')))
    #SET(%NFieldOpt1,EXTRACT(%NFieldUser,'RASQL',1))
    #SET(%NFieldOpt2,EXTRACT(%NFieldUser,'RASQL',2))
    #SET(%NFieldOpt3,EXTRACT(%NFieldUser,'RASQL',3))
    #SET(%NFieldOpt4,EXTRACT(%NFieldUser,'RASQL',4))
#!%NFieldUser, %NFieldOpt1, %NFieldOpt2, %NFieldOpt3, %NFieldOpt4
    #!--------------------------------------------------------------!
    #! The first option RASQL(NO) controls if a field is processed. !
    #!--------------------------------------------------------------!
    #! WARNING: The last field can NOT be an ommited field.         !
    #!--------------------------------------------------------------!
    #! And if the last field happens to be and 'END', you will need !
    #! to add a RASQL(END) to the last field before the 'END'. This !
    #! will suppress the last comma. Reality sometimes happens.     !
    #!--------------------------------------------------------------!
    #IF(SUB(%NFieldOpt1,1,1)='N')
      #SET(%FieldComma, %FieldComma - 1)
      #CYCLE
    #ELSIF(SUB(%NFieldOpt1,1,1)='I')                              #!RBE - IDENTITY function
      #SET(%NFNameAuto,%FieldID)
    #ENDIF
    #!
    #SET(%NFieldType,    '')                                      #! Field type
    #SET(%NFieldName,    '')                                      #! Field name
    #SET(%NFieldTSql,    '')                                      #! Field SQL type
    #SET(%NFieldDef,     '')                                      #! Field default
    #SET(%NFieldAuto,    '')                                      #! Field AUTO_INCREMENT
    #SET(%NFieldCollate, '')                                      #! Field COLLATE sequence  #! RA.2012.03.02
    #SET(%NFieldNN,      '')                                      #! Field NOT NULL
    #SET(%NFieldSize,    0)                                       #! Field decimal size
    #SET(%NFieldDec,     0)                                       #! Field decimal places
    #!------------------------------------------------!
    #! Begin removal of types that are not processed. !
    #!------------------------------------------------!
    #IF(%FieldType='GROUP')
      #SET(%InnerGroup, %InnerGroup + 1)
      #IF(INSTRING('OVER(',%FieldStatement,1,1))
        #!SET(%FieldCount, %FieldCount + 1)                       #! RAS.2001.01.15
        #!SET(%FieldComma, %FieldComma - 1)                       #! RAS.2001.01.12
      #ENDIF
      #SET(%FieldComma, %FieldComma - 1)                          #! RAS.2001.01.12
      #CYCLE
    #ENDIF
  #!
    #IF(%FieldType='END')
      #SET(%InnerGroup, %InnerGroup - 1)
      #SET(%FieldComma, %FieldComma - 1)
      #CYCLE
    #ENDIF
    #SET(%FD1L,%FieldDimension1)                                  #! Name 1 made shorter
    #SET(%FD2L,%FieldDimension2)                                  #! Name 2 made shorter
    #SET(%FD3L,%FieldDimension3)                                  #! Name 3 made shorter
    #SET(%FD4L,%FieldDimension4)                                  #! Name 4 made shorter
    #IF(%FD1L>0)                                                  #! IF we got array field,
      #IF(NOT %NoArrayOver)                                       #! AND we got groups,
        #SET(%FieldComma, %FieldComma - 1)                        #! THEN ignore the array
        #CYCLE                                                    #! field since the fields
      #ENDIF                                                      #! in the group are going
    #ENDIF                                                        #! to define the array.
    #!------------------------------------------------!
    #! Ended removal of types that are not processed. !
    #!------------------------------------------------!
    #!
    #SET(%NFieldName, %FieldID)                                   #! Field Name
    #IF(%UsePrefix)                                               #! RAS.2001.12.01
      #SET(%NFieldName, %FilePrefix & '_' & %FieldID)             #! RAS.2001.10.24
    #ENDIF                                                        #! RAS.2001.12.01
#!---- IgnoredReserved = %IgnoredReserved
    #IF(%IgnoredReserved = %FALSE)                                     #! RA.2012.02.27
      #SET(%SqlWordItem, UPPER(%NFieldName))                      #! Set up search of
      #FIND(%SqlWordList, %SqlWordItem)                           #! SQL reserved words
      #IF(%SqlWordList)                                           #! Found? Yes! We add
        #ADD(%ReservedFieldList, %FoundFileField & %FilePrefix & ':' & %NFieldName)
        #SET(%NFieldName, %NFieldName & '_')                      #! trailing underscore
      #ENDIF                                                      #! to get it through
    #ENDIF                                                        #! RA.2012.02.27
    #!
    #SET(%FieldCount, %FieldCount + 1)                            #! Add to field count
    #SET(%TheComment, %FieldDescription)
    #IF(~%TheComment)
      #SET(%TheComment, 'DCT: NO FIELD DESCRIPTION')
    #ENDIF
#!---- [%SQLPrefix].[%NFileName].[%NFieldName]   FIELD COMMENT HERE    "%TheComment"
   #IF(%DelCmnts)

IF EXISTS (
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'%SQLPrefix', N'TABLE', N'%NFileName', N'COLUMN', N'%NFieldName')
  )
  EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA', @level0name=N'%SQLPrefix', @level1type=N'TABLE', @level1name=N'%NFileName', @level2type=N'COLUMN', @level2name=N'%NFieldName'
GO
   #ENDIF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'%TheComment', @level0type=N'SCHEMA', @level0name=N'%SQLPrefix', @level1type=N'TABLE', @level1name=N'%NFileName', @level2type=N'COLUMN', @level2name=N'%NFieldName'
GO
  #ENDFOR
  #!
  #IF(ITEMS(%Key))
    #FOR(%Key)
      #IF(NOT ITEMS(%KeyField))                                 #! Key has fields? No!
        #CYCLE                                                  #! Then go to the next
      #ENDIF                                                    #! key to be processed
      #!
      #SET(%nLoc1,INSTRING('RASQL(',%KeyUserOptions,1,1))
      #SET(%nLoc2,INSTRING(')',%KeyUserOptions,1,%nLoc1))
      #SET(%nKeyUser,UPPER(SUB(%KeyUserOptions,%nLoc1,%nLoc2)))
      #!SET(%NKeyUser,UPPER(EXTRACT(%KeyUserOptions,'RASQL')))
      #SET(%NKeyOpt1,EXTRACT(%NKeyUser,'RASQL',1))
      #SET(%NKeyOpt2,EXTRACT(%NKeyUser,'RASQL',2))
      #SET(%NKeyOpt3,EXTRACT(%NKeyUser,'RASQL',3))
      #SET(%NKeyOpt4,EXTRACT(%NKeyUser,'RASQL',4))
#!%NKeyUser, %NKeyOpt1, %NKeyOpt2, %NKeyOpt3, %NKeyOpt4
      #!------------------------------------------------------------!
      #! The first option RASQL(NO) controls if a key is processed. !
      #!------------------------------------------------------------!
      #IF(SUB(%NKeyOpt1,1,1)='N')
        #CYCLE
      #ENDIF
      #!
      #SET(%NKeyName,%KeyID)
      #IF(%UsePrefixIndex)                                                           #!RBE 20050126
        #SET(%NKeyName,%FilePrefix & '_' & %KeyID)
      #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
      #IF(%IgnoredReserved = %FALSE)                                                      #! RA.2012.02.27
        #SET(%SqlWordItem, UPPER(%NKeyName))
        #FIND(%SqlWordList, %SqlWordItem)
        #IF(%SqlWordList)
          #ADD(%ReservedFieldList, %FoundKeyName & %FilePrefix & ':' & %NKeyName)
          #SET(%NKeyName, %NKeyName & '_')
        #ENDIF
      #ENDIF                                                                         #! RA.2012.02.27
    #!
    #SET(%TheComment, %KeyDescription)
    #IF(~%TheComment)
      #SET(%TheComment, 'DCT: NO KEY DESCRIPTION')
    #ENDIF
#!
#!---- [%SQLPrefix].[%NFileName].[%NKeyName]   KEY COMMENT HERE    "%TheComment"
    #ENDFOR
  #ENDIF
  #!
  #SET(%TheCount, 0)
  #IF(ITEMS(%Relation))
    #SET(%NRelateCount, ITEMS(%Relation))
#!---- RELATIONSHIPS: %NRelateCount
    #FOR(%Relation)                                           #!RBE - user options?
#!---- RELATIONSHIP TYPE: %FileRelationType
      #SET(%nLoc1,INSTRING('RASQL(',%RelationUserOptions,1,1))
      #SET(%nLoc2,INSTRING(')',%RelationUserOptions,1,%nLoc1))
      #SET(%nRelationUser,UPPER(SUB(%RelationUserOptions,%nLoc1,%nLoc2)))
      #!SET(%NRelationUser,UPPER(EXTRACT(%RelationUserOptions,'RASQL')))
      #SET(%NRelationOpt1,EXTRACT(%NRelationUser,'RASQL',1))
      #SET(%NRelationOpt2,EXTRACT(%NRelationUser,'RASQL',2))
      #SET(%NRelationOpt3,EXTRACT(%NRelationUser,'RASQL',3))
      #SET(%NRelationOpt4,EXTRACT(%NRelationUser,'RASQL',4))
#!%NRelationUser, %NRelationOpt1, %NRelationOpt2, %NRelationOpt3, %NRelationOpt4
      #!-----------------------------------------------------------------!
      #! The first option RASQL(NO) controls if a relation is processed. !
      #!-----------------------------------------------------------------!
      #IF(SUB(%NRelationOpt1,1,1)='N')
        #CYCLE
      #ENDIF
      #!
      #IF(%FileRelationType = 'MANY:1')
        #SET(%Relationfield1, '')
        #SET(%Relationfield2, '')
        #SET(%NKeyField, SUB(%FileKey, INSTRING(':',%FileKey,1,1) + 1 , 50))
        #IF(NOT %NKeyField)
          #SET(%NKeyField, SUB(%RelationKey, INSTRING(':',%RelationKey,1,1) + 1 , 50))
        #ENDIF
        #IF(%UsePrefixIndex)                                                          #!RBE 20050126
          #SET(%NKeyField, %FilePrefix & '_' & %NKeyField)
        #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
        #IF(%IgnoredReserved = %FALSE)                                                     #! RA.2012.02.27
          #SET(%SqlWordItem, UPPER(%NKeyField))
          #FIND(%SqlWordList, %SqlWordItem)
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundKeyField & %FilePrefix & ':' & %NKeyField)
            #SET(%NKeyField, %NKeyField & '_')
          #ENDIF
	#ENDIF                                                                        #! RA.2012.02.27
          #!
        #SET(%NRelateFile, %Relation)
        #IF(%UsePrefix)
          #SET(%NRelateFile, %RelationPrefix & '_' & %Relation)
        #ENDIF
#!---- IgnoredReserved = %IgnoredReserved
        #IF(%IgnoredReserved = %FALSE)                                                     #! RA.2012.02.27
          #SET(%SqlWordItem, UPPER(%NRelateFile))
          #FIND(%SqlWordList, %SqlWordItem)
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundRelatedName & %NRelateFile)
            #SET(%NRelateFile, %NRelateFile & '_')
          #ENDIF
        #ENDIF                                                                        #! RA.2012.02.07
        #!
#!---- NRelateFile = "%NRelateFile", NFileName = "%NFileName".
        #MESSAGE('Relation of: ' & %File,2)
        #SET(%RelCount, %RelCount + 1)
        #!----------------------------------------------------------!
        #! RAS.2001.07.22 - The alias name gets changed to the real !
        #! name to generate the SQL statement for the relationship. !
        #!----------------------------------------------------------!
        #IF(%FileType = 'ALIAS')
          #IF(%NoAliasConstraints = 1)          #! RAB 20040121
            #CYCLE                              #! RAB 20040121
          #ENDIF                                #! RAB 20040121
          #SET(%NFileName,%AliasFile)
          #FIX(%File, %AliasFile)
          #IF(%UsePrefix)
            #SET(%NFileName,%FilePrefix & '_' & %File)
          #ENDIF
        #ENDIF
        #IF(%TheCount = 0)
#!------------------------------------------------------------
#!PRINT '[%SQLPrefix].[%NFileName]'
        #ENDIF
        #SET(%TheCount,%TheCount+1)
#!ALTER TABLE [%SQLPrefix].[%NFileName] ADD
#!   CONSTRAINT [Rel_%NKeyField]
#!   FOREIGN KEY (%Relationfield1)
#!   REFERENCES [%SQLPrefix].[%NRelateFile] (%Relationfield2)
#!GO
        #SET(%TheComment, 'Relationship from ' & %NRelateFile & ' to ' & %NFileName & ' (1:MANY).')
        #IF(~%TheComment)
          #SET(%TheComment, 'DCT: NO RELATION DESCRIPTION')
        #ENDIF
#!
#!---- [%SQLPrefix].[%NFileName].[Rel_%NKeyField]   RELATION COMMENT HERE   "%TheComment"
      #ENDIF
    #ENDFOR
  #ENDIF

#ENDFOR
--
-- EOF: %OutCmntsName
--
  #CLOSE(%OutCmntsName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! These should be changed to run the associated program.                       #!RBE 20050127
#IF(%ShowScripts=%True)                                                         #!Option to display
  #IF(%SQLTable)
    #RUN('NOTEPAD.EXE "' & %OutTableName & '"')
  #ENDIF
  #IF(%SQLGrant)
    #RUN('NOTEPAD.EXE "' & %OutGrantName & '"')
  #ENDIF
  #IF(%SQLIndex)
    #RUN('NOTEPAD.EXE "' & %OutIndexName & '"')
  #ENDIF
  #IF(%SQLRelat)
    #RUN('NOTEPAD.EXE "' & %OutRelatName & '"')
  #ENDIF
  #IF(%SQLCmnts)                                                                #! RA.2012.03.07
    #RUN('NOTEPAD.EXE "' & %OutCmntsName & '"')
  #ENDIF
#ENDIF
#!---------------------------------------------------------------------
#!--FOR CREATE TABLE---------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#GROUP(%MSSQLField3)
#!--------------------------------------------------------------------!
#!--------------------------------------------------------------------!
#!--------------------------------------------------------------------!
#! Dump the fields here for a MSSQL database back end.                !
#!--------------------------------------------------------------------!
#!--------------------------------------------------------------------!
#!--------------------------------------------------------------------!
#SET(%InnerGroup, 0)
#SET(%FieldComma, ITEMS(%Field) - 1)
#!
#FOR(%Field)
#! FieldComma='%FieldComma'
  #SET(%nLoc1,INSTRING('RASQL(',%FieldUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FieldUserOptions,1,%nLoc1))
  #SET(%nFieldUser,UPPER(SUB(%FieldUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFieldUser,UPPER(EXTRACT(%FieldUserOptions,'RASQL')))
  #SET(%NFieldOpt1,EXTRACT(%NFieldUser,'RASQL',1))
  #SET(%NFieldOpt2,EXTRACT(%NFieldUser,'RASQL',2))
  #SET(%NFieldOpt3,EXTRACT(%NFieldUser,'RASQL',3))
  #SET(%NFieldOpt4,EXTRACT(%NFieldUser,'RASQL',4))
#!%NFieldUser, %NFieldOpt1, %NFieldOpt2, %NFieldOpt3, %NFieldOpt4
  #!--------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a field is processed. !
  #!--------------------------------------------------------------!
  #! WARNING: The last field can NOT be an ommited field.         !
  #!--------------------------------------------------------------!
  #! And if the last field happens to be and 'END', you will need !
  #! to add a RASQL(END) to the last field before the 'END'. This !
  #! will suppress the last comma. Reality sometimes happens.     !
  #!--------------------------------------------------------------!
  #IF(SUB(%NFieldOpt1,1,1)='N')
    #SET(%FieldComma, %FieldComma - 1)
    #CYCLE
  #ELSIF(SUB(%NFieldOpt1,1,1)='I')                              #!RBE - IDENTITY function
    #SET(%NFNameAuto,%FieldID)
  #ENDIF
#!
  #SET(%NFieldType,    '')                                      #! Field type
  #SET(%NFieldName,    '')                                      #! Field name
  #SET(%NFieldTSql,    '')                                      #! Field SQL type
  #SET(%NFieldDef,     '')                                      #! Field default
  #SET(%NFieldAuto,    '')                                      #! Field AUTO_INCREMENT
  #SET(%NFieldCollate, '')                                      #! Field COLLATE sequence  #! RA.2012.03.02
  #SET(%NFieldNN,      '')                                      #! Field NOT NULL
  #SET(%NFieldSize,    0)                                       #! Field decimal size
  #SET(%NFieldDec,     0)                                       #! Field decimal places
  #!------------------------------------------------!
  #! Begin removal of types that are not processed. !
  #!------------------------------------------------!
  #IF(%FieldType='GROUP')
    #SET(%InnerGroup, %InnerGroup + 1)
    #IF(INSTRING('OVER(',%FieldStatement,1,1))
      #!SET(%FieldCount, %FieldCount + 1)                       #! RAS.2001.01.15
      #!SET(%FieldComma, %FieldComma - 1)                       #! RAS.2001.01.12
    #ENDIF
    #SET(%FieldComma, %FieldComma - 1)                          #! RAS.2001.01.12
    #CYCLE
  #ENDIF
  #!
  #IF(%FieldType='END')
    #SET(%InnerGroup, %InnerGroup - 1)
    #SET(%FieldComma, %FieldComma - 1)
    #CYCLE
  #ENDIF
  #SET(%FD1L,%FieldDimension1)                                  #! Name 1 made shorter
  #SET(%FD2L,%FieldDimension2)                                  #! Name 2 made shorter
  #SET(%FD3L,%FieldDimension3)                                  #! Name 3 made shorter
  #SET(%FD4L,%FieldDimension4)                                  #! Name 4 made shorter
  #IF(%FD1L>0)                                                  #! IF we got array field,
    #IF(NOT %NoArrayOver)                                       #! AND we got groups,
      #SET(%FieldComma, %FieldComma - 1)                        #! THEN ignore the array
      #CYCLE                                                    #! field since the fields
    #ENDIF                                                      #! in the group are going
  #ENDIF                                                        #! to define the array.
  #!------------------------------------------------!
  #! Ended removal of types that are not processed. !
  #!------------------------------------------------!
#!
  #SET(%NFieldName, %FieldID)                                   #! Field Name
  #IF(%UsePrefix)                                               #! RAS.2001.12.01
    #SET(%NFieldName, %FilePrefix & '_' & %FieldID)             #! RAS.2001.10.24
  #ENDIF                                                        #! RAS.2001.12.01
#!---- IgnoredReserved = %IgnoredReserved
  #IF(%IgnoredReserved = %FALSE)                                     #! RA.2012.02.27
    #SET(%SqlWordItem, UPPER(%NFieldName))                      #! Set up search of
    #FIND(%SqlWordList, %SqlWordItem)                           #! SQL reserved words
    #IF(%SqlWordList)                                           #! Found? Yes! We add
      #ADD(%ReservedFieldList, %FoundFileField & %FilePrefix & ':' & %NFieldName)
      #SET(%NFieldName, %NFieldName & '_')                      #! trailing underscore
    #ENDIF                                                      #! to get it through
  #ENDIF                                                        #! RA.2012.02.27
#!
  #SET(%FieldCount, %FieldCount + 1)                            #! Add to field count
  #IF(%NFieldName=%NFieldPKey)                                  #! File Primary Key?
    #IF(%NFieldOpt3)                                            #! Do I have options?
      #IF(INSTRING('PKEYA',%NFieldOpt3,1,1))                    #! PKEYA already set?
      #ELSE                                                     #! No. So add it on.
        #SET(%NFieldOpt3,%NFieldOpt3 & '+PKEYA')                #! Just append option
      #ENDIF                                                    #! at the end of all.
    #ELSE                                                       #! No options found.
      #SET(%NFieldOpt3,'PKEYA')                                 #! Set primary key on
    #ENDIF                                                      #! as option and let
  #ENDIF                                                        #! it be handled
#!
  #!----------------------------------------------------------------!
  #! The second option RASQL(,<FieldType>) overrides the field type !
  #! in the dictionary. This allows conversion of data types that   !
  #! have no existance in the dictionary but exist in the SQL.      !
  #!----------------------------------------------------------------!
  #SET(%NFieldType, %FieldType)
  #IF(%NFieldOpt2)
    #SET(%NFieldType, %NFieldOpt2)
  #ENDIF
#!
  #!----------------------------------------------------!
  #! Calculate decimal size and places for some fields. !
  #! Only DECIMAL fields have characters and decimals.  !
  #! Everything else that needs it, is figured out from !
  #! the display mask used.                             !
  #!----------------------------------------------------!
  #! RAS.2001.01.12 - Added additional MySQL types.     !
  #!----------------------------------------------------!
  #SET(%NLeadZero, 0)                                               #! NO leading zero
  #SET(%NFieldSize, %FieldMemoSize)                                 #! Field decimal size
  #SET(%NFieldDec, %FieldPlaces)                                    #! Field decimal places
  #SET(%NFieldTDef, '')                                             #! RA.2012.02.22
  #CASE(%NFieldType)
  #OF  ('DEC')                                                      #! NFieldOpt2: MySQL
  #OROF('NUMERIC')                                                  #! NFieldOpt2: MySQL
  #OROF('SREAL')
  #OROF('BFLOAT4')
  #OROF('FLOAT')                                                    #! NFieldOpt2: MySQL
  #OROF('FLOAT4')                                                   #! NFieldOpt2: MySQL
  #OROF('REAL')
  #OROF('BFLOAT8')
  #OROF('DOUBLE')                                                   #! NFieldOpt2: MySQL
  #OROF('FLOAT8')                                                   #! NFieldOpt2: MySQL
    #!---- FieldMemoSize=%FieldMemoSize - FieldPlaces=%FieldPlaces
    #!---------------------------------------------------------------------!
    #! RA.2012.05.03 - We parse the display picture since the parameters   !
    #! shown above are totally inaccurate as what the actual data type is. !
    #!---------------------------------------------------------------------!
    #SET(%NFieldTDef, 'NUMBER')                                     #! RA.2012.02.22
    #SET(%nLoc1,INSTRING('-',%FieldDisplayPicture,1,1))
    #IF(NOT %nLoc1)                                                 #! RA.2012.05.03
      #SET(%nLoc1,INSTRING('$',UPPER(%FieldDisplayPicture),1,1))    #! RA.2012.05.03
    #ENDIF                                                          #! RA.2012.05.03
   #IF(NOT %nLoc1)
      #SET(%nLoc1,INSTRING('N',UPPER(%FieldDisplayPicture),1,1))
    #ENDIF
    #SET(%nLoc2,INSTRING('.',%FieldDisplayPicture,1,1))
    #SET(%NFieldSize,SUB(%FieldDisplayPicture,(%nLoc1+1),(%nLoc2-1)))
    #SET(%NFieldDec,SUB(%FieldDisplayPicture,(%nLoc2+1),LEN(%FieldDisplayPicture)))
  #!--------------------------------------------------------!
  #! RAS.2001.10.23 - Parse sizes for non-decimal numerics. !
  #! And set the leading zero flag if you find the need.    !
  #!--------------------------------------------------------!
  #OF  ('LONG')
  #OROF('ULONG')
  #OROF('SHORT')
  #OROF('USHORT')
  #OROF('BYTE')
    #SET(%NFieldTDef, 'NUMBER')                                 #! RA.2012.02.22
    #SET(%nLoc1,INSTRING('-',%FieldDisplayPicture,1,1))
    #IF(NOT %nLoc1)
      #SET(%nLoc1,INSTRING('_',UPPER(%FieldDisplayPicture),1,1))
    #ENDIF
    #IF(NOT %nLoc1)
      #SET(%nLoc1,INSTRING('N',UPPER(%FieldDisplayPicture),1,1))
    #ENDIF
    #SET(%nLoc2,(LEN(%FieldDisplayPicture) - %nLoc1 + 1))
    #SET(%NFieldSize,SUB(%FieldDisplayPicture,(%nLoc1+1),(%nLoc2-1)))
    #SET(%NLeadZero,INSTRING('N0',UPPER(%FieldDisplayPicture),1,1))
  #ENDCASE
#!-- "%FieldDisplayPicture", %nLoc1, %nLoc2, (%NFieldSize, %NFieldDec), %NLeadZero
  #!-------------------------------------------------!
  #! Deal with the initial values in the dictionary. !
  #!-------------------------------------------------!
  #SET(%NFieldDef, '')
  #!------------------------------------------------------!
  #! Deal with the dictionary field type conversion here. !
  #!------------------------------------------------------!
  #CASE(%NFieldType)
  #OF  ('BINARY')                                               #! RA.2012.03.12
    #IF(%FieldMemoSize < 1)                                     #! RA.2012.03.12
      #SET(%CStringLen, 1)                                      #! RA.2012.03.12
    #ELSE                                                       #! RA.2012.03.12
      #SET(%CStringLen, %FieldMemoSize)                         #! RA.2012.03.12
    #ENDIF                                                      #! RA.2012.03.12
    #SET(%NFieldTDef, %NFieldType)                              #! RA.2012.03.12
    #SET(%NFieldTSql, 'BINARY(' & %CStringLen & ')' )           #! RA.2012.03.12
  #OF  ('BLOB')
  #OROF('MEMO')                                                 #! RA.2012.03.02
    #!---------------------------------------------------------------------------------------------------!
    #! MEMO - Having no length of any kind because it is text. Sample bellow.                            !
    #!---------------------------------------------------------------------------------------------------!
    #! <Field Guid="{5935489f-a0a0-4c67-b705-94208f756c4c}" Ident="4114" Name="FormPage" DataType="MEMO" !
    #!   ScreenPicture="@s20" ScreenPrompt="Form Page:" ReportHeading="Form Page" WindowControl="        !
    #!   TEXT,USE(USPSForm:FormPage)" ReportControl=" TEXT,USE(USPSForm:FormPage)">                      !
    #!   <Audit CreateUser="Administrator" CreateDate=" 2 SEP 2004" CreateTime=" 4:44:13PM"              !
    #!   CreateVersionNumber="1" ModifiedUser="Administrator" ModifiedVersionNumber="1"/>                !
    #!   <Validity Check="NOCHECKS"/>                                                                    !
    #! </Field>                                                                                          !
    #!---------------------------------------------------------------------------------------------------!
    #IF(%FieldMemoSize < 1)                                     #! RA.2012.03.02
      #SET(%CStringLen, 1)                                      #! RA.2012.03.02
    #ELSE                                                       #! RA.2012.03.02
      #SET(%CStringLen, %FieldMemoSize)                         #! RA.2012.03.02
    #ENDIF                                                      #! RA.2012.03.02
#!---- %NFieldType (%FieldMemoImage) ---- NFieldSize=%NFieldSize, FieldMemoSize=%FieldMemoSize, FieldFormatWidth=%FieldFormatWith, CStringLength=%CStringLength
    #SET(%NFieldTDef, %NFieldType)                              #! RA.2012.03.02
    #IF(%FieldMemoImage=0)                                      #!RBE 20050131 - If not a binary field
      #IF(%CStringLen < 256)
        #SET(%NFieldTSql, 'CHAR(' & %CStringLen & ')' )         #! RA.2012.03.02
        #IF(%DoVarcharConvert)                                  #! RA.2012.05.02
          #SET(%CStringLen, %CStringLen - 0)                    #! RA.2012.05.02
          #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )    #! RA.2012.05.02
        #ENDIF                                                  #! RA.2012.05.02
      #ELSE
        #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )      #! RA.2012.03.02
      #ENDIF
      #SET(%NFieldTDef, 'STRING')                               #! RA.2012.03.02
    #ELSE                                                       #!RBE 20050131
      #SET(%NFieldTSql,'IMAGE')                                 #!RBE 20050131 - default to max size
    #ENDIF                                                      #!RBE 20050131  2,147,483,647
  #OF  ('STRING')
    #SET(%NFieldTDef, 'STRING')                                 #! RA.2012.02.22
    #IF(%FieldMemoSize < 256)
      #SET(%NFieldTSql, 'CHAR(' & %FieldMemoSize & ')' )
      #IF(%DoVarcharConvert)                                    #! RA.2012.05.02
        #SET(%CStringLen, %FieldMemoSize - 0)                   #! RA.2012.05.02
        #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )      #! RA.2012.05.02
      #ENDIF                                                    #! RA.2012.05.02
    #ELSE
      #SET(%NFieldTSql, 'VARCHAR(' & %FieldMemoSize & ')' )
    #ENDIF
  #OF  ('CSTRING')
  #OROF('PSTRING')
    #SET(%NFieldTDef, 'STRING')                                 #! RA.2012.02.22
    #SET(%CStringLen, %FieldMemoSize - 1)
    #IF(%CStringLen < 256)
      #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )        #! RBE 20050331 - variable string types should NEVER be set to fixed length!
    #ELSIF(%CStringLen > 7999)                                  #! RAB 20040121
      #SET(%NFieldTSql, 'TEXT' )                                #! RAB 20040121
    #ELSE
      #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )
    #ENDIF
  #OF  ('LONG')
  #OROF('INT')
  #OROF('INT4')
  #OROF('INTEGER')
    #SET(%NFieldTDef, 'NUMBER')                                 #! RA.2012.02.22
    #SET(%NFieldTSql, 'INT')
    #SET(%NFieldDef,  '')
    #SET(%NFieldNN,   '')
    #!----------------------------------------------------------------------!
    #! RAS.2002.05.31 - Now extra checking for date and time is an option.  !
    #! This allows individuals to set date or time to INT on the backend.   !
    #!----------------------------------------------------------------------!
    #IF(%ExtraDTTM)                                             #! RAS.2002.05.31
      #IF(%TimeStampOption)                                     #! RBE may be irrelevant - stub code for now.
      #ENDIF
      #IF(INSTRING('@D',UPPER(%FieldRecordPicture),1,1))
        #SET(%NFieldTSql, 'DATETIME')                           #! RAS.2002.05.30
        #SET(%NFieldNN,   '')
      #ENDIF
      #IF(INSTRING('@T',UPPER(%FieldRecordPicture),1,1))
        #SET(%NFieldTSql, 'DATETIME')                           #! RAS.2002.05.30
        #SET(%NFieldNN,   '')
      #ENDIF
    #ENDIF                                                      #! RAS.2002.05.31
  #OF  ('SHORT')
  #OROF('SMALLINT')
  #OROF('INT2')
      #SET(%NFieldTDef, 'NUMBER')                               #! RA.2012.02.22
      #SET(%NFieldTSql, 'INT')                                  #! RBE changed to use int data types
      #SET(%NFieldDef,  '')
      #SET(%NFieldNN,   '')
  #OF  ('BYTE')
  #OROF('UTINYINT')
  #OROF('UINT1')
      #SET(%NFieldTDef, 'NUMBER')                               #! RA.2012.02.22
      #SET(%NFieldTSql, 'INT')                                  #! RBE changed to use int data types
      #SET(%NFieldDef,  '')
      #SET(%NFieldNN,   '')
  #OF  ('ULONG')
  #OROF('UINT')
  #OROF('UINT4')
      #SET(%NFieldTDef, 'NUMBER')                               #! RA.2012.02.22
      #SET(%NFieldTSql, 'INT')
      #SET(%NFieldDef,  '')
      #SET(%NFieldNN,   '')
  #OF  ('USHORT')
  #OROF('USMALLINT')
  #OROF('UINT2')
      #SET(%NFieldTDef, 'NUMBER')                               #! RA.2012.02.22
      #SET(%NFieldTSql, 'INT')                                  #! RBE changed to use int data types
      #SET(%NFieldDef,  '')
      #SET(%NFieldNN,   '')
  #OF  ('INT64')
  #OROF('UINT64')
      #SET(%NFieldTDef, 'NUMBER')                               #! RA.2022.05.10: Clarion NEW types
      #SET(%NFieldTSql, 'BIGINT')                               #! RBE changed to use int data types
      #SET(%NFieldDef,  '')
      #SET(%NFieldNN,   '')
  #OF  ('DECIMAL')
  #OROF('PDECIMAL')
  #OROF('DEC')
  #OROF('NUMERIC')
  #!---------------------------------------------------------------------!
  #! RAS.2001.01.09 - IMPORTANT - READ THIS NOW.                         !
  #!---------------------------------------------------------------------!
  #! There is a very big assumption here that your doing a business type !
  #! application so you want to convert binary inaccurate formats to the !
  #! totally binary (financial) accurate format of DECIMAL.              !
  #!---------------------------------------------------------------------!
  #! I get tired of those hours of time spent on penny off errors on     !
  #! financial screens and reports with a calculator to finally come up  !
  #! with the solution of "It is a rounding error!" due to the binary    !
  #! inaccurate format representation of "floats" and "doubles" that     !
  #! are often used to represent money amounts with an actual fractional !
  #! point part for the cents. This happens often in older applications  !
  #! written in languages that did not have a decimal type and with      !
  #! people that did not have experience or knowledge enough to save     !
  #! those financial amounts as whole pennies and adjust their formulas. !
  #!---------------------------------------------------------------------!
  #! If you need the SREAL (4 bytes) and REAL (8 bytes) left alone then  !
  #! move them somewhere else.                                           !
  #!---------------------------------------------------------------------!
  #OROF('SREAL')
  #OROF('BFLOAT4')
  #OROF('FLOAT')
  #OROF('FLOAT4')
  #OROF('REAL')
  #OROF('BFLOAT8')
  #OROF('DOUBLE')
  #OROF('FLOAT8')
    #SET(%NFieldTDef, 'NUMBER')                                  #! RA.2012.02.22
    #SET(%NFieldTSql, 'DECIMAL(' & %NFieldSize & ',' & %NFieldDec & ')')
    #SET(%NFieldDef,  '')
    #SET(%NFieldNN,   '')
    #!------------------------------------------------------!
    #! RA.2012.02.20 - Checks for key fields DECIMAL(19,0), !
    #! they are BIGINT on the MSSQL backend.                !
    #!------------------------------------------------------!
    #IF(%NFieldSize = 19 AND %NFieldDec = 0)                    #! RAS.2012.02.20
      #SET(%NFieldTSql, 'BIGINT')                               #! RAS.2012.02.20
    #ENDIF                                                      #! RAS.2012.02.20
  #!--------------------------------------------------------------------!
  #! RAS.2012.02.17 - MSSQL 2008 now has DATE and TIME.                 !
  #! RAS.2004.05.30 - IMPORTANT - READ THIS NOW.                        !
  #! RBE 2005.02.04 - Amendments and new feature comments               !
  #!--------------------------------------------------------------------!
  #! MSSQL does not support individual DATE and TIME, so might need to  !
  #! think about your requirements and usage.                           !
  #!--------------------------------------------------------------------!
  #! Here are some examples of date/time treatment:                     !
  #! 1) If you DATE and TIME are in the dictionary back to back and are !
  #!    used as a unit, then consider just doing an option on the TIME  !
  #!    of RASQL(NO). This will generate the date but not the time.     !
  #! 2) Separate your date and time but store them as sortable strings. !
  #!    Something like CHAR(10) [CCYY/MM/DD] for date, and CHAR(8)      !
  #!    [HH:MM:SS] or CHAR(5) [HH:MM] for time. You have them in sort   !
  #!    order, and they can transformed rather easily for calculations. !
  #! 3) Do you combined datetime as a GROUP in the dictionary and have  !
  #!    the DATE and TIME long with the correct pictures as fields      !
  #!    inside the group.                                               !
  #! 4) The CSTRING(9) datetime dictionary definition is the variable   !
  #!    used in the database.  The GROUP and DATE and TIME variables in !
  #!    are NOT.  You MUST add RASQL(YES,DATETIME) or RASQL(YES,TIMESTAMP) !
  #!    in the CSTRING(9) field and RASQL(NO) in the GROUP and variables!
  #!    inside the GROUP. This is for the case where you want to take   !
  #!    advantage of the timestamp function on the database for some    !
  #!    date fields and datestamp for others. (disabled - see below)    !
  #!--------------------------------------------------------------------!
  #OF  ('DATE')
    #SET(%NFieldTDef, 'DATE')                                   #! RA.2012.02.22
    #SET(%NFieldTSql, 'DATE')                                   #! RA.2012.02.07
    #SET(%NFieldDef,  '')
    #SET(%NFieldNN,   '')
  #OF  ('TIME')
    #SET(%NFieldTDef, 'TIME')                                   #! RA.2012.02.22
    #SET(%NFieldTSql, 'TIME')                                   #! RA.2012.02.07
    #SET(%NFieldDef,  '')
    #SET(%NFieldNN,   '')
  #OF  ('DATETIME')                                             #! RBE 2005.02.08
    #SET(%NFieldTDef, 'DATETIME')                               #! RA.2012.02.22
    #SET(%NFieldTSql, 'DATETIME')                               #! RA.2002.05.30
    #SET(%NFieldDef,  '')
    #SET(%NFieldNN,   '')
#!  #OF('TIMESTAMP')                                            #! RBE 2005.04.07 - Clarion does not know about this data type
#!    #SET(%NFieldTSql, 'TIMESTAMP')                            #! RBE.2005.02.04
#!    #SET(%NFieldDef,  '')
#!    #SET(%NFieldNN,   '')
#!
  #OF  ('PICTURE')                                              #! RA.2012.05.02
    #SET(%NFieldTDef, 'STRING')                                 #! RA.2012.05.02
    #SET(%CStringLen, %FieldMemoSize)                           #! RA.2012.05.02
    #SET(%NFieldTSql, 'VARCHAR(' & %CStringLen & ')' )          #! RA.2012.05.02
  #ELSE
    #!---------------------------------------------!
    #! This is a field type not currently handled. !
    #! Time to do some more template programming.  !
    #!---------------------------------------------!
    #SET(%NFieldTSql,'***' & %NFieldType & '***')
  #ENDCASE
  #!----------------------------------------------------------!
  #! Deal with field validation parameters in the dictionary. !
  #!----------------------------------------------------------!
#!
#!    #FOR(%FieldScreenControl)
#!-- %FieldID: FSC(%FieldValidation): %FieldScreenControl
#!    #ENDFOR
#!
  #!----------------------------------------------------------!
  #! If you are validating something, that means it's going   !
  #! to have some value. Mark it as NOT NULL.                 !
  #!----------------------------------------------------------!
  #SET(%NFieldCheck,    '')                                     #! Field CHECK(...) constraint
  #SET(%NFieldCheckOver,'')                                     #! Field CHECK(...) constraint override
  #SET(%NFieldNN,       'NULL')
  #!
#!  #IF(%FieldValidation)
#!    #SET(%NFieldNN,     'NOT NULL')
#!  #ENDIF
  #CASE(%FieldValidation)
  #OF  ('NONZERO')
    #!------------------------------------------------------------!
    #! RA.2007.10.31 - Ignore NOT NULL on fields to avoid clarion !
    #! pre-insert of a blank record validation errors by backend. !
    #!------------------------------------------------------------!
    #SET(%NFieldNN,   'NOT NULL')                             	#! RA.2007.10.03 - Specific check
  #OF  ('INFILE')
  #OF  ('BOOLEAN')
    #IF(NUMERIC(%FieldFalseValue) AND NUMERIC(%FieldTrueValue))
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' IN (' & %FieldFalseValue & ',' & %FieldTrueValue & '))')
    #ELSE
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' IN (''' & %FieldFalseValue & ''',''' & %FieldTrueValue & '''))')
    #ENDIF
  #OF  ('INRANGE')
    #IF(%FieldRangeLow AND %FieldRangeHigh)
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' BETWEEN ' & %FieldRangeLow & ' AND ' & %FieldRangeHigh & ')')
    #ELSIF(%FieldRangeLow)
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' > ' & %FieldRangeLow & ')')
    #ELSE
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' < ' & %FieldRangeHigh & ')')
    #ENDIF
  #OF  ('INLIST')
    #SET(%ChoiceLine, '')
    #IF(ITEMS(%FieldValues))
      #SET(%ChoiceComma,ITEMS(%FieldValues) - 1)
      #FOR(%FieldValues)
        #CASE(%NFieldTDef)
        #OF('NUMBER')
          #SET(%ChoiceLine,%ChoiceLine & %FieldValues)
        #ELSE
          #SET(%ChoiceLine,%ChoiceLine & '''' & %FieldValues & '''')
        #ENDCASE
        #IF(%ChoiceComma > 0)
          #SET(%ChoiceLine,%ChoiceLine & ',')
          #SET(%ChoiceComma,%ChoiceComma - 1)
        #ENDIF
      #ENDFOR
    #ELSIF(ITEMS(%FieldChoices))
      #SET(%ChoiceComma,ITEMS(%FieldChoices) - 1)
      #FOR(%FieldChoices)
        #CASE(%NFieldTDef)
        #OF('NUMBER')
          #SET(%ChoiceLine,%ChoiceLine & %FieldChoices)
        #ELSE
          #SET(%ChoiceLine,%ChoiceLine & '''' & %FieldChoices & '''')
        #ENDCASE
        #IF(%ChoiceComma > 0)
          #SET(%ChoiceLine,%ChoiceLine & ',')
          #SET(%ChoiceComma,%ChoiceComma - 1)
        #ENDIF
      #ENDFOR
    #ENDIF
    #IF(%ChoiceLine)
      #SET(%NFieldCheck,'CHECK(' & %NFieldName & ' IN (' & %ChoiceLine & '))')
    #ENDIF
  #ENDCASE
  #!-------------------------------------------------------------!
  #! RA.2012.05.02 - Check for not creating identities.          !
  #! RA.2001.09.09 - Default detected auto-increment attribute.  !
  #!-------------------------------------------------------------!
  #IF(%NFieldName = %NFNameAuto)
    #SET(%NFieldTSql,   'BIGINT')                               #! RA.2012.02.17
    #IF(%BigintNotIdentity)                                     #! RA.2012.05.02
      #SET(%NFieldAuto, '')                                     #! RA.2012.05.02
    #ELSE                                                       #! RA.2012.05.02
      #SET(%NFieldAuto, 'IDENTITY (0, 1)')                      #! RA.2012.02.17
    #ENDIF                                                      #! RA.2012.05.02
    #SET(%NFieldNN,     'NOT NULL')
  #ENDIF
#!
  #!-----------------------------------!
  #! RA.2012.03.02 - Set COLLATE item. !
  #!-----------------------------------!
  #IF(%HaveCollation = %TRUE)                                   #! RA.2012.03.02
    #CASE(%NFieldTDef)                                          #! RA.2012.03.02
    #OF  ('STRING')                                             #! RA.2012.03.02
      #SET(%NFieldCollate, 'COLLATE ' & %CollationValue)        #! RA.2012.03.02
    #ENDCASE                                                    #! RA.2012.03.02
  #ENDIF                                                        #! RA.2012.03.02
#!
  #ADD(%FieldTable,   %FieldID)
#!
  #SET(%IFieldInKey,%Field)                                     #! Field Name
  #FIND(%LFieldInKey,%IFieldInKey)                              #! Field In Key list lookup
  #IF(%LFieldInKey)                                             #! Found? Yes, part of key
    #!-----------------------------------------------------------------------------!
    #! RA.2012.05.03 - Catch the numeric keys and make into BIGINTS automagically. !
    #!-----------------------------------------------------------------------------!
    #CASE(%FieldType)                                           #! RA.2012.05.03
    #OF  ('LONG')                                               #! RA.2012.05.03
    #OROF('DECIMAL')                                            #! RA.2012.05.03
  ---- CAUGHT KEY OR KEY COMPONENT AND CONVERTED TO BIGINT (MAKE SURE OF THIS)
      #SET(%NFieldTSql, 'BIGINT')                               #! RA.2012.05.03
    #ENDCASE                                                    #! RA.2012.05.03
    #SET(%NFieldNN,     'NOT NULL')                             #! Primary keys fields can
  #ENDIF                                                        #! not be NULL. Make it so!
#!
  #!----------------------------------------------------------------------------!
  #!----------------------------------------------------------------------------!
  #! This is the actual SQL field data line creation.                           !
  #!----------------------------------------------------------------------------!
  #!----------------------------------------------------------------------------!
#!
  #IF(%FD1L>0)                                                  #! We GOT array field
  #!----------------------------------------------------------------------------!
  #! If we got an array field at this point, we do not have arrays over groups. !
  #! We have simple array fields and must add additional fields to the table.   !
  #!----------------------------------------------------------------------------!
    #SET(%FldArray,'')                                          #! Clear created name
    #SET(%FD1S,'')                                              #! Number dimension 1
    #SET(%FD2S,'')                                              #! Number dimension 2
    #SET(%FD3S,'')                                              #! Number dimension 3
    #SET(%FD4S,'')                                              #! Number dimension 4
    #!
    #SET(%DimCount,1)                                           #! Got dimension 1
    #IF(%FD2L>0)                                                #! Dimension 2?
      #SET(%DimCount,%DimCount+1)                               #! Got dimension 2
      #IF(%FD3L>0)                                              #! Dimension 3?
        #SET(%DimCount,%DimCount+1)                             #! Got dimension 3
        #IF(%FD4L>0)                                            #! Dimension 4?
          #SET(%DimCount,%DimCount+1)                           #! Got dimension 4
        #ENDIF                                                  #! Dimension 4?
      #ENDIF                                                    #! Dimension 3?
    #ENDIF                                                      #! Dimension 2?
    #!
    #CASE(%DimCount)                                            #! Logic on dimension
    #OF(1)                                                      #! A 1 dimension array
      #LOOP,FOR(%FD1I,1,%FD1L)
        #SET(%FD1S,'_' & %FD1I)
        #SET(%FldArray,%NFieldName & %FD1S)
        #SET(%NFieldLine,   '')
        #SET(%NFieldLine,   %NFieldLine & '' & %FldArray & '')
        #SET(%NFieldLine,   %NFieldLine & ' ' & %NFieldTSql)
        #IF(%NFieldAuto)
          #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldAuto)
        #ENDIF
        #IF(%NFieldCollate)                                     #! RA.2012.03.02
          #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldCollate) #! COLLATE
        #ENDIF                                                  #! RA.2012.03.02
        #IF(%NFieldNN)
          #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldNN)
        #ENDIF
        #IF(%NFieldDef)
          #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldDef)
        #ENDIF
        #IF(%FD1I=%FD1L)                                        #! Last field?
#!-- %FldArray
          #IF(%FieldComma > 0)
            #SET(%FieldComma, %FieldComma - 1)
            #IF(SUB(%NFieldOpt1,1,1)='E')                       #! RASQL(END)
  %NFieldLine
            #ELSE
  %NFieldLine ,
            #ENDIF
          #ELSE
  %NFieldLine
          #ENDIF
        #ELSE                                                   #! Not last field
  %NFieldLine ,
        #ENDIF                                                  #! Just dump stuff
      #ENDLOOP
    #OF(2)                                                      #! A 2 dimension array
      #LOOP,FOR(%FD1I,1,%FD1L)
        #SET(%FD1S,'_' & %FD1I)
        #LOOP,FOR(%FD2I,1,%FD2L)
          #SET(%FD2S,'_' & %FD2I)
          #SET(%FldArray,%NFieldName & %FD1S & %FD2S)
          #SET(%NFieldLine,   '')
          #SET(%NFieldLine,   %NFieldLine & '' & %FldArray & '')
          #SET(%NFieldLine,   %NFieldLine & ' ' & %NFieldTSql)
          #IF(%NFieldAuto)
            #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldAuto)
          #ENDIF
          #IF(%NFieldCollate)                                   #! RA.2012.03.02
            #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldCollate) #! COLLATE
          #ENDIF                                                #! RA.2012.03.02
          #IF(%NFieldNN)
            #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldNN)
          #ENDIF
          #IF(%NFieldDef)
            #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldDef)
          #ENDIF
          #IF((%FD1I=%FD1L)AND(%FD2I=%FD2L))                    #! Last field?
#!-- %FldArray
            #IF(%FieldComma > 0)
              #SET(%FieldComma, %FieldComma - 1)
              #IF(SUB(%NFieldOpt1,1,1)='E')                     #! RASQL(END)
  %NFieldLine
              #ELSE
  %NFieldLine ,
              #ENDIF
            #ELSE
  %NFieldLine
            #ENDIF
          #ELSE                                                 #! Not last field
  %NFieldLine ,
          #ENDIF                                                #! Just dump stuff
        #ENDLOOP
      #ENDLOOP
    #OF(3)                                                      #! A 3 dimension array
      #LOOP,FOR(%FD1I,1,%FD1L)
        #SET(%FD1S,'_' & %FD1I)
        #LOOP,FOR(%FD2I,1,%FD2L)
          #SET(%FD2S,'_' & %FD2I)
          #LOOP,FOR(%FD3I,1,%FD3L)
            #SET(%FD3S,'_' & %FD3I)
            #SET(%FldArray,%NFieldName & %FD1S & %FD2S & %FD3S)
            #SET(%NFieldLine,   '')
            #SET(%NFieldLine,   %NFieldLine & '' & %FldArray & '')
            #SET(%NFieldLine,   %NFieldLine & ' ' & %NFieldTSql)
            #IF(%NFieldAuto)
              #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldAuto)
            #ENDIF
            #IF(%NFieldCollate)                                 #! RA.2012.03.02
              #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldCollate) #! COLLATE
            #ENDIF                                              #! RA.2012.03.02
            #IF(%NFieldNN)
              #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldNN)
            #ENDIF
            #IF(%NFieldDef)
              #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldDef)
            #ENDIF
            #IF((%FD1I=%FD1L)AND(%FD2I=%FD2L)AND(%FD3I=%FD3L))  #! Last field?
#!-- %FldArray
              #IF(%FieldComma > 0)
                #SET(%FieldComma, %FieldComma - 1)
                #IF(SUB(%NFieldOpt1,1,1)='E')                   #! RASQL(END)
  %NFieldLine
                #ELSE
  %NFieldLine ,
                #ENDIF
              #ELSE
  %NFieldLine
              #ENDIF
            #ELSE                                               #! Not last field
  %NFieldLine ,
            #ENDIF                                              #! Just dump stuff
          #ENDLOOP
        #ENDLOOP
      #ENDLOOP
    #OF(4)                                                      #! A 4 dimension array
      #LOOP,FOR(%FD1I,1,%FD1L)
        #SET(%FD1S,'_' & %FD1I)
        #LOOP,FOR(%FD2I,1,%FD2L)
          #SET(%FD2S,'_' & %FD2I)
          #LOOP,FOR(%FD3I,1,%FD3L)
            #SET(%FD3S,'_' & %FD3I)
            #LOOP,FOR(%FD4I,1,%FD4L)
              #SET(%FD4S,'_' & %FD4I)
              #SET(%FldArray,%NFieldName & %FD1S & %FD2S & %FD3S & %FD4S)
              #SET(%NFieldLine,   '')
              #SET(%NFieldLine,   %NFieldLine & '' & %FldArray & '')
              #SET(%NFieldLine,   %NFieldLine & ' ' & %NFieldTSql)
              #IF(%NFieldAuto)
                #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldAuto)
              #ENDIF
              #IF(%NFieldCollate)                               #! RA.2012.03.02
                #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldCollate) #! COLLATE
              #ENDIF                                            #! RA.2012.03.02
              #IF(%NFieldNN)
                #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldNN)
              #ENDIF
              #IF(%NFieldDef)
                #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldDef)
              #ENDIF
              #IF((%FD1I=%FD1L)AND(%FD2I=%FD2L)AND(%FD3I=%FD3L)AND(%FD4I=%FD4L)) #! Last field?
#!-- %FldArray
                #IF(%FieldComma > 0)
                  #SET(%FieldComma, %FieldComma - 1)
                  #IF(SUB(%NFieldOpt1,1,1)='E')                 #! RASQL(END)
  %NFieldLine
                  #ELSE
  %NFieldLine ,
                  #ENDIF
                #ELSE
  %NFieldLine
                #ENDIF
              #ELSE                                             #! Not last field
  %NFieldLine ,
              #ENDIF                                            #! Just dump stuff
            #ENDLOOP
          #ENDLOOP
        #ENDLOOP
      #ENDLOOP
    #ENDCASE
#!
  #ELSE                                                         #! We GOT normal field
  #!----------------------------------------------------------------------------!
  #! This processes the normal fields that are NOT an array.                    !
  #!----------------------------------------------------------------------------!
    #IF(UPPER(%NFieldTSql) = 'BIGINT')                          #! RA.2010.02.20
      #IF(%BigintNotIdentity)                                   #! RA.2012.05.02
  ---- BIGINT KEY DEFINITION OR KEY REFERENCE FOLLOWS (MAKE SURE IT IS)
      #ELSE                                                     #! RA.2012.05.02
  ---- BIGINT IDENTITY DEFINITION OR IDENTITY REFERENCE FOLLOWS (MAKE SURE IT IS)
      #ENDIF                                                    #! RA.2012.05.02
    #ENDIF                                                      #! RA.2010.02.20
    #SET(%NFieldLine,   '')
    #SET(%NFieldLine,   %NFieldLine & '[' & %NFieldName & ']')  #! Field Name
    #SET(%NFieldLine,   %NFieldLine & ' ' & %NFieldTSql)        #! SQL Type
    #IF(%NFieldAuto)
      #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldAuto)        #! AUTO_INCREMENT
    #ENDIF
    #IF(%NFieldCollate)                                         #! RA.2012.03.02
      #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldCollate)     #! COLLATE
    #ENDIF                                                      #! RA.2012.03.02
    #IF(%NFieldNN)
      #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldNN)          #! NOT NULL
    #ENDIF
    #IF(%NFieldDef)
      #SET(%NFieldLine, %NFieldLine & ' ' & %NFieldDef)         #! DEFAULT
    #ENDIF
    #!
    #IF(%FieldComma > 0)
      #SET(%FieldComma, %FieldComma - 1)
      #IF(SUB(%NFieldOpt1,1,1)='E')                             #! RASQL(END)
  %NFieldLine
      #ELSE
  %NFieldLine ,
      #ENDIF
    #ELSE
  %NFieldLine
    #ENDIF
  #ENDIF
#!
#ENDFOR
#!---------------------------------------------------------------------
#GROUP(%TimeStampGroup3)
#IF (%ExtraDTTM=%False)
  #SET(%TimestampOption,%False)
#ENDIF