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

okay, I thought I had the return value from a statement figured out...

So, Jimmy G helped me out with it in showing a little bit how to do it.

SqlCommand command = new SqlCommand(, object>)SqlParameter param = new SqlParameter();param.ParameterName ="@.return";param.Direction = ParameterDirection.ReturnValue;command.Parameters.Add(param);command.ExecuteNonQuery();//get the return valueint val = int.Parse(command.Parameters[0].ToString());

Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following

Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs)
dim returnValue as integer

'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable?

InsertIssue.Insert()

Response.Redirect("/addarticletoissue")

End Sub

again, thank you for your help and patience with such a beginner =)

I haven't seen your original post where jimmyg helped you out, so I am going to guess that you are trying to retrieve the autoincrement identity value of a newly added record from a SqlDataSource Try the approach detailed here:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54 about 2/3rds of the way down.

|||

ahh! thank you so very much. This helps a ton!

Offline command hangs when I try to take database offline

Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?Are there any users / connections to the database that you want to take offline? If so, you should ensure that these are disconnected first, as you cannot take a database offline until there are no outstanding connections.

Thanks,|||

Try "alter database <db> set restricted_user with rollback immediate" before setting it offline. This will terminiate user connections and rollback their transactions and only allow priviledged users to connect.