T-SQL Need Urgent help in CURL Execution issue from sql script

Question

Hi,

Thanks in advance,

We are using the CURL.exe for the FTP file transfer from one of our sql script. As we are unable to process two files at a time it allows only one file to transfer. It was working fine in our previous server which is

"Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)"

After migrating to new version we are facing issue. Please help me out in this thanks
The process cannot access the file because it is being used by another process.
FTP error code : 1

D:\PriceLists_Support\curl\curl.exe -T J:\EU_INCR\PriceLists_INCR\810\Price_XXXXXXXX.csv ftp://ftp2.XXXXXX:21/ -u FTPXXXXX:XXXXXXX3! -w "2020-12-19 03:48:41.677,140588,111,120,%{http_code},%{size_upload},%{time_total}\r\n">>X:\INCR\PriceLists_Support_INCR\ftp_log.txt

CURL Version: 7.28.1.0 (CURL.exe)

New Migrated SQL VERSION:

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) Aug 20 2020 22:33:27 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 1

So you are saying that on SQL 2008 you were able to run two of these commands in parallel, and on SQL 2017 you are not?

Well, let's first note this that SQL Server knows nothing about CURL, so the answer is likely to be found outside SQL Server. For instance, there may be differences in the OS as well. (I assume that you are not running SQL 2017 on the same version of WIndows as you were running SQL 2008 R2.) And for that matter, you may also have different versions of CURL.

I would be inclined to take a look at the entire solution. Jumping out of SQL Server with xp_cmdshell is far from always a great idea. External actions like FTP:ing files are best done outside SQL Server.

If you absolutely want to do this in SQL Server, and the issue is parallel executions, you will need to introduces a means of serialization, for instance with application locks. Or avoid the file-clash problem by using unique names.