Board index » delphi » ODBC SQL Server driver 3.5 and RAISERROR in Triggers

ODBC SQL Server driver 3.5 and RAISERROR in Triggers

We have upgraded our ODBC drivers using MDAC 1.5c.  One problem we are
experiencing is the way the new ODBC SQL Server driver 3.5 is handling
errors raised within triggers.    Previously, under ODBC SQLServer driver
2.65 the errors SQL_Error was returned.  Now, SQL_SUCCESS_WITH_INFO was
returned.

Our script generator (ErWin 2.5) uses the following syntax for example:

    raiserror 30001 'Cannot DELETE "TemplateFolders" because "Templates"
exists.'

However, the correct syntax (according to SQL Books online) is

RAISERROR ({msg_id | msg_str}, severity, state
 [, argument1 [, argument2]])
 [WITH options]

How, can we get the same result with the new ODBC drivers?

--
Regards,

Roger Weiss
Datasoft - Division of Datatec

Datasoft web site at http://www.datasoft.co.za
Datatec web Site at http://www.datatec.co.za

 

Re:ODBC SQL Server driver 3.5 and RAISERROR in Triggers


Roger,

I can't help you with the ODBC issue, but regarding the RAISERROR generated
by Erwin looks like pre-6.0. It seems like Erwin need to be
configured/upgraded to generate 6.0+ RAISERROR syntax.

HTH
--
Tibor Karaszi, ti...@cornerstone.se
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB

Quote
Roger Weiss wrote in message <6etbsj$ao...@news01.iafrica.com>...
>We have upgraded our ODBC drivers using MDAC 1.5c.  One problem we are
>experiencing is the way the new ODBC SQL Server driver 3.5 is handling
>errors raised within triggers.    Previously, under ODBC SQLServer driver
>2.65 the errors SQL_Error was returned.  Now, SQL_SUCCESS_WITH_INFO was
>returned.

>Our script generator (ErWin 2.5) uses the following syntax for example:

>    raiserror 30001 'Cannot DELETE "TemplateFolders" because "Templates"
>exists.'

>However, the correct syntax (according to SQL Books online) is

>RAISERROR ({msg_id | msg_str}, severity, state
> [, argument1 [, argument2]])
> [WITH options]

>How, can we get the same result with the new ODBC drivers?

>--
>Regards,

>Roger Weiss
>Datasoft - Division of Datatec

>Datasoft web site at http://www.datasoft.co.za
>Datatec web Site at http://www.datatec.co.za

Re:ODBC SQL Server driver 3.5 and RAISERROR in Triggers


The templates in ERwin can be easily modified to conform to
the newer syntax.  Tip: Be sure and override the template at
a diagram level so that you can reuse the change.  Also, it
helps to create a template diagram so that when you create
new diagrams these changes come with it.

Another option is to upgrade ERwin to 3.0.

P.S.  There is an ERwin Listserv at infoadvisors.com
--
Michael Yocca
SQL Server and ERwin Consultant
Ace Database www.acedb.com
Where data quality helps to prevent rework
m...@acedb.com

Roger Weiss <rog...@datasoft.co.za> wrote in article
<6etbsj$ao...@news01.iafrica.com>...

Quote
> We have upgraded our ODBC drivers using MDAC 1.5c.  One problem we are
> experiencing is the way the new ODBC SQL Server driver 3.5 is handling
> errors raised within triggers.    Previously, under ODBC SQLServer driver
> 2.65 the errors SQL_Error was returned.  Now, SQL_SUCCESS_WITH_INFO was
> returned.

> Our script generator (ErWin 2.5) uses the following syntax for example:

>     raiserror 30001 'Cannot DELETE "TemplateFolders" because "Templates"
> exists.'

> However, the correct syntax (according to SQL Books online) is

> RAISERROR ({msg_id | msg_str}, severity, state
>  [, argument1 [, argument2]])
>  [WITH options]

> How, can we get the same result with the new ODBC drivers?

> --
> Regards,

> Roger Weiss
> Datasoft - Division of Datatec

> Datasoft web site at http://www.datasoft.co.za
> Datatec web Site at http://www.datatec.co.za

Re:ODBC SQL Server driver 3.5 and RAISERROR in Triggers


Roger

With RAISEERROR, the severity parameter determines if it returns SQL_ERROR,
or SQL_SUCCESS_WITH_INFO.  You can even cause the connection to be
terminated with the correct severity figure.  You'll need to refer to SQL
Books online for the details.

Thanks
Jay

Roger Weiss <rog...@datasoft.co.za> wrote in article
<6etbsj$ao...@news01.iafrica.com>...

Quote
> We have upgraded our ODBC drivers using MDAC 1.5c.  One problem we are
> experiencing is the way the new ODBC SQL Server driver 3.5 is handling
> errors raised within triggers.    Previously, under ODBC SQLServer driver
> 2.65 the errors SQL_Error was returned.  Now, SQL_SUCCESS_WITH_INFO was
> returned.

> Our script generator (ErWin 2.5) uses the following syntax for example:

>     raiserror 30001 'Cannot DELETE "TemplateFolders" because "Templates"
> exists.'

> However, the correct syntax (according to SQL Books online) is

> RAISERROR ({msg_id | msg_str}, severity, state
>  [, argument1 [, argument2]])
>  [WITH options]

> How, can we get the same result with the new ODBC drivers?

> --
> Regards,

> Roger Weiss
> Datasoft - Division of Datatec

> Datasoft web site at http://www.datasoft.co.za
> Datatec web Site at http://www.datatec.co.za

Other Threads