renaming a file as part of a sql agent job

DBA

unread,

Aug 7, 2009, 5:21:01 PM8/7/09

to

at first this sounds simple. When a sql agent job runs, you can set it to out
put to a file and continually append that file. I am setting up another sql
agent job to do the following:
Set up archiving of those logs so that once a money the log file is copied
to an archive folder and renamed to give it the date,
e.g SQLAgentJobLog.txt becomes SQLAgentJobLog083109.txt. I have tried both
the ren and rename commands in the step to get it to do this, but will not
rename the file. Am I missing something??

Tibor Karaszi

unread,

Aug 8, 2009, 4:26:42 AM8/8/09

to

We need to see error messages. Specify another output file for the rename operation and check that
file for error messages. Also search BOL for "Agent Tokens" to see if that if useful (basically you
can timestamp the output filename from the beginning).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"DBA" <D...@discussions.microsoft.com> wrote in message
news:D485B048-C606-4835...@microsoft.com...

DBA

unread,

Aug 10, 2009, 1:41:01 PM8/10/09

to

This is the error that I am getting:
The syntax of the command is incorrect.

This is the script that I am using to rename:
rename "d:\SQLMaintLog\sqlmaintlogarchive\SQLAgentJobLog.txt "
SQLAgentJobLog%date%.txt
I also tried using the ren command

Aaron Bertrand [SQL Server MVP]

unread,

Aug 10, 2009, 1:45:10 PM8/10/09

to

Is this a T-SQL job step type? If so shouldn't it be:

EXEC master.dbo.xp_cmdshell 'rename "d:\file.txt" "newfile%date%.txt"';

On 8/10/09 1:41 PM, in article
7C061194-B6A5-45FA...@microsoft.com, "DBA"

Linchi Shea

unread,

Aug 10, 2009, 1:46:01 PM8/10/09

to

Place the command in a batch file, and call the batch file in your job.

Linchi

DBA

unread,

Aug 10, 2009, 5:15:02 PM8/10/09

to

That sp would work, but it is disabled for security purposes

Aaron Bertrand [SQL Server MVP]

unread,

Aug 10, 2009, 6:09:43 PM8/10/09

to

So you disable xp_cmdshell but you're still going to provide a way to affect
the file system from within SQL Server? If you need to do cmd shell type
things, the trick with xp_cmdshell is not disabling it but securing it (e.g.
deny access to public and grant only to explicit logins that require it).
There are a lot of things I do with xp_cmdshell that I wouldn't want to do
in other ways, though I am trying to move all such tasks out of SQL Server
altogether (e.g. a Windows scheduled task that runs a batch file or
executable, offering much more flexibility).

On 8/10/09 5:15 PM, in article
C328FEE6-1A5C-4901...@microsoft.com, "DBA"

giulippagiulippa

unread,

Nov 13, 2009, 6:35:00 AM11/13/09

to

I need to rename a file from inside a SQL job, reading a value from a sql table as an argument of the rename command. Is it possible?

Aaron Bertrand [SQL Server MVP] wrote:

So you disable xp_cmdshell but you're still going to provide a way to

10-ago-09

So you disable xp_cmdshell but you are still going to provide a way to affect

the file system from within SQL Server? If you need to do cmd shell type
things, the trick with xp_cmdshell is not disabling it but securing it (e.g.
deny access to public and grant only to explicit logins that require it).

There are a lot of things I do with xp_cmdshell that I would not want to do

in other ways, though I am trying to move all such tasks out of SQL Server
altogether (e.g. a Windows scheduled task that runs a batch file or
executable, offering much more flexibility).

On 8/10/09 5:15 PM, in article
C328FEE6-1A5C-4901...@microsoft.com, "DBA"

Previous Posts In This Thread:

On venerd? 7 agosto 2009 17.21
DBA wrote:

renaming a file as part of a sql agent job

at first this sounds simple. When a sql agent job runs, you can set it to out
put to a file and continually append that file. I am setting up another sql
agent job to do the following:
Set up archiving of those logs so that once a money the log file is copied
to an archive folder and renamed to give it the date,
e.g SQLAgentJobLog.txt becomes SQLAgentJobLog083109.txt. I have tried both
the ren and rename commands in the step to get it to do this, but will not
rename the file. Am I missing something??

On sabato 8 agosto 2009 4.26
Tibor Karaszi wrote:

We need to see error messages.
We need to see error messages. Specify another output file for the rename operation and check that
file for error messages. Also search BOL for "Agent Tokens" to see if that if useful (basically you
can timestamp the output filename from the beginning).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

On luned? 10 agosto 2009 13.41
DBA wrote:

Re: renaming a file as part of a sql agent job

This is the error that I am getting:
The syntax of the command is incorrect.

This is the script that I am using to rename:
rename "d:\SQLMaintLog\sqlmaintlogarchive\SQLAgentJobLog.txt "
SQLAgentJobLog%date%.txt
I also tried using the ren command

"Tibor Karaszi" wrote:

On luned? 10 agosto 2009 13.45

Aaron Bertrand [SQL Server MVP] wrote:

Is this a T-SQL job step type?

Is this a T-SQL job step type? If so should not it be:

EXEC master.dbo.xp_cmdshell 'rename "d:\file.txt" "newfile%date%.txt"';

On 8/10/09 1:41 PM, in article
7C061194-B6A5-45FA...@microsoft.com, "DBA"

On luned? 10 agosto 2009 13.46
Linchi Shea wrote:

Place the command in a batch file, and call the batch file in your job.
Place the command in a batch file, and call the batch file in your job.

Linchi

"DBA" wrote:

On luned? 10 agosto 2009 17.15
DBA wrote:

That sp would work, but it is disabled for security purposes"Aaron Bertrand
That sp would work, but it is disabled for security purposes

"Aaron Bertrand [SQL Server MVP]" wrote:

On luned? 10 agosto 2009 18.09

Aaron Bertrand [SQL Server MVP] wrote:

So you disable xp_cmdshell but you're still going to provide a way to

So you disable xp_cmdshell but you are still going to provide a way to affect

the file system from within SQL Server? If you need to do cmd shell type
things, the trick with xp_cmdshell is not disabling it but securing it (e.g.
deny access to public and grant only to explicit logins that require it).

There are a lot of things I do with xp_cmdshell that I would not want to do

in other ways, though I am trying to move all such tasks out of SQL Server
altogether (e.g. a Windows scheduled task that runs a batch file or
executable, offering much more flexibility).

On 8/10/09 5:15 PM, in article
C328FEE6-1A5C-4901...@microsoft.com, "DBA"

EggHeadCafe - Software Developer Portal of Choice
C# Named-Pipes InterProcess, Inter-Machine Cache Service
http://www.eggheadcafe.com/tutorials/aspnet/4de9e06b-9141-4b01-b77f-08d64d708cd1/c-namedpipes-interproce.aspx

Michael Coles

unread,

Nov 13, 2009, 11:03:12 AM11/13/09

to

"giulippa giulippa" wrote in message
news:2009111363...@hotmail.it...

>I need to rename a file from inside a SQL job, reading a value from a sql
>table as an argument of the rename command. Is it possible?

Sounds like this might be a good scenario for Powershell. Alternatively you
could write a CLR procedure to do it from within the database.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------