Solved

Error while calling CLR sproc

Posted on 2010-11-20
7
1,040 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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