Solved

SSRS URL ERROR

Posted on 2014-01-13
9
952 Views
Last Modified: 2014-01-28
I have report in SSRS when I run from  report manager it gives proper error message and but when I run from PowerShell it gives  below error.

The remote server returned an error: (500) Internal Server Error.

But when I run from SSRS report manager it gives below message and this is correct message. Is there any way I can capture below message from PowerShell Script.

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ds_checkpostfile'. (rsErrorExecutingCommand)
ERROR [HY000] Invalid table 'docline1'. On line 7, column 6. [parser-2906435]

Here is URL

http://Test/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fSoftwareDevelopment%2frs_test&rs:Command=Render&rs:Format=EXCEL&pr_date=2014/01/10
0
Comment
Question by:SURESH0518
  • 5
  • 3
9 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39778483
How are you running the report from PowerShell?  Please post the code...

I'll take a stab at it but might be wrong: are you sure you're using the correct URL to open the web service proxy?  You should be using the report server URL, not report manager.  But the URL you mentioned (which is using the report server) won't work either because it references an aspx page and not a web service endpoint.  A good URL would be http://test//ReportServer//ReportExecution2005.asmx?wsdl

Interesting read: Rendering SQL Reporting Reports with PowerShell
0
 

Author Comment

by:SURESH0518
ID: 39778862
I looked into URL you mentioned and each report will have different parameters and how do I code for each report.

Here is my code

Function  Process-ReportRendering {

     param([string]$fp_URL_Name,[string]$fp_Output_File_Name)
      #Create a WebRequest instance by calling Create with the URI of the resource and stores in a variable called $req.
      
      #Sets the property values of Method,Timeout and UseDefaultCredentials
      [Net.HttpWebRequest] $req = [Net.WebRequest]::create($fp_URL_Name)
      $req.Method = "GET"
      $req.Timeout = 600000 # = 10 minutes
      $req.UseDefaultCredentials = $true

      echo  "Getting Response"
      #Waits for return response from the specified url and stores in a varaible called $result
      [Net.HttpWebResponse] $result = $req.GetResponse()

      #Returns the data stream from the specified URI and stores in a variable called $stream
      [IO.Stream] $stream = $result.GetResponseStream()
      
      #Creates a FileStream object called $writeStream with file name specified in $fp_Output_File_Name variable
      [System.IO.FileStream]$writeStream = New-Object System.IO.FileStream($fp_Output_File_Name, [System.IO.FileMode]::Create);

      echo  "Created File $fp_Output_File_Name"

      # Creates empty byte array with size of 4096 bytes and creates a varaible $buffer
      [byte[]]$buffer = new-object byte[] 4096
      [int]$total = [int]$count = 0

    do
    {
                #Reads the stream data into $buffer variable
                $count = $stream.Read($buffer, 0, $buffer.Length)

                #Finally reads from $buffer variable and writes to the file that was specified $writestream object
                $writeStream.Write($buffer, 0, $count)
     }  while ($count -gt 0)
        echo  "Written to File"

    #Closes the $writeStream object
    $writeStream.Close()

    #Closes the #stream object
    $stream.Close()
    echo "File closed"
    return $Error.Count
}

Process-ReportRendering 'http://Test/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fSoftwareDevelopment%2frs_10023_CheckPostFile&rs:Command=Render&rs:Format=EXCEL&pr_date=2014/01/10' 'c:\junk\test.xls'

Open in new window

When I run the above code always says Exception calling "GetResponse" with "0" argument(s): "The remote server returned an error: (500) Internal Server Error.". But it does not show the actual error.

When I run the above url from Internet explorer it says

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ds_checkpostfile'. (rsErrorExecutingCommand)
ERROR [HY000] Invalid table 'docline1'. On line 7, column 6. [parser-2906435]

How can I capture above error from PowerShell Script
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39779299
Hmm, your script seems to be using a totally different method than the one explained in that article which I referenced in my previous post.  Perhaps it's worth investigating that method, as it mentions how parameters are passed in.

Has your function ever worked before or is this the first attempt?

Also, can you check the event logs on the remote server?  I would think they'd contain more details on the actual issue.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SURESH0518
ID: 39779363
This script already in production and works perfectly. This job runs from TIDAL scheduling software and whenever any errors occurs we are executing actual report since PowerShell not logging the proper error messages. It always says  "GetResponse" with "0" argument(s): "The remote server returned an error: (500) Internal Server Error.". But it does not show the actual error.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39781467
I'm not familiar with all of that, but still think that there should be details about the error somewhere on the server.  Most likely in the event logs, possibly in the SSRS logs.  Can you verify this?
0
 

Author Comment

by:SURESH0518
ID: 39785761
Yes errors exists in SSRS logs. The question is how do I retrieve particular error  programatically. I don't want to scan manually in SSRS logs directory.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39787894
I think there's a misunderstanding here.  The error to which I was referring in my previous comment is the 500 one.  I don't see why you'd want to automate this, you just need to find out why it happens and resolve the issue.  If you find any details related to the 500 error, please post them...
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39817040
Is your issue solved?  If not and my responses didn't help you any further then the appropriate action would be to Request Attention and ask to delete this question.

In any case it seems highly unlikely that my last response was the answer, even though you selected it as such...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the adminiā€¦

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question