MSCEWI4033

PUT not supported inside dynamic SQL

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

Severity

High

Description

This EWI is added to a PUT command that was part of the result of a BULK INSERT transformation, but that BULK INSERT was part of a Dynamic SQL statement.

This PUT command is not supported in this context, and has to be manually executed outside of the procedure that contains the BULK INSERT statement.

Multiple BULK INSERT statements inside the same procedure would require a manual execution of their respective PUT commands, outside of the procedure.

"EnumName": "PutNotSupportedInsideDynamicSql", "FriendlyName": "PUT not supported inside dynamic SQL", "Description": "PUT IS NOT SUPPORTED INSIDE DYNAMIC SQL. EXECUTE OUTSIDE OF CONTAINING PROCEDURE WHERE APPLICABLE.", "Severity": "High", "Code": "MSCEWI4033"

Code Example

Input Code:

CREATE PROCEDURE  [dbo].[Demo_Proc]
AS
    BEGIN
        ...
        ...
        SET @a = 'BULK INSERT ' + @e + ' FROM '''
            + @b + '''';
        EXEC (@a);
    END;

Output Code:

CREATE OR REPLACE PROCEDURE dbo.Demo_Proc ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
...
...
   A = // ** MSC-ERROR - MSCEWI4032 - 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_637775071321116498;

CREATE OR REPLACE STAGE STAGE_637775071321116498
FILE_FORMAT = FILE_FORMAT_637775071321116498;

/*** MSC-ERROR - MSCEWI4033 - PUT IS NOT SUPPORTED INSIDE DYNAMIC SQL. EXECUTE OUTSIDE OF CONTAINING PROCEDURE WHERE APPLICABLE. ***/
PUT file://${B} @STAGE_637775071321116498 AUTO_COMPRESS = FALSE;

COPY INTO PUBLIC.${E} FROM @STAGE_637775071321116498/${B}`;
   EXEC(`${A}`);
$$;

Recommendations

• PUT command has to be manually executed outside of the procedure that contains it.

• If you need more support, you can email us at [email protected]