Prepare your source code

This page takes you through some preparations that might be required to be performed to your code before running it through SnowConvert.

SnowConvert is capable of recognizing your source code and perform various analysis processes in order to accurately transform it to a Snowflake friendly form.

Currently, there are some scenarios when this processes are hindered by how some SQL statements work.

EXEC statements with embedded SQL code

If your code has EXEC statements that contain embedded code in strings (or literals), such as the following example, a small pre-processing is required for an adequate transformation.

-- EXEC statement has an embedded CREATE TABLE statement, inside a N'' literal.
EXEC dbo.sp_StoredProcedure1 @statement = N'
CREATE TABLE t_Table1
(
column1 VARCHAR(20)
)'

The CREATE TABLE code is not immediately recognized as an actual statement that has to be transformed. In order to transform that CREATE TABLE statement, we need to extract it from the EXEC statement, and add it somewhere else as a standalone statement.

Extracting such statements is made easier by executing a small python program, called ExecEmbeddedExtractor.py. After executing this program and specifying your source code folder, the files will be slightly modified so the code extracted from the EXEC statement will be replicated at the end of the file, so that SnowConvert may see it as an actual statement and transform it accurately.

SnowConvert is in charge of handling the original EXEC statement accordingly.

Tool parameters

  • --path: Specifies the path to the source code folder.

  • --encoding (Optional): Specifies the encoding of the source code files. By default uses the following encoding: 'utf-16-le'.

  • --regex (Optional): Specifies the regular expression used to match the embedded code. By default uses the following regular expression: EXEC .*N'(.*(\n.*)*)'.

Tool execution example:

python "C:/ExecEmbeddedExtractor.py" --path "C:/SourceCode"

Tool usage considerations

  • Currently, the tool only works for files that have at most 1 EXEC statement with embedded code. Having more than 1 leads to the basic regular expression matching invalid code.

  • The files inside the input folder will be modified if the regular expression is matched. Always create backups for your source code in case you don't want to lose the original version of it.

Where to get ExecEmbeddedExtractor tool?

You can contact Mobilize and ask for this tool.