bash script stuck in single line

Question

I have below command for bash script:

#!/bin/sh
sqlserver=("localhost" "testserver") 
db="master" 
user="sa"
pass="test@12345"
for item in "${sqlserver[@]}"
do
    sqlcmd -S "$item" -d $db -U $user -P $pass -I -h-1  -Q "set nocount on;SELECT name as username FROM SYSUSERS WHERE NAME LIKE '%test%'" >> "$item".txt
    while IFS= read -r line; do
        if [ -n "$line" ]
         then 
         #echo "Text read from file: $line"
         sqlcmd -S "$item" -d $db -U $user -P $pass -I -b -h-1  -Q "set nocount on;drop user $line"
         fi
    done < "$item".txt
done

script is stuck at sqlcmd -S $item -d $DATABASE -U $USERNAME -P $PASSWORD -I -b -h-1 -Q "set nocount on;drop user $line". and this is going to in loop , it's goes infinity. how to get out from this if command.

I got the error as below:

Msg 15151, Level 16, State 1, Server 66752ccaf826, Line 1
Cannot drop the user 'test2', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Server 66752ccaf826, Line 1
Cannot drop the user 'test2', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Server 66752ccaf826, Line 1
Cannot drop the user 'test2', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Server 66752ccaf826, Line 1
Cannot drop the user 'test2', because it does not exist or you do not have permission.

Issue: How to break or exit loop once end of file? and after complete file it will go with 2nd sql server with continue for loop.

For create User:

CREATE USER test1 WITHOUT LOGIN;
CREATE USER test2 WITHOUT LOGIN;

Update1: If I use echo "Text read from file: $line" then if condition running properly but when I use sqlcmd -S "$item" -d $db -U $user -P $pass -I -b -h-1 -Q "set nocount on;drop user $line" then it goes to infinite loop.

Fix your variable names (see [correct-bash-and-shell-script-variable-capitalization](https://stackoverflow.com/questions/673055/correct-bash-and-shell-script-variable-capitalization)) and run your script through http://shellcheck.net as the bash tag you used tells you to do and fix the issues it tells you about to make sure those aren't the problem then post the updated version of your script if you do still have a problem. — Ed Morton, Jan 13 '23 at 12:48
Have you tested your sqlcmd without script? `sqlcmd -S localhost -d master -U sa -P "test@12345" -I -b -h-1 -Q "set nocount on; drop user test1"` — Calvin Kim, Jan 13 '23 at 14:47
`[[ ! -z $line ]]` is same as `[[ -n $line ]]` or even `[[ $line ]]` — Jetchisel, Jan 13 '23 at 16:12
When I copy/paste your script into http://shellcheck.net it tells me `SC3030 (warning): In POSIX sh, arrays are undefined.` so you haven't fixed all of the issues yet. In this case you probably just need to change your shebang to be whatever shell you're using that supports arrays (possibly bash since you tagged your question with that but sometimes people tag their questions with the wrong shell and we need to know for sure which shell you're using). — Ed Morton, Jan 16 '23 at 13:17

Eric Marceau · Accepted Answer · 2023-01-16T20:13:45.250

1

Assuming you want the while loop to end with the end-of-file of "$item.txt", then you need to change

if [[ ! -z $line ]]

to (for bash)

if [[ -z $line ]] ; then break ; fi

or (for sh)

if [ -z $line ] ; then break ; fi

and remove the other "fi".

As for the command

sqlcmd -S $item -d $DATABASE -U $USERNAME -P $PASSWORD -I -b -h-1  -Q "set nocount on;drop user $line"

you need to resolve the issue reported, from the shell command line, before attempting to do that within the script.

edited Jan 16 '23 at 20:13

answered Jan 14 '23 at 20:45

Eric Marceau

1,601
1
8
11

Thanks for respond my question , unfortunately it's not working.. – microset Jan 16 '23 at 07:08
The script as you modified (not as I recommended) does not have a condition for exiting the loop. My recommendation above will exit the loop upon reaching the end of file, and terminate on its own. – Eric Marceau Jan 16 '23 at 20:10

bash script stuck in single line

1 Answers1