Source Command

I have tried many, many ways to get the source command to work on a simple file, create.sql. The command line client always returns "failed to open file 'c:\sql\create.sql', error 2".

I have tried putting the file in \bin, \scripts, c:\sql, \mysql server 5.0, reversing the slash, all to no avail. I have even attempted to invoke the file from a command prompt using, 'mysql -u root -p db_name <create.sql' and all it's possibilities with the consistent response, 'the system cannot find the file specified'.

If I use a ; at the end of the line, I get some version information and an error 1064 .... in correct syntax at line one. I assume that is in reference to the " ; ".

I have modified the text file with even simpler commands.

The funny thing is, the commands in the text file work great (if I don't have a typo) when I enter them individually at mysql> , but I'm tired of repetitive typing to learn mysql.

Oh, I checked the global variable basedir which came back c:\program files\mysql\mysql server 5.0. Of course I tried putting the file create.sql there?!?

Can anyone help me solve this? I'd like to move on to harder things I know this sounds confusing, I've done a lot of troubleshooting. Thanks for reading and replying.

Spent all day yesterday with the same problem.
Tried again today & is working fine.Maybe mysql needed to be shutdown or maybe I was using faulty syntax ??

Win 2000 / mysql 5.0

1. Create a simple test file called
batchtest1.sql or
batchtest1.txt or
batchtest1.bat.

save the file in your c:/mysql folder.


In the Command :

mysql> source c:\mysql\batchtest1.sql

Worked for me.

Hope this helps

Gord

Is the file on the same (physical) machine as the MySQL server (in other words: are you connecting to localhost). If it isn't, you can't use the source command, or you'll have to upload the file first using FTP.

I am attempting to use 'source' from the localhost. (ie the same physical machine as the server.

This is the command I use to get to mysql>. Maybe the problem lies here. At the command prompt I enter:
mysql -u root -p
and then enter the password. I'm wondering if it's a server setting issue ??

I tried again today by renaming the file .txt and .bat as suggested earlier, still getting the message "failed to open file 'c:\sgl\test.sgl', error: 2"

Please continue to offer suggestions.

Thanks
David

Thanks Gord for taking the time to check it out and respond. I'm sorry you too spend so much time on it. I'm still not getting the file to open and work.

Can you provide any further info to what gave you sucess?

Thanks,
David

I know this is in older post, but I had the same issue.

The way I got it to work was put the .sql file in the bin directory and the the command like this:

mysql> SOURCE create_tbl.sql;