How to output SQL SQLCMD result to a file in Windows?

I'm trying to mimic on how Oracle's send its SQL result to an a file when loading records to a table, creating indices, and creating tables in SQLCMD.

When creating a table in Oracle, we are able to generate an output file showing the structure of the table and the result which can either be that the creation of a table failed or all were successfully created.  When loading data to tables in Oracle, we can produce an output file stating how many records were read, how many records were loaded successfully, and the records that failed.  When creating indices in Oracle, we can produce an output file showing the indices that were created successfully, as well as those that failed.  (FYI, that we call these output files as log files.)

I'm trying to mimic the above abilities in SQLCMD but with no success.  I need to be able produce log files that shows what was performed correctly, along with a descriptive error message for those errors that may have occurred.  Can someone point me in the right direction?
Maria TorresData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
sqlcmd has a -o parameter for the output file.
And in any DOS command you can always send the outcome of a command into a file by using the > operator:
sqlcmd -S servername -E -i C:\scripts\myscript.sql > C:\logs\results.txt 

Open in new window

Maria TorresData AnalystAuthor Commented:
Yes, I know about the redirection within DOS, but how do I get SQL Server to write to the log when the task script is complete.  For example, the script that I have that loads data to a table, I want to be able to record the number of records that was loaded correctly, identifiy those records that did not load correctly, and at what time the script task was completed.    Is it possible within SQLCMD to do this (like Oracle's sqlplus command)?
Maria TorresData AnalystAuthor Commented:
I modified my batch file to have the redirection symbols ">>".  When I execute my batch file, I only get the Create message, followed by the date and time; the output of the script file is not capture.  How do I go about getting the output to go to the output file?  I want to give the user a message, stating whether the excution of the scripts were successful or not?

Below is the code for the batch and script files:

echo on
set directory=c:\Users\chiefadmin4\log\
set logfile=oAllTbl.txt

set logfile_dest= "%directory%%logfile%"
echo logfile_dest=%logfile_dest%

if not exist %directory% (
  mkdir %directory%
  echo Created Log directory)

echo Creating Tables %date% %time% >> %logfile_dest% 

sqlcmd -S SQLTEST3 -E -d HCDA -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" >> %logfile_dest%

Open in new window

SQL Script:

/****** Object:  StoredProcedure [dbo].[CreateAllTables]    Script Date: 4/6/2018 12:01:57 PM ******/

-- =======================================================================
-- Author:		XXXXXXXXX
-- Create date: April 1, 2018
-- Description:	Creates all required tables for a specific data source
-- =======================================================================

ALTER PROCEDURE [dbo].[CreateAllTables] 
	-- Parameters for stored procedure
	@dataSrc char(5), 
	@yr int
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.


	exec [dbo].uspCreateChargeTbl      @dataSrc, @yr;
	exec [dbo].uspCreateDiagnosisTbl   @dataSrc, @yr;
	exec [dbo].uspCreateEpisodeTbl     @dataSrc, @yr;
	exec [dbo].uspCreateHacTbl         @dataSrc, @yr;
	exec [dbo].uspCreateHcpcsTbl       @dataSrc, @yr;
	exec [dbo].uspCreatePoaTbl         @dataSrc, @yr;
	exec [dbo].uspCreatePqiTbl         @dataSrc, @yr;
	exec [dbo].uspCreateProcedureTbl   @dataSrc, @yr;


Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't use the -o parameter. You should use it to have the output sent to a file.
Maria TorresData AnalystAuthor Commented:
I did try the -o parameter with the sql command line modified to:  
sqlcmd -S SQLTEST3 -E -d HCDA -e -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" -o %logfile_dest% >> %logfile_dest%

Open in new window

This is the output that I received ====>      Creating Tables Fri 05/04/2018 14:54:25.96  
As you can see, nothing else was redirected to the output file.

When I change the code line so that it does not have the -o parameter, as follows:
sqlcmd -S SQLTEST3 -E -d HCDA -e -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" >> %logfile_dest%

Open in new window

I then get the following result:
     Creating Tables Fri 05/04/2018 10:39:48.51  
     exec uspCreateAllTables @dataSrc=ST_NJ, @yr=20

How do I go about getting the rest of the scripts (that are called by uspCreateAllTables) to be appended to the output file?  I need a trailing log of all the scripts that are called and their results (i.e., successfully executed or failed due to errors).

Thank you.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Creating Tables Fri 05/04/2018 14:54:25.96  
The above seems to be the only thing that the uspCreateAllTables is outputting. If you need to know about a table structure then you'll need to add the following code in your stored procedure:
sp_help TableName

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maria TorresData AnalystAuthor Commented:
Thank you the sp_help command enable me to send the structure to the output file.  Would you know if there is a command that enables me to send out the number of records that was loaded successfully onto the table (via BULK INSERT)?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try the SELECT @@ROWCOUNT. I can't confirm if it's work for BULK INSERT.
Maria TorresData AnalystAuthor Commented:
I was on vacation, but your suggestions helped me with my problems.  Thank you for your assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.