sqlcmd命令在命令提示符可运行，保存为.bat文件无法执行的求助

Let’s break down why your sqlcmd command works in Command Prompt but fails when saved as a .bat file (or when run via Task Scheduler), and fix it step by step:

1. Specify the Full Path to sqlcmd.exe #

Command Prompt might have sqlcmd in its system PATH, but batch files or scheduled tasks often run with a stripped-down environment. To avoid "command not found" errors, use the full path to sqlcmd.exe. The exact path depends on your SQL Server version—for example:

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe"

(Adjust the version number 170 to match your installed SQL Server Client Tools version.)

2. Fix Double Quote Escaping in Batch Files #

Batch files handle double quotes differently than Command Prompt. Your original command’s quoted strings will break unless you escape them properly. Replace each single double quote (") with two double quotes ("") inside the batch file:

@echo off
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe" -S SERVERNAME -d DATABASE -U sa -P PASSWORD -q ""select * from TABLE"" -o ""C:\Export\sqlexport.csv"" -s ","

3. Check Permissions for Scheduled Tasks #

When running via Task Scheduler, the account executing the task may lack:

• SQL Server Access: Ensure the sa account is allowed to connect from the task’s execution context (e.g., if using a local system account, verify SQL Server allows local logins for sa).
• File System Permissions: The C:\Export folder needs write permissions for the task’s running account. Right-click the folder → Properties → Security → Add the task’s account and grant "Modify" permissions.

4. Handle Special Characters in Passwords #

If your sa password includes special characters (like &, !, ^, or spaces), you’ll need to escape them in the batch file:

• For !, prefix it with ^: -P MyP@ssw0rd^!
• For &, wrap the password in double quotes (and escape them): -P ""MyP@ss&w0rd""

5. Add Logging to Diagnose Errors #

To pinpoint exactly what’s failing, add logging to your batch file. This captures both output and error messages:

@echo off
set LOG_FILE=C:\Export\sqlcmd_execution.log
echo Starting export at %date% %time% >> %LOG_FILE%
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe" -S SERVERNAME -d DATABASE -U sa -P PASSWORD -q ""select * from TABLE"" -o ""C:\Export\sqlexport.csv"" -s "," >> %LOG_FILE% 2>&1
echo Export completed at %date% %time% >> %LOG_FILE%

Run the batch file manually first, then check sqlcmd_execution.log for errors like login failures, invalid paths, or permission denials.

6. Verify Task Scheduler Settings #

When setting up the scheduled task:

• Under Actions, ensure the "Start in" field is set to the directory containing sqlcmd.exe (or just use the full path in the command as we did earlier).
• Avoid using "Run whether user is logged on or not" unless necessary—this can restrict access to network resources or local paths. Test with "Run only when user is logged on" first.

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