Solved

SSRS URL ERROR

Posted on 2014-01-13
9
920 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now