Hi,
it is quite uncommon for MS-SQL Server-DBAs to work in command line (or much more uncommon in comparison to oracle or pg-sql DBAs). Nevertheless there is a tool from the stone-age called sqlcmd which has very nice features and which is actively developed by Microsoft up to now. See here: http://www.sqlserverspecialists.com/2012/10/sqlcmd-commands-sql-server-tool.html and https://msdn.microsoft.com/en-us/library/ms162773.aspx .
Main thing is:
- connect to different db
- call dos commands (mixing up OS and DB)
- set and use variables
- load sql files for execution
- redirect output to filestreams (file, stdout, stderr)
- change output format to XML
... even if this sounds quite limited it is still helpful for many purposes like e.g.: scheduled tasks. Especially the OS / DB combination is often very useful if you need to wait for the execution of A to start db-job B.
In SQL Server Management Studio the so called sqlcmd-mode to be used inside the query window is implemented which allows a limited feature set in comparison to the command line features seen above. See: https://msdn.microsoft.com/en-us/library/ms174187.aspx
Using SQL Server Agent you need to use an Operating-System Job-Step to work with sqlcmd (like with any other OS application). See an article about differences between TSQL-JobSteps and CmdExec using sqlcmd here: http://www.travisgan.com/2014/04/sql-server-agent-job-and-sqlcmd.html
So finally: with sqlcmd scripting can be improved by using variables, call different scripts and call the OS. In the original command line version it has some possibilities (like opening a dedicated admin connection) which are nice, but another very helpful thing is that it is kept small and performant.
Kr,
Daniel
it is quite uncommon for MS-SQL Server-DBAs to work in command line (or much more uncommon in comparison to oracle or pg-sql DBAs). Nevertheless there is a tool from the stone-age called sqlcmd which has very nice features and which is actively developed by Microsoft up to now. See here: http://www.sqlserverspecialists.com/2012/10/sqlcmd-commands-sql-server-tool.html and https://msdn.microsoft.com/en-us/library/ms162773.aspx .
Main thing is:
- connect to different db
- call dos commands (mixing up OS and DB)
- set and use variables
- load sql files for execution
- redirect output to filestreams (file, stdout, stderr)
- change output format to XML
... even if this sounds quite limited it is still helpful for many purposes like e.g.: scheduled tasks. Especially the OS / DB combination is often very useful if you need to wait for the execution of A to start db-job B.
In SQL Server Management Studio the so called sqlcmd-mode to be used inside the query window is implemented which allows a limited feature set in comparison to the command line features seen above. See: https://msdn.microsoft.com/en-us/library/ms174187.aspx
Using SQL Server Agent you need to use an Operating-System Job-Step to work with sqlcmd (like with any other OS application). See an article about differences between TSQL-JobSteps and CmdExec using sqlcmd here: http://www.travisgan.com/2014/04/sql-server-agent-job-and-sqlcmd.html
So finally: with sqlcmd scripting can be improved by using variables, call different scripts and call the OS. In the original command line version it has some possibilities (like opening a dedicated admin connection) which are nice, but another very helpful thing is that it is kept small and performant.
Kr,
Daniel
No comments:
Post a Comment