You are not seeing anything in the command window because you are telling SQLCMD.EXE to direct all output to a file due to using the
-o switch. If you want results to go to a file and messages to go to the screen (and not into the file), then you cannot use the
Instead, you will need to manually redirect output (i.e. "stdout") to the file using
> on the command line, as in
> log.xls. However, by default,
PRINT messages will also go to "stdout", so you need to send those to "stderr" by using the
-r1 switch (so that they will be displayed instead of going into the file). That will leave you with (writing to C:\ is not a good practice, so maybe create a TEMP directory/folder, or something like that):
SQLCMD -S (local) -i script.sql -s, -e -r1 > C:\TEMP\log.xls
You will then see the following in the command window:
The "(18 rows affected)" will still be in the output file. If you want that in the window and not in the file, then you need to:
SET NOCOUNT ON; to the top of your SQL script
PRINT '(' + CONVERT(VARCHAR(10), @@ROWCOUNT) + ' rows affected)'; after each T-SQL query (i.e. after each
EXEC in this script).
After doing those 2 steps, you will see the following in the command window:
(1 rows affected)
(1 rows affected)
(8 rows affected)
(18 rows affected)
- You might need to remove the
-e switch if sending the original SQL queries in script.sql to the top of the output file causes a problem when opening it in Excel (which I assume you are wanting to do given the .xls file extension).
Depending on your OSs regional setting and the code page associated with the default collation of your login's default database, you might need to change the current code page of the command window so that the
Í character displays properly.
Meaning: I am in the US so my default code page for command windows is 437, yet the collation of my login's default database is a
Latin1_General_* collation which uses code page 1252. In SSMS,
PRINT 'EXTRAÍDO POR:' displays as:
in the "Messages" tab. But, in my command prompt window, I see:
Command prompt shows " I " instead of " Í ". To fix this, I needed to run the following in the command prompt window before running SQLCMD:
GO is not required in this script.