Move file into folder using xp_cmdshell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Move file into folder using xp_cmdshell

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-11 : 16:59:56

The below code checks for excel file existence and once the file is present it triggers the DTS, I scheduled this code as a sql job to run every 2 minutes and this works fine, but I want to add the timestamp to the excel file which I am moving
to the DTSArchieveFiles folder, Any help is appreciated. Thx.
Code:
Create table #test
(file1 varchar(100))
Insert into #test
Exec master..xp_cmdshell
'dir /b \\server1\folder\DTSUpload\*.xls'
if exists(select * from #test where file1 like '%.xls%')
EXECUTE
master.dbo.xp_CmdShell
"dtsrun ... /NAutoExec"
drop table #test
EXEC master..xp_cmdshell 'MOVE \\server1\folder\DTSUpload\*.xls \\server1\folder\DTSArchieveFiles'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:02:02

The file path is relative the SQL Server, not the machine where you are running QA.
You also need permissions on the folders in mind, for the account for which SQL Server is running under.

E 12°55'05.25"
N 56°04'39.16"

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-11 : 17:40:47

quote:
Originally posted by Peso

The file path is relative the SQL Server, not the machine where you are running QA.
You also need permissions on the folders in mind, for the account for which SQL Server is running under.

E 12°55'05.25"
N 56°04'39.16"

Please see my updated post. Thx.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:44:00

Something similar to this

CREATE TABLE	#Files
		(
			FileID INT IDENTITY(1, 1),
			FileName VARCHAR(257)
		)

INSERT	#Files
EXEC	xp_cmdshell 'dir /b c:\windows\*.bmp'

DELETE
FROM	#Files
WHERE	FileName IS NULL

IF NOT EXISTS (SELECT * FROM #Files)
	RETURN

DECLARE	@FileID INT,
	@OldFileName VARCHAR(257),
	@NewFileName VARCHAR(257),
	@Timestamp VARCHAR(30),
	@cmd VARCHAR(640)

SELECT	@FileID = MAX(FileID),
	@Timestamp = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 120)
FROM	#Files

EXEC	xp_cmdshell 'MOVE c:\windows\*.bmp c:\temp\'

WHILE @FileID > 1
	BEGIN
		SELECT	@OldFileName = FileName
		FROM	#Files
		WHERE	FileID = @FileID

		SELECT	@NewFileName = LEFT(@OldFileName, LEN(@OldFileName) - 4) + @Timestamp + '.bmp',
			@cmd =  'RENAME c:\windows\' + @OldFileName + ' ' + @NewFileName

		EXEC	xp_cmdshell @cmd

		SET @FileID = @FileID - 1
	END

DROP TABLE	#Files

E 12°55'05.25"
N 56°04'39.16"

Haywood
Posting Yak Master

221 Posts

Posted - 2007-12-11 : 21:05:09

You can also do a FOR loop in the command shell - moving each file one by one and appended the timestamp on each exec of the FOR loop. That way you only have to drop to the shell for the actual move op once.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-12 : 12:26:48

Since I have only 1 file, I changed the above code accordingly, but I was getting below error when renaming:
Output
The syntax of the commnad is incorrect
Null

Below is the code I used, any ideas, thx.
CREATE TABLE #Files
(
FileID INT IDENTITY(1, 1),
FileName VARCHAR(257)
)
INSERT #Files
EXEC master..xp_cmdshell 'dir /b C:\Test\Start\*.txt'

DELETE
FROM #Files
WHERE FileName IS NULL

IF NOT EXISTS (SELECT * FROM #Files where filename like '%.txt%')
RETURN
EXECUTE
master..xp_cmdshell
"dtsrun .... /NAutoExecDTS"

DECLARE @FileID INT,
@OldFileName VARCHAR(257),
@NewFileName VARCHAR(257),
@Timestamp VARCHAR(30),
@cmd VARCHAR(640)

SELECT @FileID = MAX(FileID),
@Timestamp = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 120)
FROM #Files

EXEC master..xp_cmdshell 'MOVE C:\Test\Start\*.txt C:\Test\Finished\'

SELECT @OldFileName = FileName
FROM #Files

SELECT @NewFileName = LEFT(@OldFileName, LEN(@OldFileName) - 4) + '_' + @Timestamp + '.txt',
@cmd = 'RENAME C:\Test\Finished\' + @OldFileName + ' ' + @NewFileName
EXEC master..xp_cmdshell @cmd
DROP TABLE #Files

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-13 : 12:11:20

Any ideas on this code, thanks!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-12-13 : 12:51:23

print @cmd
run that in DOS and identify where the problem is

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources