SnowConvert: Transact-SQL Functional Differences¶

Supported Environments
  • [x] Microsoft SQL Server

  • [x] Microsoft Azure Synapse Analytics

SSC-FDM-TS0001¶

Note

This FDM has been deprecated. For more information, please see SSC-EWI-TS0077.

Description¶

This error appears when you attempt to 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
Generated Code:¶
 SELECT 'a'
--           --** SSC-FDM-TS0001 - COLLATION Albanian_BIN NOT SUPPORTED **
--           COLLATE Albanian_BIN
                               ;

SELECT 'a'
--           --** SSC-FDM-TS0001 - COLLATION Albanian_CI_AI NOT SUPPORTED **
--           COLLATE Albanian_CI_AI
                                 ;

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

Best Practices¶

SSC-FDM-TS0002¶

Description¶

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

Code Example¶

Input Code:¶
 SELECT 'a' COLLATE Latin1_General_CI_AS_WS;
Copy
Generated Code:¶
 SELECT 'a' COLLATE 'EN-CI-AS' /*** SSC-FDM-TS0002 - COLLATION FOR VALUE WS NOT SUPPORTED ***/;
Copy

Best Practices¶

SSC-FDM-TS0003¶

Description¶

This message appears when executing the XP_LOGININFO procedure, which returns a specific set of columns. For more details about these columns, you can refer to the SQL Server documentation.

account nametypeprivilegemapped login namepermission path

To understand the available roles, you can query the APPLICABLE_ROLES view in Snowflake. This view provides a set of columns with role information. For more details, please refer to the Snowflake documentation.

GRANTEE

ROLE_NAME

ROLE_OWNER

IS_GRANTABLE

SQL Server source columns are mapped to Snowflake columns as shown in the following table. Please note that these mappings may not be exact equivalents.

SQL ServerSnowFlake
account nameGRANTEE
typeROLE_OWNER
privilegeROLE_NAME
mapped login nameGRANTEE
permission pathNULL

Example code¶

Input code:¶
 EXEC xp_logininfo

EXEC xp_logininfo 'USERNAME'
Copy
Generated Code:¶
 --** SSC-FDM-TS0003 - XP_LOGININFO MAPPED TO CUSTOM UDF XP_LOGININFO_UDF AND MIGHT HAVE DIFFERENT BEHAVIOR **
SELECT
*
FROM
TABLE(XP_LOGININFO_UDF());

--** SSC-FDM-TS0003 - XP_LOGININFO MAPPED TO CUSTOM UDF XP_LOGININFO_UDF AND MIGHT HAVE DIFFERENT BEHAVIOR **
SELECT
*
FROM
TABLE(XP_LOGININFO_UDF('USERNAME'));
Copy

Best Practices¶

SSC-FDM-TS0004¶

Description¶

This message appears when a BULK INSERT operation has been converted and a PUT command is included in the output code. Since the PUT command cannot be executed through the SnowSQL Web UI, users must have the Snowflake Command Line Interface (SnowCLI) installed on their system before running this command.

Code Example¶

Input Code:¶
 BULK INSERT #temptable FROM 'path/to/file.txt'  
WITH
(
   FIELDTERMINATOR ='\t',  
   ROWTERMINATOR ='\n'
);
Copy
Generated Code:¶
 CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638466175888203490
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n';

CREATE OR REPLACE STAGE STAGE_638466175888203490
FILE_FORMAT = FILE_FORMAT_638466175888203490;

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

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "#temptable" **
COPY INTO T_temptable FROM @STAGE_638466175888203490/file.txt;
Copy

Best Practices¶

SSC-FDM-TS0005¶

Description¶

This Feature Migration Detail (FMD) is added when a TRY_CONVERT or TRY_CAST operation from the source database cannot be directly converted to a TRY_CAST function in Snowflake.

The Snowflake’s TRY_CAST function is limited to converting only string expressions, while SQL Server’s TRY_CONVERT and TRY_CAST functions are more flexible and can convert expressions of any data type.

The conversion to Snowflake’s TRY_CAST function is currently limited to string expressions or expressions that can be identified as strings based on their context.

Code Example¶

Input Code:¶
 SELECT TRY_CAST(14.85 AS INT);
SELECT TRY_CONVERT(VARCHAR, 1234);
SELECT TRY_CONVERT(CHAR, 1);
SELECT TRY_CONVERT(SQL_VARIANT, '2017-01-01 12:00:00');
SELECT TRY_CONVERT(GEOGRAPHY, 'LINESTRING(-122.360 47.656, -122.343 47.656 )');
Copy
Generated Code:¶
 SELECT
CAST(14.85 AS INT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/;
SELECT
TO_VARCHAR(1234);
SELECT
TO_CHAR(1);
SELECT
TO_VARIANT('2017-01-01 12:00:00');
SELECT
TO_GEOGRAPHY('LINESTRING(-122.360 47.656, -122.343 47.656 )');
Copy

Best Practices¶

SSC-FDM-TS0006¶

Description¶

This message appears when SnowConvert detects a procedure containing an EXECUTE AS 'user_name' clause. Since Snowflake does not support this feature, it is automatically converted to EXECUTE AS CALLER.

This parameter defines the security permissions that control how the procedure runs.

Note

For additional information about the clause functionality, refer to the documentation.

Code Example¶

Input Code:¶
 CREATE PROCEDURE SelectAllCustomers
WITH EXECUTE AS 'user_name'
AS
BEGIN
      SELECT * FROM Customers;
END;
Copy
Generated Code:¶
 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "Customers" **
CREATE OR REPLACE PROCEDURE SelectAllCustomers ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
--** SSC-FDM-TS0006 - EXECUTE AS 'user_name' CLAUSE DOES NOT EXIST IN SNOWFLAKE AND THE USER CALLING THE PROCEDURE SHOULD HAVE ALL THE REQUIRED PRIVILEGES **
AS
$$
      DECLARE
            ProcedureResultSet RESULTSET;
      BEGIN
            ProcedureResultSet := (
            SELECT
                  *
            FROM
                  Customers);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;
Copy

Best Practices¶

SSC-FDM-TS0007¶

Description¶

This message appears when SnowConvert detects a procedure containing a FOR REPLICATION clause. Since Snowflake does not support this feature, the clause is automatically removed during conversion.

This procedure is specifically designed for replication purposes and cannot be executed on the Subscriber side.

Note

For additional information about this clause’s functionality, please refer to the documentation.

Code Example¶

Input Code:¶
 CREATE PROCEDURE SelectAllCustomers
WITH FOR REPLICATION
AS
BEGIN
      SELECT * FROM Customers;
END;
Copy
Generated Code:¶
 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "Customers" **
CREATE OR REPLACE PROCEDURE SelectAllCustomers ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
--** SSC-FDM-TS0007 - FOR REPLICATION CLAUSE DOES NOT EXIST IN SNOWFLAKE **
AS
$$
      DECLARE
            ProcedureResultSet RESULTSET;
      BEGIN
            ProcedureResultSet := (
            SELECT
                  *
            FROM
                  Customers);
            RETURN TABLE(ProcedureResultSet);
      END;
$$;
Copy

Best Practices¶

SSC-FDM-TS0008¶

Description¶

A warning appears when the FORMATMESSAGE function is detected because it has been replaced with FORMATMESSAGE_UDF. Please note that FORMATMESSAGE_UDF may not handle all format types correctly and could generate errors in certain situations.

Negative numerical values will keep their negative sign instead of being converted. The format specifier %I64d is not compatible with UDFs and will generate an error when used.

In the FORMATMESSAGE_UDF function, an error will occur if the number of provided arguments does not match the number of placeholders in the message template.

This User-Defined Function (UDF) cannot process message number identifiers.

Code Example¶

Input Code:¶
 SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50); -- Unsigned int 50, 4294967246
SELECT FORMATMESSAGE('Unsigned octal %o, %o', 50, -50); -- Unsigned octal 62, 37777777716
SELECT FORMATMESSAGE('Unsigned hexadecimal %X, %x', -11, -50); -- Unsigned hexadecimal FFFFFFF5, ffffffce
SELECT FORMATMESSAGE('Unsigned octal with prefix: %#o', -50); -- Unsigned octal with prefix: 037777777716
SELECT FORMATMESSAGE('Unsigned hexadecimal with prefix: %#X, %x', -11,-50); -- Unsigned hexadecimal with prefix: 0XFFFFFFF5, ffffffce
SELECT FORMATMESSAGE('Bigint %I64d', 3000000000); -- Bigint 3000000000
SELECT FORMATMESSAGE('My message: %s %s %s', 'Hello', 'World'); -- My message: Hello World (null) 
Copy
Generated Code:¶
 SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Unsigned int %u, %u', ARRAY_CONSTRUCT(50, -50)); -- Unsigned int 50, 4294967246
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Unsigned octal %o, %o', ARRAY_CONSTRUCT(50, -50)); -- Unsigned octal 62, 37777777716
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Unsigned hexadecimal %X, %x', ARRAY_CONSTRUCT(-11, -50)); -- Unsigned hexadecimal FFFFFFF5, ffffffce
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Unsigned octal with prefix: %#o', ARRAY_CONSTRUCT(-50)); -- Unsigned octal with prefix: 037777777716
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Unsigned hexadecimal with prefix: %#X, %x', ARRAY_CONSTRUCT(-11, -50)); -- Unsigned hexadecimal with prefix: 0XFFFFFFF5, ffffffce
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('Bigint %I64d', ARRAY_CONSTRUCT(3000000000)); -- Bigint 3000000000
SELECT
--** SSC-FDM-TS0008 - FORMATMESSAGE WAS CONVERTED TO CUSTOM UDF FORMATMESSAGE_UDF AND IT MIGHT HAVE A DIFFERENT BEHAVIOR. **
FORMATMESSAGE_UDF('My message: %s %s %s', ARRAY_CONSTRUCT('Hello', 'World')); -- My message: Hello World (null) 
Copy

Best Practices¶

  • Do not use the %I64d placeholder in messages.

  • Use a direct string message instead of a message ID as the first argument.

  • Ensure the number of placeholders matches the number of arguments that follow the message.

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

SSC-FDM-TS0009¶

Description¶

This warning appears when a column is defined with the ENCRYPTED WITH option. Since Snowflake does not support column-level encryption, this option will be removed during the conversion process and a warning message will be generated.

Code Example¶

Input Code:¶
 CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] NVARCHAR(60)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        )
);
Copy
Generated Code:¶
 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(60)
--    --** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
--           ENCRYPTED WITH (
--               COLUMN_ENCRYPTION_KEY = MyCEK,
--               ENCRYPTION_TYPE = RANDOMIZED,
--               ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
--           )
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
   ;
Copy

Best Practices¶

SSC-FDM-TS0010¶

Description¶

This warning appears when the SQL Server function DB_NAME is converted to Snowflake’s CURRENT_DATABASE function. Since Snowflake’s CURRENT_DATABASE function does not support database IDs as parameters and only returns the name of the current database, this conversion may affect your code’s behavior.

Code Example¶

Input Code:¶
 SELECT DB_NAME(someId);
Copy
Generated Code:¶
 SELECT
CURRENT_DATABASE() /*** SSC-FDM-TS0010 - CURRENT_DATABASE function has different behavior in certain cases ***/;
Copy

Best Practices¶

SSC-FDM-TS0011¶

Note

This FDM is no longer supported. For more information, please see SSC-EWI-TS0078.

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 direct inputs.

Code Example¶

Input Code:¶
 ALTER TABLE
    T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER DEFAULT RANDOM(10);
Copy
Generated Code:¶
 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "T_ALTERTABLETEST", "RANDOM" **
ALTER TABLE IF EXISTS T_ALTERTABLETEST
ADD
    COLUMN COL10 INTEGER
--                         --** SSC-FDM-TS0011 - DEFAULT OPTION NOT ALLOWED IN SNOWFLAKE **
--                         DEFAULT RANDOM(10)
                                           ;
Copy
#### Best Practices¶

SSC-FDM-TS0012¶

Description¶

This warning appears in Table-Valued User Defined Functions when the system cannot determine a column’s return type during conversion. In these cases, STRING is automatically used as the default type to match the CAST operation in the SELECT statement.

Code Example¶

Input Code:¶
 CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);
Copy
Generated Code:¶
 CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
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
$$
    SELECT
    CAST(DepartmentID AS STRING),
    CAST(Name AS STRING),
    CAST(GroupName AS STRING)
    FROM
    HumanResources.Department
$$;
Copy

Best Practices¶

  • Check the data type that could not be found and update it in the RETURNS TABLE statement.

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

SSC-FDM-TS0013¶

Description¶

This warning appears when the source code contains cursors that can be modified. In Snowflake Scripting, cursor rows are read-only and cannot be modified.

Example Code:¶

Input Code:¶
 CREATE OR ALTER PROCEDURE modifiablecursorTest
AS
BEGIN
    -- Should be marked with SSC-FDM-TS0013
    DECLARE CursorVar CURSOR
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar2 INSENSITIVE CURSOR
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar3 CURSOR KEYSET SCROLL_LOCKS
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar4 CURSOR DYNAMIC OPTIMISTIC
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar6 CURSOR STATIC
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE CursorVar7 CURSOR READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    -- Shouid not be marked
    DECLARE CursorVar5 CURSOR STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    RETURN 'DONE';
END;
Copy
Generated Code:¶
 CREATE OR REPLACE PROCEDURE modifiablecursorTest ()
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-FDM-TS0013
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar3 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar4 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar6 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE **
		CursorVar7 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		-- Shouid not be marked
		CursorVar5 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
	BEGIN
		RETURN 'DONE';
	END;
$$;
Copy

Best Practices¶

SSC-FDM-TS0014¶

Description¶

A warning appears when converting a SQL Server computed column to Snowflake. This warning indicates that the functionality might not be exactly the same in Snowflake as it was in SQL Server.

Code Example¶

Input Code:¶
 CREATE TABLE [TestTable](
    [Col1] AS (CONVERT ([REAL], ExpressionValue))
);
Copy
Generated 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

Best Practices¶

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

  • Make manual adjustments to any expressions that were not automatically transformed.

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

SSC-FDM-TS0015¶

Description¶

This warning appears when a SQL Server column’s data type is not supported in Snowflake.

Code Example¶

Input Code:¶
 CREATE TABLE table1
(
column1 type1
);
Copy
Generated Code:¶
 CREATE OR REPLACE TABLE table1
(
column1 VARIANT /*** SSC-FDM-TS0015 - DATA TYPE TYPE1 IS NOT SUPPORTED IN SNOWFLAKE ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Best Practices¶

SSC-FDM-TS0016¶

Description¶

This warning appears when converting a SQL Server FOR XML clause to Snowflake. It indicates that the column order in the resulting XML output might differ between the two platforms.

Code Example¶

Given a sample table named employee as shown below.

Id

Name

Hint

1

Kinslee Park

Developer

2

Ezra Mata

Developer

3

Aliana Quinn

Manager

Input Code:¶
 SELECT
  	e.id,
  	e.name as full_name,
  	e.hint
  FROM
  	employee e
  FOR XML PATH;
Copy
 <row>
    <id>1</id>
    <full_name>Kinslee Park</full_name>
    <hint>Developer</hint>
</row>
<row>
    <id>2</id>
    <full_name>Ezra Mata</full_name>
    <hint>Developer</hint>
</row>
<row>
    <id>3</id>
    <full_name>Aliana Quinn</full_name>
    <hint>Manager</hint>
</row>
Copy
Generated Code:¶
 SELECT
	--** SSC-FDM-TS0016 - XML COLUMNS IN SNOWFLAKE MIGHT HAVE A DIFFERENT FORMAT **
	FOR_XML_UDF(OBJECT_CONSTRUCT('id', e.id, 'full_name', e.name, 'hint', e.hint), 'row')
FROM
	employee e;
Copy
 <row type="OBJECT">
    <full_name type="VARCHAR">Kinslee Park</full_name>
    <hint type="VARCHAR">Developer</hint>
    <id type="INTEGER">1</id>
</row>
<row type="OBJECT">
    <full_name type="VARCHAR">Ezra Mata</full_name>
    <hint type="VARCHAR">Developer</hint>
    <id type="INTEGER">2</id>
</row>
<row type="OBJECT">
    <full_name type="VARCHAR">Aliana Quinn</full_name>
    <hint type="VARCHAR">Manager</hint>
    <id type="INTEGER">3</id>
</row>
Copy

Best Practices¶

SSC-FDM-TS0017¶

Description¶

This warning appears when functions such as SUSER_NAME or SUSER_SNAME include a user identifier parameter, as Snowflake’s CURRENT_USER function does not support user parameters.

Input Code:¶

 SELECT SUSER_NAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);
Copy
Generated Code:¶
 SELECT
CURRENT_USER() /*** SSC-FDM-TS0017 - User ID parameter used in SUSER_NAME function is not supported in CURRENT_USER function and it was removed. ***/;
Copy

Best Practices¶

SSC-FDM-TS0018¶

Note

This FDM is no longer supported. For more information, please see SSC-EWI-TS0079.

Description¶

This Feature Migration Detail (FMD) appears when SnowConvert detects a DBCC (Database Console Command) statement in the source code. Please note that Snowflake does not support most DBCC statements.

Code Example¶

Input Code:¶
 DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS
Copy
Generated Code:¶
 ----** SSC-FDM-TS0018 - DATABASE CONSOLE COMMAND 'CHECKIDENT' IS NOT SUPPORTED. **
--DBCC CHECKIDENT(@a, RESEED, @b) WITH NO_INFOMSGS
Copy

Recommendations¶

SSC-FDM-TS0019¶

Severity¶

Poor

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
Generated 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
		--** SSC-FDM-TS0019 - 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-FDM-TS0020¶

Description¶

This Feature Detection Marker (FDM) is triggered when a default constraint is found within an Alter Table statement.

Currently, this constraint type is not supported. To work around this limitation, you should define the table before using the Alter Table command. This approach helps SnowConvert identify the references, and the default constraint will be included in the table definition. If you don’t follow this approach, the constraint will only appear as a comment in the 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 (getdate()) 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;

----** SSC-FDM-TS0020 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION **

--ALTER TABLE table1
--ADD
--CONSTRAINT col1_constraint DEFAULT 50 FOR col1
                                              ;

----** SSC-FDM-TS0020 - DEFAULT CONSTRAINT MAY HAVE BEEN ADDED TO TABLE DEFINITION **

--ALTER TABLE table1
--ADD
--CONSTRAINT col1_constraint DEFAULT (CURRENT_TIMESTAMP() :: TIMESTAMP) FOR col1
                                                                              ;
Copy

Known Issues¶

  • If multiple default constraints are defined for the same column, only the first one will appear in the Create Table Statement.

  • If a default constraint references a column that doesn’t exist, the transformation will fail because the required column is missing.

Recommendations¶

SSC-FDM-TS0021¶

Severity¶

Poor

Note

For better readability, we have simplified some sections of the code in this example.

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:¶
 --** SSC-FDM-TS0022 - 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/*** SSC-FDM-TS0021 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/  SET MASKING POLICY "default";
Copy

Note

The MASKING POLICY must be created before executing the ALTER TABLE statement. Please note that the behavior may not be exactly the same as the source system, and you might need to adjust roles and user privileges accordingly.

Recommendations¶

SSC-FDM-TS0022¶

Severity¶

Poor

Note

For better readability, we have simplified some sections of the code in this example.

Description¶

This error occurs when a MASKING POLICY requires specific roles or privileges to be assigned for the data masking to function correctly.

Code Example¶

Input code¶
 ALTER TABLE tableName
ALTER COLUMN columnName
ADD MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)');
Copy
Generated Code:¶
 --** SSC-FDM-TS0022 - 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/*** SSC-FDM-TS0021 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/  SET MASKING POLICY "partial_1_xxxxx_1";
Copy

Note

On line 6, you can add one or more role values separated by commas. Each role value must be enclosed in single quotes.

Recommendations¶

SSC-FDM-TS0023¶

Severity¶

Poor

Description¶

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

Input Code:¶
 SET @varErrorMessage = ERROR_MESSAGE()
Copy
Output Code¶
 BEGIN
VARERRORMESSAGE := SQLERRM /*** SSC-FDM-TS0023 - ERROR MESSAGE COULD BE DIFFERENT IN SNOWFLAKE ***/;
END;
Copy

Recommendation¶

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

SSC-FDM-TS0024¶

Description¶

This Function Detection Mechanism (FDM) is triggered when At Time Zone is used with CURRENT_TIMESTAMP. This check is necessary because the results may vary in certain scenarios.

The key distinction between 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)

Input Code:¶

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

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

Output Code:¶
 SELECT
CONVERT_TIMEZONE('Pacific/Honolulu', CURRENT_TIMESTAMP() /*** SSC-FDM-TS0024 - 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-FDM-TS0025¶

Description¶

This Function Design Memo (FDM) explains how the DB_ID_UDF function aims to replicate the behavior of the DB_ID SQL Server function. While SQL Server assigns permanent, unique identifiers to databases that are never reused, Snowflake handles database IDs differently. In Snowflake, database IDs are sequential numbers assigned at creation time. If a database is deleted, its ID number becomes available and will be assigned to the next newly created database.

Input Code:¶

 SELECT DB_ID('my_database');
Copy

6

Output Code:¶
 SELECT
DB_ID_UDF('my_database') /*** SSC-FDM-TS0025 - DB_ID_UDF MAY HAVE A DIFFERENT BEHAVIOR IN CERTAIN CASES ***/;
Copy

6

Recommendations¶

SSC-FDM-TS0026¶

Description¶

When transforming an INSERT statement that includes a table DML factor, a temporary table must be created. Note that this Feature Detection Message (FDM) indicates that DELETE operations are not included in this transformation. For more details about this pattern, please refer to INSERT with Table DML Factor with MERGE as DML .

Input Code:¶

 INSERT INTO T3
SELECT
	col1,
  col2
FROM (
  MERGE T1 USING T2
  	ON T1.col1 = T2.col1
  WHEN NOT MATCHED THEN
    INSERT VALUES ( T2.col1, T2.col2 )
  WHEN MATCHED THEN
    UPDATE SET T1.col2 = t2.col2
  OUTPUT
  	$action ACTION_OUT,
    T2.col1,
    T2.col2
) AS MERGE_OUT
 WHERE ACTION_OUT='UPDATE';
Copy
Output Code:¶
 --** SSC-FDM-TS0026 - DELETE CASE IS NOT BEING CONSIDERED, PLEASE CHECK IF THE ORIGINAL MERGE PERFORMS IT **
CREATE OR REPLACE TEMPORARY TABLE MERGE_OUT AS
	SELECT
		CASE
			WHEN T1.$1 IS NULL
				THEN 'INSERT'
			ELSE 'UPDATE'
		END ACTION_OUT,
		T2.col1,
		T2.col2
	FROM
		T2
		LEFT JOIN
			T1
			ON T1.col1 = T2.col1;

MERGE INTO T1
USING T2
ON T1.col1 = T2.col1
WHEN NOT MATCHED THEN
	   INSERT VALUES (T2.col1, T2.col2)
WHEN MATCHED THEN
	UPDATE SET
		T1.col2 = t2.col2
		!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - OUTPUT CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
		OUTPUT
			$action ACTION_OUT,
		  T2.col1,
		  T2.col2 ;

		INSERT INTO T3
		SELECT
	col1,
	col2
		FROM
	MERGE_OUT
		WHERE
	ACTION_OUT ='UPDATE';
Copy

Recommendations¶

SSC-FDM-TS0027¶

Description¶

This Finding During Migration (FDM) warns that the SET ANSI_NULLS ON statement might work differently in Snowflake compared to the source database. For detailed information about this statement, please refer to the ANSI_NULLS documentation.

Input Code¶

 SET ANSI_NULLS ON;
Copy
Output Code¶
 ----** SSC-FDM-TS0027 - SET ANSI_NULLS ON STATEMENT MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE **
--SET ANSI_NULLS ON
Copy

Recommendations¶

SSC-FDM-TS0028¶

Description¶

This notification indicates that when using SP_EXECUTESQL, the output parameters must be listed in the same sequence as they appear in the SQL string being executed. If the order is different, the output values will be incorrectly assigned.

Code Example¶

Correct case¶

The order of @MaxAgeOUT and @MaxIdOUT parameters matches in both the SQL query string and the output parameters definition.

When converting the code, the statement SELECT $1, $2 INTO :MAXAGE, :MAXID FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) will store the values from columns $1 and $2 into the variables MAXAGE and MAXID respectively.

Transact¶
 CREATE PROCEDURE CORRECT_OUTPUT_PARAMS_ORDER
AS
BEGIN
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    EXECUTE sp_executesql
        N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE ID < @id AND AGE < @age;',
        N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT',
        30,
        100,
        @MaxAgeOUT = @MaxAge OUTPUT,
        @MaxIdOut = @MaxId OUTPUT;

    SELECT @MaxAge, @MaxId;
END
Copy
Snowflake¶
 CREATE OR REPLACE PROCEDURE CORRECT_OUTPUT_PARAMS_ORDER ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF('SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   ID < @id AND AGE < @age;', '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT', ARRAY_CONSTRUCT('', '', 'MAXAGEOUT', 'MAXIDOUT'), ARRAY_CONSTRUCT(
    30,
    100, :MAXAGE, :MAXID));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXAGE,
      :MAXID
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;
Copy

Common Issues¶

The order of output parameters @MaxAgeOUT and @MaxIdOUT in the stored procedure definition differs from their order in the SQL query string.

As a result, during code conversion, the statement SELECT $1, $2 INTO :MAXID, :MAXAGE FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) will assign values in an unexpected order. The value of Max(AGE) will be assigned to :MAXID, while Max(ID) will be assigned to :MAXAGE, which is the opposite of what’s intended.

This issue can be resolved by adjusting either the order of output parameters in the SELECT INTO statement or by modifying the order in the SQL string to match.

Transact¶
 CREATE PROCEDURE INCORRECT_OUTPUT_PARAMS_ORDER
AS
BEGIN
    DECLARE @MaxAge INT;
    DECLARE @MaxId INT;

    EXECUTE sp_executesql
        N'SELECT @MaxAgeOUT = max(AGE), @MaxIdOut = max(ID) FROM PERSONS WHERE ID < @id AND AGE < @age;',
        N'@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT',
        30,
        100,
        @MaxIdOut = @MaxId OUTPUT,
        @MaxAgeOUT = @MaxAge OUTPUT;

    SELECT @MaxAge, @MaxId;
END
Copy
Snowflake¶
 CREATE OR REPLACE PROCEDURE INCORRECT_OUTPUT_PARAMS_ORDER ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/07/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    MAXAGE INT;
    MAXID INT;
    ProcedureResultSet RESULTSET;
  BEGIN
     
     
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE TRANSFORM_SP_EXECUTE_SQL_STRING_UDF('SELECT
   MAX(AGE),
   MAX(ID) FROM
   PERSONS
WHERE
   ID < @id AND AGE < @age;', '@age INT, @id INT, @MaxAgeOUT INT OUTPUT, @MaxIdOUT INT OUTPUT', ARRAY_CONSTRUCT('', '', 'MAXIDOUT', 'MAXAGEOUT'), ARRAY_CONSTRUCT(
    30,
    100, :MAXID, :MAXAGE));
    --** SSC-FDM-TS0028 - OUTPUT PARAMETERS MUST HAVE THE SAME ORDER AS THEY APPEAR IN THE EXECUTED CODE **
    SELECT
      $1,
      $2
    INTO
      :MAXID,
      :MAXAGE
    FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    ProcedureResultSet := (
    SELECT
      :MAXAGE,
      :MAXID);
    RETURN TABLE(ProcedureResultSet);
  END;
$$;
Copy

Recommendations¶