Error while calling CLR sproc

Posted on 2010-11-20
Last Modified: 2013-11-10
Im getting the below error while calling a CLR stored procedure which is designed to call MDX queries from analysis server.

2010-11-19 12:57:40,931 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 6522, SQLState: S1000
2010-11-19 12:57:40,931 ERROR [org.hibernate.util.JDBCExceptionReporter] A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_GetFleetAverage": ^M
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.Net.WebException: The remote server returned an error: (500) Internal Server Error.^M
System.Net.WebException: ^M
   at System.Net.HttpWebRequest.GetResponse()^M
   at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseStream()^M
   at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetResponseDataType()^M
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: ^M
   at DiscreteWireless.Marcus.BaseCubeDao.ExecuteMdxQuery(String query)^M
   at DiscreteWireless.Marcus.FleetsCubeDao.GetAverageMeasure(Int64 accountsRid, Int64 usersRid, Int64 fleetsRid, DateTime startDate, DateTime endDate, String measureType)^M
   at DiscreteWireless.Marcus.MdxStoredProcedures.GetFleetAverage(SqlInt64 accountsRid, SqlInt64 usersRid, SqlInt64 fleetsRid, SqlDateTime startDate, SqlDateTime endDate, SqlString measureType)^M
Question by:venk_r
  • 4
  • 3
LVL 11

Expert Comment

ID: 34180153
Two portions of the error message stick out to me:

error occurred during execution of user-defined routine or aggregate
The connection...timed out

Is it possible the GetFleetAverage aggregation function sometimes takes too long to return a result? Try using it at a much lower level of detail where you know very few records need to be aggregated. If it works there but not at a higher level, it might be simply taking too long.


Author Comment

ID: 34239249
Is there anyway I can increase connection timeout on the analysis server?

Author Comment

ID: 34239254
Will increasing the Externalconenctiontimeout paramter on AS  help?
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 11

Expert Comment

ID: 34239689
Client applications can change their timeout value via the connection string they use to connect. There's probably a way to change the server's default value but I'm not up on admin topics like this.

Author Comment

ID: 34239709
Thanks for the reply.The connection string from the client side has already been set to 0  as connectiontimeout.
LVL 11

Accepted Solution

CraigYellick earned 500 total points
ID: 34239763
That's too bad, it means the server process is timing out waiting for the aggregation function to finish, independent of the client-side process.  

Setting the server's time-out to be longer is probably not a good solution. The function needs to either be optimized or users need to make sure it isn't part of expressions that are long-running.

Author Closing Comment

ID: 34409455

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

12 Experts available now in Live!

Get 1:1 Help Now