asked on

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?

Vitor Montalvão

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 Torres

ASKER

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 Torres

ASKER

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:

BATCH FILE
=========

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:
=========

USE [HCDA]
GO
/****** Object:  StoredProcedure [dbo].[CreateAllTables]    Script Date: 4/6/2018 12:01:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================
-- 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
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SET ANSI_PADDING ON;

    
	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;

END

Open in new window

Vitor Montalvão

You didn't use the -o parameter. You should use it to have the output sent to a file.

Maria Torres

ASKER

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.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maria Torres

ASKER

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)?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maria Torres

ASKER

I was on vacation, but your suggestions helped me with my problems. Thank you for your assistance.