Solved

Error message returned with standard DELETE on SQL Server 2005 database restored in SQL Server 2012

Posted on 2014-12-12
7
182 Views
Last Modified: 2015-01-29
I have come across an issue with executing DELETE statement in SQL Server 2012 in a database which has been restored
from SQL Server 2005 to SQL Server 2012,

This is the delete statement:

DELETE FROM [Table] WHERE Field1_ID='xxx'

I restored a SQL Server 2005 backup on to a new SQL Server 2012 server using the following restore command:

DECLARE @backupfile varchar(200)
SET @backupfile = 'E:\SQL_BACKUP\DB_FULL_20141211_182000.bak'

RESTORE DATABASE DB_T
   FROM DISK = @backupfile
   WITH
   MOVE 'DB_T_Data' TO 'D:\SQL_DATA\DB_T.mdf',
   MOVE 'DB_T_Log' TO 'E:\SQL_LOGS\DB_T.ldf'

The RESTORE message output indicated that 'upgrade' steps did occur as part of the restore.  

Also I did the following to actually change the compatability level, from 2005 to 2012  
ALTER DATABASE DB_T
SET COMPATIBILITY_LEVEL = 110;

I have seen some articles online which suggest that the compatability level is the root cause.

However, as I have indicated above, I have taken all steps to get the 2005 database to the 2012 compatibility level.

Please let me know if anyone has encountered anything similar.
0
Comment
Question by:Barry Cunney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40495965
and the error message is?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40495966
so far, guessing it's a trigger on the table that is trying to look up for some other db ...
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40495972
What is the error?
Also, since you migrated the database, any rebuild index ran?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 17

Author Comment

by:Barry Cunney
ID: 40496210
Hi,
The error message is as follows:
Msg 102, Level 15, State 1, Procedure TD_Table, Line 58 Incorrect syntax near '@ERRNO'.
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40496223
You didn't post your code but looks like you are using an old way to raise error in SQL Server. Didn't you run the Upgrade Advisor before migrating the database? This kind of incompatibilities can be discovered by the Upgrade Advisor.
Anyway, check the syntax in this MSDN article.
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 40533287
Hi Guys,
I just figured out the root cause.
The actual compatibility level of the database was 'SQL Server 2000'.
The database had resided on SQL Server 2005 and when a backup of this database was restored on SQL Server 2012, it showed the compatibility level as 'SQL Server 2005'.
However when I checked the compatibility level of this database on the SQL Server 2005 evironment, it showed 'SQL Server 2000' - so we thought we were restoring a 2005 database to 2012, but in actual fact we had restored a 2000 level database to 2012 and so it was not really upgraded, and so we did not get a valid 2012 database - and one of the fall outs of this was that a standard single line DELETE-> DELETE FROM [Table] WHERE Field1_ID='xxx'  gave the the error   'Msg 102, Level 15, State 1, Procedure TD_Table, Line 58 Incorrect syntax near '@ERRNO'.'

We are now in the process of getting this database validly upgraded to 2005/2008 level and then upgraded to 2012 level.
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 40533290
I will revert later and award points
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question