Click here to Skip to main content
13,351,465 members (45,247 online)
Click here to Skip to main content
Add your own
alternative version


17 bookmarked
Posted 4 Mar 2008

Renaming a SQL Reporting Service File

, 5 Mar 2008
Rate this:
Please Sign up or sign in to vote.
Editing Reporting Service 2005 Subscription Job and Renaming the rendered file


You need to rename a Report file render to \\Server\Public\Reports\Test directory after  SQL Server Reporting Services 2005

 Subscription was  executed and created that file there. This must be done so that the consumer of the report can have

an archived stock of reports with dates. E.g 01_02_thru_03_20_2008Production_RGU_Report and so and so on.

Using the code


Solution: There are two ways to solve this problem, but  both do address the problem of how do you know when the subscription services
have completed the creation of the report file.  In this article we will look at both options. Option 1 Use SSIS to create a file System Task

 to perform the rename. Option 2 Write an SQL Script using sp_ReplaceFileOrDirNames function to rename the file.

 Image left out

Both options will do the work of renaming the file but which one will be able to synchronized with Subscription Services??? We are about to find out.


Go to the screen on the snap shot below by logging on to your server, expand the nodes, then right click on
Job Activity Monitor, and then click View Job Activity. Now find the Job Name for the subscription Event,

which may be something like 6FD6B328-692B-4749-A6E1-E4E760CC0A5D. Double click on it.

 Once you are there, you will be able to do a new step, delete, edit or insert into this step.

Here is where you want to click Insert, and follow the prompt to complete the new step.

ImageLeft out

You will be using  master..xp_cmdshell
store procedure, to rename the production Production_RGU_Report. By having a T-SQL stored procedure rename the file,

 you avoid having to use both a Subscription Event and a programmer having to come after wards to rename the file on a daily basis.

Here is the T-SQL


SET @fn=REPLACE(convert(char(8),getdate(),1),'/','')

SET @cmd ='Ren \\Server\MyShared\New Folder\Report.xls \\chrd0dv02\MyShared\New Folder\Production_RGU_Reports '+@fn+'.xls'

Select @fn,@cmd

EXEC master..xp_cmdshell @cmd


 Image left out

You can type any name you want in the Step Name box. You can now select Transaction-SQL script (T-SQL)
for Type. Then Type in the SQL Script above of any appropriate script for renaming the files you want
to rename. Make sure that the paths
are correct for your system. 

Image left out

Let us say that you had an SQL Server integrated service package store on this server or
any server on your network. Change Type to "SQL Server integrated service package" then
 find the Package on the appropriate server. In the second  
article I will discuss creating that Package to do the rename.

After adding the second step, make sure that you use the up down button to set
this new step that we have created as the second step after the report is run.

So now we have the first Step 6FD6B328-692B-4749-A6E1-E4E760CC0A5D, then the 
second step Rename file. This job will run as a combined package.

Points of Interest

Editing an automate job created by Reporting Service 2005 Subscription.


Later this week I will submitting an article on creating a SSIS package to rename a file in sync with the Subscription Time event.



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Matthew Taylor
Software Developer Cox Telecommunication
United States United States
15+ years Information System and Software Development experience.
Currently Senior Software Engineer @ Cox Telecommunication Hamptons Roads.

1.Master of Science Electronic Commerce Development.
2.Microsoft Certified Application Developer (MCAD)
3.Brain Bench Certified .Net Developer

Currently persuiting my PMP from Should be taking the exam in 2 months.

You may also be interested in...


Comments and Discussions

QuestionCan you rename a file before its sent? Pin
Rob Eckert9-Jan-09 6:36
memberRob Eckert9-Jan-09 6:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 5 Mar 2008
Article Copyright 2008 by Matthew Taylor
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid