ADO error handling when connecting to SQL Server 2000

I use Delphi to connect to SQL Server 2000 using the ADO components. I'm
executing stored procedure that makes some calls to another stored procs. In
one of the sub called stored procs foreign key is violated and error message
is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
constraint ..' i see it in the SQL Query analyser).
When i use this connection string:
Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
ID=sa;Initial Catalog=TechDB;Data Source=.
the error is not reported in my application, it seems that the driver
ignores the error message. I have no Exception generated. So i tried with
another driver for SQL Server (ODBC). The ADO connection string was:
Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
Source=LOCA_RMP;Initial Catalog=TechDB
This one is reporting error in the above situation (i get Exception with
'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ). But
the problem is that it generates exception when 'print' command is executed,
so it thinks that every message is an error. Besides the connecting thru
ODBC DSN seems to be slower and i think it's not native for ADO .
I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
messy to use "if @.@.error<>0 set @.local_error=1" after every line of code. Is
there another way to do this? Am i supposed to raiserror at the end of the
stored proc?
Any suggestions are appreciated,
Georgi PeshterskiFor a complete discussion on Error handling in Stored Procs, see
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"George Peshterski" <unseen@.mail.bg> wrote in message
news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
> I use Delphi to connect to SQL Server 2000 using the ADO components.
> I'm
> executing stored procedure that makes some calls to another stored procs.
> In
> one of the sub called stored procs foreign key is violated and error
> message
> is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
> constraint ..' i see it in the SQL Query analyser).
> When i use this connection string:
> Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
> ID=sa;Initial Catalog=TechDB;Data Source=.
> the error is not reported in my application, it seems that the driver
> ignores the error message. I have no Exception generated. So i tried with
> another driver for SQL Server (ODBC). The ADO connection string was:
> Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
> Source=LOCA_RMP;Initial Catalog=TechDB
> This one is reporting error in the above situation (i get Exception
> with
> 'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ).
> But
> the problem is that it generates exception when 'print' command is
> executed,
> so it thinks that every message is an error. Besides the connecting thru
> ODBC DSN seems to be slower and i think it's not native for ADO .
> I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
> messy to use "if @.@.error<>0 set @.local_error=1" after every line of code.
> Is
> there another way to do this? Am i supposed to raiserror at the end of the
> stored proc?
> Any suggestions are appreciated,
> Georgi Peshterski
>|||Thank you for the useful links, i'm reading the articles now and there are
many things i didn't knew.
As for my problem i found that the foreign key error that ADO hides happens
in INSTEAD OF trigger in the subprocedures. I read that error in trigger
cancels the batch but i'm still curious why ADO isn't reporting it to me,
i'm going to examine carefully the articles about this problem ...
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> For a complete discussion on Error handling in Stored Procs, see
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "George Peshterski" <unseen@.mail.bg> wrote in message
> news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
procs.
with
it's
code.
the
>|||Make sure you have SET NOCOUNT ON at the beginning of your procs and
triggers. This will suppress DONE_IN_PROC messages that are returned as
empty closed recordsets in ADO.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Peshterski" <unseen@.mail.bg> wrote in message
news:e6k39MgDFHA.392@.TK2MSFTNGP14.phx.gbl...
> Thank you for the useful links, i'm reading the articles now and there are
> many things i didn't knew.
> As for my problem i found that the foreign key error that ADO hides
> happens
> in INSTEAD OF trigger in the subprocedures. I read that error in trigger
> cancels the batch but i'm still curious why ADO isn't reporting it to me,
> i'm going to examine carefully the articles about this problem ...
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> procs.
> with
> it's
> code.
> the
>