0

What I am trying to do is the following: Everyday I get a CSV file that eventually needs to be imported into SQL via Bulk Insert. This all has worked great for a long time but recently, one of the columns in the CSV file contains text that needs to be replaced before the bulk insert.

After some searching i found the following code for a BAT-file:

@echo off &setlocal
set "search=%1"
set "replace=%2"
set "textfile=Input.txt"
set "newfile=Output.txt"
(for /f "delims=" %%i in (%textfile%) do (
    set "line=%%i"
    setlocal enabledelayedexpansion
    set "line=!line:%search%=%replace%!"
    echo(!line!
    endlocal
))>"%newfile%"
del %textfile%
rename %newfile%  %textfile%

Now when i put this bat-file in the directory and double click to run it that works great. But the problem now is that i want to integrate it in a SQL Stored Procedure I put it in the procedure like this:

EXEC master..xp_CMDShell 'E:\FolderName\REPLACE.BAT'

When i try to run that i get the message "File not found" in SQL. Both for the stored procedure as for running it just as a query (under my own credentials, which are admin rights). Just to be sure i gave the SQL-user also full rights on the specific folder, but that didnt help either.

How can I get this work inside SQL Server?

Rowan Richards
  • 401
  • 8
  • 20
Richard_2413
  • 59
  • 2
  • 8
  • Just to be clear, other xp_CMDShell commands in the same stored procedure are running fine so its not a problem with xp_CMDShell not being enabled – Richard_2413 Nov 23 '18 at 15:13
  • Does the "E" drive actual driver or shared folder(I mean mapped)? – Zeki Gumus Nov 23 '18 at 15:16
  • 2
    you'll have to replace E:\ with the UNC path to the folder if you're running it outside of the server environment – JonTout Nov 23 '18 at 15:29
  • Isn't backslash ( \ ) an escape character in SQL? Try replacing each \ with \\ or with `/` like this: EXEC master..xp_CMDShell 'E:\\FolderName\\REPLACE.BAT' – Jack White Nov 23 '18 at 20:58
  • I dont think it has something to do with the driveletter/UNC-path. In the same script/procedure are the following lines which works great: EXEC master..xp_CMDShell 'E:\FolderName\Batchcode\Copy_files.bat' EXEC master..xp_CMDShell 'E:\FolderName\Batchcode\Rename_2.bat' – Richard_2413 Nov 26 '18 at 08:13

0 Answers0