How to move text file in Stored Prcoedure
-
How can I move a text file within a stored Prcoedure?
-
R u talking about an actual file or a text column?
-
I am talking about an actual text file
-
Maybe someone has a better solution... but providing that this is for administrative work :
EXEC master..xp_cmdshell 'dos command to move file here', no_output
-
Stored procedure using xp_cmdshell:
CREATE PROCEDURE DeanTest(@FileName1 varchar(255), @FileName2 varchar(255)) AS
Declare @cmd varchar(2000)
Select @cmd = 'move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process'
Print @cmd
SELECT @cmd = 'master..xp_cmdshell ' + '''' + @cmd + ''
Print @cmd
Exec @cmd
GO
Here is the error:
move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process
master..xp_cmdshell 'move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process
Server: Msg 203, Level 16, State 2, Procedure DeanTest, Line 8
The name 'master..xp_cmdshell 'move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process' is not a valid identifier.
Any ideas
-
ok,
CREATE PROCEDURE DeanTest(@FileName1 varchar(255), @FileName2 varchar(255)) AS
Declare @cmd varchar(2000)
Select @cmd = 'move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process'
Print @cmd
SELECT @cmd = 'master..xp_cmdshell ' + '''' + @cmd + ''''
Print @cmd
--exec master..xp_cmdshell 'move \\netservice2\public\Dean\Junge\DeanTest.txt \\netservice2\public\Dean\Junge\DeanTest.process'
exec @cmd
GO
The command works, but not when using the exe @cmd, am I not putting @cmd toegether right?
-
The final Solution that works is:
CREATE PROCEDURE MoveFile(@FileName1 varchar(255), @FileName2 varchar(255)) AS
Declare @cmd varchar(2000)
Set @cmd = 'move '+@FileName1+' '+@FileName2
exec master..xp_cmdshell @cmd
GO
-
That's what I was about to finish typing
. -
I can't make it work! I have the following error:
Access is denied.
-----------------------------------------------
use master
declare @FileName1 varchar(100)
declare @FileName2 varchar(100)
declare @cmd varchar(1000)
set @FileName1 = 'C:\Data\Rhapsody\NCWTExtract\*.*'
set @FileName2 = '\\server\Data\Rhapsody\CWTExport\'
Set @cmd = 'move '+@FileName1+' '+@FileName2
exec xp_cmdshell 'bcp accRepository.dbo.CWT_Extract_Formatted out C:\Data\Rhapsody\NCWTExtract\temp.csv -t, -Sosl-win2003 -U -P -c', NO_OUTPUT
exec xp_cmdshell @cmd
Where C:\Data\.. ' is from local server, where the SQL Server resides at (i.e. DB server); whereas \\server\Data\...' is referring to another machine, says web server.
-
make sure that the account the server is using has access to the network, also make sure he can access the said folders and that he can create files in it.
-
The use of xp_CmdShell is considered to be a security risk by most DBA's and, I believe, the default is you must have"SA" (not just "dbo" rights to use it. You can have your DBA grant rights to it just for you.
--Jeff Moden
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply