I basically want to create a batch script that has embedded sql commands and I was wondering if there is a way to do this using cmdsql. I'm using sql server 2008 r management studio and I've downloaded sqlcmd v2.0.
I made a batch script which attempted to connect to a database and execute a simple select statement, but when I ran the script it went into interactive mode after connecting to the database. It wouldn't execute the sql in the script, it would only allow a user to type in sql commands. The code is below:
sqlcmd -S <servername>\<instancename>
Select Number FROM Table1
GO
I changed the column/table/database etc. names as this is work-related but you get the idea. I'm quite new to batch scripting and don't have much experience, I have more experience with sql.
The standard way to feed input into a program is preparing the input and redirecting it via a | pipe. For example:
(
echo Select Number FROM Table1
echo GO
echo . . .
echo EXIT or QUIT or BYE...
) | sqlcmd -S <servername>\<instancename>
However, if the purpose of your Batch file is just to execute sql commands (and have no Batch logic), an easier way is to prepare a .txt file with the same input you would type via the keyboard:
sqlcmd -S <servername>\<instancename>
Select Number FROM Table1
GO
... and then feed that file into cmd.exe this way:
cmd < theFile.txt
In this case, don't forget to insert both the exit command for sql AND the exit command for cmd.exe!