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?
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?
Microsoft SQL Server
Last Comment
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)?
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
=========
SQL Script:
=========
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%
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
You didn't use the -o parameter. You should use it to have the output sent to a file.
ASKER
I did try the -o parameter with the sql command line modified to:
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:
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.
sqlcmd -S SQLTEST3 -E -d HCDA -e -Q "exec uspCreateAllTables @dataSrc=%1, @yr=%2" -o %logfile_dest% >> %logfile_dest%
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%
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I was on vacation, but your suggestions helped me with my problems. Thank you for your assistance.
And in any DOS command you can always send the outcome of a command into a file by using the > operator:
Open in new window