ALTER TABLE SWITCH statement failed. The table '%.*ls' has view '%.*ls' with schemabinding.

Error Message:
Msg 4937, Level 16, State 1, Line 3
ALTER TABLE SWITCH statement failed. The table ‘%.*ls’ has view ‘%.*ls’ with schemabinding.

Severity level:
16.

Description:
This error message appears, when you try to execute an ALTER TABLE SWITCH command for which one of the participating table has a schema-bound view.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement can not be executed this way. Both, source and target, in an ALTER TABLE SWITCH command can not have a schema-bound view. You need to remove the schemabinding, before you can execute the command.

Versions:
This error message was introduced with SQL Server 2005.

Example(s):
USE Pubs
GO
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;
GO

IF OBJECT_ID (‘dbo.NonPartView’, ‘View’) > 0
   DROP VIEW dbo.NonPartView;
GO

CREATE PARTITION FUNCTION myPartFunction (int)
    AS RANGE LEFT FOR VALUES (1, 10, 100);
GO

CREATE PARTITION SCHEME myPartScheme
    AS PARTITION myPartFunction
   ALL TO ([PRIMARY]);
GO

CREATE TABLE myPartTable
(
 c1 int
)
ON myPartScheme (c1);

GO
CREATE TABLE myNonPartTable
(
 c1 int
)
ON [PRIMARY];
GO
CREATE VIEW dbo.NonPartView
WITH SCHEMABINDING
AS
SELECT c1
  FROM dbo.myNonPartTable
GO
CREATE UNIQUE CLUSTERED INDEX cix_NonPartView
    ON dbo.NonPartView (c1);
GO

ALTER TABLE myPartTable SWITCH PARTITION 1 TO dbo.myNonPartTable ;
GO
DROP VIEW NonPartView
DROP TABLE myNonPartTable, myPartTable;
DROP PARTITION SCHEME myPartScheme;
DROP PARTITION FUNCTION myPartFunction;

Remarks:
In the above example we try to execute an ALTER TABLE SWITCH command. Since the target table has a schema-bound view, the error is raised.

]]>

Leave a comment

Your email address will not be published.