current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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!

share|improve this answer
add comment
up vote 1 down vote

You could try to read the documentation. A synopsis of the documentation is available from the command line by typing sqlcmd -?

To run a single SQL-Server query from within a batch file, using the default database:

sqlcmd -S <servername>\<instancename> -Q "Select Number FROM Table1"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.