📋

Description

Error 330, ERR_SQL_EXST_SYNONYM, indicates an attempt to create a database synonym with a name that already exists. This typically occurs during database operations, custom development, or data migration within SAP S/4HANA, where unique identifiers are strictly enforced.

💬

Error Message

ERR_SQL_EXST_SYNONYM

🔍

Known Causes

3 known causes

⚠️

Attempting to Create Existing Synonym

A user or process tried to define a new database synonym using a name that is already in use within the current schema or database.

⚠️

Script or Application Error

An SQL script, custom application, or data import routine executed without proper checks, leading to a redundant synonym creation command.

⚠️

Manual Configuration Oversight

During manual database or system configuration, a synonym was inadvertently created with a name already assigned to another object.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Synonym medium

Locate the existing synonym and remove it before creating the new one.

1

Connect to your SAP HANA database using a SQL client (e.g., SAP HANA Studio, hdbsql).

2

Query the system views to find existing synonyms with the same name.

SELECT SYNONYM_NAME, SCHEMA_NAME, SYNONYM_OBJECT_NAME, SYNONYM_OBJECT_SCHEMA FROM SYS.SYNONYMS WHERE SYNONYM_NAME = '<YOUR_SYNONYM_NAME>';

3

If a duplicate synonym is found, drop it. Replace `<SCHEMA_NAME>` with the schema where the duplicate synonym resides and `<YOUR_SYNONYM_NAME>` with the name of the duplicate synonym.

DROP SYNONYM <SCHEMA_NAME>."<YOUR_SYNONYM_NAME>";

4

Once the duplicate is removed, attempt to create the new synonym again.

2. Verify Object Ownership and Schema for Synonym Creation medium

Ensure the user creating the synonym has appropriate privileges and is targeting the correct schema.

1

Identify the user attempting to create the synonym and verify their privileges. The user needs CREATE SYNONYM privilege on the target schema.

2

Determine the schema where the target object (table, view, etc.) resides. Ensure this is the schema you intend to create the synonym in.

3

When creating the synonym, explicitly specify the schema name if it's not the current user's default schema. Replace `<TARGET_SCHEMA>` with the schema name and `<YOUR_SYNONYM_NAME>` with the desired synonym name.

CREATE SYNONYM <TARGET_SCHEMA>."<YOUR_SYNONYM_NAME>" FOR <OBJECT_SCHEMA>."<OBJECT_NAME>";

4

If the synonym is intended to be public, use the `PUBLIC` keyword. However, be cautious with public synonyms as they can lead to naming conflicts.

CREATE PUBLIC SYNONYM "<YOUR_SYNONYM_NAME>" FOR <OBJECT_SCHEMA>."<OBJECT_NAME>";

3. Review Development/Deployment Scripts medium

Examine application or deployment scripts for redundant synonym creation statements.

1

If this error occurs during an application deployment or upgrade, review the deployment scripts (SQL scripts, transport files, etc.) for any statements that create synonyms.

2

Look for instances where the same synonym name is being created multiple times within the same script or across different scripts that are executed in sequence.

3

Modify the scripts to ensure that a synonym with a given name is only created once. You can add checks like `IF NOT EXISTS` or ensure that a `DROP SYNONYM` statement precedes the `CREATE SYNONYM` statement if the script might be run multiple times.

DO BEGIN
  IF NOT EXISTS (SELECT 1 FROM SYS.SYNONYMS WHERE SYNONYM_NAME = '<YOUR_SYNONYM_NAME>' AND SCHEMA_NAME = '<TARGET_SCHEMA>') THEN
    CREATE SYNONYM <TARGET_SCHEMA>."<YOUR_SYNONYM_NAME>" FOR <OBJECT_SCHEMA>."<OBJECT_NAME>";
  END IF;
END;

4

Re-deploy or re-execute the corrected scripts.

🔗

Related Errors

5 related errors

SAP S/4HANA Error 330: Duplicate SQL Synonym Name