OLAP connection problems

I have two systems setup with SQL Server 2000 + Analysis Services. Both servers have been patched with both SP3a patches.

I wrote a trivial command line app that tests connection strings through ADO.

On each system, I can connect locally perfectly using the connection string:
"Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

However, once I change "Data Source" to refer to the other system, the call to ADODB.Connection.Open throws a "System.Runtime.InteropServices.COMException" with message: "Unspecified error". (The simple app is written in C# and talks to ADO through Interop)

I've tried referencing ADO 2.1, ADO 2.5 and ADO 2.7
I've tried a "Provider" of "MSOLAP.2" as well as just "MSOLAP"
I've also tried a variety of different connection strings with many different permissions settings.

It is very frustrating to get an absolutely generic error message.

Any ideas?

Thanks in advance!Have you tried adding "User ID=xxx;Password=xxx"

or

"Integrated Security=SSPI"|||Yes, just retried all of them and they all result in the same System.Runtime.InteropServices.COMException, "Unspecified error"

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"|||Let me reclarify. This one works fine:

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

These (that reference another system) fail with System.Runtime.InteropServices.COMException, "Unspecified error":

adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"

I experience the same effect from two systems; either system can open its own database but can't open a remote one.|||Can't think of anything here...Maybe these links can give you some ideas?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_8wfm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_64fb.asp

ODBC/SQL M$ EXCEL 97 vs XP

Any ideas appreciated...
Users can export to Excel97 but cannot using ExcelXP. Get
error: "unknown token received from sql server"
Have patched SQL & OfficeXP locally to current M$ levels.
ODBC connects no problem.
I'm a tech, not a programmer, so any ideas really
appreciated.
JimmieDExport from what how? More details needed.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 26 Dec 2003 05:55:00 -0800, "JimmieD"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>Any ideas appreciated...
>Users can export to Excel97 but cannot using ExcelXP. Get
>error: "unknown token received from sql server"
>Have patched SQL & OfficeXP locally to current M$ levels.
>ODBC connects no problem.
>I'm a tech, not a programmer, so any ideas really
>appreciated.
>JimmieD

---

|||SQL Database using a macro. It appears to download the
info but it looks like right before it displays the data
in EXCELXP, the error appears.
Macro/export functions OK in EXCEL97.
Jim

quote:

---

>--Original Message--
>Export from what how? More details needed.
>-- Mary
>MCW Technologies
>http://www.mcwtech.com
>On Fri, 26 Dec 2003 05:55:00 -0800, "JimmieD"
><anonymous@.discussions.microsoft.com> wrote:
>
Get[QUOTE]
levels.[QUOTE]
>.
>

---

|||I think you're going to have a hard time with this one unless you get
a programmer to help you. If you're using an Excel macro, it's written
in VBA. A programmer can help you step through the code to see where
it's failing.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 26 Dec 2003 08:33:06 -0800, "JimmieD"
<anonymous@.discussions.microsoft.com> wrote:
[QUOTE]
>SQL Database using a macro. It appears to download the
>info but it looks like right before it displays the data
>in EXCELXP, the error appears.
>Macro/export functions OK in EXCEL97.
>Jim
>Get
>levels.