Processing priority based on client connection?

Is there a way to set a process priority in SQL Server 2000 based upon the
client or connection type being used to access the data? For example, on a
particular database, I want to give the client application for which the
database was designed priority over other connections such as Microsoft
Access or Seagate Crystal Reports.
Can this be done?As far as I know, it is not possible to set priority based on the client =or the connection.
-- Keith
"Glen Appleton" <buglen@.hotmail.com> wrote in message =news:O432%23p2uDHA.1788@.tk2msftngp13.phx.gbl...
> Is there a way to set a process priority in SQL Server 2000 based upon =the
> client or connection type being used to access the data? For example, =on a
> particular database, I want to give the client application for which =the
> database was designed priority over other connections such as =Microsoft
> Access or Seagate Crystal Reports.
> > Can this be done?
> >

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQ

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”

amoDimension.Process(ProcessType.ProcessFull)

The following exception is returned:

- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.

"} System.Exception

The connection string to the “DataSource” is:

ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String

ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity

Everything work properly is if I switch the connection type to “Windows Authentication”

Thanks,

Yones

Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.

Adrian Dumitrascu

|||

Thank you very much Adrian.

It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.

Could you please tell me how can I re-specify the password?

Thanks,

Yones|||

> Could you please tell me how can I re-specify the password?

Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.

Adrian Dumitrascu

|||

I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.

m_amoServer.Connect(i_strDataSource)

m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)

The result:

m_amoDataBase.DataSources (0)

ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String

ConnectionStringSecurityPasswordRemoved {1}

Thanks Again,

Yones

|||

The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.

Adrian Dumitrascu

Processing a dimension using AMO does not work if the connection to the “DataSource” used is

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”

amoDimension.Process(ProcessType.ProcessFull)

The following exception is returned:

- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.

"} System.Exception

The connection string to the “DataSource” is:

ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String

ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity

Everything work properly is if I switch the connection type to “Windows Authentication”

Thanks,

Yones

Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.

Adrian Dumitrascu

|||

Thank you very much Adrian.

It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.

Could you please tell me how can I re-specify the password?

Thanks,

Yones|||

> Could you please tell me how can I re-specify the password?

Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.

Adrian Dumitrascu

|||

I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.

m_amoServer.Connect(i_strDataSource)

m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)

The result:

m_amoDataBase.DataSources (0)

ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String

ConnectionStringSecurityPasswordRemoved {1}

Thanks Again,

Yones

|||

The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.

Adrian Dumitrascu

Process AS2000 Cubes in SSIS

Hi,

I would like to process an AS 2000 cube in SSIS.

I'm using the 'Analysis Services Processing Task', but I just get the error 'A connection cannot be made. Ensure the server is running'.

It would be an unfortunate limitation if you can only process 2005 cubes in SISS. Does anyone know if its possible to process 2000 cubes in SSIS?

Many thanks

Jeremy

'Analysis Services Processing Task' works against AS 2005 only. There is no built-in task to process AS 2000 objects.

You can try the Script Task and write code that will send the process request to a AS 2000 server

Regards,
Ovidiu Burlacu

|||

Its quite a clunky, fiddly way to go about things, but if its the only way to do it then so be it

Many thanks

Jeremy

|||I am trying to do the same thing! - we have a 3rd part dw app that uses sql 2000 olap, and will not support an upgrade to 2005 olap. Did you find anything new?

process add and configuration

SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.

I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.

The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.

But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.

But Howto change the Processing Command withing the Process ADD Destination -

or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)

Best Regards

HANNES

I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?

Best regards, HANNES

|||

I got it

The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.

You always have to code the database name in the XML Command

HANNES

process add and configuration

SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.

I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.

The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.

But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.

But Howto change the Processing Command withing the Process ADD Destination -

or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)

Best Regards

HANNES

I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?

Best regards, HANNES

|||

I got it

The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.

You always have to code the database name in the XML Command

HANNES