November 8, 2007 at 12:51 pm
Hi,
I have coded to create a folder in my system automatically suing a T-Sql.
However, i want to name the folder along with the date it is created so that next time it creates a folder it doesnt think it is existing.
What do i need to add in my code?
This is my code:
declare @cmdpath nvarchar(60), @Location nvarchar(100)
set @Location = N'C:\yakka'
set @cmdpath = 'MD '+ @Location
exec master.dbo.xp_cmdshell @cmdpath
November 8, 2007 at 1:02 pm
you mean you want it to create c:\yakka_11_08_2007, then the next time create c:\yakka_11_09_2007, etc.?
The Redneck DBA
November 8, 2007 at 1:09 pm
Yes. It should somewhat be a combination of name and the date.
Coz the folders will be stored created periodically and stored in the same server.
So everytime the Job creates a folder, it shouldnt create with same name.
November 8, 2007 at 1:15 pm
Try this...(I just modified your code). You can rearrange the MM/DD/YYYY order as needed. I just typed that on the fly, so might check that all my parens match...
Rama (11/8/2007)
Hi,I have coded to create a folder in my system automatically suing a T-Sql.
However, i want to name the folder along with the date it is created so that next time it creates a folder it doesnt think it is existing.
What do i need to add in my code?
This is my code:
declare @cmdpath nvarchar(60), @Location nvarchar(100)
set @Location = 'C:\yakka' + CONVERT(VARCHAR(2), MONTH(Getdate())) + '-'
set @Location = @Location + CONVERT(VARCHAR(2), DAY(Getdate())) + '-'
set @Location = @Location + CONVERT(VARCHAR(4), YEAR(Getdate()))
set @cmdpath = 'MD '+ @Location
exec master.dbo.xp_cmdshell @cmdpath
The Redneck DBA
November 8, 2007 at 1:27 pm
Hi Jason,
it works perfect. thanks for your time & help.
Also one more thing. Now this folder is created and i also have a script to copy files into this folder.
I am quite stuck with how to zip this folder using a SP.
Do you have any idea?
My SP looks like this... 🙁 but it doesnt work...
create proc sp_dba_ZipFile @zippedfile varchar(50),@srcfile varchar(50)
AS Declare @cmdstr varchar(8000)
Set @cmdstr = 'pkzipc -add '+ @zippedfile +' ' + @srcfile
exec master..xp_cmdshell @cmdstr
GO
Exec sp_dba_zipfile 'C:\yakka11-8-2007.zip' , 'C:\yakka11-8-2007'
November 8, 2007 at 1:33 pm
Your syntax looks OK off the top of my head. A couple of things to try:
1) You probably will need to put the full path to the zip executable
(i.e. "C:\Program Files\PKWARE\PKZIPC\PKZIPC.exe") or whereever it is. Note that you'll need the double quotes arround the path if there is a space in it.
2) Are you sure the user that this is running as has the necessary permissions to all of the paths involved?
The Redneck DBA
November 8, 2007 at 1:39 pm
Well I will compile all the action and schedule it as a SQL Job.
So it will be deployed in production DB. The user neednt do anything with this, as the job is going to run automatically once in every week.
:hehe:
November 8, 2007 at 1:42 pm
Right...but whatever user the sqlserveragent service is running as will need appropriate permissions to the source/destination paths, and permissions to execute the zip execuatable.
The Redneck DBA
November 8, 2007 at 1:45 pm
Oh...
Probly i would wanna talk this to my PL
Thanks...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply