Executing SQL Server Scripts From Command Line With Append Output File


I know you feel SSMS is pretty easy but if you have 1000 Sql scripts to be deployed ? Do you still prefer SSMS ?

So to avoid wasting time best way is to run scripts using command prompt or batch file.

    /**** Syntax: ****/
sqlcmd -S MY_SERVER\MY_INSTANCE  -U domain\windows_user -P my_password -Drive:\saurabh\saurabh.sql
sqlcmd -S MY_SERVER\MY_INSTANCE  -U Sql_user -P my_password -i Drive:\saurabh\saurabh.sql

Here every thing is self explanatory and  "Drive:\saurabh\saurabh.sql" is sql script having code "Select @@servername"

/**** Example ****/
1.  
Syntax: sqlcmd -S Server\Instance   -U user -P password -i path\file.sql

Example: sqlcmd -S SAURABHSINHA-PC\SQL2014   -U Saurabh -P saurabh -i C:\SAURABH\cmd.sql




2. If you want windows authentication , best way is open command prompt with "Run as Administrator"
Syntax:sqlcmd -E -S Servername\instance   -Drive:\folderpath\cmd.sql
Example:





/**** Query output of sql server from command prompt in flat file ****/
è  Here using “>;” will clear output file and write data into it

sqlcmd -S SAURABHSINHA-PC\SQL2014   -U Saurabh -P saurabh -i C:\SAURABH\cmd.sql > C:\SAURABH\out.txt

sqlcmd -E -S SAURABHSINHA-PC\SQL2014   -i C:\SAURABH\cmd.sql  C:\SAURABH\out.txt


/**** Query output of sql server from command prompt Appended in flat file ****/
è  Here using “>>” will append output file

sqlcmd -S SAURABHSINHA-PC\SQL2014   -U Saurabh -P saurabh -i C:\SAURABH\cmd.sql >> C:\SAURABH\out.txt

sqlcmd -E -S SAURABHSINHA-PC\SQL2014   -i C:\SAURABH\cmd.sql >> C:\SAURABH\out.txt


3 comments:

  1. How to append with sqlcmd? This seemed like a simple question - but you were the ONLY one with the answer! Thank you!

    ReplyDelete
  2. how to write error message of sqlcmd to output file

    ReplyDelete