Solved

Error while calling CLR sproc

Posted on 2010-11-20
7
1,037 Views
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
0
Comment
Question by:venk_r
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:CraigYellick
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.

0
 
LVL 8

Author Comment

by:venk_r
ID: 34239249
Is there anyway I can increase connection timeout on the analysis server?
0
 
LVL 8

Author Comment

by:venk_r
ID: 34239254
Will increasing the Externalconenctiontimeout paramter on AS  help?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Expert Comment

by:CraigYellick
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.
0
 
LVL 8

Author Comment

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

Accepted Solution

by:
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.
0
 
LVL 8

Author Closing Comment

by:venk_r
ID: 34409455
thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

861 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