When I try to execute the SQL Server 2012 BCP.exe utility to dump the contents of a table to a file using a fully qualified path to the exe,
D:\SQL2012\110\Tools\Binn\bcp.exeDBNAME.DBO.TABLENAME OUT %FileServerProject%\IMPLEMENTATION\DAT\Pre_Run_BaseTables\CDB_ACCT_CURR.DAT -S%SqlServer% -T -N >> %LogFolder%\Log.log
...I get an ACCESS DENIED error.
However, when I remove the fully qualified path to the exe and run,
bcp.exeDBNAME.DBO.TABLENAME OUT %FileServerProject%\IMPLEMENTATION\DAT\Pre_Run_BaseTables\CDB_ACCT_CURR.DAT -S%SqlServer% -T -N >> %LogFolder%\Log.log
It works fine.
If the path isn't qualified, my understanding is that Windows will search each folder specified in the PATH environmental variable looking for the specified exe, and execute first one found. So, I executed the following command from the console to see my PATH variable.
ECHO %PATH%
Cleaning up the miscellaneous unrelated paths from the output, I saw the following SQL-related folder returned in this order:
D:\SQL2012\110\DTS\Binn\;
D:\SQL2012 (86)\110\Tools\Binn\;
D:\SQL2012\110\Tools\Binn\;
The latter Tools\Binn path was the only folder that contained the bcp.exe utility.
My question is this:
Since the same EXE was executed whether I explicitly qualified the path or left Windows to find it by searching the path variable, why did I get an ACCESS DENIED error when I ran using the fully qualified path and not when I did not qualify the path?
Note that in both cases I was running under an ID that had read and execute rights to the TOOLS\BINN folder. In the case where I was using the fully qualified path, if I added the account to the Local group, it would work but this was not a viable solution. Also, the ID had LogOn As Batch rights to the server.
Update:
I now have no doubt that when I execute using the unqualified bcp.exe path that I am in fact running the only copy of bcp.exe on the server. For starters, I used Search Everything to extensively search every drive on the server. I found three occurrences. I then renamed the 2 that I did not want to accidentally reference.
I then reran the job using the unqualified bcp.exe path and using Task Manager's process tab, I found bcp.exe running under the service account. I then right-clicked on the file name and selected the context menu "Open File Location", and it took me to the only location of the bcp.exe file that was not renamed -- the file that I was intentionally trying to target with the fully qualified name.
D:\SQL2012\110\Tools\Binn
Since the bcp.exe was not qualified, it ran successfully.
Update 2 So far, 42 people have looked at this. I'd be curious if people are looking at this saying "that's impossible, that the facts of this case must not be exactly as I've stated."
Almost there: I simplified the batch file down to the bare minimum to reproduce the problem. You will notice that I changed our true path names, but I kept the gist of it.
Here's the "Before" code:
----------------------------------------------------------------
Output of messages for workload object TESTDUMP/GHG9999I.11/MAIN
Start date Fri Sep 25 13:33:36 2015
----------------------------------------------------------------
C:\Users\MyServiceAccount>WHERE bcp.exe
INFO: Could not find files for the given pattern(s).
C:\Users\MyServiceAccount> D:\SQL2012\110\Tools\Binn\bcp.exe MyDB.DBO.MyTable OUT \\MyFileServer\IMData\MyDB_SOURCE\IMPLEMENTATION\DAT\Pre_Run_BaseTables\MyTable.DAT -S MyDbServer\int -T -N 1>>\\MyFileServer\IMData\MyDB_SOURCE\Logs\MyTable_BCP_out.log
Access is denied.
C:\Users\MYSERVICEACCOUNT>ECHO RESULT=1
RESULT=1
Here's the "After,", which worked
----------------------------------------------------------------
Output of messages for workload object TESTDUMP/GHG9999I.10/MAIN
Start date Fri Sep 25 13:33:00 2015
----------------------------------------------------------------
C:\Users\MyServiceAccount>WHERE bcp.exe
INFO: Could not find files for the given pattern(s).
C:\Users\MyServiceAccount>"D:\SQL2012\110\Tools\Binn\bcp.exe" MyDB.DBO.MyTable OUT \\MyFileServer\IMData\MyDB_SOURCE\IMPLEMENTATION\DAT\Pre_Run_BaseTables\MyTable.DAT -S MyDbServer\int -T -N 1>>\\MyFileServer\IMData\MyDB_SOURCE\Logs\MyTable_BCP_out.log
C:\Users\MYSERVICEACCOUNT>ECHO RESULT=0
RESULT=0
Note that the difference is that the BCP path that worked was enclosed in quotes. I would have thought that this would have been important only if the path contained embedded spaces. I am confused why it mattered in this case.
A secondary new concern is why the WHERE command failed to work when running under MYSERVICDEACCOUNT through a scheduler. The command works when I log in manually under MYLANID, navigate to c:\users\mylanid, and try it.
If someone can explain why the quotes mattered, they get 100 pts and my gratitude.
Update 4:
I located the AgentParm.txt file on a server where the same code works. It was under the Program Files folder:
# Agent settings for nt-x86-64
agentname=MyWorkingServer
log.archive=2
oscomponent.jvm=server
On the server that we have been discussing where I have the issue when the batch file is unquoted, I see the following in the Program Files (x86) folder. All other lines were the same, so I excluded them. I did not see any mentioned of oscomponent.cmdprefix.force.quotes.full.
# Agent settings for nt-x86-32
agentname=MyServer
Do I need the 64 bit version of CA scheduler to run 64 bit exes? If so, will I have an issue running 32 bit exes (like SQL Server dtexec.exe) with the 64 bit CW Scheduler?
