📋

Description

This error occurs when attempting to alter a SQL Server object to a type incompatible with its current definition. It typically arises when trying to change a view into a table, or vice versa, using the ALTER statement.

💬

Error Message

Cannot perform alter on '%.*ls' because it is an incompatible object type.

🔍

Known Causes

3 known causes

⚠️

Incorrect Object Type

The most common cause is attempting to change an object's type (e.g., view to table) using ALTER. SQL Server does not allow this direct type conversion.

⚠️

Accidental ALTER Statement

An ALTER statement might be executed unintentionally targeting the wrong object, leading to an attempt to modify an object to an incompatible type.

⚠️

Scripting Errors

Errors in SQL scripts, particularly those generated programmatically, can result in ALTER statements targeting objects with incorrect type assumptions.

🛠️

Solutions

3 solutions available

1. Verify Object Type Before Altering easy

Ensure you are attempting to alter an object of the expected type.

1

Identify the object name causing the error. The error message '%.*ls' will contain the name of the object.

2

Query the `sys.objects` catalog view to determine the actual type of the object.

SELECT name, type_desc FROM sys.objects WHERE name = 'YourObjectName';

3

Compare the `type_desc` with the type of object you intended to alter. For example, you cannot `ALTER TABLE` a stored procedure or `ALTER VIEW` a table.

4

If the object type is incompatible, you will need to use the appropriate DDL statement for that object type (e.g., `CREATE PROCEDURE`, `CREATE VIEW`, `DROP TABLE` and `CREATE TABLE`).

2. Drop and Recreate Incompatible Objects medium

For persistent incompatibilities, drop and recreate the object with the correct definition.

1

Identify the object name and its current type from `sys.objects` (as per Solution 1).

SELECT name, type_desc FROM sys.objects WHERE name = 'YourObjectName';

2

Determine the correct definition for the object you intended to create or modify.

3

If the object is a user-defined object (e.g., stored procedure, view, function, table), and you are trying to `ALTER` it as a different type, you will likely need to drop and recreate it.

DROP PROCEDURE YourObjectName;
-- or
DROP VIEW YourObjectName;
-- or
DROP FUNCTION YourObjectName;
-- or
-- For tables, if you need to change structure significantly, consider a different approach like creating a new table and migrating data.

4

Recreate the object with its correct definition.

CREATE PROCEDURE YourObjectName AS
BEGIN
    -- Your procedure logic
END;
-- or
CREATE VIEW YourObjectName AS
SELECT Column1, Column2 FROM YourTable;
-- or
CREATE FUNCTION YourFunctionName ()
RETURNS INT
AS
BEGIN
    RETURN 1;
END;

3. Resolve Dependencies Interfering with Alter medium

Dependencies on the object might prevent certain ALTER operations, especially when changing fundamental properties.

1

Identify the object name that cannot be altered.

2

Query `sys.dm_sql_referencing_entities` and `sys.dm_sql_referenced_entities` to find objects that depend on or reference the target object.

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities('YourSchema.YourObjectName', 'OBJECT');

SELECT referenced_schema_name, referenced_entity_name, referenced_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities('YourSchema.YourObjectName', 'OBJECT');

3

Analyze the results. If other objects (like views, stored procedures, triggers) reference the object you're trying to alter, and the alteration would break these references, SQL Server prevents it.

4

Address the dependencies. This might involve altering the dependent objects to accommodate the change, or temporarily dropping and recreating them after the primary object is altered.

ALTER VIEW DependentViewName AS
SELECT ... FROM YourSchema.YourNewObjectName; -- Update references

-- Or, drop and recreate dependent objects after altering the main object.

SQL Server Error 2010: Incompatible Alter