December 19, 2013 at 9:01 am
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?
December 19, 2013 at 9:33 am
In SSMS I created your table (dropping the FK reference), then ran the insert into the table with no errors. Not sure what could be happening.
December 19, 2013 at 9:40 am
Do you have any triggers defined on the table? As Lynn says, the SQL posted will work fine against the table as it's defined above.
December 19, 2013 at 9:46 am
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.
December 19, 2013 at 9:47 am
Hi Howard. No triggers or any other object aside from the foreign key reference.
December 19, 2013 at 9:56 am
What happens if you change the SQL to include the database name as well?
INSERT INTO DatabaseName.SchemaName.TableName
December 19, 2013 at 11:26 am
Sowbhari (12/19/2013)
What happens if you change the SQL to include the database name as well?
INSERT INTO DatabaseName.SchemaName.TableName
Unfortunately no difference. I'm wondering if this is truly a SQL Server problem or perhaps a problem with SqlPackage or DacPac deployment.
December 20, 2013 at 2:50 am
Has anyone changed that ID column to an Identity column without telling you?
December 20, 2013 at 7:49 am
crmitchell (12/20/2013)
Has anyone changed that ID column to an Identity column without telling you?
No. It failed on deployment to our common dev server. But this is also failing on my local dev box. I can see locally that this is not an identity.
Like most of Microsofts products, objects have GUID identifiers. Perhaps this is a case of a stale object ID (as sometimes happens in SSIS)?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply