Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database that currently lives in SQL Server 2005. We are currently looking at upgrading to SQL 2012.

I installed 2012 in a test environment (Windows Server 2012 virtual machine). Backed up my database, and restored it to the new server.

When I try to modify data in a particular table, the error I get is:

No row was updated.

The data in row X was not committed.

Error Source: .Net SqlClient Data Provider.

Error Message: Incorrect syntax near '20001'.

Running the same query, or manually editing the table in SQL 2005 and SQL 2008 works just fine. Only doing the exact same thing in SQL 2012 throws the error.

I'm leaning towards something in the design of the particular table that 2012 doesn't like, but I don't even know where to start.

Any ideas? Suggestions? Thanks in advance!

Output of Table from SQL Server 2005:

USE [WHCSQL]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 02/04/2014 14:24:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [BarcodeID] [varchar](20) NULL,
    [SSNum] [varchar](11) NULL,
    [LastName] [varchar](25) NULL,
    [FirstName] [varchar](15) NULL,
    [MI] [varchar](1) NULL,
    [Preferred] [varchar](15) NULL,
    [Address] [varchar](60) NULL,
    [Address2] [varchar](40) NULL,
    [City] [varchar](25) NULL,
    [State] [varchar](10) NULL,
    [Zipcode] [varchar](10) NULL,
    [HomePhone] [varchar](15) NULL,
    [EmergencyPhone] [varchar](15) NULL,
    [EmergencyContact] [varchar](50) NULL,
    [DateOfBirth] [datetime] NULL,
    [DepartmentID] [int] NULL,
    [CommissionLevel] [tinyint] NULL,
    [CommissionCycle] [int] NULL,
    [CommissionPlanID] [int] NULL,
    [HourlyWage] [decimal](7, 2) NULL,
    [Password] [varchar](14) NULL,
    [Access] [char](200) NULL,
    [HireDate] [datetime] NULL CONSTRAINT [DF_Employee_HireDate]  DEFAULT (getdate()),
    [TermDate] [datetime] NULL,
    [Active] [bit] NULL CONSTRAINT [DF_Employee_Active]  DEFAULT (1),
    [ClubNumber] [int] NULL,
    [LimitAccessByClubs] [bit] NOT NULL CONSTRAINT [DF_Employee_LimitAccessByClubs]  DEFAULT (0),
    [LUDate] [datetime] NULL,
    [Email] [varchar](50) NULL,
    [FingerPrint] [varchar](4000) NULL,
    [CellPhone] [varchar](15) NULL,
    [HashedPassword] [varchar](32) NULL,
 CONSTRAINT [EMP_IDKey_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [EMP_BarcodeIDKey] UNIQUE NONCLUSTERED 
(
    [BarcodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [IDX]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_CommissionLevel] CHECK  (([CommissionLevel] = 0 and [CommissionLevel] <= 3))
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [CK_Employee_CommissionLevel]

Interestingly, when I run the same output against the same database but where it is restored into SQL Server 2012, there are three additional lines at the bottom:

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_HireDate]  DEFAULT (getdate()) FOR [HireDate]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_Active]  DEFAULT (1) FOR [Active]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_LimitAccessByClubs]  DEFAULT (0) FOR [LimitAccessByClubs]
GO
share|improve this question

migrated from serverfault.com Feb 8 at 11:41

This question came from our site for system and network administrators.

4  
Can you post the actual query that you are trying to run against the table? It sounds like it's getting truncated or you're using a coding method that is no longer supported. –  Mark Sinkinson Mar 10 at 16:10
    
Are there any triggers or views associated with the table? –  Jeremy Mar 10 at 16:52
    
Please post the query that you run against this table. We can't do anything but speculate without it. Which column are you trying to update? –  swasheck May 18 at 15:24
    
The extra three lines are just defining the defaults: they are defined as part of the CREATE TABLE statement in the SQL2005 generated version. Both ways of defining defaults are equally valid. –  David Spillett Aug 29 at 16:28

1 Answer 1

Make sure to check about the MSDN page about Breaking Changes in SQL 2012. It could be caused by old sql syntax that has to be changed if you want upgrade to SQL 2012.

The problem could also be that your .net program that is trying to access your database is of an older version, and it needs to be upgraded to .net 4.0 or 4.5.

share|improve this answer
    
I appreciate your reply! Currently have .Net 4.5 installed. I had suspected old query syntax, but I get the same message when manually modifying the table's data in Management Studio -> Edit All Rows. –  Davan Jan 31 at 23:35
    
I think it would help if I could see the Table Definition, so please post the CREATE script for the particular table, right click on the table -> Script Table as -> Create To -> New Query Editor Window. –  mikeng Jan 31 at 23:54
    
Sorry for the delay on this. When I run this output in SQL Server 2005, here is what I get: edit - it's too big to enter into this comment box... –  Davan Feb 4 at 21:34
    
Code? :) put the code in the question. –  mikeng Feb 4 at 21:37
    
Sorry about that, took me a minute to get it formatted. –  Davan Feb 4 at 21:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.