Error SQL72014: Conversion failed when converting the varchar value to data type int

Question

I'm having a problem with inserting test data into a table. I am getting the error listed in the title. I have done a lot of googling without finding a solution.

Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:

CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
    [ID] NUMERIC(18,0) NOT NULL,
    [Name] VARCHAR(500),
    [PartStructureKey] NUMERIC(18,0) NOT NULL,
    [PartNumber] VARCHAR(500) NOT NULL,
    [ParentPartNumber] VARCHAR(500) NULL,
    [TargetCost] DECIMAL(30,4) NULL,
    [UnitCost] DECIMAL(30,4) NULL,
    [CostMaturityID] INT NULL,
    [Ratio] DECIMAL(16,2) NULL,
    [Contribution] DECIMAL(16,2) NULL,
    [ChildPartTargetWeight] NUMERIC(18,2) NULL,
    [ChildPartWeight] NUMERIC(18,2) NULL,
        CONSTRAINT [FK_tblKppHierarchyPcl_tblCostMaturity] FOREIGN KEY (CostMaturityID) 
          REFERENCES    tblCostMaturity([CostMaturityID]) 
)

Using a Script.PostDeployment1.sql file, I am trying to populate the table with test data like so:

INSERT INTO [dbo].[tblKppHierarchyPcl]
       ([ID]
       ,[Name]
       ,[PartStructureKey]
       ,[PartNumber]
       ,[ParentPartNumber]
       ,[TargetCost]
       ,[UnitCost]
       ,[CostMaturityID]
       ,[Ratio]
       ,[Contribution]
       ,[ChildPartTargetWeight]
       ,[ChildPartWeight]) VALUES
(61090,'Coolant Quick Disconnect',125216,'FS-252-6FO','H432677DB-1',27.03,70.61,2,2.61,0.01,0,NULL)

I am trying to push the data to the database via SqlPublish.

My problem is this: When the post-deployment script tries to insert the data, I get the following error:

Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion 
failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.

So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.

Any ideas?

EDIT: I am using SQL Server 2012. There are no triggers on this table.

What version of SQL Server?, do you have any triggers on that table? — Lamak
Well, I don't see where the error is then, it works for me in sqlfiddle (if I take the reference out) — Lamak
That's the weird thing. If I execute that insert statement in SSMS, it works just fine. But this has to be part of a deployment; I can't just insert it manually. Other tables get populated fine. Just this one has an issue. — sshirley

Breakskater Breakskater · Accepted Answer · 2014-04-05T00:07:10

The problem is due to the column's datatype originally being an int, then changing the columns datatype to a varchar and a problem with the database project caching the previous schema. Deploying twice will refresh the database project's schema to detect the new datatype.

Error SQL72014: Conversion failed when converting the varchar value to data type int

1 Answers