SQL Scripts

The following EDI scripts are available for advanced users in the SYSPRO scripts folder. For SYSPRO 7 and prior versions the scripts folder is under the base folder (Base\Scripts). For SYSPRO 8 the scripts are in the Plugin\CustomStore folder.

Please backup the database prior to running scripts.
Use all scripts with extreme caution.
Some of the scripts require the removal of comment marks before running.

To execute a script, open the script in the Microsoft SQL Server Management Studio.  Make sure the correct database is selected in the ribbon bar and execute the script.

EDIBinView.sql Used to setup a SQL view for the Handheld Recommended Putaway function. 
(typcially, not needed)
EDIDropConstraints.sql Used to clear all constraints from any of the EDI Tables.
(external constraints to EDI tables are not standard)
EDIDropTables.sql Used to drop ALL EDI tables from the database.
(Script is commented out for safetly, but must always be used with extreme caution)
EDIKeys.sql Used to rebuild the SQL keys on the EDI tables.
(if a table is copied within SQL, the keys and indexes get lost-- this script will rebuild them)
EDISyspro7.sql Used to convert SYSPRO Key fields in the database to SYSPRO 7 format.  
(only used when converting from 6.1 to SYSPRO 7 (or higher) and only after EDIUpdate.sql is run)
EDITables.sql Used to create the EDI tables in a SYSPRO database where they do not exist.
(no need to run more than once)
EDIUpdate.sql Used to make sure the EDI database has been upgraded as necessary-- can be run multiple times.
(IMPORTANT:  if any errors are returned, they should be reported to Support)
EDIReplaceNulls.sql Used to remove null values in the SQL data-- can be run multiple times.

 

SYSPRO 7 Installation / Update

  1. The installation of a new company in SYSPRO 7 is identical to the current systems.

  2. If a fresh installation is being done, this step is not necessary; however, if an in-place upgrade or the import is being run, it will be necessary to remove the EDI table from DDS before doing the upgrade:

    • Under Support Systems within the EDI menu, launch "Update DDS File Information"

    • Select "Remove DDS Information"

  3. Companies converting data from earlier versions of SYSPRO should first run the database conversion:

    • EDICNV in SYSPRO if C-ISAM or

    • EDIUpdate.SQL if SQL

  4. SQL companies should run EDISyspro7.sql against the SQL databases:

    • The purpose of EDISyspro7.sql is to convert Solutions for SYSPRO tables to the SYSPRO 7 key formats

    • Important note:  all SQL statements in EDISyspro7.sql are commented-out and must be manually edited per the instructions at the beginning of each section in EDISyspro7.sql before the SQL is executed.  For example:

      • If numeric sales orders are being used, "un-comment" the SQL in the sales order section by removing the /* at the beginning and the */ at the end of that section

      • If numeric job numbers are being used, "un-comment" the SQL in the job number section by removing the /* at the beginning and the */ at the end of that section

      • After all sections of EDISyspro7.sql have been reviewed and manually edited as appropriate, run EDISyspro7.sql against the company database

      • Be careful with the section regarding the table EDIXRefLabel.  This update could cause problems if there are numeric codes which are not A/R customer codes in the table.  The 11/11/2015 release does not contain this update.  The update is as follows... 

        UPDATE EDIXRefLabel SET Code='00000000'+Code
        WHERE SUBSTRING(Code,8,8)=' ' AND (Code IS NOT NULL AND Code>' ' AND Code <'A')

  5. SQL companies upgrading to SYSPRO 7 should run EDIReplaceNulls.SQL against all databases to remove null values from the SQL data.  This is a one-time requirement as all future changes to the database will include commands to clear the nulls.  Note:  this can be run on SYSPRO 6.1 systems also as the script is release and version independent.  The script looks for any EDI field containing a null value and clears it.

  6. All companies run EDICV7 in SYSPRO.

  7. Note:  Any entries in the Label Cross-References that use an A/R customer code as the label code and  where the customer code is numeric, those cross-references must be deleted and re-entered using the full 15-digit internal key version of the A/R code, rather than the shortened code that is seen when it displays.

  8. Note:  if Document Tracking is being used, an export of the documents must be performed to reload the tracking tables.

  9. Note regarding Outgoing address configurations and country codes. For Outgoing addresses, if prior to the SYSPRO 7 upgrade, addresses were set to use "Address Line 5" for Country Code, please check the conversion results closely in this area. The SYSPRO 7 conversion will insert an additional address line thus causing the Country Code to be in Address Line 6. If this is the case, the setting in the "Country Options" tab of the Outgoing Address Configuration Cross-Reference must be changed to reflect Line 6 accordingly.

Solutions

Tutorials