So I have this batch file that is running through SSMS SQL Server Agent as a job.
@echo off
title This will be the batch script to create our 3 .csv files for Scott.
sqlcmd /S MYSERVER /d QWI /E /Q "SELECT [geography], current_year, [quarter], industry AS sector, ethnicity, [A0] AS Race0, [A1] AS Race1, [A2] AS Race2, [A3] AS Race3, [A4] AS Race4, [A5] AS Race5, [A7] AS Race7 FROM (SELECT [geography], race, current_year, [quarter], industry, ethnicity, HirAS FROM QWI.dbo.RaceEthnicity) p PIVOT(SUM(HirAS) FOR race IN( [A0], [A1], [A2], [A3], [A4], [A5], [A7] )) AS pvt WHERE [geography] = '53077' AND current_year = '1990' AND [quarter] = '3' ORDER BY current_year, pvt.geography, sector, ethnicity" /s"," /o "RaceEthnicityResults.csv"
Basically, I'm creating a csv file with a query using sqlcmd in the batch file. Everything runs properly and the file is created with the right information inside. The issue is that when I access the file, it can only be used as a "read only" file because it is being used by "another user". I know this is probably because the process is not being closed/killed properly, but SQL Server Agent is showing a success and the job does finish. I am running the process as an "sa".
I have tried adding everything to the end of the batch file, EXIT EXIT /b /c WAIT PAUSE call taskkill /f /im notepad.exe exit /b 0 and basically any combination thereof, but with no luck in changing the issue so far. Any tips here?
Well, so my temp fix for now was to move the sqlcmd directly to SQL Server Agent in one of the tasks instead of calling the batch file. This was recommended by a colleague and it did the trick. I would like to know if anyone else has had success killing a task like this, etc. and what steps they used though. Just for future jobs. :)