G'Day,
Env: Delphi 5.01, Informal MDAC 2.6 patch installed, MDAC 2.6, SQL7 SP2
I seem to be one of the lucky ones (not!!!) that "lost" all sensible error
reporting once upgraded to MDAC 2.6. I am now faced with hex values for
errors and not too friendly error strings. That aside. I have the following
stored procedure (See below). What is strange about this is that I can run
it heaps of times in the query analyzer and get no errors. However
occasionally / sporadically I get the error "OLE Error 80040E14" which is
"DB_E_ERRORSINCOMMAND". If I execute the stored procedure again on excatly
the same record. Then it succeeds. Calls to this stored procedure after the
initial failure work OK. Swicthing to another stored procedure and then back
again may cause this stored procedure to fail, but may not. Added to this
the above behaviour is not restricted to this procedure alone but seemingly
random other stored procedures.
I thought I read that things could get a little problematic if you had a
return value from a stored procedure as well as output paramaters, but there
are stored procedures that have no output paramaters and just return values,
and they fail too. Irrespective of whether I am using .Open or .ExecProc
this appears to happen.
I apologise that this is so long and complex, but I am really stumped on
this one. Can anyone shed any light on this for me?
TIA
To set up my stored procedure I use the following Delphi code:
procedure TDM.SQL7_Init_StoredProcParams(var Use_StoredProc :
TADOStoredProc; The_StoredProc : TAppStoredProcs);
////////////////////////////////////////////////////////////////////////////
////
// PURPOSE: Reads the information from the global variable that contains all
the
// stored procedure information. It then uses this information to create
// the paramaters for the suppied stored procedure
const
PROC_NAME = 'TDM.SQL7_Init_StoredProcParams';
ERR_MSG = 'I was unable to initialise the paramaters';
var
c : SmallInt;
begin
try
with Use_StoredProc, AppStoredProcsInfo[The_StoredProc] do
begin
Use_StoredProc.Close;
//Assign Name
Use_StoredProc.ProcedureName := ProcName;
//Create all the paramaters
Parameters.Clear;
Parameters.CreateParameter('RETURN_VALUE', ftInteger,
pdReturnValue, 0, null);
for c := Low(Params) to High(Params) do
begin
Parameters.CreateParameter(Params[c].ParamName,
Params[c].DataType, Params[c].Direction, Params[c].Size, null);
end;
end;
except
on E : Exception do
Handle_AppExceptions(UNIT_NAME, PROC_NAME, ERR_MSG, E.Message,
FALSE);
end;
end;
And to call them:
function SQL7_Open_StoredProc(Use_DB : TADOConnection; var The_StoredProc :
TADOStoredProc;
RaiseException : boolean = TRUE) : integer;
////////////////////////////////////////////////////////////////////////////
////
// PURPOSE: Opens the specified stored procedure
var
sMsg : string;
begin
//Default behavior
result := 0;
with The_StoredProc do
begin
Close;
Connection := Use_DB;
Prepared := TRUE;
Open;
//Return the error code if we can
if The_StoredProc.Parameters.FindParam('RETURN_VALUE') <> nil then
result :=
The_StoredProc.Parameters.ParamByName('RETURN_VALUE').Value;
//Now raise an exception
if result <> 0 then
begin
//Only raise the exception should the user want to
if not RaiseException then Exit;
if Use_DB.Errors.Count = 0 then
begin
sMsg := 'An error occurred while executing the stored procedure
[' + The_StoredProc.ProcedureName + ']';
raise ESQL7.Create(sMsg);
end else
begin
sMsg := 'An error occurred while executing the stored procedure
[' + The_StoredProc.ProcedureName + ']';
sMsg := sMsg + 'SQL Server reported the error as: [' +
IntToStr(Use_DB.Errors[0].NativeError) + Use_DB.Errors[0].Description + ']';
raise ESQL7.Create(sMsg);
end;
end;
end;
end;
function SQL7_Exec_StoredProc(Use_DB : TADOConnection; var The_StoredProc :
TADOStoredProc;
RaiseException : boolean = TRUE) : integer;
////////////////////////////////////////////////////////////////////////////
////
// PURPOSE: Allows a stored procedure to be executed
var
sMsg : string;
begin
//Default behavior
result := 0;
with The_StoredProc do
begin
Close;
Connection := Use_DB;
Prepared := TRUE;
ExecProc;
//Return the error code if we can
if The_StoredProc.Parameters.FindParam('RETURN_VALUE') <> nil then
result :=
The_StoredProc.Parameters.ParamByName('RETURN_VALUE').Value;
//Now raise an exception
if result <> 0 then
begin
//Only raise the exception should the user want to
if not RaiseException then Exit;
if Use_DB.Errors.Count = 0 then
begin
sMsg := 'An error occurred while executing the stored procedure
[' + The_StoredProc.ProcedureName + ']';
raise ESQL7.Create(sMsg);
end else
begin
sMsg := 'An error occurred while executing the stored procedure
[' + The_StoredProc.ProcedureName + ']';
sMsg := sMsg + 'SQL Server reported the error as: [' +
IntToStr(Use_DB.Errors[0].NativeError) + Use_DB.Errors[0].Description + ']';
raise ESQL7.Create(sMsg);
end;
end;
end;
end;
CREATE PROCEDURE [dbo].[sp_AddRundownSlug] @ProgramID TProgramID, @DayID
TDayID,
@TimeOfDayID TTimeOfDayID,
@NewSlugID TSlugID OUTPUT AS
DECLARE @Err Int
DECLARE @NextSlugID TSlugID
DECLARE @NewSlugElementID TSlugElementID
-- Get the next slug ID
BEGIN TRANSACTION
SELECT @NextSlugID = (SELECT Max(SlugID) + 1 FROM tblSlugs
WHERE ProgramID = @ProgramID AND
DayID = @DayID AND
TimeOfDayID = @TimeOfDayID)
-- NBNBNB This is temporary and is for the news system specific
implementation
-- Start slugs at 10
SELECT @NextSlugID = IsNull(@NextSlugID, 10)
-- Add the slug
INSERT INTO tblSlugs (ProgramID, DayID, TimeOfDayID, SlugID, Description)
VALUES (@ProgramID, @DayID, @TimeOfDayID, @NextSlugID, 'New Slug')
SELECT @Err = @@ERROR
IF @Err <> 0 GOTO error
-- Now add the slug element
EXEC sp_AddRundownElement @ProgramID, @DayID, @TimeOfDayID, @NextSlugID,
@NewSlugElementID
SELECT @Err = @@ERROR
IF @Err <> 0 GOTO error
-- Assign the element id to the output paramater
SELECT @NewSlugID = @NextSlugID
COMMIT TRANSACTION
-- Return Success
RETURN @Err
/* Error Handling */
error:
ROLLBACK TRANSACTION
RETURN @Err
GO
------------------------------------------------------------------------
--Donovan [[EMAIL PROTECTED]]
Donovan J. Edye [www.edye.wattle.id.au]
Namadgi Systems, Delphi Developer [www.namsys.com.au]
Voice: +61 2 6285-3460 Fax: +61 2 6285-3459
TVisualBasic = Class(None);
Heard just before the 'Big Bang': "...Uh Oh...."
------------------------------------------------------------------------
GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
replacement. Also includes freeware delphi windows explorer components.
------------------------------------------------------------------------
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
