如何编写批处理调用sqlcmd查询SQL Server并记录不可达实例

Here's a modified version of your batch file that adds error checking to log unreachable instances. It leverages sqlcmd's exit codes to detect connection failures:

@echo off
setlocal

:: Initialize log files (clear existing content if they exist)
echo. > Destination\QueryResult.csv
echo. > UnreachableInstances.txt

echo Starting SQL instance query process...
echo.

:: Loop through each instance in the list file
for /F "tokens=*" %%S in (SQLLIST.txt) do (
    echo Processing: %%S
    
    :: Run the SQL query and append results to CSV
    sqlcmd -E -h -1 -W -M -S %%S -i "C:\Foldername\Query.sql" >> "Destination\QueryResult.csv" -s ","
    
    :: Check if sqlcmd failed (non-zero exit code = connection error)
    if errorlevel 1 (
        echo FAILED to connect to %%S
        echo %%S >> UnreachableInstances.txt
    ) else (
        echo Successfully queried %%S
    )
    echo.
)

echo Process complete!
echo Query results saved to: Destination\QueryResult.csv
echo Unreachable instances logged to: UnreachableInstances.txt

endlocal

Key Improvements Explained: #

1. Log Initialization: The script clears both the result CSV and unreachable instances log at startup to avoid appending to old data.
2. Error Level Checking:
   • sqlcmd returns 0 on successful execution, and a non-zero code (usually 1) if it can't connect to the instance.
   • if errorlevel 1 triggers when the previous command failed, writing the instance name to UnreachableInstances.txt.
3. Progress Feedback: Added echo statements to show which instance is being processed and whether it succeeded or failed, making debugging easier.
4. Quoted Paths: Wrapped file paths in quotes to handle spaces in folder/filenames (a common gotcha in batch scripts).

Optional Tweaks: #

• If you want to suppress sqlcmd's error messages from appearing in the console, modify the sqlcmd line to:

  sqlcmd -E -h -1 -W -M -S %%S -i "C:\Foldername\Query.sql" >> "Destination\QueryResult.csv" -s "," 2>nul
  

  The 2>nul redirects error output to the null device.

内容的提问来源于stack exchange，提问作者polo