-1

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

Code from 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
tukan
  • 17,050
  • 1
  • 20
  • 48
user10172705
  • 13
  • 2
  • 2
    What is the question? What is the error you are getting? Fill those to your post. – tukan Aug 03 '18 at 14:35
  • 2
    And what happens when you just run a single sql file from the command prompt? That should have been your first debug step before even posting this question. – Squashman Aug 03 '18 at 14:36
  • 1
    Is this supposed to be part of the string: `-i"FILENAME.SQL" -oENTERED_PATH_FOR_OUTPUT_STRING\FILENAME.SQL.lng text**og`? – Compo Aug 03 '18 at 15:40
  • @ tukan - I'm getting 'Access is denied' error – user10172705 Aug 03 '18 at 17:07
  • @Squashman - Same error even if it's a single file. If I place scripts where my batch file is - it executes perfect and printing logs in the logs folder. However, the issue is getting error when scripts are in /scripts folder – user10172705 Aug 03 '18 at 17:09
  • @Compo - "%%G" is the file name. If I place all the scripts @C:\project\Queries_Testing\ (where the batch file is), batch file works fine and print logs in the logs folder. Issue is only when the scripts in C:\project\Queries_Testing\scripts folder – user10172705 Aug 03 '18 at 17:11
  • @user10172705, Compo was asking you what this is: `text**og` at the end of your `sql` command. – Squashman Aug 03 '18 at 17:17
  • Why do you ask for the `scriptsPath` and never use it in the rest of your code? Shouldn't you be using it with the `FOR` command? `for %%G in ("%scriptsPath%\*.sql")` – Squashman Aug 03 '18 at 17:19
  • @user10172705 - That's formatting this website added..It is...... for %%G in (*.sql) do sqlcmd /S %serverName% /d %databaseName% -U %userName% -P %password% -i"%%G" -o%outputFolder%\%%G.log – user10172705 Aug 03 '18 at 17:20
  • @user10172705, update your question by editing it. You literally have `text**og` as part of your code. – Squashman Aug 03 '18 at 17:22
  • @Squashman - I tried that way also.. using scriptsPath in FOR loop.. same issue. For some reason, it's sqlcmd is unable to read scripts from a different folder. – user10172705 Aug 03 '18 at 17:22
  • 1
    @user10172705, then its not a code problem and your question is off topic for StackOverFlow. – Squashman Aug 03 '18 at 17:23
  • @Squashman - is there a way to modify code to read sql files from another folder? Has anybody dealt with such before – user10172705 Aug 03 '18 at 17:25
  • 1
    @user10172705, I guess you don't understand what an access denied error means. If it can't read the script from that folder then it is a file permissions problem. The user doesn't have access to that folder. It is not a code problem. It is a file permissions problem. – Squashman Aug 03 '18 at 17:30
  • @Squashman - Seriously man.. you don't think i understand.. what you don't understand is, why it's giving error though it has permissions both scripts and logs folder. Help if you can, otherwise don't waste your time by writing such comments – user10172705 Aug 03 '18 at 17:48
  • @user10172705, that doesn't mean the permissions are inheriting to sub folders. – Squashman Aug 03 '18 at 17:53
  • 2
    @user10172705, regardless of the ghost text appended to the end of that string, you should still really be using, `-i"FILENAME.SQL" -o"ENTERED_PATH_FOR_OUTPUT_STRING\FILENAME.lng"`, which means using, `-i"%%G" -o"%outputFolder%\%%~nG.log"`. I'm also assuming that the `.sql` files are in `%scriptsPath%` so you should be setting that as the current working directory, or including it as a doublequoted string with a trailing backslash inside your `For` parentheses, prefixing the `*.sql`, e.g. `in ("%scriptsPath%\*.sql") do`. – Compo Aug 03 '18 at 18:09
  • Ah yes. If they were using the script path with the `FOR` command the `-i` option would be correct but the `-o` option would expand to `C:\project\Queries_Testing\logs\C:\project\Queries_Testing\Scripts\filename.sql.log` But I would think it would throw an error saying the specified path is invalid. – Squashman Aug 03 '18 at 18:15
  • @Squashman, I asked them to use `-i"%%G" -o"%outputFolder%\%%~nG.log"`, which when using `in ("%scriptsPath%\*.sql") do`, which I also suggested, should translate to `-i"FILENAME.SQL" -o"C:\project\Queries_Testing\Scripts\FILENAME.log"`. – Compo Aug 04 '18 at 15:35
  • @Compo my point was they said earlier they tried to use the script path but they never used the ~n modifier with the -o option so the output path would have thrown an error. – Squashman Aug 04 '18 at 15:44
  • @Squashman, okay thanks for clearing that up, I'll put my comments together as an answer and await feedback. – Compo Aug 04 '18 at 15:53
  • @Compo, yes, I was waiting for you to answer. You saw the issue with using `~n` modifier. We got way off track on this question, mostly because the error they were getting did not seem to fit the code they were using. – Squashman Aug 04 '18 at 17:12

1 Answers1

1

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.

Compo
  • 36,585
  • 5
  • 27
  • 39
  • I didn't get any error when I executed the batch file with the changes you mentioned above.. thank you !! However, logs folder is blank.. no logs were written.. thoughts? – user10172705 Aug 06 '18 at 13:58
  • @user10172705, I'm not sure, especially as I have no idea of the content of any of the `sql` files or if they are supposed to output anything. Also, I'm assuming that your `%output%` directory does exist, so perhaps you should start by removing the `@Echo Off` at the top and run your batch file, to verify that the correct `sqlcmd` lines are being run and to see if any messages are being output. – Compo Aug 06 '18 at 14:28
  • @user10172705, I note that the output you've shown should read, `Scripts\*.cmd`, have you missed out the backslash here, `"%scriptsPath%\*.sql"`? Also it appears that your `%dbname%` has a space in it, so perhaps you should be using `/d "%dbName%"` and the same for your `%usrName%`, i.e. `-U "%usrName%"`. I would perhaps suggest you modify the entire line to enclose the `%password%` with doublequotes too, e.g. `) Do sqlcmd /S "%serverName%" /d "%dbName%" -U "%usrName%" -P "%password%" -i"%%A" -o"%output%\%%~nA.log" `. If that works, let me know and I'll adjust my answer above to suit. – Compo Aug 06 '18 at 15:01
  • @Compo..it worked !! please edit your answer accordingly... this was the first step of my task.. I'll write to you when I have issues in my next step.. thanks again – user10172705 Aug 06 '18 at 16:26