asked on

Create a folder and copy files to

Hi experts,

Is it possible to create a folder named it as the date, and put all files in that folder using a stored procedure?
As lists below is the sp that I am going to create, however, how to make the folder name as the new date that when the sp execute exery time?

exec xp_cmdshell 'mkdir F:\data\myfolder_date'
exec xp_cmdshell ' copy c:\data\*.zip F:\data\myfolder_date'

thank you for your help.

itdrms

DECLARE @CurDt varchar(14)
SET @curDT = convert(char(8),getdate(),112)+REPLACE(convert(char(14),getdate(),108),':','')
DECLARE @cmdTXT varchar(4000)
SET @cmdTXT = 'mkdir F:\data\myfolder_'+@CurDt
exec xp_cmdshell @cmdTXT

Lowfatspread

declare TheDate varchar(26)
set TheDate = getdate()

declare @cmd varchar(300)
set @cmd = 'F:\data\myfolder_'+TheDate
exec xp_cmdshell @cmd
set @cmd = ' copy c:\data\*.zip F:\data\myfolder_'+TheDate
exec xp_cmdshell @cmd

Lowfatspread

sorry
declare @TheDate varchar(26)
set @TheDate = getdate()

declare @cmd varchar(300)
set @cmd = 'F:\data\myfolder_'+@TheDate
exec xp_cmdshell @cmd
set @cmd = ' copy c:\data\*.zip F:\data\myfolder_'+@TheDate
exec xp_cmdshell @cmd

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LIULIHUA

ASKER

Hi Lowfatspread,

I think I headed the problem with server login.

I replaced "F" drive as "\\w2ksql1" , so the
"set @cmd = 'F:\data\myfolder_'+@TheDate" replace as
"set @cmd = 'madir \\W2ksql1:\database\WeeklyData_'+@TheDate"
but this seems not work, please see the output below. thanks.

/*** output***/
'madir' is not recognized as an internal or external command,
operable program or batch file.
NULL

Aneesh

try this

its not madir it is 'mkdir'

DECLARE @cmd VARCHAR(5000)
SET @cmd = 'mkdir \\W2ksql1\database\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd
set @cmd = ' copy C:\data\*.zip \\W2ksql1\database\WeeklyData\myfolder_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd

itdrms

The command is "mkdir" and the colon needs to be removed.
Did you attempt the first solution? The others put spaces in the dates which create a quote issue -- but can be done

LIULIHUA

ASKER

Hi aneeshattingal,

this is the scripts I ran,

DECLARE @cmd VARCHAR(5000)
SET @cmd = 'mkdir \\W2ksql1:\database\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd
set @cmd = ' copy c:\data\*.zip \\W2ksql1:\database\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd

And this is the output:
The network path was not found.
Error occurred while processing: \\W2ksql1:\database\Syngenta.
NULL

I have the permission to create a folder in this drive in server as my login, where should I add it to my scripts? please advice. thanks.

Aneesh

LIULIHUA,
> \\W2ksql1:\d

Youdont need the colon after the shared path

DECLARE @cmd VARCHAR(5000)
SET @cmd = 'mkdir \\W2ksql1\database\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd
set @cmd = ' copy c:\data\*.zip \\W2ksql1\database\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd

LIULIHUA

ASKER

Hi aneeshattingal,
this is the output,
/***/
The network path was not found.
NULL

itdrms

aneeshattingal's solution puts a space in the folder name. See my solution, or you have to add the double quotes.

itdrms

I apologize -- I see the replace of the space

itdrms

Go to explorer and make sure \\W2ksql1\database is a valid path. By going from a mapped drive to a UNC you are probably missing
\\W2ksql1\???????\database\WeeklyData

Aneesh

make sure that the path \\W2ksql1\database exists, better u paste this in Start->run

LIULIHUA

ASKER

I checked "Map Network drive" and browsed to the folder it show "\\W2ksql1\database " there.

itdrms

Change the "exec master..xp_cmdshell " commands to "print" instead and post the results.

Aneesh

Since you are accessing a network drive , make sure that the user u r connecting to the database has enough rights to access the network drive

itdrms

Keep in mind folders named May252006 vs. folders names 20060525 will sort differently. If you want to run the script more than once in a day you will have ot include a time stamp.

LIULIHUA

ASKER

as I said, I have full permission to create folder in server manually so it should bring the same security when the command run from the pc with my login isn't it?

i did try command in cmd with the code:

'mkdir \\W2ksql1\database\WeeklyData'

I got " the network path was not fund"

LIULIHUA

ASKER

Hi,

I maped \\W2ksql1\database as "Z:" drive and test it in command line like "mkdir z:\dataload\weeklydata" it do created weeklydata folder for me. But, when I tried the same approch in sql:

SET @cmd = 'mkdir z:\dataload\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')
exec master..xp_cmdshell @cmd
the output said:
*******
The system cannot find the drive specified.
NULL

Why?

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LIULIHUA

ASKER

What I am doing here is to create a stored percedure to create a folder and copy the .zip files into that folder every monaday. I know ActiveX will add a lot of overhead and turn to very slow. so I choose to create a stored procedure and going to set up dts package to run as a data movement project later.

itdrms

The z: drive is the z: drive on the server.
Once you can make the unc work in the command prompt, you will be able to get it to work in your sp.

if you issue the command "net use z:"
from your command you should see the remote name is
\\W2ksql1\database
if you are correct on the path.

LIULIHUA

ASKER

Hi,

Don't worry about the path, when I run everything in server side, the path will not become the problem. thank you for the helps.