Getting "An existing connection was forcibly closed by the remote host (System)" error

I cannot connect to Analysis Services through SSMS. Receiving a "An existing connection was forcibly closed by the remote host (System) error." I changed my password this morning due to our company security policies and now I can't do squat. This is definitely a bug in the service as something is cached and not clearing out. I've rebooted both the server and client a couple of times. Running AS 2005 SP1 on Windows Server 2003. I can connect through another machine. Here's the message I'm getting. Don't know where to look to fix the problem.

===================================

Cannot connect to hctx-idwdev.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

===================================

An existing connection was forcibly closed by the remote host (System)

Program Location:

at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

I've uninstalled the client tools and reinstalled. After rebooting, I STILL CANNOT CONNECT TO ANALYSIS SERVICES FROM THIS MACHINE.

Major issue addressed in next service pack? Is there a local cache I need to clear? Any help would be greatly appreciated.

|||Fixed the problem even though the solution is less than ideal. To resolve, we had to delete my user profile on the machine. Apparently, something used by the drivers is being stored in the Document and Settings for the current user. What exactly we weren't able to identify.

Getting "An existing connection was forcibly closed by the remote host (System)" e

I cannot connect to Analysis Services through SSMS. Receiving a "An existing connection was forcibly closed by the remote host (System) error." I changed my password this morning due to our company security policies and now I can't do squat. This is definitely a bug in the service as something is cached and not clearing out. I've rebooted both the server and client a couple of times. Running AS 2005 SP1 on Windows Server 2003. I can connect through another machine. Here's the message I'm getting. Don't know where to look to fix the problem.

===================================

Cannot connect to hctx-idwdev.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()

===================================

An existing connection was forcibly closed by the remote host (System)

Program Location:

at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

I've uninstalled the client tools and reinstalled. After rebooting, I STILL CANNOT CONNECT TO ANALYSIS SERVICES FROM THIS MACHINE.

Major issue addressed in next service pack? Is there a local cache I need to clear? Any help would be greatly appreciated.

|||Fixed the problem even though the solution is less than ideal. To resolve, we had to delete my user profile on the machine. Apparently, something used by the drivers is being stored in the Document and Settings for the current user. What exactly we weren't able to identify.sql

get unspecified error when browsing data of CUBE in Analysis servise

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.

You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

get unspecified error when browsing data of CUBE in Analysis servise

Please give me solution,

I am getting this message when I am browsing data of cude

"Unable to browse data of <cude name>
unspecified error"

please tell me the solution of this.You're going to have to be a little more specific here. What tools are you using? What version of SQL Server? What cube? What's working / not working?|||Sir,
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||
I have SQL Server 2000 installed on Windows 2000 Advance Server. On same machine I installed MS Analysis servise. By default Analysis service have FoodMart2000 database which have some cubes and dimension. When I right click on cube and browse the data, it open the data browsing window and following message display

"Unspecified error unable to browes the data"

So please solve my problem and what can i do|||

What service pack have you installed? If you look at the Microsoft knowledgebase you will see that there are several problems which caused this. Typically it is fixed once you install SP4.

_-_-_ Dave

|||hey asif, m also getting the same problem, did u find any solution. I have installed SP4

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!