Monday, August 11, 2014

SQL command works from Management Studio but not command line (SqlCmd)

SQL Server has a command line component which allows users to run SQL commands directly or in files from the command line.  The command is sqlcmd (or Invoke-SqlCmd for Powershell).

I recently ran into a situation in which my commands would run just fine in SQL Server Management Studio, but failed from the command line.  Specifically, I was running a series of SQL commands stored in files.  I would run the files one after another.

What I found is that some of the files were missing the "USE [DatabaseName]" directive.  So, when those files ran, they were not targeting the correct database.  These worked in Management Studio however, because the earlier scripts had the directive, and Management Studio holds onto that setting.  That is, once a script sets the target database, it will use that database until instructed otherwise.  The command line tools do not do this.

No comments:

Post a Comment