Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

SSRS URL ERROR

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
SURESH0518
Asked:
SURESH0518
  • 5
  • 3
1 Solution
 
ValentinoVBI ConsultantCommented:
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
 
SURESH0518Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
SURESH0518Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
SURESH0518Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
ValentinoVBI ConsultantCommented:
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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now