SQL Server 2012 annoyance...
-
One of my clients has upgraded to 64bit SQL Server 2012. We have moved the test database there and I am having a bizarre issue that's doing my head in... Even the most simple update query causes an error, which of course, is causing stored procedures to fall over...
update tbl_User
set USR_Email = 'myemail@domain.name.au'
where UserID = 5causes the following error: "Msg 102, Level 15, State 1, Procedure tbl_Userupdate, Line 53 Incorrect syntax near '@errorNumber'." The strange part is that the query itself runs fine. Any suggestions? Perhaps a setting that's been missed by the sysadmin who did the installation?
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
One of my clients has upgraded to 64bit SQL Server 2012. We have moved the test database there and I am having a bizarre issue that's doing my head in... Even the most simple update query causes an error, which of course, is causing stored procedures to fall over...
update tbl_User
set USR_Email = 'myemail@domain.name.au'
where UserID = 5causes the following error: "Msg 102, Level 15, State 1, Procedure tbl_Userupdate, Line 53 Incorrect syntax near '@errorNumber'." The strange part is that the query itself runs fine. Any suggestions? Perhaps a setting that's been missed by the sysadmin who did the installation?
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Alright, so a massive facepalm moment... Further testing revealed that the problem is with a trigger on the table that was system generated to handle table relationships, and the error raising code in the trigger is being handled differently by SQL 2012 (this code is legacy from many versions of SQL ago)... so if I fix the trigger, I fix the problem (one would assume). FML.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Alright, so a massive facepalm moment... Further testing revealed that the problem is with a trigger on the table that was system generated to handle table relationships, and the error raising code in the trigger is being handled differently by SQL 2012 (this code is legacy from many versions of SQL ago)... so if I fix the trigger, I fix the problem (one would assume). FML.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
I hate fucking triggers spit for this exact reason, for some completely unknown reason you get an error that just does not make sense and if you are not in the habit of looking under that particular rock you can spend hours chasing down the issue. I allow audit triggers and absolutely none others, the audit triggers are generated with a script that will both remove and create them according to schema table.
Never underestimate the power of human stupidity RAH
-
I hate fucking triggers spit for this exact reason, for some completely unknown reason you get an error that just does not make sense and if you are not in the habit of looking under that particular rock you can spend hours chasing down the issue. I allow audit triggers and absolutely none others, the audit triggers are generated with a script that will both remove and create them according to schema table.
Never underestimate the power of human stupidity RAH
These were in the system generated update and insert triggers (handling table relationships and cascades), auto generated by SQL Server about a decade ago when the system diagrams were created... any wonder I didn't think to look there!!
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
These were in the system generated update and insert triggers (handling table relationships and cascades), auto generated by SQL Server about a decade ago when the system diagrams were created... any wonder I didn't think to look there!!
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
_Damian S_ wrote:
any wonder I didn't think to look there!!
Especially if you are not in the habit of using the bloody things. I wonder how many versions they have upgraded through, I don't recall sql auto generating triggers.
Never underestimate the power of human stupidity RAH