Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Reporting Services Tip #3

Reporting Services Tip #3 - rs.exe, backslash problem

In a previous blog, I wrote how you can deploy your reports via Report Manager.  It was mentioned in the comments section that "you can use the web service to upload the RDL directory from code".  Recently, I started to look into this so that deploying reports would be easier, that is without the deploy option in Visual Studio. 

Reporting Services comes with a command line utility, rs.exe, that allows you to administer Report Server via scripts that run Web Service operations.  These scripts must be written in VB.NET and a suggested extension for them is rss (Reporting Services script file).

You can use rs.exe to publish the sample reports provided by MS.  Information about this can be found in SQL Server Reporting Services Books Online.  Reading this information helped me write a script that publishes my reports.  The problem with their script is that it assumes that the reports do not already exist on that Report Server.  My script, however, overwrites them if they exist.  I also didn't want to hard code the database server name, which is what their script does, so I used a variable in the script instead and will get the value from the rs.exe command.  The rss code is at the end of this weblog.

To call the rss script via rs.exe, you specify the file name, URL of Report Server, and the input parameters.  This rss script has 3 inputs.  One for the SQL Server name, one for the name of the parent folder, and the other for the path to the rdl files.  One thing to note is that SQL Server Reporting Services Books Online shows how to pass in the input parameters.  But the documentation is incorrect, which I found out from MS via a support case that I opened for a different problem which I'll discuss in a bit.  The documentation says to do this -v a="b" c="d".  This should be corrected to -v a="b" -v c="d".  So you must specify the v switch for each input parameter. 

Here's an example call:

rs -i PublishReports.rss -s http://ServerName/reportserver -v filePath="C:\Reports" -v sqlServerName="SQLServerName" -v parentFolder="SomeParentFolder"

PublishReports.rss is the name of the script file that I used.  If you don't want to navigate to it via the command line, then you can include its path as well:

rs -i C:\SomeLocation\PublishReports.rss -s http://ServerName/reportserver -v filePath="C:\Reports" -v sqlServerName="SQLServerName" -v parentFolder="SomeParentFolder"

The support case that I opened with MS had to do with me trying to call rs.exe with 3 input parameters like this:

rs -i PublishReports.rss -s http://ServerName/reportserver -v filePath="C:\Reports\" -v sqlServerName="SQLServerName" -v parentFolder="SomeParentFolder"

The difference between this last one and the others is the ending backslash in the filePath input parameter.  When you try running it, it will give you an error.  It turns out that when you have more than 2 input parameters, you must escape this backslash in the rss code.  Obviously this is a bug, but I'm not sure if this will be included in the next service pack or not.  In the code that I have below, I have included the backslash to avoid this bug.

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder

Public Sub Main()

 rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 Dim name As String

 'Create the parent folder
 Try
  rs.CreateFolder(parentFolder, "/", Nothing)
  Console.WriteLine("Parent folder created: {0}", parentFolder)
  
 Catch e As Exception
  'Console.WriteLine(e.Message)
  
 End Try
   
 CreateHOSDataSource(sqlServerName)
 
 PublishReport("Report1")
 PublishReport("Report2")
 PublishReport("Report3")
 PublishReport("Report4")
 PublishReport("Report5")
 
End Sub

Public Sub CreateHOSDataSource(ByVal sqlServerName As String)

 Dim name As String = "DataSource1"
 Dim parent As String = "/" + parentFolder

 'Define the data source definition.
 Dim definition As New DataSourceDefinition()
 definition.CredentialRetrieval = CredentialRetrievalEnum.Store
 definition.ConnectString = "data source=" & sqlServerName & ";initial catalog=DB1"
 definition.Enabled = True
 definition.EnabledSpecified = True
 definition.Extension = "SQL"
 definition.ImpersonateUser = False
 definition.ImpersonateUserSpecified = True
 definition.WindowsCredentials = False
 definition.UserName = "SomeUser"
 definition.Password = "somepassword"

 Try
  rs.CreateDataSource(name, parent, True, definition, Nothing)
  
 Catch e As Exception
  Console.WriteLine(e.Message)
  
 End Try
 
End Sub


Public Sub PublishReport(ByVal reportName As String)

 Try
  Dim stream As FileStream = File.OpenRead(filePath + "\" + reportName + ".rdl")
  definition = New [Byte](stream.Length) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()
       
    Catch e As IOException
        Console.WriteLine(e.Message)
       
    End Try

    Try
        warnings = rs.CreateReport(reportName, parentPath, True, definition, Nothing)

        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            For Each warning In warnings
                Console.WriteLine(warning.Message)
            Next warning
           
        Else
            Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
           
        End If
       
    Catch e As Exception
        Console.WriteLine(e.Message)
       
    End Try
   
End Sub

 

Print | posted on Wednesday, January 05, 2005 11:03 AM | Filed Under [ SQL Server - Reporting Services ]

Feedback

Gravatar

# OdeToCode Links Jan 06

1/6/2005 10:19 PM | OdeToCode Links
Gravatar

# re: Reporting Services Tip #3

Good example! :-) I'm in the process of trying to automate the deployment of multiple reports to multiple databases.
1/13/2005 9:50 AM | roy
Gravatar

# re: Reporting Services Tip #3

This is a very good example , as we are in the hunt of the code .
1/19/2005 10:24 AM | Sravan
Gravatar

# How to publish Image file that is embeded in the report file

My reports have company logo embeded into it. How can I publish the reports with this logo?
When I am publishing the reports, the logo part is not added into the report.Please let me know how to do the same.
6/1/2005 12:10 PM | Jit
Gravatar

# re: Reporting Services Tip #3

The order of the parameters appears to be important otherwise the script will not pass the variables across. The -vparentFolder must appear first.
To embed a logo make sure that it's embedded in your .rdl file.
This method of publishing reports lets you change graph scales dynamically as you can load the .rdl as xml and amend the node inner text of the fly !
8/8/2005 2:13 AM | Nick Lucas
Gravatar

# re: Reporting Services Tip #3

Hi everybody !
Is it possible to use MS Graph in Reporting Services ?

Thanx !
AJ
9/17/2005 5:08 PM | AJ
Gravatar

# re: Reporting Services Tip #3

Can you advise?
My directory name have spaces in the name. RS is not accepting it.

filePath="C:\inetpub\wwwroot\Reports\report demo project\"


C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services>rs -i C:\inetpub\wwwroot\Reports\report demo project\PublishReports.rss -s http://dev.ppm.iweb.eds.com/reportserver/ -v filePath="C:\temp\"

ERROR IS:

Could not find
file "C:\inetpub\wwwroot\Reports\report".
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services>


Thanks

10/1/2005 3:31 PM | Katherine
Gravatar

# re: Reporting Services Tip #3

You don't need a utility to remove reports as Report Manager can do this. You are able to remove multiple reports at one time, whereas adding them you can't.
10/21/2005 9:51 AM | Tara
Gravatar

# re: Reporting Services Tip #3

Do think it's possible to deploy an image on the report server in the same way?

2/13/2006 8:50 AM | Rania Badawi
Gravatar

# re: Reporting Services Tip #3

I am in a need of displaying a chart in my report. This chart image comes in the form of a Byte array from a webservice function. Everything works fine from development environment, report displays chart returned by web service. But when I publish my reporting service project to the Reporting Server, chart image doesn't show up. I have added app_code.dll to the publicassemblies folder. Any idea?

Thanks
2/28/2006 12:53 PM | Mehul Thakkar
Gravatar

# re: Reporting Services Tip #3

This procedure will upload an image used in report if you have it as a separate file (*.jpg). In case of different imagetypes or other resources, you have to change MIME type (image/jpg) in the code below...
It is modified code from reporting script sample, so there are few lines not needed, but I dont have a time to correct it as it is working and that is important. :)

Public Sub PublishImage(ByVal imageName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + imageName)
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

Catch e As IOException
Console.WriteLine(e.Message)
End Try

Try
rs.CreateResource(imageName, parentPath, False, definition, "image/jpeg", Nothing)

Console.WriteLine("Image: {0} published successfully", imageName)

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
3/10/2006 3:56 AM | Martin Koci
Gravatar

# re: Reporting Services Tip #3

Thanks very much VGT. That tool was greate..

Thanks,
Dhanushka
6/6/2006 8:20 AM | Dhanushka
Gravatar

# re: Reporting Services Tip #3

From my machine when I execute rs.CreateDataSource to create a datasorce on a different machine, I get the error "The request failed with HTTP status 404: Not Found.
".
The Credentials info specified is as below:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Both the machines are in the same domain.
Can anybody help in what I need to change in order to solve this.
8/2/2006 12:15 AM | Vishal Mistry
Gravatar

# re: Reporting Services Tip #3

If I want to create a data source by referrign reportingservice2005 and using rs.createdatasource.

the question is I am executing this code from client machine and the want to create a datasource on the server So how and where do I specify that.

thanks
8/2/2006 12:24 AM | Vishal Mistry
Gravatar

# re: Reporting Services Tip #3

How do i depoly multiple RDL files at one go...i want to do this using a script or command file..
8/8/2006 11:23 PM | Manoj
Gravatar

# re: Reporting Services Tip #3

Manoj, that's the whole point of this blog. The code provided can do this for you.
8/9/2006 9:45 AM | Tara
Gravatar

# re: Reporting Services Tip #3

Hi All!

Is it possible in the .rss input file to indicate that a certain report being published should have the "Hide in list view" property set during report installation?

Many thanks!
9/15/2006 10:44 AM | Murray
Gravatar

# re: Reporting Services Tip #3

Thanks for posting this sample. I discovered that by including the line:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

you cancel out any username and password that is passed to rs.exe on the command line. If, like I did, you need to specify credentials to the ReportServer, you can simply comment out this line and it should work.
9/21/2006 3:41 PM | jason
Gravatar

# re: Reporting Services Tip #3

I am a Quality Engineer. I have been working on testing the reports created using Reporting Services. For the performace figures like 'how much time is required for execution of particualr report?' that i am taking from ExecutionLog table of ReportServer Database. But the problem is the time taken for execution of report is not same although we execute the same report with the same parameter and with the sane database. It has lots of variations in timings. I have tries restarting services before taking exact time? but its of no use. Can anybody guide me to take the exact timings (consistency) for the same report execution?
9/22/2006 2:27 AM | Dinesh
Gravatar

# re: Reporting Services Tip #3

Hi, can anyone give me a heads up on whether this will work for a reporting service hosted on a secure site (https)?

many thanks

Matt
10/2/2006 6:11 PM | Matt
Gravatar

# re: Reporting Services Tip #3

If I publish a report once, it will publish correctly. However, the second, third, etc. times I want to publish the report, it should replace the previous one and I'm not achieving this. What should I do? Many thanks.

Pedro Martins
11/24/2006 9:56 AM | Pedro Martins
Gravatar

# re: Reporting Services Tip #3

If you use the code in this blog, it will overwrite the previous version. It does this in this line of code:

rs.CreateReport(reportName, parentPath, True, definition, Nothing)

How are you publishing your reports?
11/27/2006 11:31 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET