I'm trying to create a batch file to execute all the scripts from a folder and print logs in a different folder and getting access id denied error. Below is my folder structure. Thanks in advance.
Scripts path - C:\project\Queries_Testing\Scripts
Logs - C:\project\Queries_Testing\logs
Batch file - C:\project\Queries_Testing\executeQueries.bat
@ECHO OFF
setlocal enabledelayedexpansion
set /p serverName=Enter DB Servername :
set /p dbName=Enter Database Name :
set /p userName=Enter Username :
set /p password=Enter password :
set /p scriptsPath=Enter Scripts Path :
set /p output=Enter path for output:
for %%G in (*.sql) do sqlcmd /S %serverName% /d %dbName% -U %userName% -P %password% -i"%%G" -o%output%\%%G.lng text**og
ECHO Finished!
pause
Here's an example script which uses the information I provide in my comments.
executeQueries.bat
@Echo Off
Set /P "serverName=Enter DB Server Name: "
Set /P "dbName=Enter Database Name: "
Set /P "usrName=Enter User Name: "
Set /P "password=Enter User Password: "
Set "scriptsPath=C:\project\Queries_Testing\Scripts"
Set "output=C:\project\Queries_Testing\logs"
For %%A In ("%scriptsPath%\*.sql"
) Do sqlcmd /S "%serverName%" /d "%dbName%" -U "%usrName%" -P "%password%" -i"%%A" -o"%output%\%%~nA.log"
Echo Finished!
Pause
The code above uses the provided locations for the scripts and logs directories with a standard Set command. Once you've verified that it works, you may revert back to the Set /P format as used in the 4 lines above them.
Please note that your script does not provide any verification routines for the end users input information. If they input incorrect information, the sqlcmd will be run using it, and may be problematic or produce errors.
Please provide feedback accordingly; thank you.