I want to run a query with CMD file

I want to create a CMD file which can run a saved query(Data export) and save the result in a particular file. Please help.

In Windows download and install Microsoft Command Line Utilities 15 for SQL Server 32 or 64 bit.

In batch file: sqlcmd -S server\sql_server -d database -q "yourQuery" > file.txt or sqlcmd -S server\sql_server -d database -q "yourQuery" > \path_to_file\file.txt or sqlcmd -S server\sql_server -d database -i "\path_to_file\file.sql" > file.txt or sqlcmd -S server\sql_server -d database -i "\path_to_file\file.sql" > \path_to_file\file.txt

In Windows download and install Microsoft Command Line Utilities 15 for SQL Server 32 or 64 bit.

In batch file: sqlcmd -S server\sql_server -d database -q "yourQuery" > file.txt or sqlcmd -S server\sql_server -d database -q "yourQuery" > \\path_to_file\file.txt or sqlcmd -S server\sql_server -d database -i "\\path_to_file\file.sql" > file.txt or sqlcmd -S server\sql_server -d database -i "\\path_to_file\file.sql" > \\path_to_file\file.txt

sqlcmd -S server\sql_server -d database -q "yourQuery" > \path_to_file\file.txt

If I want to run above query, I should replace

server with Server name

database with Database name

yourQuery with query I want to run

path_to_file\file.txt with destination path and filename.

is my understanding correct?

Yes. A query may also be an executive procedure: sqlcmd -S server\sql_server -d database -q "EXEC yourProcedure" > \path_to_file\file.txt Very mportant: the bat file *.bat must be placed in the SQL server directory.

Thank you. I will test this in the next week and update about the result

Hi I have run some test below is the format. sqlcmd -S 192.168.1.1 -d picodb -q SELECT * FROM messagehistory mh LEFT JOIN sourceref sr ON mh.RefID = sr.RefID WHERE Timestamp_dt > NOW(); ORDER BY timestamp_dt DESC, timestamp_ms DESC LIMIT 100000

C:\test\testfile.csv the file generated was empty. Either my query is wrong or something is missing.

Save your query to a file: yourQuery.sql, save the file in the server directory and try this: sqlcmd -S 192.168.1.1 -d picodb -i "\path_to_file\yourQuery.sql" > C:\test\testfile.csv

below is the batch file

sqlcmd -S localhost -d picodb -i "C:\Program Files\MariaDB 10.5\data\picodb\Datahistory.sql" > D:\Test_SQL\testfile.csv

below is the error message received. "The system cannot find the file specified."

Try this: sqlcmd -U YourServerUserName -P 'YourServerPassword' -S localhost -d picodb -i "C:\Program Files\MariaDB 10.5\data\picodb\Datahistory.sql" > D:\Test_SQL\testfile.csv

The query is working with below format. however, I am not able to get the result in csv or txt file

cd /D "C:\Program Files\MariaDB 10.5\bin" mysql.exe -uroot -ppassword -h127.0.0.1 -Dpicodb <Datahistory.sql

I used below line in .sql file to save the data on file and it works. INTO OUTFILE 'C:/Data/Export/Export_Data.txt' However there is a catch. The query doesn't replace the file if the filename already exist.

If its possible to add date and time to the filename, then it will work perfect. Do you have any solution?