Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error while calling CLR sproc

Posted on 2010-11-20
7
Medium Priority
?
1,068 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:Craig Yellick
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 11

Expert Comment

by:Craig Yellick
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:
Craig Yellick earned 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

916 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