SQLTeam.com | Weblogs | Forums

SQLCMD Batch File Issues

sql2008r2

#1

Trying to get a sqlcmd bat file to work. Basically it is just dumping info to a .txt file stored on a NAS. Works perfectly if pasted directly into a cmd prompt but if executed via a batch (aiming to have this be a scheduled job) I receive an error (listed below). The service account I am running the script as has SA rights to the DB.

Any help would be much appreciated...

SET MY_PATH=C:\Program Files\Microsoft SQL Server\100\Tools\Binn
SET SERVER=SERVERNAME
SET DB=COMPANY_REPORTS
SET INPUTFILE=C:\scripts\COMPANY_REPORTS_SQL\ReportScripts\Script_ProjectTracker.sql
SET OUTPUTFILE=\STORAGE\LOCATION\FOLDER\DATA.txt
"%MY_PATH%\SQLCMD.exe" -S %SERVER% -C -E -d %DB% -s"|" -h-1 -W -k -i %INPUTFILE% | %windir%\System32\findstr /V /C:"-" /B > %OUTPUTFILE%

ERROR OUTPUT:
Msg 4060, Level 11, State 1, Server SERVERNAME, Line 1
Cannot open database " COMPANY_REPORTS" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server SERVERNAME, Line 1
Login failed for user 'DOMAIN\SERVICEACCOUNT'.


#2
  1. Verify that the service account has read access to the database (the job is being run under the service account, not sa)
  2. perhaps setup a proxy login for running this job and give it access to the database and to the files in the filesystem
  3. Add a drive letter to the OUTPUTFILE Set statement