Solved

Exception - hot to get SQLServer Err. number

Posted on 2000-05-18
7
381 Views
Last Modified: 2010-04-04
I'm using D5 and ADO components to access SQL Server. Is there a way to retrieve SQL server error code, when exception is raised? For example: I use ADOQuery to execute stored procedure, and the procedure is missing, how can I catch this error?
0
Comment
Question by:pvs001d
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Author Comment

by:pvs001d
Comment Utility
Adjusted points from 50 to 100
0
 
LVL 2

Expert Comment

by:IPCH
Comment Utility
Can you send part of you code?
0
 
LVL 15

Accepted Solution

by:
simonet earned 100 total points
Comment Utility
You should use the TADOStoredProc component.

After you call the EXecProc method, the parameter @RESPOST (or something like that... it's always the 1st parameter in the TADOStoredProc parameters list) will be filled with the result code of the execution.

Just read its value and you know what happened. The Online Books for SQL Server 7 lists all the error codes that can be returned.

Alex
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 15

Expert Comment

by:simonet
Comment Utility
Correction:

the parameter is called @RETURN_VALUE and every stored procedure automatically defines this parameter. Remember that you must set its direction as output in the TADOStoredProc's Parameter's editor.

Alex
0
 

Author Comment

by:pvs001d
Comment Utility
Answer accepted
0
 

Expert Comment

by:radu_antohi
Comment Utility
The paramater is called @RETURN_VALUE, but the parameter direction is not 'pdOutput', but 'pdReturnValue'.

Delphi Example:

var
  Ret: Integer;
begin
  ADOConnection1.Connected := True;

  ADOStoredProc1.Parameters.Clear;
  ADOStoredProc1.Parameters.CreateParameter('@RETURN_VALUE', ftInteger, pdReturnValue, 0, NULL);

  ADOStoredProc1.ExecProc;

  Ret := ADOStoredProc1.Parameters.ParamByName('@RETURN_VALUE').Value;

  ShowMessage(IntToStr(Ret));
end;
0
 

Expert Comment

by:radu_antohi
Comment Utility
Another way to get the error id is from the exception raised with RAISERROR by the SQL Server.

try
 SP.ExecProc;
except
 on E: EOleException do
 begin
  if (ADOConnection.Errors.Count > 0) then
  begin
   NativeError := ADOConnection.Errors[0].NativeError;
   Description := ADOConnection.Errors[0].Description;
  end
 end;
end;

The best way for error handling is to use both methods (exceptions raised with RAISERROR and RETURN_VALUE), because there are exceptions that are not catched by tke OLEDb provider or by Delphi. I don't know where is the problem.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now