File not found from within SQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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:FolderNameREPLACE.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?
sql-server tsql batch-file xp-cmdshell edited Nov 24 '18 at 7:34
Rowan Richards
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
add a comment |
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:FolderNameREPLACE.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?
sql-server tsql batch-file xp-cmdshell edited Nov 24 '18 at 7:34
Rowan Richards
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13
add a comment |
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:FolderNameREPLACE.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?
sql-server tsql batch-file xp-cmdshell edited Nov 24 '18 at 7:34
Rowan Richards
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
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:FolderNameREPLACE.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?
sql-server tsql batch-file xp-cmdshell sql-server tsql batch-file xp-cmdshell edited Nov 24 '18 at 7:34
Rowan Richards
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
edited Nov 24 '18 at 7:34
Rowan Richards
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
edited Nov 24 '18 at 7:34
Rowan Richards
120212
edited Nov 24 '18 at 7:34
Rowan Richards
120212
edited Nov 24 '18 at 7:34
Rowan Richards
120212
120212
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
asked Nov 23 '18 at 15:10
Richard_2413Richard_2413
244
244
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13
add a comment |
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13
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
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
Does the "E" drive actual driver or shared folder(I mean mapped)?
– Zeki Gumus
Nov 23 '18 at 15:16
2
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
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
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53449072%2ffile-not-found-from-within-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53449072%2ffile-not-found-from-within-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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:FolderNameBatchcodeCopy_files.bat' EXEC master..xp_CMDShell 'E:FolderNameBatchcodeRename_2.bat'
– Richard_2413
Nov 26 '18 at 8:13