How to retrieve SQL error code from the Error object?

Hello!
I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On the
server side i have created stored procedure that can raise an error with
some SQL error code like 547 or 550 and i want to catch this code on the
client side to log and to call other procedure, but Error object does not
pass this code, there are only OLE DB error code (same for different errors)
and Description, but NativeError and SQLState fields is empty. How can i
recieve SQL error code over Error object? Thanks!
Best regards, Konstantin KnyazevKostya
There are some errors in SQL Server that terminate batch immediately after
raising like Violation of PRIMARY KEY constraint. In your case I'd try to do
that on the server side
CREATE PROC myProc
AS
--Do something
IF @.@.ERROR <>0
RETURN -1
ELSE
RETURN 0
DECLARE @.err INT
EXEC @.err=myProc
IF @.err =-1
PRINT 'I have got an error'
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On
the
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different
errors)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>|||Hi
You can cycle throught the errors in the connection object such as:
procedure TForm1.Button1Click(Sender: TObject);
var
i:integer;
begin
try
form2.show;
sp.ExecProc;
except
on e: Eaccessviolation do
showmessage(e.message)
else
if cnn.errors.Count <> 0 then
begin
for i:=0 to cnn.errors.count-1 do
showmessage(cnn.errors[i].desc_ription);
end;
end;
end;
from the thread http://tinyurl.com/4trhq
John
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hi!
In my case, Connection.Errors collection is empty, probably due a
asynchronious call, there are only Error object passed in the parameters of
the ExecuteComplete method.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:87F6042E-5AFD-4118-A7F9-E7FBB0C8EB4C@.microsoft.com...
> Hi
> You can cycle throught the errors in the connection object such as:
> procedure TForm1.Button1Click(Sender: TObject);
> var
> i:integer;
> begin
> try
> form2.show;
> sp.ExecProc;
> except
> on e: Eaccessviolation do
> showmessage(e.message)
> else
> if cnn.errors.Count <> 0 then
> begin
> for i:=0 to cnn.errors.count-1 do
> showmessage(cnn.errors[i].desc-ription);
> end;
>
> end;
> end;
> from the thread http://tinyurl.com/4trhq
> John
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||It was a while since I did something similar, but I can recall having to use
error values of at least a certain number, otherwise SQL Server supresses
them, you would need to check the documentation to confirm this..
"Konstantin Knyazev" wrote:

> Hello!
> I'm using MS SQL 2000 as a server and Delphi 7 over ADO as a client. On th
e
> server side i have created stored procedure that can raise an error with
> some SQL error code like 547 or 550 and i want to catch this code on the
> client side to log and to call other procedure, but Error object does not
> pass this code, there are only OLE DB error code (same for different error
s)
> and Description, but NativeError and SQLState fields is empty. How can i
> recieve SQL error code over Error object? Thanks!
> Best regards, Konstantin Knyazev
>
>|||Hello!
Yes, i did it, but further i have changed system to use linked servers and
distributed query that requires XACT_ABORT ON, but your method requires
XACT_ABORT OFF.
Best regards, Konstantin Knyazev
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BQSdu3LFHA.2988@.TK2MSFTNGP14.phx.gbl...
> Kostya
> There are some errors in SQL Server that terminate batch immediately
after
> raising like Violation of PRIMARY KEY constraint. In your case I'd try to
do
> that on the server side
> CREATE PROC myProc
> AS
> --Do something
> IF @.@.ERROR <>0
> RETURN -1
> ELSE
> RETURN 0
> --
> DECLARE @.err INT
> EXEC @.err=myProc
> IF @.err =-1
> PRINT 'I have got an error'
>
>
>
>
>
>
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:O7pl8EvLFHA.1144@.TK2MSFTNGP09.phx.gbl...
> the
not
> errors)
>|||Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
link to article? I can't find anything :(
Best regards, Konstantin Knyazev
"Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> It was a while since I did something similar, but I can recall having to
use
> error values of at least a certain number, otherwise SQL Server supresses
> them, you would need to check the documentation to confirm this..
> "Konstantin Knyazev" wrote:
>
the
not
errors)|||For my problem I was using RAISERROR in SQL Server Stored Proc
[http://msdn.microsoft.com/library/d...r />
_5ooi.asp]
Try modifying the paramters until you get a reply back to the client. Since
I dont know exactly what you are doing I cant be more specific. But in my
case I wrote a small app to test these paramters until I got a result.
"Konstantin Knyazev" wrote:

> Errors that i'm trying to catch is 547 and 550. Sorry, can you give me a
> link to article? I can't find anything :(
> Best regards, Konstantin Knyazev
> "Mike (adoanywhere)" <Mikeadoanywhere@.discussions.microsoft.com> wrote in
> message news:B2304F57-46C6-4033-AB7D-639E89994413@.microsoft.com...
> use
> the
> not
> errors)
>
>