Click here to Skip to main content
Click here to Skip to main content
Go to top

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

Introduction

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.

History

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

 

License

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

Share

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.
 
Qualification:
1.Master of Science Electronic Commerce Development.
2.Microsoft Certified Application Developer (MCAD)
3.Brain Bench Certified .Net Developer
 
Currently persuiting my PMP from PMI.org. Should be taking the exam in 2 months.

Comments and Discussions

 
QuestionCan you rename a file before its sent? PinmemberRob Eckert9-Jan-09 5:36 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 5 Mar 2008
Article Copyright 2008 by Matthew Taylor
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid