CmdExec job to rename file with yesterday's date 1

[kittyyo]

Hi all!

I've been searching for a while, maybe you can help:

I need an SQL Server Agent job to rename a file with the original filename + yesterday's date.
I figrued out that I should use an Operating System (CmdExec) job step like this

ren test.txt test-%date%.txt

but I'm stuck with getting yesterday's date or passing a parameter to the command (instead of %date%).

I don't want to activate xp_cmdshell for this task.

Thanks for any recommendations!
Anne

 

[markros]

Why do you want to do this using SQL Agent? Why not schedule a job with a bat file.

Finally, you can not use / in the file name (I guess you're aware of that already).

 

[kittyyo]

As I'm using the German localized version of Windows, the %date% doesn't include any slashes, just dots.

I considered SQL agent for two reasons:
a) The job that creates the file, which I want to rename, is already an SQL agent job (file contains database data).
b) I didn't find any suitable solution that gives me yesterday's date on the command line, such that I can include it into the new file name.

So I hoped there would be a possibility to calculate yesterday's date with SQL server and then rename the file using this value. However, I found that only working with the xp_cmdshell SP, which to activate is a security issue.

 

[sqlfable]

What version of SQL Server? 2000, 2005 or 2008?

2005+ this is a scripting task using System.IO via an SSIS package. Or call a CLR procedure given the DB is trustworthy on.
2000- I would create a .vbs or .js and call it up from the step. Much easier to error handle.

 

[kittyyo]

2005. I have never done anything relating to SSIS, but it would be a good time to start.
Also the DB is trustworthy on - so I can do filesystem tasks with a CLR without further "activations"? Great!

Thanks a lot for your guidance!

 

[sqlfable]

No problem. If you try the CLR procedure side, use the Move. It would be pretty short..

something like

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MoveFile(string sfilename, string dfilename)
{
try
{
File.Move(sfilename, dfilename);
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error writing to file : " + ex.Message);
}
}
};


And call it like any procedure
Exec MoveFile 'D:\file.txt','D:\file20092803.txt'