Errors trigger from SQL8 R2 to SQL2012 data base

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Errors trigger from SQL8 R2 to SQL2012 data base

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2013-12-12 : 06:25:00

Hello

I just installed sql2012 and I attached a database that works well on SQL2008R2

it worked well except that at the triggers, I have error messages:

Here is my code(trigger)

ALTER trigger [ dbo]. [ Ti_tuser ] on [ dbo]. [ Tuser ] for insert as
begin
    declare
       @ numrows int,
       @ int numnull ,
       @ errno int,
       @ errmsg varchar (255)
    select @ numrows = @ @ rowcount
    if @ numrows = 0
       return


    if update ( CMATRICULE )
    begin
       select @ numnull = ( select count (*)
                          from inserted
                          Where CMATRICULE is null )
       if @ numnull ! = @ numrows
          if (select count (*)
              TEMPLOYE from t1, t2 inserted
              Where t1.CMATRICULE = t2.CMATRICULE ) numrows = @ - @ numnull
          begin
             select @ errno = 30002 ,
                    @ errmsg = ' code ' TEMPLOYE " unknown. Creation of " Tuser " prohibited. "
             goto error
          end
    end
    return
/ * Error handling * /
error:
    raiserror @ errno @ errmsg
    rollback transaction
end

The error message is

Msg 102, Level 15 , State 1 ? , ti_tuser Procedure , Line 37
Incorrect syntax near ' @ errno .

Thank you for helping me because it tye code is everywhere in my database
Google Traduction pour les entreprises :Google Kit du traducteur

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 10:39:13

For some reason it looks like spaces have been introduced into your trigger code around most of the special characters. (/ * @ [ ] ) all have extra spaces around them. First confirm that the code in your 2008 server does not have the spaces then I would try taking a backup from the 2008 server and restoring it to the 2012 server. Rather than however you did it.

Be One with the Optimizer
TG

wided
Posting Yak Master

218 Posts

Posted - 2013-12-12 : 11:04:09

the line / * Error handling * /
is a comment
I tried to restore database and not attached, it is the same problem

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 11:35:06

There are spaces introduced all over this code. Sorry I've never see this problem. I would guess it has something to do with encoding. UTF-8 vs unicode

Just to confirm please script out and post this trigger from the 2008 instance.

EDIT:
see what I mean (below)?


ALTER trigger [ dbo]. [ Ti_tuser ] on [ dbo]. [ Tuser ] for insert as
begin
    declare
       @ numrows int,
       @ int numnull ,
       @ errno int,
       @ errmsg varchar (255)
    select @ numrows = @ @ rowcount
    if @ numrows = 0
       return

vs.

ALTER trigger [dbo].[Ti_tuser] on [dbo].[Tuser] for insert as
begin
    declare
       @numrows int,
       @int numnull ,
       @errno int,
       @errmsg varchar (255)
    select @numrows = @@rowcount
    if @numrows = 0
       return

Be One with the Optimizer
TG

wided
Posting Yak Master

218 Posts

Posted - 2013-12-12 : 11:52:43

it is the same

i found

raiserror @errno @errmsg

must be

raiserror(@errmsg,16,1)

wided
Posting Yak Master

218 Posts

Posted - 2013-12-12 : 11:53:30

it works

thanks TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 13:33:12

you're welcome.
I see, so it was just scripting out the code and posting it here that introduced the extra spaces.
Yeah if you used that old syntax for raiserror then you may want to search for any code that contains join hints to specify a specific index. that syntax also changed at some point.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources