Error after migrating the database from SQL Server 2005 to a newer version

Problem

You have recently moved your database from SQL Server 2005 to a newer version, and now for some actions you see an error like this:

'Oops something is wrong Column parameter or variable @P2: Cannot find data type IDs'

Solution

Microsoft was supposed to make the version of SQL fully compatible, but in reality you need to execute two sql scripts after you restore you database backup at the new server:

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
if (@sqlVers >= 10)
BEGIN
DECLARE @SQLString nvarchar(500);
SET @SQLString =
N'CREATE TYPE [dbo].[Ids] AS TABLE(
[ID] [int] Not NULL PRIMARY KEY CLUSTERED
)'
EXECUTE sp_executesql @SQLString
END
GO

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
if (@sqlVers >= 10)
BEGIN
DECLARE @SQLString nvarchar(500);
SET @SQLString = N'
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''Ids'' AND ss.name = N''dbo'')
DROP TYPE [dbo].[Ids]
CREATE TYPE [dbo].[Ids] AS TABLE(
[Id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)'
EXECUTE sp_executesql @SQLString
END
GO