SnowConvert: SQLServer Issues

SSC-EWI-TS0060

Severity

Medium

Description

This warning appears when an unsupported time component is used as a parameter in a date-related function in Snowflake. For a complete list of supported date and time components, please refer to the Date & Time Functions | Snowflake Documentation.

Code Example

Input code

SELECT
    -- Supported
    DATEPART(second, getdate()),
    -- Not supported
    DATEPART(millisecond, getdate()),
    DATEPART(microsecond, getdate());
Copy

Output code:

SELECT
    -- Supported
    DATE_PART(second, CURRENT_TIMESTAMP() :: TIMESTAMP),
    -- Not supported
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0060 - TIME PART 'millisecond' NOT SUPPORTED AS A FUNCTION PARAMETER ***/!!!
    DATEPART(millisecond, CURRENT_TIMESTAMP() :: TIMESTAMP),
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0060 - TIME PART 'microsecond' NOT SUPPORTED AS A FUNCTION PARAMETER ***/!!!
    DATEPART(microsecond, CURRENT_TIMESTAMP() :: TIMESTAMP);
Copy

Recommendations

  • You can create a User-Defined Function (UDF) to manually extract time components that are not directly supported in Snowflake.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TS0070

The EWI feature has been deprecated. For current information, please see the SSC-FDM-TS0024 documentation.

Description

This warning appears when using CURRENT_TIMESTAMP with the AT TIME ZONE clause. The warning indicates that results may be inconsistent across different scenarios due to the dynamic nature of CURRENT_TIMESTAMP.

The key distinction between Microsoft SQL Server and Snowflake’s CURRENT_TIMESTAMP function is their time zone handling:

  • SQL Server: Returns the system date and time based on the server’s local time zone

  • Snowflake: Returns the date and time in UTC (Coordinated Universal Time)

SELECT current_timestamp at time zone 'Hawaiian Standard Time';
Copy

2024-02-08 16:52:55.317 -10:00

SELECT
CONVERT_TIMEZONE('Pacific/Honolulu', CURRENT_TIMESTAMP() !!!RESOLVE EWI!!! /*** SSC-EWI-TS0070 - CURRENT_TIMESTAMP in At Time Zone statement may have a different behavior in certain cases ***/!!!);
Copy

2024-02-08 06:53:46.994 -1000

Recommendations

Here is an example of how to maintain consistent formatting in Snowflake.

SELECT current_timestamp at time zone 'Hawaiian Standard Time';
Copy

2024-02-08 16:33:49.143 -10:00

In Snowflake, you can modify the default time zone using the ALTER SESSION command. For example:

ALTER SESSION SET TIMEZONE = 'Pacific/Honolulu';

SELECT
CONVERT_TIMEZONE('Pacific/Honolulu', 'UTC', CURRENT_TIMESTAMP());
Copy

2024-02-08 16:33:49.143

SSC-EWI-TS0044

Severity

Critical

Parts of the code have been omitted to keep the example clear and concise.

Description

This warning appears when using the FOR XML clause, which is not available in Snowflake SQL.

Code Example

Input Code:

SELECT TOP 1 LastName
FROM AdventureWorks2019.Person.Person
FOR XML AUTO;
Copy

Output Code:

SELECT TOP 1
LastName
FROM
AdventureWorks2019.Person.Person
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;
Copy

Recommendations

Consider using User-Defined Functions (UDFs) to replicate the functionality of your source code. Below are examples of UDFs that can help you recreate the original behavior:

SQL Server

CREATE TABLE TEMPTABLE (Ref INT, Des NVARCHAR(100), Qty INT)

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

GO

-- FOR XML
SELECT *
FROM TempTable
FOR XML AUTO

GO

-- FOR XML RAW
SELECT *
FROM TempTable
FOR XML RAW
Copy
-- FOR XML
<TempTable Ref="100001" Des="Normal" Qty="1"/><TempTable Ref="100002" Des="Foobar" Qty="1"/><TempTable Ref="100003" Des="Hello World" Qty="2"/>

-- FOR XML RAW
<row Ref="100001" Des="Normal" Qty="1"/><row Ref="100002" Des="Foobar" Qty="1"/><row Ref="100003" Des="Hello World" Qty="2"/>
Copy

Snowflake

CREATE OR REPLACE TABLE TEMPTABLE (
Ref INT,
Des VARCHAR(100),
Qty INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2);

-- FOR XML
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML AUTO CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML AUTO;

-- FOR XML RAW
SELECT
*
FROM
TempTable
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;
Copy
-- FOR XML
<TempTable DES="Normal" QTY="1" REF="100001"  /><TempTable DES="Foobar" QTY="1" REF="100002"  /><TempTable DES="Hello World" QTY="2" REF="100003"  />

-- FOR XML RAW
<row DES="Normal" QTY="1" REF="100001"  /><row DES="Foobar" QTY="1" REF="100002"  /><row DES="Hello World" QTY="2" REF="100003"  />
Copy

SSC-EWI-TS0035

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to understand.

Description

Currently, Snowflake does not support Cursor Variables that are declared but not initialized. As a result, an Early Warning Indicator (EWI) has been added and the related code has been commented out.

Code Example

Input Code:

CREATE OR ALTER PROCEDURE notInitializedCursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0035
    DECLARE @MyCursor CURSOR, @MyCursor2 CURSOR;
    -- Should not be marked
    DECLARE cursorVar CURSOR FORWARD_ONLY STATIC READ_ONLY
        FOR
        SELECT someCol
        FROM someTable;
    RETURN 'DONE';
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE notInitializedCursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        -- Should be marked with SSC-EWI-TS0035
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0035 - CURSOR VARIABLE DECLARED BUT NEVER INITIALIZED, THIS IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        MYCURSOR CURSOR;
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0035 - CURSOR VARIABLE DECLARED BUT NEVER INITIALIZED, THIS IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        MYCURSOR2 CURSOR;
        -- Should not be marked
        cursorVar CURSOR
        FOR
            SELECT
                someCol
            FROM
                someTable;
    BEGIN


        RETURN 'DONE';
    END;
$$;
Copy

Recommendations

SSC-EWI-TS0001

Severity

Critical

Parts of the output code have been removed to make the example clearer and easier to follow.

Description

This error warning indicator (EWI) appears when SnowConvert encounters a critical error that prevents it from generating the function body during translation.

Example Code

SQL Server

CREATE FUNCTION func1 ()
RETURNS VARCHAR
SELECT
   *
FROM
   TABLE1
Copy

Snowflake

CREATE OR REPLACE FUNCTION func1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0001 - THE BODY WAS NOT GENERATED FOR FUNCTION 'func1' ***/!!!
AS
$$

$$;
Copy

Recommendations

SSC-EWI-TS0025

Severity

Low

Generate Procedures and Macros in JavaScript by adding either the flag -t JavaScript or --PLTargetLanguage JavaScript to your command.

Parts of the output code have been removed to make the example clearer and easier to follow.

Description

This Early Warning Indicator (EWI) appears when translating the ERROR_SEVERITY built-in function. The function returns a default value of 16, which represents the most common severity level in SQL Server. The generated User-Defined Function (UDF) should retrieve this value.

Code Example

Input Code:

-- Additional Params: -t JavaScript
CREATE procedure proc1()
as
BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0 from table1;
END TRY
BEGIN CATCH
    return ERROR_SEVERITY();
END CATCH;
GO
Copy

Output Code:

CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    try {
        EXEC(`    -- Generate a divide-by-zero error.
    SELECT
       TRUNC( 1/0) from
       table1`);
    } catch(error) {
        return SELECT(`   !!!RESOLVE EWI!!! /*** SSC-EWI-TS0025 - CUSTOM UDF 'ERROR_SEVERITY_UDF' INSERTED FOR ERROR_SEVERITY FUNCTION. ***/!!!
   ERROR_SEVERITY_UDF()`);
    }
$$;
Copy

Recommendations

SSC-EWI-TS0074

Severity

Low

Parts of the code have been omitted to keep the example clear and concise.

Description

This warning appears during the conversion of TRY_CAST and TRY_CONVERT functions. The results in Snowflake might be different from the original because some data type dependencies could not be resolved. This typically happens when the required dependencies are not present in the source code.

Input Code:

SELECT TRY_CONVERT( INT, col1) FROM TABLE1;

SELECT TRY_CAST(COL1 AS FLOAT) FROM TABLE1
Copy

Output Code

SELECT
CAST(col1 AS INT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CONVERT FUNCTION DUE TO MISSING DEPENDENCIES ***/!!!
FROM
TABLE1;

SELECT
CAST(COL1 AS FLOAT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CAST FUNCTION DUE TO MISSING DEPENDENCIES ***/!!!
FROM
TABLE1;
Copy

Recommendation

If you need technical assistance, please contact our support team at snowconvert-support@snowflake.com.

SSC-EWI-TS0034

Severity

High

Description

This warning appears when SnowConvert cannot automatically determine the column structure for a function’s RETURNS TABLE clause during code conversion. As a result, the RETURNS TABLE clause is left empty in the generated code. This typically occurs when the column information cannot be inferred from the original source code.

Code Example

Input Code:

CREATE FUNCTION Sales.ufn_SalesByStore2()
RETURNS TABLE
AS
RETURN
(
  WITH CTE AS (
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
  )
  SELECT tab.* FROM CTE tab
);

GO

SELECT * FROM GetDepartmentInfo();
Copy

Output Code:

CREATE OR REPLACE FUNCTION Sales.ufn_SalesByStore2 ()
RETURNS TABLE(
  DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
  --** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
    WITH CTE AS (
    SELECT
      DepartmentID,
      Name,
      GroupName
    FROM
      HumanResources.Department
    )
    SELECT tab.* FROM
    CTE tab
$$;

SELECT
    *
FROM GetDepartmentInfo() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;
Copy

Recommendations

  • To resolve this issue, ensure that all required objects are accessible to your code. If the problem continues after confirming all necessary objects are available, please contact us with details about your specific use case.

  • For additional support, email us at snowconvert-support@snowflake.com

SSC-EWI-TS0041

Severity

Medium

Description

This Early Warning Indicator (EWI) applies to the following XML data type methods, which are currently not available in Snowflake SQL:

  • Value: The actual data or content stored in a variable or field

  • Query: A request to retrieve specific information from a database

  • Exist: To check if a particular element or condition is present

  • Modify: To change or update existing data or settings

  • Nodes: Individual elements or connection points within a data structure

Code Example

Input Code:

CREATE PROCEDURE xml_procedure
    @inUserGroupsXML XML
AS
BEGIN
    SELECT  entities.entity.value('TypeID[1]', 'VARCHAR(100)') AS TypeID
        ,entities.entity.value('Name[1]', 'VARCHAR(100)') AS Name
    INTO  #tmpUserGroups
    FROM  @inUserGroupsXML.nodes('/entities/entity') entities(entity)
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE xml_procedure (INUSERGROUPSXML TEXT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CREATE OR REPLACE TEMPORARY TABLE T_tmpUserGroups AS
            SELECT
                XMLGET(entity, '$') :: VARCHAR(100) AS TypeID
                ,
                XMLGET(entity, '$') :: VARCHAR(100) AS Name
            FROM
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0041 - XML TYPE METHOD nodes IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                T_inUserGroupsXML('/entities/entity') entities (
                    entity
                );
    END;
$$;
Copy

Recommendations

SSC-EWI-TS0010

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

High

Description

This error occurs when a Common Table Expression (CTE) is incorrectly used within a view. Views are read-only representations of queries that define how to retrieve and display data, rather than how to modify it.

Code Example

Input Code:

Create View viewName
as
with commonTableExpressionName (
   columnName
) as
(
   select
      1
)
((select
   1 as col2)
union
(
   select
      1 as col3
));
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0010 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED IN SNOWFLAKE. ***/!!!
CREATE OR REPLACE VIEW viewName
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - WITH CTE NOT SUPPORTED IN SNOWFLAKE ***/!!!
with commonTableExpressionName (
   columnName
) as
(
   select
      1
)
((select
   1 as col2)
union
(
   select
      1 as col3
));
Copy

Recommendations

SSC-EWI-TS0024

Severity

Low

Description

This warning appears when a BULK INSERT operation within a stored procedure cannot be detected. As a result, the required dependencies for the complete transformation will not be created. Additionally, the converted COPY INTO command will attempt to retrieve files from a tempStage, which must be manually created by the user.

Code Example

Input Code:

CREATE PROCEDURE BULK_PROC2
AS
BULK INSERT dbo.table1 FROM 'E:\test.txt'
WITH
(
   FIELDTERMINATOR ='\t',
   ROWTERMINATOR ='\n'
);

GO
Copy

Output Code:

CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461207064166040
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n';

CREATE OR REPLACE STAGE STAGE_638461207064166040
FILE_FORMAT = FILE_FORMAT_638461207064166040;

--** SSC-FDM-TS0004 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI **
PUT file://E:\test.txt @STAGE_638461207064166040 AUTO_COMPRESS = FALSE;

CREATE OR REPLACE PROCEDURE BULK_PROC2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   // END REGION

   EXEC(`COPY INTO dbo.table1 FROM @STAGE_638461207064166040/test.txt`);
$$
Copy

Recommendations

SSC-EWI-TS0075

Severity

Medium

Description

Built-in procedure translation is not currently available.

Example Code

Input Code:

EXEC sp_column_privileges_rowset_rmt 'Caption';
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0075 - TRANSLATION FOR BUILT-IN PROCEDURE 'sp_column_privileges_rowset_rmt' IS NOT CURRENTLY SUPPORTED. ***/!!!
EXEC sp_column_privileges_rowset_rmt 'Caption';
Copy

Recommendations

SSC-EWI-TS0055

This Early Warning Indicator (EWI) is no longer in use. Please see the updated information in SSC-FDM-TS0020.

Severity

Medium

Description

This warning appears when a default constraint is found within an Alter Table statement.

Currently, this constraint type is not supported. However, there is a workaround available: if the table is defined before the Alter Table statement, we can identify the references and combine the default constraint with the table definition. If this is not possible, the constraint will be commented out in the converted code.

Code Example

Input Code:

CREATE TABLE table1(
  col1 integer,
  col2 varchar collate Latin1_General_CS,
  col3 date
);

ALTER TABLE table1
ADD col4 integer,
  CONSTRAINT col1_constraint DEFAULT 50 FOR col1,
  CONSTRAINT col1_constraint DEFAULT 30 FOR col1;
Copy

Output Code:

CREATE OR REPLACE TABLE table1 (
  col1 INTEGER DEFAULT 50,
  col2 VARCHAR COLLATE 'EN-CS',
  col3 DATE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

ALTER TABLE table1
ADD col4 INTEGER,
  CONSTRAINT col1_constraint
                             !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                             DEFAULT 50 FOR col1,
  CONSTRAINT col1_constraint
                             !!!RESOLVE EWI!!! /*** SSC-EWI-TS0055 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION ***/!!!
                             DEFAULT 30 FOR col1;
Copy

If all statements within an Alter Table command are invalid, the entire Alter Table command will be commented out in the converted code.

Known Issues

When multiple default constraints are defined for the same column, only the first constraint will be included in the Create Table Statement.

Recommendations

SSC-EWI-TS0061

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to understand.

Description

This warning appears when an unsupported ALTER COLUMN statement is detected.

Code Example

Input Code:

ALTER TABLE SampleTable
ALTER COLUMN SampleColumn INT NULL SPARSE;
Copy

Output Code:

ALTER TABLE IF EXISTS SampleTable
ALTER COLUMN SampleColumn
                          !!!RESOLVE EWI!!! /*** SSC-EWI-TS0061 - ALTER COLUMN COMMENTED OUT BECAUSE SPARSE COLUMN IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                          INT NULL SPARSE;
Copy

Recommendations

SSC-EWI-TS0045

Severity

Low

Parts of the code output have been removed to keep the example clear and concise.

Description

This Early Warning Issue (EWI) is generated for all label names that are referenced by GOTO statements in SQL Server.

Code Example

Input Code:

CREATE PROCEDURE GoToProcedure
AS
BEGIN
DECLARE @TotalMaarks INT
SET @TotalMaarks = 49
IF @TotalMaarks >= 50
    GOTO Pass
IF @TotalMaarks < 50
    GOTO Fail
Pass:
    SELECT 1;
    SELECT * FROM TABLE1;
    RETURN 1;
Fail:
    SELECT 2;
    SELECT * FROM TABLE2;
    RETURN 2;
END
Copy

Output Code:

CREATE OR REPLACE PROCEDURE GoToProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        TOTALMAARKS INT;
    BEGIN

        TOTALMAARKS := 49;
        IF (:TOTALMAARKS >= 50) THEN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GOTO' NODE ***/!!!
            GOTO Pass
        END IF;
        IF (:TOTALMAARKS < 50) THEN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GOTO' NODE ***/!!!
            GOTO Fail
        END IF;
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0045 - LABELED STATEMENT IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        Pass:
        SELECT 1;
    SELECT
            *
        FROM
            TABLE1;
        RETURN 1;

        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0045 - LABELED STATEMENT IS NOT SUPPORTED IN SNOWFLAKE SCRIPTING ***/!!!
        Fail:
        SELECT 2;
    SELECT
            *
        FROM
            TABLE2;
        RETURN 2;

    END;
$$;
Copy

Recommendations

Related EWI

  1. SSC-EWI-0073: This code requires a review to ensure functional equivalence.

SSC-EWI-TS0009

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity

High

Description

This error indicates the presence of nested transactions, which occur when a new transaction is started within an existing transaction in SQL Server. When an additional BEGIN statement is executed after the first one, a new transaction is created and the transaction counter increases by one.

Snowflake does not support nested transactions. When you use a second BEGIN statement, it will be ignored, and only one transaction will be maintained. For more details about SQL Server transaction behavior, please see SQL Server Transactions.

Code Example

Input Code:

CREATE PROC transactionsTest
AS
BEGIN TRANSACTION
   SELECT @@TRANCOUNT AS TransactionCount_AfterFirstTransaction
   INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (1), (2)
   BEGIN TRANSACTION
      SELECT @@TRANCOUNT AS TransactionCount_AfterSecondTransaction
      INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (3), (4)
   COMMIT;
   SELECT @@TRANCOUNT AS TransactionCount_AfterFirstCommit
COMMIT;
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE transactionsTest ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      ProcedureResultSet1 VARCHAR;
      ProcedureResultSet2 VARCHAR;
      ProcedureResultSet3 VARCHAR;
      return_arr ARRAY := array_construct();
   BEGIN
      !!!RESOLVE EWI!!! /*** SSC-EWI-TS0009 - THE FOLLOWING TRANSACTION MAY CONTAIN NESTED TRANSACTIONS WHICH ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      BEGIN TRANSACTION;
      ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
      CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
         SELECT
            :TRANCOUNT AS TransactionCount_AfterFirstTransaction;
      return_arr := array_append(return_arr, :ProcedureResultSet1);
      INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (1), (2);
      BEGIN TRANSACTION;
      ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
      CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
         SELECT
            :TRANCOUNT AS TransactionCount_AfterSecondTransaction;
      return_arr := array_append(return_arr, :ProcedureResultSet2);
      INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (3), (4);
      COMMIT;
      ProcedureResultSet3 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
      CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet3) AS
         SELECT
            :TRANCOUNT AS TransactionCount_AfterFirstCommit;
      return_arr := array_append(return_arr, :ProcedureResultSet3);
      COMMIT;
      --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
      RETURN return_arr;
   END;
$$;
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '12' COLUMN '1' OF THE SOURCE CODE STARTING AT 'END'. EXPECTED 'BATCH' GRAMMAR. CODE '80'. **
--END

!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;
Copy

Recommendations

  • Snowflake will ignore nested transactions instead of generating compilation errors. You can review the assessment reports to identify any nested transactions in your code.

  • For additional support, please contact us at snowconvert-support@snowflake.com

Related EWI

  1. SSC-FDM-0020: Results from multiple queries are stored in temporary tables

  2. SSC-EWI-0001: Unknown or invalid token found in the source code line

  3. SSC-EWI-0040: The statement is not supported

SSC-EWI-TS0039

Severity

Medium

Description

This warning appears when multiple SET statements are found for the same cursor. In Snowflake Scripting, you can only have one SET statement per cursor. Any additional SET statements for the same cursor will be automatically commented out during conversion.

Example Code:

Input Code:

CREATE OR ALTER PROCEDURE multipleSetExample
AS
BEGIN
    DECLARE @MyCursor CURSOR;
    DECLARE @MyCursor2 CURSOR STATIC READ_ONLY
	FOR
	SELECT FirstName
	FROM vEmployee;
    DECLARE @MyCursor3 CURSOR;

    SET @MyCursor = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;

    SET @MyCursor3 = CURSOR STATIC READ_ONLY
    FOR
    SELECT *
    FROM someTable;

    SET @MyCursor = CURSOR DYNAMIC
        FOR
        SELECT col2
        FROM exampleTable;

    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;

    RETURN 'DONE';
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE multipleSetExample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		MYCURSOR CURSOR
		FOR
			SELECT col3
			FROM defaultTable;
		MYCURSOR2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		MYCURSOR3 CURSOR
		FOR
			SELECT *
			FROM someTable;
	BEGIN





		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

		SET @MyCursor = CURSOR DYNAMIC
		    FOR
		    SELECT col2
		    FROM exampleTable;
		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR2 SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
		RETURN 'DONE';
	END;
$$;
Copy

Recommendations

SSC-EWI-TS0078

Severity

Medium

Description

This error occurs when expressions such as function calls, variable names, or named constants are placed after the default case in a switch statement.

Snowflake accepts only explicit constant values, such as numbers or strings, as input.

Code Example

Input Code:

ALTER TABLE
    T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER DEFAULT RANDOM(10);
Copy

Output Code:

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "T_ALTERTABLETEST", "RANDOM" **
ALTER TABLE IF EXISTS T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER
                         !!!RESOLVE EWI!!! /*** SSC-EWI-TS0078 - DEFAULT OPTION NOT ALLOWED IN SNOWFLAKE ***/!!!
                         DEFAULT RANDOM(10);
Copy

Recommendations

SSC-EWI-TS0079

Severity

Medium

Description

This warning appears when SnowConvert detects a DBCC (Database Console Command) statement in your source code. DBCC statements are database maintenance commands that are typically not compatible with Snowflake’s architecture.

Code Example

Input Code:

DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0079 - DATABASE CONSOLE COMMAND 'CHECKIDENT' IS NOT SUPPORTED. ***/!!!
DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS;
Copy

Recommendations

SSC-EWI-TS0049

Parts of the code have been removed to make it easier to understand.

Severity

Medium

Description

Most IF statements that contain a Begin ... End block are supported and will execute successfully without generating the SSC-EWI-TS0049 error.

Code Example

Input Code:

CREATE OR ALTER FUNCTION [PURCHASING].[FOO](@status INT)
Returns INT
As
Begin
    declare @result as int = 10;
    SELECT @result = quantity FROM TABLE1 WHERE COL1 = @status;
    IF @result = 3
    BEGIN
        IF @result>0 SELECT @result=0  ELSE SELECT @result=1
        SELECT @result = 1
    END
    return @result;
End
Copy

Output Code:

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO (STATUS INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        RESULT INT := 10;
    BEGIN

        SELECT
            quantity
        INTO
            :RESULT
        FROM
            TABLE1
        WHERE
            COL1 = :STATUS;
        IF (:RESULT = 3) THEN
            BEGIN
                IF (:RESULT >0) THEN SELECT
                        0
                    INTO
                        :RESULT;
                ELSE
                    SELECT
                        1
                    INTO
                        :RESULT;
                END IF;
        SELECT
                    1
                INTO
                    :RESULT;
            END;
        END IF;
        RETURN :RESULT;
    END;
$$;
Copy

When using a basic code example (like the one shown above), the conversion works correctly. However, in certain edge cases, the “IF” statement does not convert properly, which results in an EWI (Error, Warning, or Information) message being generated.

Manual Support

Case 1: Single Statement

In these scenarios, the transformation is simple because the converted statement will be part of a SELECT clause.

IF @result = 0
BEGIN
    SET @result =1
END
Copy
CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
( SELECT 1 AS RESULT )
Copy

Case 2: Multiple Statements

When transforming multiple SQL statements, convert them sequentially. Start with the first statement (N), and use its output as the source table for the next statement (N+1).

IF @result = 0
BEGIN
    Statement1
    Statement2
    Statement3
END
Copy
CASE WHEN (SELECT RESULT FROM CTE2)= 0 THEN
(
    SELECT TransformedStatement3
    FROM (
        SELECT TransformedStatement2
        FROM (
            SELECT TransformedStatement1
        ) T1
    ) T2
)
Copy

Case 3: Multiple set statements

In these situations, you will need to create a separate transformation for each SET statement.

IF @result = 0
BEGIN
    SET @var1 = 1
    SET @var2 = 3
    SET @var3 = @var2
END
Copy
WITH CTE1 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0) = 0 THEN
        (SELECT 1) AS VAR1)
WITH CTE2 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT 3) AS VAR2)
WITH CTE3 AS (
    SELECT
        CASE WHEN (SELECT
                        RESULT
                    FROM
                        CTE0)= 0 THEN
        (SELECT T1.VAR2
        FROM ((SELECT 3) AS VAR2) AS T1) AS VAR3)
...
Copy

Recommendations

SSC-EWI-TS0037

Severity

Medium

Parts of the code have been removed to make the example clearer and easier to follow.

Description

Cursors in Snowflake Scripting can only move forward through the result set (non-scrollable). You can only use FETCH NEXT to retrieve records one at a time.

Code Example

Input Code:

CREATE OR ALTER PROCEDURE scrollablecursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0037
    DECLARE CursorVar CURSOR SCROLL STATIC READ_ONLY
	FOR
	SELECT FirstName
	FROM vEmployee;
    -- Should not be marked
    DECLARE CursorVar2 CURSOR STATIC READ_ONLY
	FOR
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar3 CURSOR FORWARD_ONLY STATIC READ_ONLY
	FOR
	SELECT FirstName
	FROM vEmployee;
    RETURN 'DONE';
END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE scrollablecursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		-- Should be marked with SSC-EWI-TS0037
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0037 - SNOWFLAKE SCRIPTING CURSORS ARE NON-SCROLLABLE, ONLY FETCH NEXT IS SUPPORTED ***/!!!
		CursorVar CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		-- Should not be marked
		CursorVar2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		CursorVar3 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
	BEGIN



		RETURN 'DONE';
	END;
$$;
Copy

Recommendations

SSC-EWI-TS0013

This warning is no longer valid. Please see SSC-FDM-TS0013 for current documentation.

Severity

Low

Description

This warning appears when converting a SQL Server computed column to Snowflake. It alerts you that the functionality might not be exactly the same after the conversion.

Code Example

Input Code:

CREATE TABLE [TestTable](
    [Col1] AS (CONVERT ([REAL], ExpressionValue))
);
Copy

Output Code:

CREATE OR REPLACE TABLE TestTable (
    Col1 REAL AS (CAST(ExpressionValue AS REAL)) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Recommendations

  • This message is for your information only. No action is needed.

  • Please add any manual modifications to expressions that were not automatically transformed.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TS0076

This warning message is no longer in use. Please see SSC-EWI-0002 for current documentation.

Severity

Medium

Description

Default parameters must be placed at the end of the parameter list in Snowflake stored procedures and functions. Any parameters with default values should be moved to the end of the parameter declaration sequence.

Example Code

Input Code:

CREATE PROCEDURE MySampleProc
    @Param1 NVARCHAR(50) = NULL,
    @Param2 NVARCHAR(10),
    @Param3 NVARCHAR(10) = NULL,
    @Param4 NVARCHAR(10)
AS
    SELECT 1;
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0076 - DEFAULT PARAMETERS MAY NEED TO BE REORDERED. SNOWFLAKE ONLY SUPPORTS DEFAULT PARAMETERS AT THE END OF THE PARAMETERS DECLARATIONS. ***/!!!
CREATE OR REPLACE PROCEDURE MySampleProc (PARAM1 STRING DEFAULT NULL, PARAM2 STRING, PARAM3 STRING DEFAULT NULL, PARAM4 STRING)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ProcedureResultSet RESULTSET;
    BEGIN
        ProcedureResultSet := (
        SELECT 1);
        RETURN TABLE(ProcedureResultSet);
    END;
$$;
Copy

Recommendations

SSC-EWI-TS0056

This warning is no longer valid. Please see SSC-FDM-TS0021 for current documentation.

Severity

Low

Parts of the code have been removed to make the example clearer and easier to understand.

Description

This Early Warning Indicator (EWI) appears when an Alter Table statement includes a MASKED WITH clause. It indicates that the system has created an approximate MASKING POLICY to replace the MASKED WITH function.

Code Example

Input Code:

ALTER TABLE table_name
ALTER COLUMN column_name
ADD MASKED WITH (FUNCTION = 'default()');
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0057 - MASKING ROLE MUST BE DEFINED PREVIOUSLY BY THE USER ***/!!!
CREATE OR REPLACE MASKING POLICY "default" AS
(val STRING)
RETURNS STRING ->
CASE
WHEN current_role() IN ('YOUR_DEFINED_ROLE_HERE')
THEN val
ELSE 'xxxxx'
END;

ALTER TABLE IF EXISTS table_name MODIFY COLUMN column_name!!!RESOLVE EWI!!! /*** SSC-EWI-TS0056 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/!!!  SET MASKING POLICY "default";
Copy

A MASKING POLICY must be created before executing the ALTER TABLE statement. Please note that the policy’s behavior may be approximate and might require adjustments to roles and user permissions.

Recommendations

SSC-EWI-TS0023

Severity

Low

Parts of the code output have been removed to keep the example clear and concise.

Description

This warning appears when a BULK INSERT option cannot be converted. The corresponding options should be specified as FILE FORMAT options in Snowflake.

Code Example

Input Code:

BULK INSERT #PCE FROM 'E:\PCE_Look-up_table.txt'
WITH
(
   FIELDTERMINATOR ='\t',
   ROWTERMINATOR ='\n',
   FIRE_TRIGGERS
);
Copy

Output Code:

CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461199649565070
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n'
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0023 - 'FIRE_TRIGGERS' BULK OPTION COULD NOT BE TRANSFORMED TO ANY OF THE EXISTING FILE FORMAT OPTIONS ***/!!!
FIRE_TRIGGERS;

CREATE OR REPLACE STAGE STAGE_638461199649565070
FILE_FORMAT = FILE_FORMAT_638461199649565070;

--** SSC-FDM-TS0004 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI **
PUT file://E:\PCE_Look-up_table.txt @STAGE_638461199649565070 AUTO_COMPRESS = FALSE;

COPY INTO T_PCE FROM @STAGE_638461199649565070/PCE_Look-up_table.txt;
Copy

Recommendations

Related EWI

  1. SSC-FDM-TS0004: The PUT statement cannot be executed through the user interface.

SSC-EWI-TS0072

Severity

Low

Parts of the output code have been removed to make the example clearer and easier to follow.

Description

This warning appears when both SELECT statements and OUTPUT parameters need to be returned. In such cases, the results from SELECT statements take precedence over OUTPUT parameters.

Input Code:

CREATE PROCEDURE SOMEPROC(@product_count INT OUTPUT,  @123 INT OUTPUT)
AS
BEGIN
		SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Employee;
END
Copy

Output Code:

CREATE OR REPLACE PROCEDURE SOMEPROC (PRODUCT_COUNT INT, _123 INT)
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		ProcedureResultSet1 VARCHAR;
		ProcedureResultSet2 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
			SELECT
				*
			from
				AdventureWorks.HumanResources.Department;
		return_arr := array_append(return_arr, :ProcedureResultSet1);
		ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
			SELECT
				*
			from
				AdventureWorks.HumanResources.Employee;
		return_arr := array_append(return_arr, :ProcedureResultSet2);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0072 - RETURN statement will be ignored due to previous RETURN statement ***/!!!
		RETURN OBJECT_CONSTRUCT('PRODUCT_COUNT', :PRODUCT_COUNT, '_123', :_123);
	END;
$$;
Copy

Recommendations

Related EWI

  1. SSC-FDM-0020: When multiple result sets are returned, they will be stored in temporary tables.

SSC-EWI-TS0046

Severity

Medium

Description

This warning appears when the code references SQL Server system tables that either don’t have a Snowflake SQL equivalent or are not supported in Snowflake.

Code Example

Input Code:

SELECT *
FROM
    sys.all_sql_modules
WHERE
    [STATE] = 0; -- state must be ONLINE
Copy

Output Code:

SELECT
    *
FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0046 - SYSTEM TABLE sys.all_sql_modules IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
    sys.all_sql_modules
WHERE
    STATE = 0; -- state must be ONLINE
Copy

Recommendations

SSC-EWI-TS0017

Severity

Low

Description

This warning appears when SnowConvert detects a masked column within a CREATE TABLE statement. In Snowflake, column masking cannot be implemented by simply adding an option in the column definition. To achieve the same data masking functionality as SQL Server, you will need to manually implement the masking logic.

Code Example

Input Code:

CREATE TABLE TABLE1
(
  [COL1] nvarchar MASKED WITH (FUNCTION = 'default()') NULL,
  [COL2] varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  [COL3] varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
  [COL4] smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
);
Copy

Output Code:

CREATE OR REPLACE TABLE TABLE1
(
  COL1 VARCHAR
               !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
               MASKED WITH (FUNCTION = 'default()') NULL,
  COL2 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
  COL3 VARCHAR(100)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'email()') NOT NULL,
  COL4 SMALLINT
                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
                MASKED WITH (FUNCTION = 'random(1, 100)') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
Copy

Recommendations

SnowConvert currently does not support the automatic generation of MASKING POLICIES. You will need to create these policies manually.

First, create a role that will be responsible for managing masking policies.

create role masking_admin;
Copy

Next, grant the required privileges to the newly created role.

grant create masking policy on schema PUBLIC to role masking_admin;
allow table_owner role to set or unset the ssn_mask masking policy -- (optional)
grant apply on masking policy ssn_mask to role table_owner;
Copy

Create the masking policy functions in the next step.

-- default mask
create or replace masking policy default_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else 'xxxx'
end;

-- partial mask
create or replace masking policy partial_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else LEFT(val,1) || 'xxxxx' || RIGHT(val,1)
end;

-- email mask
create or replace masking policy email_mask as (val string) returns string ->
case
when current_role() in ('ANALYST') then val
else LEFT(val,1) || 'XXX@XXX.com'
end;

-- random mask
create or replace masking policy random_mask as (val smallint) returns smallint ->
case
when current_role() in ('ANALYST') then val
else UNIFORM(1,100,RANDOM())::SMALLINT
end;
Copy

To demonstrate masking functions, we will show examples of SQL Server masking functions and their equivalent implementations in Snowflake.

Apply the masking policy to the column that previously had masking enabled in SQL Server.

alter table if exists TABLE1 modify column COL1 set masking policy default_mask;
alter table if exists TABLE1 modify column COL2 set masking policy partial_mask;
alter table if exists TABLE1 modify column COL3 set masking policy email_mask;
alter table if exists TABLE1 modify column COL4 set masking policy random_mask;
Copy

If you need additional assistance, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TS0057

This Early Warning Indicator (EWI) has been deprecated. Please refer to the SSC-FDM-TS0022 documentation for current information.

Severity

Low

Parts of the code have been removed to make the example clearer and easier to understand.

Description

This error occurs when a MASKING POLICY requires a role or privilege assignment to function correctly. Without proper permissions, the data masking will not work.

Code Example

Input code

ALTER TABLE tableName
ALTER COLUMN columnName
ADD MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)');
Copy

Output code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0057 - MASKING ROLE MUST BE DEFINED PREVIOUSLY BY THE USER ***/!!!
CREATE OR REPLACE MASKING POLICY "partial_1_xxxxx_1" AS
(val STRING)
RETURNS STRING ->
CASE
WHEN current_role() IN ('YOUR_DEFINED_ROLE_HERE')
THEN val
ELSE LEFT(val, 1) || 'xxxxx' || RIGHT(val, 1)
END;

ALTER TABLE IF EXISTS tableName MODIFY COLUMN columnName!!!RESOLVE EWI!!! /*** SSC-EWI-TS0056 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/!!!  SET MASKING POLICY "partial_1_xxxxx_1";
Copy

As shown in line 6, you can add one or more role values in the placeholder, separating them with commas. Remember that each role value must be enclosed in single quotes.

Recommendations

SSC-EWI-TS0063

Severity

Critical

Description

This warning appears when the code contains time zones that Snowflake does not support.

Code Example

Input Code:

SELECT current_timestamp at time zone 'Turks And Caicos Standard Time';
Copy

Output Code:

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0063 - TIME ZONE NOT SUPPORTED IN SNOWFLAKE ***/!!!
CURRENT_TIMESTAMP() at time zone 'Turks And Caicos Standard Time'
                                                                 ;
Copy

Recommendations

SSC-EWI-TS0032

The External Web Interface (EWI) is generated exclusively when translating Stored Procedures to JavaScript. However, this feature is no longer recommended, as Snowflake Scripting is now the preferred language for Stored Procedures.

Severity

High

Generate Procedures and Macros in JavaScript by adding either the flag -t JavaScript or --PLTargetLanguage JavaScript to your command.

Parts of the code output have been removed to make the example clearer.

Description

This warning appears when converting a concatenated literal containing a BULK INSERT statement. The conversion process translates BULK INSERT into a Snowflake PUT command. However, this PUT command cannot be executed when it originates from Dynamic SQL.

To handle BULK INSERT statements correctly, you must execute the PUT command separately from the procedure. If your procedure contains multiple BULK INSERT statements in Dynamic SQL, it’s recommended to:

  1. Split the procedure into smaller parts

  2. Manually execute the corresponding PUT command for each BULK INSERT statement

This approach ensures proper file loading and data insertion.

Code Example

Input Code:

-- Additional Params: -t JavaScript
CREATE PROCEDURE  [dbo].[Load_FuelMgtMasterData]
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE
            @SQLString VARCHAR(500)
        ,   @ImportName VARCHAR(200)
        ,   @Today DATE
        ,   @Yesterday DATE
        ,   @SourceAffiliates VARCHAR(200);

        SET @Today = GETDATE();
        SET @Yesterday = DATEADD(DAY, -1, @Today);
        TRUNCATE TABLE dbo.SourceFM_Affiliates;
        SET @ImportName = '\\' + +@@ServerName
            + '\WorkA\merchantportal\affiliates.txt';
        SET @SQLString = 'BULK INSERT ' + @SourceAffiliates + ' FROM '''
            + @ImportName + '''';
        EXEC (@SQLString);
    END;
Copy

Output Code:

CREATE OR REPLACE PROCEDURE dbo.Load_FuelMgtMasterData ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
    /*        SET NOCOUNT ON*/
    ;
    let SQLSTRING;
    let IMPORTNAME;
    let TODAY;
    let YESTERDAY;
    let SOURCEAFFILIATES;
    TODAY = SELECT(`   CURRENT_TIMESTAMP() :: TIMESTAMP`);
    YESTERDAY = SELECT(`   DATEADD(DAY, -1, ?)`,[TODAY]);
    EXEC(`TRUNCATE TABLE dbo.SourceFM_Affiliates`);
    IMPORTNAME = `\\` + SERVERNAME + `\WorkA\merchantportal\affiliates.txt`;
    SQLSTRING =
        // ** SSC-EWI-TS0032 - THE BULK INSERT WAS PART OF A DYNAMIC SQL, WHICH MAKES SOME OF THE TRANSLATED ELEMENTS INVALID UNLESS EXECUTED OUTSIDE DYNAMIC CODE. **
        `CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638461213351333410;

CREATE OR REPLACE STAGE STAGE_638461213351333410
FILE_FORMAT = FILE_FORMAT_638461213351333410;

PUT file://${IMPORTNAME} @STAGE_638461213351333410 AUTO_COMPRESS = FALSE;

COPY INTO ${SOURCEAFFILIATES} FROM @STAGE_638461213351333410/${IMPORTNAME}`;
    EXEC(`${SQLSTRING}`);
$$;
Copy

Recommendations

  • Extract and execute the PUT command generated by the Dynamic BULK INSERT statement before running the procedure.

  • For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TS0073

This Early Warning Issue is no longer supported. Please see SSC-FDM-TS0023 for current documentation.

Severity

Low

Description

This warning appears during the transformation of the ERROR_MESSAGE() function. Please note that the exact error message may differ in Snowflake.

Input Code:

SET @varErrorMessage = ERROR_MESSAGE()
Copy

Output Code

BEGIN
VARERRORMESSAGE := SQLERRM !!!RESOLVE EWI!!! /*** SSC-EWI-TS0073 - ERROR MESSAGE COULD BE DIFFERENT IN SNOWFLAKE ***/!!!;
END;
Copy

Recommendation

If you need assistance, please contact our support team at snowconvert-support@snowflake.com.

SSC-EWI-TS0080

Severity

High

Description

SQL Server’s EXECUTE AS command allows users to temporarily switch their execution context. This change affects both their execution privileges and the output of context-sensitive functions such as USER_NAME(). To return to the original context, users can use the REVERT command after completing their operations.

Procedures in Snowflake require an execution context, which can only be set during procedure creation or modification using either CREATE PROCEDURE or ALTER PROCEDURE statements. You cannot change this context while the procedure is running.

Code Example

Input Code:

CREATE PROCEDURE proc1()
WITH EXECUTE AS OWNER
AS
BEGIN
	SELECT USER_NAME();
	EXECUTE AS CALLER;
	SELECT USER_NAME();
	REVERT;
	SELECT USER_NAME();
END

GO
Copy

Output Code:

CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/05/2024" }}'
EXECUTE AS OWNER
AS
$$
	DECLARE
		ProcedureResultSet1 VARCHAR;
		ProcedureResultSet2 VARCHAR;
		ProcedureResultSet3 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet1);
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0080 - CHANGING THE EXECUTION CONTEXT AT RUNTIME IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
	EXECUTE AS CALLER;
		ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet2);
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0080 - CHANGING THE EXECUTION CONTEXT AT RUNTIME IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
	REVERT;
		ProcedureResultSet3 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet3) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet3);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
	END;
$$;
Copy

Recommendations

SSC-EWI-TS0047

This warning is no longer valid. Please see SSC-FDM-TS0019 for current documentation.

Severity

Low

Description

This warning indicates that the RAISERROR Error Message might be different due to SQL Server’s string formatting rules.

Code Example

Input Code:

CREATE PROCEDURE RAISERROR_PROCEDURE
AS
BEGIN
RAISERROR ('This is a sample error message with the first parameter %d and the second parameter %*.*s',
           10,
           1,
           123,
	   7,
	   7,
	   'param2');
END
Copy

Output Code:

CREATE OR REPLACE PROCEDURE RAISERROR_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0047 - RAISERROR ERROR MESSAGE MAY DIFFER BECAUSE OF THE SQL SERVER STRING FORMAT ***/!!!
		SELECT
			RAISERROR_UDF('This is a sample error message with the first parameter %d and the second parameter %*.*s',
			10,
			1, array_construct(
			123,
7,
7,
'param2'));
	END;
$$;
Copy

Recommendations

SSC-EWI-TS0016

Parts of the code have been removed to make the example clearer and easier to follow.

Description

This warning appears when SnowConvert detects an ODBC Scalar function in your source code. Please note that ODBC Scalar functions cannot be converted to user-defined functions in Snowflake.

Code Example

Input Code:

SELECT {fn CURRENT_DATE_UDF()};
Copy

Output Code:

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CURRENT_DATE_UDF' NODE ***/!!!
CURRENT_DATE_UDF() !!!RESOLVE EWI!!! /*** SSC-EWI-TS0016 - USER DEFINED FUNCTIONS ARE NOT SUPPORTED IN ODBC SCALAR FUNCTION. ***/!!!;
Copy

Related EWI

  1. SSC-EWI-0073: A functional equivalence review is required.

Recommendations

SSC-EWI-TS0036

Severity

Medium

Parts of the code have been omitted to keep the example clear and concise.

Description

This warning appears when the code contains Cursor types that are not Local Cursors. Since Snowflake Scripting currently only supports Local Cursors, all Cursor types will be automatically converted to Local Cursors during translation.

Code Example

Input Code:

CREATE OR ALTER PROCEDURE globalCursorTest
AS
BEGIN
    -- Should be marked with SSC-EWI-TS0036
    DECLARE MyCursor CURSOR GLOBAL STATIC READ_ONLY
        FOR
        SELECT *
        FROM exampleTable;
    -- Should not be marked
    DECLARE MyCursor2 CURSOR LOCAL STATIC READ_ONLY
        FOR
        SELECT testCol
        FROM myTable;
    RETURN 'DONE';
END;
Copy
CREATE OR REPLACE PROCEDURE globalCursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        -- Should be marked with SSC-EWI-TS0036
        !!!RESOLVE EWI!!! /*** SSC-EWI-TS0036 - SNOWFLAKE SCRIPTING ONLY SUPPORTS LOCAL CURSORS ***/!!!
        MyCursor CURSOR
        FOR
            SELECT
                *
            FROM
                exampleTable;
        -- Should not be marked
        MyCursor2 CURSOR
        FOR
            SELECT
                testCol
            FROM
                myTable;
    BEGIN


        RETURN 'DONE';
    END;
$$;
Copy

Recommendations

SSC-EWI-TS0067

Severity

Critical

Parts of the code output have been removed to keep the example clear and concise.

Description

This warning appears when OPENXML contains invalid parameters, particularly when the specified XML path is inaccessible.

To resolve this Early Warning Indicator (EWI), you must provide the complete node path in the parameters.

Input Code:

SELECT
    *
FROM
    OPENXML (@idoc, @path, 1) WITH (
        CustomerID VARCHAR(10),
        ContactName VARCHAR(20)
    );
Copy

Output Code:

SELECT
    *
FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-TS0067 - INVALID PARAMETERS IN OPENXML TABLE-VALUED FUNCTION ***/!!!
    OPENXML(@idoc, @path, 1);
Copy

Input code (Explicit parameter)

SELECT
    *
FROM
    OPENXML (@idoc, '/ROOT/Customer', 1) WITH(
        CustomerID VARCHAR(10),
        ContactName VARCHAR(20)
    );
Copy

Output code (Explicit parameter)

SELECT
    Left(value:Customer['@CustomerID'], '10') AS 'CustomerID',
    Left(value:Customer['@ContactName'], '20') AS 'ContactName'
FROM
    OPENXML_UDF($idoc, ':ROOT:Customer');
Copy

Recommendations

SSC-EWI-TS0043

Severity

Medium

Parts of the code output have been removed to keep the example clear and concise.

Description

This warning appears for the WITH XMLNAMESPACES clause, which is not available in Snowflake SQL.

Code Example

Input Code:

WITH XMLNAMESPACES ('uri' as ns1)
SELECT ProductID as 'ns1:ProductID',
Name      as 'ns1:Name',
Color     as 'ns1:Color'
FROM Production.Product
WHERE ProductID = 316
FOR XML RAW, ELEMENTS XSINIL
Copy

Output Code:

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 XMLNAMESPACES ('uri' as VARIANT /*** SSC-FDM-TS0015 - DATA TYPE NS1 IS NOT SUPPORTED IN SNOWFLAKE ***/)
SELECT
ProductID AS "ns1:ProductID",
Name AS "ns1:Name",
Color AS "ns1:Color"
FROM
Production.Product
WHERE
ProductID = 316
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW, ELEMENTS XSINIL;
Copy

Recommendations

  • Consider using User-Defined Functions (UDFs) to replicate the functionality of your source code. Below you’ll find examples of UDFs that can help you recreate the original behavior:

CREATE  TABLE PRODUCT (ProductID INTEGER, Name VarChar(20), Color VarChar(20));
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT(PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

WITH XMLNAMESPACES ('uri' as ns1)
SELECT ProductID as 'ns1:ProductID',
       Name      as 'ns1:Name',
       Color     as 'ns1:Color'
FROM Product
FOR XML RAW
Copy
CREATE OR REPLACE TABLE PRODUCT (
       ProductID INTEGER,
       Name VARCHAR(20),
       Color VARCHAR(20))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/12/2024" }}'
;

INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(1,'UMBRELLA','RED');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(2,'SHORTS','BLUE');
INSERT INTO PRODUCT (PRODUCTID, NAME, COLOR) VALUES(3,'BALL','YELLOW');

--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **

WITH
     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0043 - WITH XMLNAMESPACES IS NOT SUPPORTED IN SNOWFLAKE ***/!!! XMLNAMESPACES ('uri' as ns1)
SELECT
       ProductID AS "ns1:ProductID",
       Name AS "ns1:Name",
       Color AS "ns1:Color"
FROM
       Product
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML RAW CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML RAW;
Copy

Related EWI

  1. SSC-PRF-TS0001: Performance Warning - The code contains a Common Table Expression (CTE) that might be recursive. Check if you need to add the ‘RECURSIVE’ keyword.

  2. SSC-EWI-TS0044: The ‘FOR XML’ clause cannot be used in Snowflake as it is not supported.

  3. SSC-FDM-TS0015: Please note that the Regexp_Substr Function in Snowflake only works with POSIX regular expressions.

SSC-EWI-TS0077

Severity

Low

Description

This error appears when you use a collation clause that Snowflake does not support.

Code example

Input Code:

SELECT 'a' COLLATE Albanian_BIN;

SELECT 'a' COLLATE Albanian_CI_AI;

CREATE TABLE ExampleTable (
    ID INT,
    Name VARCHAR(50) COLLATE collateName
);
Copy

Output Code:

SELECT 'a'
--           !!!RESOLVE EWI!!! /*** SSC-EWI-TS0077 - COLLATION Albanian_BIN NOT SUPPORTED ***/!!!
-- COLLATE Albanian_BIN
                     ;

SELECT 'a'
--           !!!RESOLVE EWI!!! /*** SSC-EWI-TS0077 - COLLATION Albanian_CI_AI NOT SUPPORTED ***/!!!
-- COLLATE Albanian_CI_AI
                       ;

CREATE OR REPLACE TABLE ExampleTable (
    ID INT,
    Name VARCHAR(50)
--                     !!!RESOLVE EWI!!! /*** SSC-EWI-TS0077 - COLLATION collateName NOT SUPPORTED ***/!!!
-- COLLATE collateName
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Recommendations

SSC-EWI-TS0026

Severity

Low

Description

This warning appears when a Common Table Expression (CTE) containing a DELETE FROM statement is converted into a CREATE OR REPLACE TABLE statement.

Code Example

Input Code:

WITH Duplicated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RN
FROM WithQueryTest
)
DELETE FROM Duplicated
WHERE Duplicated.RN > 1
Copy

Output Code:

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0026 - WITH DELETE QUERY TURNED TO CREATE TABLE ***/!!!
CREATE OR REPLACE TABLE WithQueryTest AS
SELECT
*
FROM
WithQueryTest
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY
ID
ORDER BY ID) = 1;
Copy

Recommendations