Problems continued - Cannot establish remote connection to Analysis Services

hello,

i am trying to connect to Analysis services server through another pc but i am unable to do so.

Using the following commands i manage to connect to analysis services server at my pc

Server.srv=new Server ();

srv.Connect('ip address or name');

but not to another pc. Are there any settings under sql server should i change ?

I assume that you are using Analysis Services 2005.

First, make sure that the Analysis Services allows remote connections:

On the machine running analysis services, please perform the following steps:

- All programs\Microsoft SQL Server 2005\Configuration Tools\Sql Server Surface Area Configuration

- Click on the Surface Area Configuration for Services and Connections

- Select Remote Connections under the MSSQLSERVER\Analysis Services node

- Make sure that Local and Remote connections is selected (to allow remote connections)

At this point, the client machine should be able to connect to the remote server. Before everything works, you will need to make sure that the client machine connection code is running under credentials that can access Analysis Services

|||

Well i have already enable remote connections and chech credentials as well

This is the error i am getting :

{"A connection cannot be made. Ensure that the server is running."} System.Exception {Microsoft.AnalysisServices.ConnectionException}

{"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"} System.Exception {System.Net.Sockets.SocketException}

Any help ? thanks.

|||That is unusual. Does PING work for the remote computer? Also, could you please check any firewall settings on the remote computer? Analysis Services (msmdsrv.exe) needs permissions through the firewall|||

PING works fine among the two ps's and analysis services is excepted under firewall .

I am still facing the same problem.

Can you please provide me the exact connection string from a pc1 with ip 10.181.11.1 to a pc2 with ip 10.181.11.2 (asp form in pc1 to analysis server in pc2)?

Maybe i use a wrong connection string

This is the error i am getting :

Unable to read data from the transport connection : an existing connection was forcibly closed by the remote host

Any help?

Thanks

|||

Are you using OLE DB or Adomd.Net?

The Connection String should look like "Data Source=10.181.11.2; Initial Catalog=<Your Database>"

One possible reason for the error is that your current user does not have permissions on the server. Are the computers in the same domain? If not, is Everyone allowed to access a database on the server?

|||

I am using ADOMD connection,,if you have an example connection string it would be helpful to check with the one i use

(i have tried different ways of setting the connection string but still cannot connect ).

As far as user credentials are concerned i have also tried to set them in different ways.

I am not quite sure which user (WinXP user, Database Engine User , IIS user, Analysis Services user) must have access to which place,, i am a bit confused about this.

I appreciate your help. Thank you

|||

To begin, let's make sure the physical connection works correctly. On the Analysis Services machine, create a new Analysis Services role which has permissions on your target database, and make sure that Everyone is a member of that role.

Then, try to connect from your client machine, using a connection string like

"Data Source=<IP of the remote machine>; Initial catalog=<name of the DB which is accessible to Everyone>"

If this works, the physical connection is OK and we'll focus on credentials.

Are your machines part of the same Windows NT domain? Tell me a few things about your application, I understand it is a web application, is it correct? Is it impersonating the remote user or running uner a set of specified credentials, or running under the default credentials (machine\ASPNET user)?

And, if you don't mind, could you please reply to this thread rather than creating a new post? It is easier to keep track of the issues

|||

We did what you suggested but we still get the same error.

Do we need to give the permissions and to the actual database (database engine) except to the one in analysis services ?

Our machines are part of the same workgroup connected via a router.

It is a web application and it is runnig under the default credentials.

But we tried with a windows application to check if we can connect to the analysis services and we still get the error

We can access a remote database engine througn sql server management studio but not remote analysis services server.

Is there a way to access a remote analysis services through sql server management studio?

Thanks

|||

This reply involves another error (not the one mentioned above). It seems to have a problem in creating the DIMENSION through source code and the problem seems to be on 'Process' command. We removed the Process command from the code and tried to process it through anaysis services management studio and still got error 'process failed' . This is a critical issue ,,i cannot continue to create cubes, etc.

As far as my application is concerned ,,it is a web application ,,the user will choose through an asp form measures and dimensions and then dimensions,cubes,structures will be created dynamically. So the problem is at initial state since i cannot create the dimenions.

Pls help.

This is the source code i use for dimension creation :

static void CreateDateDimension(Database db)

{

// Create the Date dimension

Dimension dim = db.Dimensions.Add("Invitquest");

dim.Type = DimensionType.Time;

dim.UnknownMember = UnknownMemberBehavior.Hidden;

dim.AttributeAllMemberName = "All Periods";

dim.Source = new DataSourceViewBinding(datasourceName);

dim.StorageMode = DimensionStorageMode.Molap;

#region Create attributes

DimensionAttribute attr;

attr = dim.Attributes.Add("Invitquest");

attr.Usage = AttributeUsage.Key;

attr.Type = AttributeType.Date;

attr.OrderBy = OrderBy.Key;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "invno"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "tableid");

attr = dim.Attributes.Add("YPopto");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "Ypopto"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "Ypopto");

attr = dim.Attributes.Add("cancer");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "CANCER"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "CANCER");

attr = dim.Attributes.Add("AM BIO");

attr.Type = AttributeType.Regular;

attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "Invitquest", "AMBIO"));

// attr.NameColumn = CreateDataItem(db.DataSourceViews[0], "Invitquest", "AMBIO");

#endregion

dim.Update();

}

Thanks.

|||I merged all these threads.|||

> Our machines are part of the same workgroup connected via a router.

This seems to be the issue. Analysis Services uses exclusively Windows Integrated security. User accounts are not shared across machines in a workgroup, only in a domain (or related domains). This might explain both the management studio failure as well as the custom application failure.

Can you try allowing Everyone access to the Analysis Services database? Eventually , the permissions for the Everyone role can be restricted to what is needed (most likely, Read Definition and predictions), but you can start with Everyone as administrator -- not a great security suggestion, but it allows you to easily figure out if this is the source of the problem.

sql

Problem With Variables in Execute SQL Task

I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.

when I run the package i get the following error:

Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.

Please help i have very little hair left!!

Is the SQLSourceType=Variable and the SourceVariable set to the name of your variable in the task editor? It looks as if maybe SQLSourceType=Variable and then you're setting the SqlStatementSource property through an expression to the value of your variable.
|||

Hi Jay

i have checked and the SqlSourceType is Variable and the SourceVariable is set to User:: SQLTest2 which is the name of the variable that contains the Value INSERT blah blah blah....

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

Any help appreciated

|||Delete the Execute SQL Task and try recreating it. I think, too, there must be an expression set somewhere incorrectly.|||

Racsco wrote:

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

That shouldn't be happening. Maybe you should delete the Execute SQL task and recreate it.
|||

Hi Guys

thanks for your continuing help.

I have deleted and recreated it and now i get the following error

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

any ideas?

|||Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.|||

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.
|||

JayH wrote:

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.

Oh, right. I saw the INSERT and the SELECT together and misread it as two distinct statements.

So unless you have another SQL statement after the result set, set the result set to "None."|||

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

|||

Racsco wrote:

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

Your description sounds good. I assume you have a reason for doing it this way instead of just bulk loading into rptlifespan_transactionimagecopy from the data flow. So from your "frustration" comment I assume you're still having a problem. Is it the "An error occurred while extracting the result into a variable..."? I don't see how you could be getting that if your ResultSet is None and there are no mappings on the Resultset page.
|||Forget the data flow.

Code block because of the $@.!* emoticon conversions...

1 - Execute SQL Task: select uniqueID from table. Put that into an object variable (User::ObjectVar, perhaps) and set the result set type to "Full Result Set"
2 - Foreach Loop - Set it to loop on the object variable created from step 1 (User::ObjectVar). Map the output accordingly to your "holding" variable (User::UniqueID)
3 - Execute SQL Task within the foreach loop: SQLSourceType: Variable SourceVariable: User::SQLTest3

That's it. One thing to note: On the variable, User::UniqueID, make sure you have EvaluateAsExpression set to TRUE and that you are using the expression property to build the insert string.|||

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

|||

Racsco wrote:

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

On the properties for SQLTest3, since you are concatenating another user variable in your SQL statement, you need to do that in the expression. Set the property, EvaluateAsExpression = TRUE for the variable, SQLTest3. Then use the expression:

"INSERT INTO ... SELECT " + [User::UniqueID]|||

Hi Phil

Went home and slept on it, came in deleted the lot and started fresh. What I did differently was to cast the UniqueID as a string from the initial select rather than casting it in User:: SQLtest3 variable and it works fine now.

Thanks for all your help and patience and also thanks to JayH

scott