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 -i 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
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"
/**** 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
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
sqlcmd -E -S SAURABHSINHA-PC\SQL2014 -i C:\SAURABH\cmd.sql >> C:\SAURABH\out.txt
How to append with sqlcmd? This seemed like a simple question - but you were the ONLY one with the answer! Thank you!
ReplyDelete