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 in SQL Server 2005. We are currently looking at upgrading to SQL Server 2012.

I installed SQL Server 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 Server 2005 and SQL Server 2008 works just fine. Only doing the exact same thing in SQL Server 2012 throws the error.

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

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 '14 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 '14 at 16:10
    
Are there any triggers or views associated with the table? –  Jeremy Mar 10 '14 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 '14 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 '14 at 16:28

2 Answers 2

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 '14 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 '14 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 '14 at 21:34
    
Code? :) put the code in the question. –  mikeng Feb 4 '14 at 21:37
    
Sorry about that, took me a minute to get it formatted. –  Davan Feb 4 '14 at 21:39

@davan I suggest you to do the following which I have mentioned below and this may resolve your problem

  • YOU CAN USE THE SQL 2012 UPGRADE ADVISOR
  • DBCC CHECKDB COMMAND WITH DATA_PURITY

DBCC CHECKDB WITH DATA_PURITY;

Reason why suggesting to perform this task

This following statement above checks your data for values that are no longer valid for the column datatype. For databases created prior to SQL 2005

  • Check for SQL Server Database compatibility use the following query to identify that

    SELECT name AS 'DB_Name', compatibility_level , version_name = 
    CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
    END
    from sys.databases
    

If the compatibility level of a user database is 100 or higher before upgrade, it remains the same after upgrade. If the compatibility level is 90 before upgrade in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2014.

  • UPDATING STATISTICS IN THE DATABASE

Use the below command

 USE Your_DB_Name;
 GO
 EXEC sp_updatestats;

Source where the information was found thomaslarock.com

share|improve this answer

Your Answer

 
discard

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