Calling stored procedure. Get error "Cursor not returned from Query".

[Tom Reiertsen]

Hello.

I am having some troubles calling a Stored Procedure using TSQLStoredProc.
I get the error "Cursor not returned from Query".

I think the error is related to the fact that the stored procedure is
returning data selected from a temporary (dataset)variable (see the included
SP).

It can be used fine from ASP.Net so I guess it must be related to how I try
to call it from Delphi.

Please help!

Best Regards,

Tom Reiertsen,
Reiertsen Software Systems.

CREATE PROCEDURE GetDepartureTimeFromDateAndPlaces
@departuredate datetime, /* Date of departure */
@departureplace NVARCHAR(50), /* Name of departure place */
@arrivalplace NVARCHAR(50) /* Name of arrival place */
AS
BEGIN

DECLARE @tmptable TABLE
(
RouteID int,
FromID int,
ToID int,
Time datetime,
DepPlace nvarchar(50),
ArrPlace nvarchar(50),
ArrTime datetime,
DepDate datetime,
Number int,
TicketName nvarchar(50),
TicketSeats int,
TicketPrice int,
TicketType nvarchar(50),
FreeSeats int,
Campaign int,
CampaignName nvarchar(50),
Capacity int
)

INSERT INTO @tmptable
SELECT DISTINCT
R.RouteID AS RouteID, DepS.StopID AS FromID, ArrS.StopID AS ToID,
DepS.[Time] AS Time, DepP.Name AS [From], ArrP.Name AS [To],
ArrS.[Time] AS ArrTime, R.[Date] AS Date, R.Number AS Number, C.Name
AS TicketName, C.Seats AS TicketSeats, C.Price AS TicketPrice,
C.CampaignID AS TicketType, C.Seats -
(SELECT SUM(R.Seats)
FROM Reservation R
WHERE R.RouteID = C.RouteID) AS FreeSeats, C.CampaignTicketID
AS Campaign, C.Name AS CampaignName, V.Capacity - ISNULL
((SELECT SUM(Res.Seats)
FROM Reservation Res
WHERE R.RouteID = Res.RouteID), 0) AS Capacity
FROM Route R INNER JOIN
Stop DepS ON R.RouteID = DepS.RouteID INNER JOIN
Stop ArrS ON R.RouteID = ArrS.RouteID AND DepS.[Time] < ArrS.[Time]
AND DepS.StopID <> ArrS.StopID INNER JOIN
Place DepP ON DepS.PlaceID = DepP.PlaceID INNER JOIN
Place ArrP ON ArrS.PlaceID = ArrP.PlaceID INNER JOIN
Vessel V ON ISNULL(ArrS.VesselID, R.VesselID) = V.VesselID LEFT
OUTER JOIN
CampaignTicket C ON R.RouteID = C.RouteID AND C.FromPlace =
@departureplace AND C.ToPlace = @arrivalplace
WHERE (R.[Date] = @departuredate) AND (DepP.Name = @departureplace) AND
(ArrP.Name = @arrivalplace)
ORDER BY DepS.[Time], TicketPrice

SELECT
RouteID,
FromID,
ToID,
Time,
DepPlace,
ArrPlace,
ArrTime,
DepDate,
Number,
TicketName,
TicketSeats,
TicketPrice,
TicketType,
FreeSeats,
Campaign,
CampaignName,
MIN(CAPACITY) as Capacity
FROM @tmptable
GROUP BY RouteID, FromID, ToID,Time,DepPlace,ArrPlace,ArrTime,
DepDate,
Number,
TicketName,
TicketSeats,
TicketPrice,
TicketType,
FreeSeats,
Campaign,
CampaignName
ORDER BY Time

END

[Tom Reiertsen]

"Tom Reiertsen" <t...@reiertsen.com> skrev i melding
news:4106...@newsgroups.borland.com...

>
> I am having some troubles calling a Stored Procedure using TSQLStoredProc.
> I get the error "Cursor not returned from Query".
>

Ok, I finally found the answer myself. For the record, here's the solution:

Add the line:

SET NOCOUNT ON

in the beginning of the stored procedure to solve this. I'm not quite sure
that the positioning of this statement needs to be at the beginning, but I
had to place it before any INSERT statements I made in my stored procedure.