How to print results in SQLcmd window

Question

I'tm trying to show in SQLcmd window the results of these queries:

SELECT SYSDATETIME() AS "date";

PRINT 'EXTRAÍDO POR:'
SELECT SUSER_NAME() AS "Owner"

PRINT 'PASSWORD SETTINGS';
exec xp_loginconfig;

PRINT 'PASSWORD SETTINGS';
select * from sys.sql_logins;

And using this command in SQLcmd:

sqlcmd -S myServer -i inputpath -s, -o outputPath(like C:\log.xls) -e

So the sqlcmd output the file and does not show nothing in sqlcmd screen. How can i print these "Print" commands in the sqlcmd screen? (command promt).

When I run it on SSMS works well, like:

(1 row affected) OWNER:

(1 row affected) PASSWORD SETTINGS

(8 rows affected) PASSWORD SETTINGS

I would like to show in the prompt command this, like in the SSMS.

Thanks!

Answer 1

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 -o switch.

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:

EXTRAÍDO POR:
PASSWORD SETTINGS
PASSWORD SETTINGS

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:

1. Add SET NOCOUNT ON; to the top of your SQL script
2. Add PRINT '(' + CONVERT(VARCHAR(10), @@ROWCOUNT) + ' rows affected)'; after each T-SQL query (i.e. after each SELECT and EXEC in this script).

After doing those 2 steps, you will see the following in the command window:

(1 rows affected)
EXTRAÍDO POR:
(1 rows affected)
PASSWORD SETTINGS
(8 rows affected)
PASSWORD SETTINGS
(18 rows affected)

ALSO:

1. 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).

2. 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:

   EXTRAÍDO POR:

   in the "Messages" tab. But, in my command prompt window, I see:

   EXTRAIDO POR:

   Command prompt shows " I " instead of " Í ". To fix this, I needed to run the following in the command prompt window before running SQLCMD:

   chcp 1252
   

---

P.S. No, GO is not required in this script.

Answer 2

If you're open to using PowerShell, you can do something like this.

sqlcmd -i inputpath -S myServer -E -d MYDB | Tee-Object -file "C:\log.xls"