Solved

SSRS URL ERROR

Posted on 2014-01-13
9
926 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

14 Experts available now in Live!

Get 1:1 Help Now