?
Solved

Create a folder and copy files to

Posted on 2006-05-25
24
Medium Priority
?
1,557 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:LIULIHUA
  • 8
  • 8
  • 5
  • +1
24 Comments
 
LVL 6

Expert Comment

by:itdrms
ID: 16760640
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16760648
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16760657
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 16760789

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

Author Comment

by:LIULIHUA
ID: 16760814
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16760849
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
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16760863
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
0
 

Author Comment

by:LIULIHUA
ID: 16760865
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.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16760916
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

0
 

Author Comment

by:LIULIHUA
ID: 16760961
Hi aneeshattingal,
this is the output,
/***/
The network path was not found.
NULL
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16760983
aneeshattingal's solution puts a space in the folder name.  See my solution, or you have to add the double quotes.
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16760987
I apologize -- I see the replace of the space
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16760996
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16761001
make sure that the path \\W2ksql1\database exists, better u paste this in Start->run
0
 

Author Comment

by:LIULIHUA
ID: 16761036
I checked "Map Network drive" and browsed to the folder it show "\\W2ksql1\database " there.
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16761071
Change the "exec master..xp_cmdshell " commands to "print" instead and post the results.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16761078
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
0
 
LVL 6

Expert Comment

by:itdrms
ID: 16761082
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.
0
 

Author Comment

by:LIULIHUA
ID: 16761149
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"

0
 

Author Comment

by:LIULIHUA
ID: 16761784
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?
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 16762393
>>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"
>>

no it doesn't use your login it uses the SQL server agent account (by default...)

it isn't a good idea to run this sort of script , from a security point of view....
you should really run exports from a client application and offload the O/S requirements to the application server rather
than the Database server....


>>SET @cmd = 'mkdir z:\dataload\WeeklyData_'+ REPLACE(CONVERT(varchar(11),GETDATE(),100),' ','')


you need to convert the DATE once and then consistently use it in each subsequent command you issue
otherwise each statement can and will produce a "different" datetime...

ie job starts at 23:59:59
by the time the second command is issued its sometime tomorrow....



what are you really trying to do?
why aren't you using BCP or DTS to accomplish the task...

hth

   
   
0
 

Author Comment

by:LIULIHUA
ID: 16762460
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.



0
 
LVL 6

Expert Comment

by:itdrms
ID: 16762555
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.
0
 

Author Comment

by:LIULIHUA
ID: 16762584
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question