Error after migrating the database from SQL Server 2005 to a newer version | Targetprocess - Visual management software

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

Still have a question?

We're here to help! Just contact our friendly support team

Email us
The more details you can give us the better
Live chat
Prefer instant messaging? Try our live chat
Service Desk
Add tickets, comments and track status in our Helpdesk
Slack Community
Shape the future direction of Targetprocess

Find out more about our APIs, Plugins, Mashups and custom extensions. Join our community of passionate users and even discuss directly with our developers.

Start your free trial

Enter your email
By clicking "Continue", you acknowledge and agree that we will process your personal data in accordance with our Service Privacy Policy and Terms of Service.

We’ve sent you a confirmation e-mail — please, go check it.

Or get a live
product demo