How to display number of rows affected in a text file sql

Question

I am using a batch file to run SQL statements from another file. How do I log the result or error message on a text file in same folder. Here is what I have done:"

@echo off
sqlcmd.exe -S "(localdb)\v11.0" -i C:\SQLBatch\CreateLinkServer.sql -o C:\SQLBatchCreateLinkServer.txt

The log file (SQLBatchCreateLinkServer.txt) only contains "Changed database context to 'master'.". How do I get the batch to write all the details in the log such as no. of rows affected and which query is being excuted.

Thank you

In your .sql do you have the command `set nocount on`? If so, remove it, or add a script for `set nocount off`. See the opposite approach done [here](https://stackoverflow.com/q/2014129/1422451). — Parfait, Nov 04 '17 at 13:20

donsonde · Accepted Answer · 2017-11-05T18:29:01.237

0

The suggestion from Parfait helped resolve the issue.

I had this

@echo off sqlcmd.exe -S "(localdb)\v11.0" -i C:\SQLBatch\CreateLinkServer.sql -o C:\SQLBatchCreateLinkServer.txt

instead of

@echo off sqlcmd.exe -S "(localdb)\v11.0" -i C:\SQLBatch\CreateLinkServer.sql -o C:\SQLBatch\CreateLinkServer.txt

hence the count not writing to specified file.

The difference is that instead of the name of the file being SQLBatchCreateLinkServer.txt, it should have been CreateLinkServer.txt in a folder named SQLBatch

edited Nov 05 '17 at 18:29

answered Nov 04 '17 at 17:55

donsonde

87
8

1

What is the difference between these two commands? It is not readily clear. Include it in the body of answer. – Parfait Nov 04 '17 at 19:02

How to display number of rows affected in a text file sql

1 Answers1