• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1079
  • Last Modified:

Error while calling CLR sproc

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
0
venk_r
Asked:
venk_r
  • 4
  • 3
1 Solution
 
Craig YellickDatabase ArchitectCommented:
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.

0
 
venk_rAuthor Commented:
Is there anyway I can increase connection timeout on the analysis server?
0
 
venk_rAuthor Commented:
Will increasing the Externalconenctiontimeout paramter on AS  help?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Craig YellickDatabase ArchitectCommented:
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.
0
 
venk_rAuthor Commented:
Thanks for the reply.The connection string from the client side has already been set to 0  as connectiontimeout.
0
 
Craig YellickDatabase ArchitectCommented:
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.
0
 
venk_rAuthor Commented:
thanks
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!

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