Streamlining a batch file calling a sql file

Question

We currently have a scheduler to run audits. This scheduler will call a batch file with a parameter, and the batch file calls a sql script (based on the parameter), which in turn calls a stored proc.

For each audit that runs, a separate batch file, and sql file. Best case I'd like to combine the two below files into 1 file that can be used for every new audit. Worst case I'd at least like to combine to get 1 file for each audit instead of two. Hopefully you all can help?

Batch File

@echo on
Echo Running SQL Command File for '%1' Data Audit Check

del "D:\Internal_Reports\%1\%1.txt"

sqlcmd -S localhost -d database -i "D:\DataAudit\%1.sql"  -s "," > D:\Temp\%1.csv -I -W -k 1

if %ERRORLEVEL% GTR 0 COPY "D:\Temp\%1.csv" "D:\Internal_Reports\%1\%1.txt"

if %ERRORLEVEL% NEQ 0 goto Error_1
echo No Errors
goto end

:Error_1
if %ERRORLEVEL% NEQ 1 goto Error_2
echo No Errors
goto end

:Error_2
echo Errorlevel %ERRORLEVEL% 
set FileName=%1%2
echo Filename %FileName%
echo %ERRORLEVEL% > D:\ErrorLevel\%FileName%
EXIT /B %ERRORLEVEL%
:end

SQL File

set NoCount on
DECLARE
@createdBy varchar(16),

@dataAuditBatchId int, 

@createdDtTm datetime

select 
@createdBy = 'AutomatedAudit'
exec CreateNewDataAuditBatch @createdBy, @dataAuditBatchId output

-- Content Scripts
exec specificAuditStoredProc @createdBy, @dataAuditBatchId
select * from vAuditErrors where JobName in ('specificAuditStoredProc')
:exit(select Case When Counter = 0 then 0 Else 1 End 'errorCode'
from (select CAST(Count(*) AS varchar(4)) AS Counter from vAuditErrors 
    where JobName in ('specificAuditStoredProc'))
CountTable
)

Answer 1

Your best bet would be PowerShell in this case. You can combine both worlds of Batch Scripting and direct access to SQL.

Copy the below code into a text file: Audit.ps1

Create a File called: AuditFile.txt, put your SpecificAuditProc names on each line.

Then in your batch scheduler run this: "powershell -command "& c:\Audit.ps1 -name 'ProcName'"

Heres the code [Untested]:

       param([Parameter(Mandatory=$true)][String]$name="")

$createdBy = "AutomatedAudit"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=LOCALHOST;Database=HT;Integrated     Security=True"
$SqlConnection.Open()  

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = "[CreateNewDataAuditBatch]"
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.Parameters.Add("@createdBy", $creadtedBy)
$SqlCmd.Parameters.Add("@dataAuditBatchId ")
$SqlCmd.Parameters["@dataAuditBatchId"].Direction = [system.Data.ParameterDirection]::Output
$SqlCmd.ExecuteNonQuery()
$dataAuditBatchId = $Command.Parameters["@dataAuditBatchId"].value
$SqlCmd.Dispose() 


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = "[$name]"
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.Parameters.Add("@createdBy", $creadtedBy)
$SqlCmd.Parameters.Add("@dataAuditBatchId ", $dataAuditBatchId)
$SqlCmd.ExecuteNonQuery()
$SqlCmd.Dispose() 

$sqlcheck = @(Invoke-Sqlcmd -Query "select * from vAuditErrors where JobName in ('$name')" -ServerInstance "LOCALHOST\HT")
if ($sqlcheck.Count -ne 0) {
$sqlcheck > D:\Internal_Reports\$name\$name.txt
$sqlcheck.Count >> D:\ErrorLevel\$name
}

$Connection.Close()
$Connection.Dispose() 

Answer 2

This is a simple problem to solve in pure batch PROVIDED you clarify a few points.

1. What is "%1" here? The database name perhaps?
2. Do you have just one SQL file and if not, what elements in the sample you posted need to be replaced for different databases?

(sorry - this isn't really an answer per se, but I'll make some (i hope, useful) comments on your btch as posted. The SO comment facility really doesn't suit here...

@echo on
Echo Running SQL Command File for '%1' Data Audit Check

del "D:\Internal_Reports\%1\%1.txt"

sqlcmd -S localhost -d database -i "D:\DataAudit\%1.sql"  -s "," > D:\Temp\%1.csv -I -W -k 1

OK: so in all probability, %1 id the database name. So - why -d database and not -d %1 ??

Why the > D:\... and not '-o D:...` ??

if %ERRORLEVEL% GTR 0 COPY "D:\Temp\%1.csv" "D:\Internal_Reports\%1\%1.txt"

Ack! The fail-to-fail scenario in all its glory! Most commonly, you'd get ERRORLEVEL 0 from the sqlcmd BUT COPY can change ERRORLEVEL. If the COPY succeeds, then ERRORLEVEL will be 0, but if COPY fails, ERRORLEVEL would be non-zero and that's the value that will be used by the steps following...

if %ERRORLEVEL% NEQ 0 goto Error_1
echo No Errors
goto end

:Error_1
if %ERRORLEVEL% NEQ 1 goto Error_2
echo No Errors
goto end

You could arrive at :error_2 by simply executing

if errorlevel 2 goto error2

directly after the SQLCMD is executed (means "If errorlevel is 2 OR GREATER")

:Error_2
echo Errorlevel %ERRORLEVEL% 
set FileName=%1%2
echo Filename %FileName%
echo %ERRORLEVEL% > D:\ErrorLevel\%FileName%
EXIT /B %ERRORLEVEL%
:end

Suddenly an unexplained %2 appears...?