Sql server stored procedure timeout from C++

[xargon]

Hi,

I have a stored procedure problem. I have a C++ application that calls a stored procedure. However, many times the application just terminates as the stored procedure times out. It does not happen all the times, but quite frequently (~30%). It never happens when I run the stored procedure from the SQL server osql console app. I do not understand why it should time out. Any suggestions on this would be highly appreciated.

Here is the stored procedure:


CREATE PROCEDURE DeleteTicketData
@ticketNum int
AS
declare @testVorgangID int
-- delete errors
DELETE FROM TestVorgangEinleseFehler_T WHERE TicketNumber = @ticketNum
Select @testVorgangID = TestVorgangID from TestTickets_T WHERE TestTicketID = @ticketNum
IF (@testVorgangID IS NOT NULL)
BEGIN
declare @testID int
DELETE From TestVorgaenge_T where TestVorgangID = @testVorgangID
DECLARE tests_cursor CURSOR FOR
SELECT TestID FROM Tests_T WHERE TestVorgangID = @testVorgangID
Open tests_cursor
-- Perform the first fetch
FETCH NEXT FROM tests_cursor INTO @testID
-- as long as there are more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM TestAktionen_T Where TestID = @testID
DELETE FROM TestDSPACETable_T Where TestID = @testID
DELETE FROM TestEreignisse_T Where TestID = @testID
DELETE FROM TestZustaende_T Where TestID =@testID
DELETE Tests_T WHERE CURRENT OF tests_cursor
FETCH NEXT FROM tests_cursor INTO @testID
END

CLOSE tests_cursor
DEALLOCATE tests_cursor
END
GO



I am totally puzzled as to why it should time out some of the times. Any suggestions would be really appreciated.

Thanks and have a great new year everyone :)

Pankaj

[ITGURU]

It will be be better to use create Parent and child relation between related tables and implement Cascade delete functionality for deleting record in related table if any record is deleted in a table instead of deleting individual tables rows through stored procedure or SQL Query. It also save time for executing any SQL Query.

Hope this will solve your problem.