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

migration rejected from serverfault.com Aug 25 at 10:36

This question came from our site for system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by ypercube, Colin 't Hart, Paul White Aug 25 at 10:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

6  
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

1 Answer 1

@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