Error message returned with standard DELETE on SQL Server 2005 database restored in SQL Server 2012
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_201
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.
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_201
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.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
and the error message is?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the error?
Also, since you migrated the database, any rebuild index ran?
Also, since you migrated the database, any rebuild index ran?
ASKER
Hi,
The error message is as follows:
Msg 102, Level 15, State 1, Procedure TD_Table, Line 58 Incorrect syntax near '@ERRNO'.
The error message is as follows:
Msg 102, Level 15, State 1, Procedure TD_Table, Line 58 Incorrect syntax near '@ERRNO'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
I will revert later and award points