CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2012export
NilsSoderstrom
NilsSoderstrom

Reputation: 21

Schedule exports to Text file

I have a normal SQL query:

SELECT * FROM fmain

and I want the result of this query to be saved into a text file on a regular basis.

That is to say I want the query to run itself and then populate the text file every morning at 8 AM (or so).

I am running SQL Server Management Studio 2012, and am a novice SQL user, so please try and answer in as much explanatory fashion as possible.

Please help me solve this.

Cheers, Nils

Upvotes: 1

Views: 1597

Answers (1)

pas
pas

Reputation: 98

you can do that with a Sql script, a batch and a scheduled Task.

First of all, create the Sql Script in your preferred directory, for example C:\TEMP\MySelect.sql, with your sql statement.

EXEC xp_cmdshell 'SQLCMD -S . -d YOUR_DATABASE_NAME -Q "EXEC SELECT * FROM fmain" -s "," -o "C:\TEMP\MY_FILE.txt" -h-1 -W -s"|"';

Then create the batch file, like C:\TEMP\call_sql.bat

@ECHO OFF
echo "Start"
sqlcmd -i C:\TEMP\MySelect.sql

Then configure the Task Scheduler to run every morning at 8 AM. You can do that by the program interface or using a script:

schtasks /create /sc DAILY /ST 08:00  /tn "EXPORT fMain every DAY Script" /tr "C:\TEMP\call_sql.bat"

I hope this can help you

Upvotes: 2

Related Questions