SQL Server CSV file locked by "another user" after creating through batch file/SSMS job

tvz2xvvm 于 2023-03-11 发布在其他

关注(0)|答案(2)|浏览(115)

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?

sql-server

来源：https://stackoverflow.com/questions/75699796/csv-file-locked-by-another-user-after-creating-through-batch-file-ssms-job

2条答案

按热度按时间

htzpubme1#

When a CSV file is locked by "another user" after creating it through a batch file or SSMS job, it means that another process or user has a lock on the file, preventing other processes from accessing it. To resolve this issue, you can try the following steps:

Check if any other process or user has the file open. You can use tools like Process Explorer or Resource Monitor to identify which process is locking the file.
If the file is being locked by a process, you can try to terminate the process or wait until it releases the lock on the file.
If the file is being locked by another user, you can try to contact the user and ask them to release the lock on the file.
If none of the above steps work, you can try to rename the file or move it to a different location to release the lock.
If you frequently encounter this issue, you can modify your batch file or SSMS job to use a different file name or location to avoid file locks. Remember to save and close any files before attempting to move or rename them, as you may lose any unsaved changes.

赞(0）回复(0）举报 2023-03-11

erhoui1w2#

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. :)

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server CSV file locked by "another user" after creating through batch file/SSMS job

2条答案

相关问题

热门标签

最新问答