Mysterious syntax error in TADOCommand
- From: "Ralph Moritz" <ralmoritz@xxxxxxxxx>
- Date: 22 Nov 2006 03:14:26 -0800
Hi everyone,
the following function loads a SQL script file into memory and executes
it using a TADOCommand. It works fine for most scripts, but I'm having
a problem with a certain stored procedure. I've verified the syntax of
the stored procedure by running it in the MS SQL Server query
analyzer.
The error message being returned when I try to execute the script is:
"Incorrect syntax near 'OUTPUT'"
The keyword OUTPUT appears in the script in the following context:
EXEC @Ret = spBilling_GetCallCharge
@Faxno = @Faxno,
@Date = @Date,
@Duration = @Duration,
@Charge = @Charge OUTPUT
Does anyone have an idea as to what could be causing this error? The
entire stored procedure in question is provided below the Delphi code.
TIA,
Ralph
[Delphi code]
function ExecuteScript(ScriptFile, ConnectionString: String): Boolean;
var
script: TStringList;
cmd: TADOCommand;
begin
if FileExists(ScriptFile) then
begin
cmd := TADOCommand.Create(nil);
cmd.ConnectionString := ConnectionString;
script := TStringList.Create;
script.LoadFromFile(ScriptFile);
cmd.ParamCheck := False;
cmd.CommandText := ListToString(script);
cmd.CommandType := cmdText;
cmd.ExecuteOptions := [eoExecuteNoRecords];
Result := True;
try
try
cmd.Execute;
except
on E: Exception do begin
Exception_Message := E.Message;
Result := False;
end;
end
finally
cmd.Free;
script.Free;
end;
end
else begin
Result := False;
end;
end;
[SQL stored proc]
CREATE PROCEDURE [dbo].[spBilling_GenerateData]
@From datetime,
@Until datetime
AS
/* Delete records that fall into period to avoid duplicates.
(Simply updating existing records doesn't seem to work.) */
DELETE FROM Billing_CallCharge
WHERE Date BETWEEN @From AND @Until
DECLARE @UserName varchar(30),
@Duration int,
@Date datetime,
@Time datetime,
@Charge float,
@Faxno varchar(20)
DECLARE c1 CURSOR FOR
SELECT
UserUniqueID,
Send_Time,
FaxDate,
FaxTime,
FaxNumber
FROM FaxArchiver
WHERE FaxDate BETWEEN @From AND @Until
AND LEN(FaxNumber) > 2
OPEN c1
FETCH NEXT FROM c1 INTO
@UserName,
@Duration,
@Date,
@Time,
@Faxno
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Date = CONVERT(varchar, @Date, 111) + ' ' + CONVERT(varchar,
@Time, 108)
DECLARE @Ret int
EXEC @Ret = spBilling_GetCallCharge
@Faxno = @Faxno,
@Date = @Date,
@Duration = @Duration,
@Charge = @Charge OUTPUT
-- Ignore records where call charge calculation failed.
IF @Ret != 0
BEGIN
FETCH NEXT FROM c1 INTO
@UserName,
@Duration,
@Date,
@Time,
@Faxno
CONTINUE
END
INSERT INTO Billing_CallCharge
(
UserName,
Duration,
Date,
Charge,
Faxno
)
VALUES
(
@UserName,
@Duration,
@Date,
@Charge,
@Faxno
)
FETCH NEXT FROM c1 INTO
@UserName,
@Duration,
@Date,
@Time,
@Faxno
END
CLOSE c1
DEALLOCATE c1
.
- Prev by Date: Project using Firebird
- Next by Date: Re: ADOQuery in thread hangs
- Previous by thread: Project using Firebird
- Next by thread: Cancelling an ADO Command takes too long
- Index(es):
Relevant Pages
|
