query database for value, set as variable in a batch script and use variable to find a specific file on network drive

Question

bit new at this and hoping someone can help. I have the following batch script that queries a database, pulls back a result and sets it to a variable.

set "sqlcmdServer=sqlinstance"
for /f %%A in (
'sqlcmd -h -1 -r 1 -Q "SET NOCOUNT ON; Declare @v varchar(100) set @v   = (select idcorrent from database.dbo.table (nolock) where [identity] = 'UK' and idletter = 'EX' and stcorres = 14) Select @v"'
) do set "var=%%A"

echo %var%
pause

This very nicely brings me back a string. What i would like to then do is use this variable within the same script, to search a folder on a network drive for a single file (pdf in amount a shed load of other files specifically) that contains this variable. The filename will always end with the variable i.e. "somecharacters_variable.pdf"

I was messing around with this:

for /f "delims=" %%s in ('xcopy /l /e /y "C:\Users\USERNAME\Desktop\test1\*.pdf"r "C:\Users\USERNAME\Desktop\test2" ^|find ":"') do copy "%%s" "C:\Users\USERNAME\Desktop\test2"

which, when testing, successfully found my test file and moved it from one desktop folder to another. So I tried adding it to the end of my batch script, using setlocal enabledelayedexpansion and replacing desktop dir with the network drive and inserting the variable as such(changed network drive name for purpose of this question):

set "sqlcmdServer=sqlinstance"
setlocal enabledelayedexpansion
for /f %%A in (
'sqlcmd -h -1 -r 1 -Q "SET NOCOUNT ON; Declare @v varchar(100) set @v   = (select idcorrent from database.dbo.table (nolock) where [identity] = 'UK' and idletter = 'EX' and stcorres = 14) Select @v"'
) do set "var=%%A"

echo !var!
for /f "delims=" %%s in ('xcopy /l /e /y "\\networkdrive\test\test\*!var!.pdf"r "C:\Users\USERNAME\Desktop\test2" ^|find ":"') do copy "%%s" "C:\Users\USERNAME\Desktop\test2"

But this didn't work.

is it achievable the way I'm trying? if not how would i go about it?

Any help/guidance is appreciated

Kind regards

There is an r appended to the xcopy source, which I guess is unintended... — aschipfl
"But this didn't work." In what way did it not work? Was there an error message? What did happen? — lit
@aschipfl whoops! no the r is not meant to be there! no idea how i didn't spot that, but thank you. Having just re-tested my entire script without that r - i can get it to work! but only when doing desktop folder to desk folder find/copy. — Nighthawkz
Instead of C:\Users\USERNAME you could use %USERPROFILE% to point to the directory of the currently logged on user... — aschipfl
@Liturgist sorry i should have expanded that to begin with. by it doesn't work - when i execute the script the command window just stays open and it hangs. There are a lot of files to search through - If i did a manual search via file explorer on the network drive for the variable, it takes a minute or two to find the file, but I've purposeful executed the script and left the command window open for a good 10/15 minutes but it just hangs. I've not accidentally put any pauses or stops in the script that i can see. — Nighthawkz

Wes Larson Wes Larson · Accepted Answer · 2016-05-05T22:36:18

I think the problem lies in the difference between this:

xcopy /l /e /y C:\Users\USERNAME\Desktop\test1\*.pdf" "C:\Users\USERNAME\Desktop\test2

and this:

xcopy /l /e /y "\\networkdrive\test\test\*!var!.pdf" "C:\Users\USERNAME\Desktop\test2"

When I run the first one (with the path name changed to match my environment) it returns something like this:

C:file1.pdf
C:SubFolderName\file2.pdf
C:AnotherSubFolder\AndSoOn.pdf

However, when I run the second one, the results look like this:

\\networkdrive\test\test\file1.pdf
\\networkdrive\test\test\subfolder\file2.pdf
\\networkdrive\test\test\anotherSubFolder\AndSoOn.pdf

Note, there is no colon (:) to find in the output of the second statement.

To get around this, and use a shorter statement, you can use dir /b /s instead.

for /f "delims=" %%s in ('dir /b /s "*%var%.pdf"') do (
    copy "%%s" "C:\Users\USERNAME\Desktop\test2"
)

query database for value, set as variable in a batch script and use variable to find a specific file on network drive

1 Answers