I've got a C# application developed with Visual Studio .Net 2003 which uses Adomd.net (8.0) to access cubes on SQL Server 2000 Analysis Services as well as SQL Server 2005 Analysis Services. In the MSDN reference page I noticed that I can set the connection parameter "ConnectTo=Default", and now, after installing the SQL Server 2005 Client Connectivity components, I can make connections to both AS2K and AS2K5 from my development machine (XP SP2).
However, I can't figure out the dependencies to make this work on other systems. On another XP SP2 system (pretty bare-bones), if I install either the SQL Server 2000 or 2005 Client Connectivity components, I can connect to AS2K5, but when I try connecting to AS2K, I get:
AdomdConnectionException: A connection cannot be made. Ensure that the server is running. --> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it.
Following another thread in this forum, I tried installing PTSLITE for AS2K, but that had no effect.
On another XP SP2 system, with SQL Server 2000 client components, AS2K connections work but AS2K5 connections fail silently.
On a Win2K machine, AS2K5 connections work, but trying AS2K results in "AdomdErrorResponseException: The provider could not determine the value."
Can anyone tell me the essential prerequisites for making both connections? Thanks!
There are several components that are getting invoked to establish connection to different servers. You might need to try to see if each one of the is working separately.
1. Connection to AS 2005.
You can use ADOMD.NET 9 by installing Microsoft ADOMD.NET
from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
2. Connection to AS 2000
ADOMD.NET is using AS OLEDB 8.0 ( MSOLAP.2) OLE DB provider to connect to AS 2000.
After installing ptslite.exe (Microsoft SQL Server 2000 PivotTable Services ) from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en you can test if you can establish connection to AS 2000 by using MDX Sample application ( shipped with AS 2000)
ADOMD.NET v 9 is avaliable for download from the same page.
For your reference. The installation folder for ADOMD.NET is %SystemDrive%\Program Files\Microsoft.NET\ADOMD.NET\
There you fill find 80 or 90 sub-folders for correspodingly ADOMD.NET v8 and v9.
Hope using this information you will be able to troublshoot what is going on.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
|||
Thanks for the response.
However, I believe ADOMD.NET 9.0 depends on Framework 2.0, and I'm not ready to upgrade to Visual Studio 2005 just yet. I've been able to connect to AS2005 without it anyway.
I tried downloading the latest version of ptslite, but it still says the AS 2000 server "actively refused the connection".
|||
Also, does the .NET Framework cache the OLEDB providers? I've tried unregistering/renaming C:\Program Files\Common Files\System\Oledb\msolap80.dll to verify dependence, but the application connectivity still works the same!
I'm still not close to getting dual connectivity to work properly, so any suggestions are appreciated!
|||
To troubleshoot connectivity issues I would start from trying to establish connection to AS2000 using MDX Sample appication.
It is going to use AS OLEDB 8 ( MSOLAP.2 ) to connect.
See if you can copy MDX Sample app to your client machine and you can establish connection from there.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
|||
Well, I'm making a little progress. I finally realized that one test XP system wasn't able to connect to AS 2000 because I wasn't logged into it with a domain account, so it didn't have permission to access the cubes. That doesn't explain why it was able to access our AS 2005 system, though!
That brings me to the issue of my Windows 2000 test system. Logged into it with a domain account, the MDX Sample App works fine, but my .NET application still can't connect to AS 2000. (It still says "Exception: The provider could not determine the value.") It does connect to AS 2005, though. I verified in C:\Winnt\assembly that Microsoft.AnalysisServices.AdomdClient is version 8.0.700, the same as on my XP systems.
Any further suggestions? Thanks!
|||
hello Jon,
the list of pre-requisites for the adomd.net 8.0 is (as from the readme file):
* Microsoft .NET Framework Class Library 1.0 SP2 or greater
* MSXML 4.0 or greater
* AS2000 OLE DB provider required for Microsoft Analysis Services 2000 data access
so, i'd check on msxml4.0 if msolap80 is definitelly present.
if that does not help, wrap up the connection.Open() into following block of code, to see if it could reveal more information about the error:
try
{
// code here
}
catch (Exception e)
{
Exception ex = e;
while (ex != null)
{
Debug.WriteLine("========Exception================");
Debug.WriteLine("Type: " + ex.GetType().FullName);
Debug.WriteLine("Message: " + ex.Message);
Debug.WriteLine("Stack :" + ex.StackTrace);
AdomdErrorResponseException errResponse = ex as
AdomdErrorResponseException;
AdomdConnectionException conException = ex as
AdomdConnectionException;
if (errResponse != null)
{
foreach (AdomdError r in errResponse.Errors)
{
Debug.WriteLine("::::ERROR::::");
Debug.WriteLine("code: " +
r.ErrorCode.ToString());
Debug.WriteLine("msg: " + r.Message);
}
}
else if (conException != null)
{
Debug.WriteLine("ExceptionCause:" +
conException.ExceptionCause.ToString());
}
ex = ex.InnerException;
}
} // catch
hope this helps,
|||
Thanks for the response, but I don't think those prerequisites are correct. I installed the PTSLITE package, which include msolap80.dll, but it didn't make any difference in the connectivity behavior on my one Windows 2003 system. Also, I ran the application in the debugger on XP and when a connection was made to AS 2000, the list of loaded modules did not include msolap80.dll or msxml4.dll.
I did find some more unexpected behavior, though: on both Windows 2000 and Wndows 2003, I'm not able to connect to some AS 2000 servers (running on Win2K), but I tried another AS 2000 server running on Windows 2003, and the connection works! When the connection fails, the error information is:
========Exception================
Type: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException
Message: The provider could not determine the value.
Stack : at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection+IXmlaClientProviderEx.Discover(String requestType, String requestNamespace, IDictionary restrictions, Boolean throwOnErrors)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, IDictionary adomdRestrictions)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(Guid schema, Object[] restrictions)
at DataSupport.OlapReader.GetOlapCatalogs(String source, Boolean useWinSecurity, String userID, String password)
::::ERROR::::
code: 8
msg: The provider could not determine the value
|||
hello Jon,
according to the stack trace, it looks like you were able to connect, but what fails is the schema rowset request (it seems that the call is to the GetSchemaDataSet....., not conneciton.Open)? is it so? if so, could you please provide more details as to the actual schema request, and restrictions provided?
thank you,
|||
You're right, I guess the connection.Open() succeeds, and it's GetSchemaDataSet() that doesn't behave consistently on all instances. We call it with the constant AdomdSchemaGuid.Catalogs and an empty array of restrictions.
I notice in the reference page for AdomdSchemaGuid a note that "Some members of the AdomdSchemaGuid class (such as the CATALOGS schema rowset) may not be supported by your provider." But shouldn't this be consistent on all installations of SQL Server 2000 Analysis Services and all clients? As I said originally, my Win XP development system can connect to (and get the list of catalogs from) all AS instances, and one Win 2003 system can do the same for all AS 2000 systems, but a Win 2000 system and one Win 2003 system can't get the list of catlogs for some AS 2000 systems (running on Win 2000).
The reference page also says, "Refer to your database documentation to determine whether you can retrieve this schema information using other techniques." What other techniques might work better?
|||
hello Jon,
good thing is that we now seems to have determined exactly which operation fails, and that connection seems to succeed.
yes, i think Catalogs schema should work with AS. it is not yet clear what exacty is wrong here. could you please paste the exact connection string you have for adomd.net when this fails. Also could you please clarify whether you connect to same AS server (i mean you say from some machines getting catalogs succeeds but from other fails, so what i'm trying to understand is whether you try to connect to same server in both cases). Also, is it the same user that connects (i.e. does it have same rights when successfull and when failure ?)
Also, just to try isolating the problem more, could you please run the following code and see what happens (and post all error details if failure happens):
try
{
using (OleDbConnection connection = new OleDbConnection())
{
connection.ConnectionString = "Provider=MSOLAP.2;<your same connection string as for adomd.net>";
connection.Open();
// should be similar restrictions and schema as for the adomd.net code
DataTable table = connection.GetOleDbSchemaTable(AdomdSchemaGuid.Catalogs, new object[0] { });
}
}
catch (Exception ex)
{
while (ex != null)
{
Debug.WriteLine("========Exception================");
Debug.WriteLine("Type: " + ex.GetType().FullName);
Debug.WriteLine("Message: " + ex.Message);
Debug.WriteLine("Stack :" + ex.StackTrace);
if (ex is OleDbException)
{
OleDbException oledb = ex as OleDbException;
foreach (OleDbError r in oledb.Errors)
{
Debug.WriteLine("::::ERROR::::");
Debug.WriteLine("code: " + r.NativeError.ToString());
Debug.WriteLine("msg: " + r.Message);
}
}
ex = ex.InnerException;
}
}
thank you,
|||
Mary:
The connection string we're using with the AdomdConnection object is "Data Source=as2kserver;Integrated Security=SSPI;ConnectTo=Default". It is the same in all cases, whether the GetSchemaDataSet() succeeds or not.
Trying the OleDbConnection method, I get the following error message for the servers that fail:
========OLEDB Exception==========
Type: System.InvalidOperationException
Message: The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
Stack : at System.Data.OleDb.DBBindings.get_Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.OleDb.OleDbDataReader.DumpToTable(OleDbConnection connection, IRowset rowset)
at System.Data.OleDb.OleDbConnection.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at DataSupport.OlapReader.GetOlapCatalogs(String source, Boolean useWinSecurity, String userID, String password)
|||
hello Jon,
then i have to conclude that this is somehow related to msolap80 (oledb provider for AS2000), since the code above used it directly and also fails with same error. so this does not seem like adomd.net specific issue. (adomd.net essentially uses msolap80 when working with AS2000)
in this case i would expect that MDXSample application when run from same box and connecting to same AS server should also fail enumerating catalogs. does it happen indeed?
can you double check on msolap80: is it properly registered? what's it's version? is the version the same as msolap80 has on the box that successfully enumerates catalogs?
thank you,
|||
Mary:
I just ran MDXSample on my Windows 2000 system and it connected to my AS 2000 server (on Win2000) and listed catalogs and cubes successfully, but my .NET application with your code using OleDb still failed in GetOleDbSchemaTable() with the same "provider could not determine the Object value" error. Both accessed another AS 2000 server (running on Windows 2003) successfully.
On that Win2000 client system, C:\WINNT\assembly shows Microsoft.AnalysisServices.AdomdClient as version 8.0.700.0, while the File version and Product Version of C:\Program Files\Microsoft.NET\Adomd.NET\80\Microsoft.AnalysisServices.AdomdClient.dll show as 8.0.702.0. When I search in regedit for "MSOLAP.2", it finds C:\Program Files\Common Files\System\OLE DB\msolap80.dll.
It seems like there's some slight difference between my AS 2000 servers that is only noticeable from a .NET application.
|||
Jon,
i don't have a clear picture of what's wrong exactly (apart from it being related to msolap80 itself, since the test with OleDbConnection.....), but i guess one needs to find what is different in the case when failure happens from when it succeeds.
i'd check on the following:
1. whether the same version (service pack) is installed: between the AS2000 server that the app fails against, and the AS2000 server that you said the app succeeds working with.
2. check on the version of ptslite that you installed on the box from which app works fine, and on the box from which connection to one of the AS2000 server does not work.
3. try to see if you have some client machine from which the app succeeds against that AS 2000 server (on Win2000) (against which the client machine in question fails).
4. maybe .net is different on the box from which client works ok comparing to .net version on the box where client app fails? (btw. is the app asp.net or winforms/console ? if it's asp.net, i'd try a small winforms/console app with just that oledb code to see if that makes any difference)
sorry for not being able to pinpoint the issue faster, but i think the key is to try and spot the difference between the boxes where it works and where it does not.
thanks,
