Hi

I am running MYSQL 5.0 ON WINXP pro.

I have the following procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS LSMW_COST_SWITCH $$
CREATE PROCEDURE LSMW_COST_SWITCH()
BEGIN
SELECT * FROM C003_COSTING_LSMW ORDER BY NG_PART ASC limit 100;
SELECT * INTO OUTFILE 'E:/Work/Processed Files/test.txt' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' FROM C003_COSTING_LSMW ;

END $$

DELIMITER ;

This fails returning the following error message:
"You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near '$$ DELIMITER' at line 9

If I remove the SELECT INTO... CLAUSE the procedure works just fine.

I have tried the SELECT * INTO OUTFILE 'E:............. as a simple query , and it works just fine.
Can someone pls help?
thanks and greets

It looks like there are a lot of permissions issues when using SELECT INTO OUTFILE...

Also, the docs specify that LOAD DATA INFILE cannot be used in stored procedures, but don't mention SELECT INTO OUTFILE.

But since they are basically counterparts and require the same permissions to execute, my guess would be that you can't use it either.

Thanks Jfulton, I did look into the 'Load data infile ' and its complement 'Select into outfile', and though Load.... cannot be embedded in a stored procedure the Select INTO... is treated as a select statement extension and CAN be used in a procedure.
I did some fixes here and there and got it to work partially
i.e. Create procedure....
Begin

Select ........ FIELDS TERMINATED BY '|' ........

END

By trial and error for some silly reason if I had the 'LINES TERMINATED by '\n\r' then I got the error but FIELDS TERMINATED '|' seems to be working okay.

In anycase you are right this seems to be a permissions issue, which one is the problem.! Maybe this will be of help to some in future.

thanks for the response.
greets