Using SnowConvert

In this page you will find information about how to use SnowConvert for SQL Server. The configuration and settings, the conversion process and some of the most common errors.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Before we get started, recall that we highly recommend that you go through our training labs in order to get the most out of SnowConvert.

Configuration and Settings

When you first start Snowflake SnowConvert, you will see the following screen (or one just like it depending on your target):

Notifications

Each time you start SnowConvert, the program will check to see if you have any notifications. These will appear in the top right corner of the screen. If you do not have any notification, nothing will appear. The most common type of notifications that you will receive are listed below.

Update Available

Snowflake is constantly working to improve SnowConvert. If there is an update available, you will get a notification indicating that you may download the latest version. There will be an "Update" button in the notification, and you can download the newest version of SnowConvert by simply pressing that button. A download will automatically start. You do not need to enter new license information as that will be kept automatically. Once the download is complete, you will be prompted to run the installer. (You can see "Execute the Installer" above to learn more about installing SnowConvert.) The upgrade installation for SnowConvert will behave exactly as though you are downloading it for the first time, with the exception that you will not have to re-enter your license information.

When you launch the upgraded version of SnowConvert for the first time, it will prompt you to see the release notes about this latest version of SnowConvert. This will open your default browser and take you to the release notes section of our documentation site.

License expiration

If you have a time-based license, you will receive notifications as you approach the end of your license period. These will come in three different stages:

1. The first notification will let you know that your license expires in 15 days.

2. The second will notify you when your license expires in 2 days.

3. You will get a final notification if you open SnowConvert and your license has expired indicating that you need to renew your license. Note that you will not be able to use the migrator if your license has expired.

You will be prompted to reach out to [e-mail] by each of these notifications to renew your license.

If you are using a lines of code based license, you will not get a notification when you try to run the migrator indicating that the following run will exceed your maximum lines of code. As with the time-based license, you will be prompted to reach out to [e-mail] to renew or upgrade your license.

Hopefully, you're up and running with SnowConvert. Now for our guides on conversion from Teradata, Oracle, and SQL Server.

Conversion Settings

If you're not interested in changing any of the default settings, you can start the conversion process by simply clicking on the "Let's begin" button in the center of the window. If you do want to configure any of the settings first, you can choose File (in the top left of the application) -> Settings.

Note that you can also reach this screen from the input/output folder screen (described in the next section). Once you have started the conversion process, you can not change any of these settings unless you restart SnowConvert.

There are three pages in the Conversion Settings Window:

• General

• DB Object Names

• Prepare Code

• Stored Procedures Target Language

These pages are described in more detail below.

General

On the general page (as is shown above), there are three options you can select. Each is described below.

Generate debug log: This allows you to specify if you want a debug log to be written while SnowConvert is performing the conversion. Note that you will get a general conversion log and various reports each time you run the tool. The debug log is very detailed and can be helpful in working through any critical errors that may have occurred during the conversion process. This log is set to be generated by default, unless you change it here.

Comment objects with missing dependencies: SnowConvert can comment out dependencies that are not created in the source code or it can provide a comment in the output code that declares that a given dependency is missing. The default setting is to provide a comment declaring that a dependency is missing, but the code will still be converted. An example is given below with one single view created without the declaration of any dependencies (note that the below code is in Teradata, but the same applies for Oracle):

REPLACE VIEW VIEW1 AS SELECT * FROM TABLE1;

If you leave the default setting, this is the resulting code:

/**** WARNING: MISSING DEPENDENT OBJECT "table1" ****/
CREATE OR REPLACE VIEW PUBLIC.view1
AS 
   SELECT * FROM PUBLIC.table1;

If you enable this option, then the resulting code will be:

/**** WARNING: MISSING DEPENDENT OBJECT "table1" ****/
/*CREATE OR REPLACE VIEW PUBLIC.view1
AS
   SELECT
   *
   FROM PUBLIC.table1;*/ 

Note that SnowConvert uses an Abstract Syntax Tree (AST) to build relationships between each of the elements in the code you are converting. Due to this architecture, the more complete the codeset you put into SnowConvert, the more complete your output will be. As an example, if you have a table that is created in one .sql file and called in another, but you have not included the .sql file in the input folder... SnowConvert will still convert the input code, but will give you a "missing object warning" regarding the missing table as illustrated above. You could encounter some more serious errors if multiple dependencies are excluded, so please try to include the most complete version of your source code in the Input folder.

Disable EWI comments generation: This option allows you to disable the generation of comments for Errors, Warnings, and Issues on the converted code. The default value for this setting is false because the generation of EWIs provides the user an overview of general problems and troubleshooting after the migration process. However, if the user is aware of it and wants a cleaner final code, this option can be enabled.

Set Encoding of the Input Files (Skip Auto Detection) SnowConvert tries to automatically detect the file encoding. However, it's not always possible to identify the correct encoding, and this can sometimes (rarely) cause an error. In such a scenario, you can manually select the file encoding for the files to process. Below are the encodings supported by .NET Core (the platform used to build SnowConvert):

Descriptive Name	Name	Code
US-ASCII	US-ASCII	20127
Unicode (UTF-7)	UTF-7	65000
Unicode (UTF-8)	UTF-8	65001
Unicode	UTF-16	1200
Unicode (Big Endian)	UnicodeFFFE	1201
Unicode (UTF-32)	UTF-32	12000
Unicode (UTF-32 Big Endian)	UTF-32BE	12001
Western European (ISO)	ISO-8859-1	28591

DB Object Names

Object Name Customization screen.

On this page, you can set database or schema names that can be applied to the objects that are converted by SnowConvert. Snowflake uses the DATABASE.SCHEMA.OBJECT naming convention, let's take for example the following code and then we will convert it with the available conversion options:

CREATE MULTISET TABLE TABLE1
(
    COL1 NUMBER
);

CREATE MULTISET TABLE MYSCHEMA.TABLE2
(
    COL1 NUMBER
);

REPLACE VIEW VIEW1 AS
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM MYSCHEMA.TABLE2;

• Schema: If you'd prefer the Teradata database name to be used as the schema name, then you can specify a database name to be applied to the Snowflake output instead. If you specify a Schema Name MyCompanySchema, you will get the following converted code:

CREATE TABLE MyCompanySchema.TABLE1
(
    COL1 NUMBER(38, 19)
);

CREATE TABLE MyCompanySchema.TABLE2
(
    COL1 NUMBER(38, 19)
);

CREATE OR REPLACE VIEW MyCompanySchema.VIEW1
AS
   SELECT * FROM MyCompanySchema.TABLE1
   UNION ALL
   SELECT * FROM MyCompanySchema.TABLE2;

• Database: And, if you specify a Database name MyCompanyDb, you will get the following converted code:

CREATE TABLE MyCompanyDb.PUBLIC.TABLE1
(
   COL1 NUMBER(38, 19)
);

CREATE TABLE MyCompanyDb.MYSCHEMA.TABLE2
(
   COL1 NUMBER(38, 19)
);

CREATE OR REPLACE VIEW MyCompanyDb.PUBLIC.VIEW1
AS
   SELECT * FROM MyCompanyDb.PUBLIC.TABLE1
   UNION ALL
   SELECT * FROM MyCompanyDb.MYSCHEMA.TABLE2;

• None (default): With this option, no additional schemas or databases will be added to the input code. The output names will remain the same. As seen below:

CREATE TABLE TABLE1
(
    COL1 NUMBER(38, 19)
);

CREATE TABLE MYSCHEMA.TABLE2
(
    COL1 NUMBER(38, 19)
);

CREATE OR REPLACE VIEW VIEW1
AS
    SELECT * FROM TABLE1
    UNION ALL
    SELECT * FROM MYSCHEMA.TABLE2;

• Use Existing Name Qualification: This is a flag, it is only available when a Schema or Database options above are selected. It preserves the name qualification from the input code, placing either Schemas or Databases only where they are missing. Opposite, when this flag is disabled, it will add Schemas or Databases everywhere, overwriting those that come in the input code. See the examples below where a new schema is included:

Use Existing Name Qualification Enabled

CREATE TABLE MYSCHEMA.TABLE1
(
    COL1 NUMBER(38, 19)
);

CREATE TABLE MYDB.TABLE2
(
    COL1 NUMBER(38, 19)
);

CREATE OR REPLACE VIEW MyCompanychema.VIEW1
AS
   SELECT * FROM MyCompanySchema.TABLE1
   UNION ALL
   SELECT * FROM MYSCHEMA.TABLE2;

Use Existing Name Qualification Disabled

CREATE TABLE MyCompanySchema.TABLE1
(
    COL1 NUMBER(38, 19)
);

CREATE TABLE MyCompanySchema.TABLE2
(
    COL1 NUMBER(38, 19)
);

CREATE OR REPLACE VIEW MyCompanySchema.VIEW1
AS
   SELECT * FROM MyCompanySchema.TABLE1
   UNION ALL
   SELECT * FROM MyCompanySchema.TABLE2;

Prepare Code

Conversion Settings - Prepare code

On this page, you can set different options for preprocessing the source code before the conversion.

Pretty print: This option applies indentation to the original code and gets it well organized, styled and formatted.

Maintain the input folder structures: Applies arrangement to multiple databases represented as multiple folders and keeps their original structure in the output folder to avoid mixing different files from different databases.

Generate errors report: When this option is enabled, an error report is generated in the preprocessing step, indicating if there are any parsing errors and, if so, the location of the files where the parsing errors were found.

Stored Procedures Target Language

Conversion Settings - Stored Procedures Target Language

On this page, you can choose whether stored procedures are migrated to JavaScript embedded in Snow SQL, or to Snowflake Scripting. The default option is Snowflake Scripting.

Reset Settings

While not a separate page, the reset settings option appears on every page. If you've made changes, you can reset SnowConvert to its original settings. When you select this option, you will be prompted to reset the settings on only this page or every page in the configuration settings.

Help Menu

The help menu can be found in the top left corner of the SnowConvert screen. When you choose the help menu, you will get the following options.

User Guide

The user guide is this documentation site that you are reading right now. By clicking on "User Guide", your default browser will open and you will be taken to the documentation site for SnowConvert.

License Information

This will give you information about your license, and will allow you to change or update your current license. It will also let you know who the license is registered to, what execution mode this license is available for (assessment or full conversion), and when the license expires.

Check for Updates

SnowConvert should check for updates every time it starts up. However, if you're not sure whether or not you have the latest version, you can select this option and the program will check for updates. If an update is available, you will be given the option to download it. If there are no updates available, you will get a brief message indicating that "you're up to date!"

View License Agreement

This will take you to the terms and conditions page of the Snowflake website. Brace for some legalese, but if you're looking for the terms and conditions of use for this product, look no further.

About SnowConvert (for Teradata, Oracle or SQL Server)

Finally, this will give you some basic information about the version of SnowConvert that you are currently running, including what version of the application you are currently running and what version of the conversion core the application is using. Sometimes there are updates to the conversion core and not the rest of the application. That should be illustrated here.

Alright... enough with the settings. Let's move on and take a look at the conversion process; how SnowConvert actually works.

Conversion Process

So how do you use SnowConvert? Follow the step below on the conversion process, which will walk you through the inputs you will have to provide SnowConvert and the processes that SnowConvert goes through internally.

Step 1: Declaring the Input and Output Folder

When you first launch Snowflake SnowConvert, you'll see the welcome screen shown above.

If you select "Let's Begin" in the middle of the screen, you will be taken to the following screen where you can specify the input and output folders for your conversion.

Input Folder: Your Input folder is the folder where you should place all of the files you are looking to convert. Recall that to get the most value out of your conversion with SnowConvert, you should attempt to include all or as much of the source code as possible in the input folder. You can either click on the browse button or type the path manually.

Output Folder: The output folder is the folder you would like SnowConvert to output the logs, reports, and output code from the conversion process. This will all be output in this folder. Note that each of the filenames for the converted code will be the same as they were in the source code. If you had a tables.sql file in the source code, this will be output to a tables.sql file in the output folder.

Prepare my code for conversion: When this checkbox is enabled, SnowConvert will execute an extra step before the parsing. This is because the extracted scripts from the SQL Server database, many times are not friendly with SnowConvert, and they need some arrangement before being loaded and converted. It also removes statements that are not relevant in Snowflake. This setting will generate a new folder with the arranged postfix that will be used as the input folder of the migrator. Example:

Before preparing code

original.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view1]'))
EXEC dbo.sp_executesql @statement = N'
CREATE  VIEW [dbo].[view1] AS 
SELECT * FROM TABLE1
' 
GO

After preparing code

arranged.sql

CREATE  VIEW [dbo].[view1] AS 
SELECT * FROM TABLE1

Step 2: Conversion Start and Validation

Note that for both the input and output folder, the program will validate the input and the output directories that you've selected. The following validation checks will be done:

• To determine if there are "convert-able" files in the input directory.

• If there are already files in the output directory, you will get a message once you hit "Start Conversion" that will ask you if you'd still like to proceed with the conversion.

• If one or both of the file paths are invalid, you will get a message indicating so.

Once you have declared both an input and an output folder, you can select "Start Conversion", and SnowConvert will begin the transformation. The icon with the gear next to the "Start Conversion" button takes you to the same configuration settings page that is described above.

Step 3: Conversion States

Once you begin the conversion process, there are three different conversion states that will be updated on the following screen.

Conversion in Progress screen

Note that you will also get a readout of how many files are being converted, and the total size of the source files.

Snowflake SnowConvert's conversion tasks are divided up as follows:

1. Preparing Source Code: The files inside the input folder are arranged before being loaded into the migrator. This task will generate a new folder with the arranged files. This new folder will be used as the input folder of the migrator if this step was executed. In the new folder structure, each DDL will represent a file. Each file will be organized under the following structure: database\DDL type\schema\DDL name

2. Loading Source Code: The files inside the input folder are loaded into the migrator. The migrator analyzes the input and prepares to parse through the source code. If there are any errors in loading the files, there will be parsing errors reported in the assessment documents.

3. Converting: The conversion process begins and produces some metadata about the loaded files including the size and quantity of the files reported on the screen. While the conversion process is taking place, SnowConvert is building a complete AST and a complete symbol table to properly create functional equivalence on the output code.

4. Writing results: This final state let's you know that the conversion process is complete, and the application is creating the output code and reports to populate the output folder.

During each stage, the cards depicting each stage on the screen will change color to indicate what is currently taking place. The loading icon shows that the state is executing. The check icon shows that the state was successfully executed. Finally, if you encounter a red stage with an X icon, that indicates that something went wrong while executing that specific task.

Step 4: Conversion Results

Once the conversion has finished, the "Next" button will be available at the bottom right of the screen. On the next screen, a short summary will be available with some summary statistics about the conversion that just took place.

Included on this screen are the following:

• Total Files: The number of files that were processed by SnowConvert during this conversion.

• Total Issues: Number of warnings and errors that were generated by "SnowConvert while converting this set of files. You can learn more about issues and errors by visiting our page on Issues and Troubleshooting.

• Total Parsing Errors: The number of conversion elements that were not converted due to the parser not being about to process a specific object or element. Again, you can learn more about issues and errors in SnowConvert by visiting our page on Issues and Troubleshooting.

• Conversion Time: The actual time it took to convert these files.

• Conversion Speed: The speed in lines of code per second that SnowConvert was able to convert the source code. The number listed is the number of source code lines of code converted each second.

• LOC Conversion Summary: A summary giving you the percentage of total lines of code that were successfully converted by SnowConvert. The following details are covered in the summary:

  • Lines of Code (LOC): The total number of lines of code that are in the source code files.

  • LOC Conversion Percentage: The percentage of the total lines of code (given above) that were successfully converted by SnowConvert.

• Object Conversion Summary: A summary giving the percentage of total objects converted by SnowConvert. An object in SnowConvert is generally given as a table, view or procedure. Some objects are within another object, but this count will be of all total objects. You can find more information in the reports about the type of objects that were found in the source code. This is described in more detail below under reports. The following details are covered in this summary:

  • Objects: The number of objects detected in the source code. Objects could include, tables, views, and procedures.

  • Object Conversion Percentage: The percentage of the total objects (found above) that were successfully converted by SnowConvert.

After the statistics, there are a few options for examining the output. You can click on one of the following options:

• View Reports: If you click on view reports, SnowConvert will take you to the reports file in the output folder that you specified before the conversion. This folder was created by SnowConvert during the "Writing Converted Code and Reports" section of the conversion process. The following reports are generated by SnowConvert:

  • Assessment Report: This is the report that has all of the details regarding your conversion, and lays out how much work will still be required to complete the migration. A more detailed explanation on the assessment report is coming soon.

  • There are also 3 csv files that are included in the reports folder:

    • Assessment: This csv file contains all the raw data used to build the assessment report.

    • Top level objects: The top level objects report provides a general overview of the main objects present in your source code.

    • Issues: This may be the most critical report. SnowConvert collects each issue found in the conversion, lists the severity level of each issue, and gives you the exact location in the source code of where that issue took place. If you're interested in what went wrong with the migration, visit the assessment report, and immediately come here. For more information on issues in SnowConvert, visit our page on Issues and Troubleshooting.

• View Logs: Clicking on "View Logs" will take you to the folder containing the log files. The logs are text files that contain time-stamped entries that illustrate the process that SnowConvert has gone through. If there is a critical error, the logs will be the best place to troubleshoot what went wrong, and exactly when did it go wrong. The log in this folder:

  • Conversion Log: The conversion log is always included in any run of SnowConvert, and contains timestamped information related to the conversion process in addition to the information found in the errors log.

• View Output: The "View Output" button will take you to the output folder. In the output folder, you will also see the two folders listed above: reports and logs. These are the same folders that are linked to above by the "View Reports" and "View Logs" buttons. However, the output folder also has all of the converted code that is output by SnowConvert. Note that each source code file will have an output file of a similar (and equivalent) name in this folder that contains the output code.

• Start Over: This button will close and restart SnowConvert. The migrator will need to reset between runs, so this is required if you want to run SnowConvert again... even on the exact same workload. It is not required to use this button, however. Closing SnowConvert and restarting it from your desktop will have the exact same effect.

Application Errors

In this section, you are going to see possible errors that may show up in the SnowConvert application and their cause. Note that these are not errors with the parsing or conversion of the code. All parsing and conversion errors and warnings are described in the Issues and Troubleshooting section of this documentation.

The following types of errors are going to be the most common.

Input and Output Errors

In step 1 of the conversion process, you have to declare an input and output folder for the conversion. You could receive one of the following error messages below the pathway if there's an error with the chosen input/output folder.

Please enter an input path.

If you type an invalid file path or don't browse to a folder in either the input or output directory, you will get a short error in the window asking you to "Please enter an input [or output] path."

The resolution of this error is pretty simple, you have to type a path that exists or choose a folder in the browse menu.

Input folder must contain [the correct] extensions.

If you choose a folder that doesn't have any files that can be converted by SnowConvert, then you will get the following error message: "Input folder must contain .sql extensions"

The resolution of this error requires you to choose a directory that has one of the given file extensions.

The path must have a valid format (Can't contain / : * ? " < > |).

If you choose a pathway that contains an invalid character, you will get the following error message: "The path must have the valid format (Can't contain / : * ? " < > |)."

You will need to choose a folder that has a valid file path and doesn't include any invalid characters.

Output folder isn't empty!

A warning will come up when you try to execute SnowConvert and the output folder is not empty. This will not come up when you attempt to write an input/output folder name, but rather when you click "Start Conversion". The warning looks like this:

You can click the cancel button and select another folder or proceed with the risk of potentially overwriting certain files. If the output of SnowConvert is writing a filename that is already in the output folder, the file that is already there will be overwritten.

Note: Folders located in network paths are not supported. The folder must be local to the computer running SnowConvert.

Conversion process errors

In the conversion process, you are going to see if a state has an error, in case some state is valid the next button is going to be available.

Report errors

When you try to see the report's summary and it has errors you are going to see an incomplete conversion page and a button to open the log folder containing the reports.

System errors

If the system crashes, you are going to see the "Something went wrong" message. In this message, you will be given the option to send an email to [e-mail] with this error report in order for the SnowConvert team to improve the tool.