MSSQL Error: Could not resolve expression for schemabound object or constraint
I’m using Microsoft SQL Server 2005 and the database on which I executed the ALTER VIEW query has compatibility level set to SQL Server 2000(80). The ALTER VIEW query returned this error:
Could not resolve expression for schemabound object or constraint
If you encounter this issue, check the collation of database and collation of MS SQL Server. In my case server collation was set to SQL_Latin1_General_CP1_CI_AS and database collation Latin1_General_CI_AS.
If you want to find out which columns have specified collation and you do not want to do it manually in SSMS, you can run this script:
FROM information_schema.COLUMNS
WHERE collation_name = 'SQL_Latin1_General_CP1_CI_AS'
This query will return all columns in all tables/views with specified collation.
If you want to change the collation, following script will generate ALTER statements for you.
WARNING – The script is not absolutely correct, because it will generate ALTER statements for views as well and you cannot alter all types of columns (indexed, used as primary key…). For more details which columns cannot be altered see MSDN ALTER TABLE.
DECLARE @columnName nvarchar(128)
DECLARE @columnType nvarchar(128)
DECLARE @columnLength INT
DECLARE @isNullable VARCHAR(3)
DECLARE @SQL nvarchar(1000)
DECLARE column_cursor CURSOR FOR
SELECT TABLE_NAME, column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.COLUMNS
WHERE collation_name = 'SQL_Latin1_General_CP1_CI_AS'
ORDER BY TABLE_NAME
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @tableName, @columnName, @columnType, @columnLength, @isNullable
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@isNullable='YES')
BEGIN
SELECT @SQL =( 'ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @columnType + '(' + CAST(@columnLength AS nvarchar) + ') COLLATE ' + ' Latin1_General_CI_AS')
END
ELSE
BEGIN
SELECT @SQL =( 'ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @columnType + '(' + CAST(@columnLength AS nvarchar) + ') COLLATE ' + ' Latin1_General_CI_AS NOT NULL')
END
PRINT(@SQL)
FETCH NEXT FROM column_cursor INTO @tableName, @columnName, @columnType, @columnLength, @isNullable
END
CLOSE column_cursor
DEALLOCATE column_cursor
GO
The other possibility would be to set COLLATE in ALTER VIEW, but I didn’t try that.
I realize, this is not the best solution, but I needed the quick solution. The better solution would be to change the MS SQL Server collation to same as DB and to recreate whole database with correct collation.
