You can use sqlcmd to run a Transact-SQL script file. A Transact-SQL script file is a text file that can contain a combination of Transact-SQL statements, sqlcmd commands, and scripting variables.
1. Save the file as Script.sql in the C drive.
To run the script file
1. Open a command prompt window.
2. In the Command Prompt window, type: sqlcmd -S Server1\instanceName -i C:\Script.sql
3. Press ENTER.
A list of Adventure Works employee names and addresses is written to the command prompt window.
To save this output to a text file
1. Open a command prompt window.
2. In the Command Prompt window, type: sqlcmd -S Server1\instanceName -i C:\Script.sql -o C:\Output.txt
3. Press ENTER.
How would you execute a sqlcmd with an input file -i somesql and output to a csv file -o somecsv with SSIS with parameters for the -i file and -o file locations?
ReplyDeleteI've used an 'Execute Process Task Editor' with the following properties set -
ReplyDeleteExecutable: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe
Arguments: -S servername -Q "exec dbname.dbo.storedprocname" -o outputlocation filename -E -w 480
How can I replace these hard coded properties with runtime supplied values?
ReplyDeleteYou need to execute the script like the following.This command is used only to execute the .sql scripts.Whatever you want to execute put it on a .sql file and execute it throug the following command.It will execute.
This command is not to execute the SP in SQL CMD and get the out put in text format.Kindly read the post carefully before executing it.
C:\Users\Debasish>sqlcmd -S DEV -i d:\test.sql -o d:\out.txt
Sorry for the late reply.