hi ,

I have a very strange problem.
I'm using mysql 5.013, innodb,windows xp, asp.

I have a stored procedure that call another ones.

DELIMITER $$

DROP PROCEDURE IF EXISTS `bcm`.`sp_SetUploadedCliToClients` $$
CREATE PROCEDURE `sp_SetUploadedCliToClients`(Transaction_Id integer)
BEGIN



DECLARE Cli_Count INTEGER;

DECLARE File_Id INTEGER;



SELECT TransactionLogName, TranSactionLogDescription FROM bcm.transactionlog

WHERE TransactionId=transaction_Id ORDER BY TransactionLogPriority, TransactionLogId;



SELECT COUNT(*) INTO Cli_Count FROM TransactionClis WHERE TransactionId=Transaction_Id;



IF Cli_Count > 0 THEN

INSERT INTO Files (FileName,FileDescription,FileGroupId,FileCreateDa te)

SELECT TransactionFileName, TransactionFileDescription,
GroupId,NOW() FROM filetransactions

WHERE TransactionId=Transaction_Id;

SET File_Id = LAST_INSERT_ID();

CALL sp_SetNewClients(Transaction_Id);

CALL sp_SetClientsGroups(Transaction_Id,File_Id);

CALL sp_CloseTransaction(Transaction_Id,File_Id);


END IF;

END $$

DELIMITER ;

It is too complicated to explain all the logic ill try to give the most importent things i think related to my problem.

CALL sp_SetNewClients - insert new rows to clients table with auto increment key.
insert into files - insert new rows to files table with auto increment key

after inserting new row to 'files' im getting the LAST_INSERT_ID() cause i need it for the next sps's.
But not for the sp_SetNewClients.

This sp most of the time return error (duplicate entry 'some number' for key 1).
the 'some number' seems to be the next incremented id for the 'files' table.
When i look in the tables after it fails it seems that the sp_SetNewClients fails.

when i run the sp queries manualy line after line using "query browser" it seems to be ok.

AFTER HOURS OF STRUGGLING I FOUND A WORKAROUND :
WHEN I CHANGED THE SP AND CALLED THE CALL sp_SetNewClients
before inserting a new file like this everything was ok.
Like this :
CALL sp_SetNewClients(Transaction_Id);

INSERT INTO Files (FileName,FileDescription,FileGroupId,FileCreateDa te)

SELECT TransactionFileName, TransactionFileDescription,
GroupId,NOW() FROM filetransactions

WHERE TransactionId=Transaction_Id;

SET File_Id = LAST_INSERT_ID();

CAN ANYONE UNDERSTAND WHAT WAS THE PROBLEM.

Thanks a lot
Barak