MSCEWI3123

Database Link connections not supported

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

Severity

Medium

Description

A database link connection reference was removed from the object name because the database links and its references are not supported in Snowflake. The only part that is kept is the name before the @ character.

Example Code

Input Code:

-- Creation of the database link
CREATE DATABASE LINK mylink
    CONNECT TO user1 IDENTIFIED BY password1
    USING 'connection_str';

-- Statements that use the database link we created
SELECT * FROM employees@mylink;

INSERT INTO employees@mylink
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK');

UPDATE employees@mylink SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM employees@mylink
    WHERE employee_id = 999;

Output Code:

-- ** MSC-WARNING - MSCEWI3073 - CREATE DATABASE LINK NOT SUPPORTED [ DBLINK: mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] **
---- Creation of the database link
--CREATE DATABASE LINK mylink
--    CONNECT TO user1 IDENTIFIED BY password1
--    USING 'connection_str'

    -- Statements that use the database link we created
SELECT * FROM
    --** MSC-ERROR - MSCEWI3123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] **
    DATAWAREHOUSE.PUBLIC.employees;

INSERT INTO
--** MSC-ERROR - MSCEWI3123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] **
DATAWAREHOUSE.PUBLIC.employees
    (employee_id, last_name, email, hire_date, job_id)
    VALUES (999, 'Claus', '[email protected]', CURRENT_DATE, 'SH_CLERK');

UPDATE
--** MSC-ERROR - MSCEWI3123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] **
DATAWAREHOUSE.PUBLIC.employees
    SET min_salary = 3000
    WHERE job_id = 'SH_CLERK';

DELETE FROM
    --** MSC-ERROR - MSCEWI3123 - DBLINK CONNECTIONS NOT SUPPORTED [ DBLINK : mylink | USER: user1/password1 | CONNECTION: 'connection_str' ] **
    DATAWAREHOUSE.PUBLIC.employees
    WHERE employee_id = 999;

Recommendations

• It is important to check that all DB Links have different names, if two DB Links share the same and the code is migrated multiple times, then the EWI can change de information based on what DB Link is processed first.

• Move the database objects from the database link reference into the same database instance that is being used in Snowflake.

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