Analysis Server not working with MSDE SQL Server

I installed Visual Studio .NET v.1.0 and the included MSDE version of
SQL Server. I have an additional copy of MS SQL Server 2000 Enterprise
Ed. on CD. I installed Microsoft Analysis Services from this CD on my
machine with MSDE SQL Server; it appeared to install cleanly.
I ran Analysis Manager from the Start menu under Windows XP Pro. The
local SQL Server showed red block indicating the server was not
running. I tried to connect, and got the following error message:
Unable to connect to the registry on the server (myserver_name), or you
are not a member of the OLAP Administrators group on this server.
Does anyone know the problem here? I was wondering whether I had to
install MS SQL Server enterprise ed. to use Analysis Server with it,
rather than more limited MSDE version of SQL Server ?!
Thanks all.
Fred Z.
hi Fred,
silversw2000@.yahoo.com wrote:
> I installed Visual Studio .NET v.1.0 and the included MSDE version of
> SQL Server. I have an additional copy of MS SQL Server 2000 Enterprise
> Ed. on CD. I installed Microsoft Analysis Services from this CD on my
> machine with MSDE SQL Server; it appeared to install cleanly.
> I ran Analysis Manager from the Start menu under Windows XP Pro. The
> local SQL Server showed red block indicating the server was not
> running. I tried to connect, and got the following error message:
> Unable to connect to the registry on the server (myserver_name), or
> you are not a member of the OLAP Administrators group on this server.
> Does anyone know the problem here? I was wondering whether I had to
> install MS SQL Server enterprise ed. to use Analysis Server with it,
> rather than more limited MSDE version of SQL Server ?!
> Thanks all.
> Fred Z.
I do not think Analysis Service is supported on MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An workaround (was The Answer (was Re: UDA and SQL Data Access))

Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?

Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.

I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.

|||

I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.

I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.

|||

I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".

I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):

A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."

Thus UDAs have some significant limitations in this version of SQL Server. They are:

No data access|||

vb_hal,

I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).

What I wished for was to be able to write something like

SELECT dbo.PERCENTILE(some_column, 0.5)

FROM some_table

GROUP BY some_other_column

To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:

SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )

FROM some_table

GROUP BY some_other_column

I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.

So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).

--

Carlos

|||

I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?

From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.

Any feedback is greatly appreciated.

blackjackIT

|||

Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.

Thanks!

An unexplained error message for an Xquery

Hello,
When I issue this query:

select doc.query ('
for $b in /a/b,
$c in /a/c
return $b,$c
')
from T1
where id=6

I recieve this error message:
.Net SqlClient Data Provider: Msg 2227, Level 16, State 1, Line 5
XQuery [T1.doc.query()]: The variable '$c' was not found in the scope in which it was referenced.

Interestingly, when I reverse the order of the variables in the return clause (i.e. make it c$,$b ), the unidentified variable in the error message becomes $b instead of $c. i.e. the system always does not identify the second variable. The query always works fine if the return clause has only one variable, be it $b or $c.

Am I missing something here, or is it a bug?

thanks
-Arsany

The behavior is correct in this case. The problem here is with the precedence of the ',' operator with respect to the FLWOR statement. Basically, the ',' operator in the return statement is not binding to the return clause of the FLWOR, but rather creating a new XQuery expression. If you put parenthesis around the return statement (as shown below) this will enforce the precedence that you want in your query:

select doc.query ('
for $b in /a/b,
$c in /a/c
return ($b,$c)
')
from T1
where id=6
|||Thank you Mike
-Arsany Sawiressql

An existing connection was forcibly closed by the remote host

Hi,

I am using ASP.NET 2.0 application to fetch data from a SQL server 2005 using stroed proc.

Stored proc is also using one table from Linked Server (SQL 2000). Linked Server is configured to use login's current security context.

I am getting the following error message

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/15/2006 4:26:30 PM
Event time (UTC): 6/15/2006 8:26:30 PM
Event ID: 988964e6fa5249e38b1bc6c9a5ecd1e4
Event sequence: 850
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/MyApp-1-127948732130920720
Trust level: Full
Application Virtual Path: /MyApp
Application Path: C:\Inetpub\wwwroot\MyApp\
Machine name: MyServer
Process information:
Process ID: 352
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://MyServer/MyApp/Page1.aspx
Request path: /MyServer/MyApp/Page1.aspx
User host address: XX.XX.XXX.XX
User: DomainName/UserName

Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Please Guide

Thanks

Is it intermittent or consistent?

1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This looks like a consistent issue. It normally means that you need to configuration IIS account to access SQL Server. You can take a look at this link, http://support.microsoft.com/kb/247931/en-us.

2. TCP Provider: An existing connection was forcibly closed by the remote host.

This normally is an intermittent issue. It happens when you enable connection pooling and server closes the connection for reason like connection clean up, restart, network failure, the ASP.NET( sqlclient ) use dead connection from the pool to write data and thus get the failure you see. Re-retry connection should solve the issue.

|||

Hi Nan,

Re: 1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

IIS is configured properly to access data from SQL Server and it is working fine when IIS & SQL are on the same box. But the above message comes only when the stored proc tries to access data from a 2nd SQL Server (linked server which is not on the same box as IIS). Somehow it is not passing the credential to the 2nd SQL Server. 2nd SQL server is linked using login's current security context. Also ASP.NET 2 web.config file says <identity impersonate="true" />. I am using NT domain right now. Is this issue of "Double hop"?

Re: 2. TCP Provider: An existing connection was forcibly closed by the remote host.

Is connection pooling is by default? Can we change so that it should not use it?

Thanks

Shafiq

An error has occurred while establishing a connection to the server

Hi I have just reinstalled .Net and SqlServer2005. I am running my application from local machine and sqlserver is also install local machine. but when I run application. Get follwing Error Message

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

What was the application trying to do when you got this message? It may be that the connection string is not correct, it may be that the server is setup for only windows authentication.

|||

Hi

From your description, it seems that you met error when you are trying to connect to your SQLServer,right?

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

For this kind of issue, we should use the following steps to diagnose the problem.

First, you also should make sure that if 1433 port has been enabled on your server, which is the default port for SQLServer remote connections.

Generally, you can try the following ways to handle your problem.

1. Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition
2. Enable the SQL Server Browser service
3. Create exceptions in Windows Firewall
4. Create an exception for SQL Server 2005 in Windows Firewall
5. Create an exception for the SQL Server Browser service in Windows Firewall

More details for each step, you may see the following link:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Thanks.

An attempt has been made to use a data extension 'DLLName' that is not registered for this

Hello,

I've created my own data extension which uses ADODB.NET (OleDB Provider) and connects to the named SQL instance. During the development phase of the report I can connect to my data source, retrieve data from it and display it in the report preview with no problems. But as soon as I want to deploy this report and my data source I get the message: “An attempt has been made to use a data extension 'DLLName' that is not registered for this report server.". I went thru all *.config files and set up them up as per instructions how to. I can see my extension in the combo box during designing my report which tells me that this is done correctly. I can create the report in the preview as well. But whatever I do, when I want to deploy it I'm always getting this message.

I've come across one post where it says that Reporting Services are limited to the default, unnamed instance of SQL Server (local). I don't think it should matter to me in this case because I'm using my OleDB Provider to connect to the database. Report doesn't know anything about it. It gets data from my extension, not directly from the database. But I've tried switching from my named instance to (local) just to see if it is going to work, but no luck.

I'd appreciate somebody giving me some suggestions on this issue.

Thanks...

did you update rssrvpolicy.config to grant fulltrust to your extension? Usually this error message appears when report server gets an exception when loading the extension, and this usually happens when extension is not trusted or it fails in constructor. The fact that it works in report designer (which runs everything in fulltrust by default) most likely means that extension is not getting fulltrust when loaded on server.|||

Yes I did. Here is the code from the rssrvpolicy.config:

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="MyCodeGroup" Description="Code group My data processing extension">

<IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\bin\My.dll"/>

</CodeGroup>

But it still doens't work. I still am getting the same message when I try to deploy my report.

Any suggestions would be appreciated.

Thanks...

An Amazing Errorrrrrrrrrrrrrrrr

Hi All Friends

i have an amazing error in my project

i wrote a project in ASP.NET 2

a part of this project change a value in a row in a table in sql 2005 to another value

i used it in my computer (With Local host and my own iis) and it works properly and without any problem

but when i upload it in internet it have this error:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. "

can anyone help me?

i think the error is from my table in sql 2005 but i dont know what is wrong in it

thanks for your cares friends and im waiting for your usefull answers

It means that someone changed the row between the time you initially retrieved it, and the time you did the update. Or you are pointing at the wrong database for the update.

|||

No it cannot happen

because this form has a password that no one have it and no one know about this form

it cant be

but it's a Notice here :When i delete my table and recreate it again it work probably but sometimes it has this error again

|||

Well, if you're absolutely sure that there is no way that the row could have been changed--there could be other forms or other apps that are updating the table--then there is something wrong in the concurrency code. Did a wizard generate this for you, or did you write some custom code?

Don

amount manipulation in crystal report

I am developing a project in vb.net .
I am displaying the amount in my detail section.
There is a situation in which , if the amount is negative it has to be displayed as receipts and if else it has to be showed as payments .

like

Description Payments Receipts
monthly instalment 6000.23 0.00
finance charges 0.00 6000.23(this is -ve value)

I have struck here . Can any one help me to come out of this situation.

I am attaching two jpeg files in which

1 vbcode_rpt : will show the present look of the report.

2 PBcode_rpt : will show how i want the report to be presented.

thanks in advance

vijiPlz explain one thing to me that do have single Field in the database for Payments and Receipts or individual Field.

in ur case u can apply formula as (if its single Field)

if {Reportname.Field}<0 then
Payments
elseif {Reportname.Field} then
Receipts|||hai silly star

i got the solution for that situation.

Both the columns are one column in the database ie. the amount column for that description.

if the amount column value is < 0 then it has to be displayed as receipts and if it is > 0 then it has to be displayed as payments .

i added two unbound currency columns in the details section and in the formula dialog I added this formula to get my requirement done.

This is in the unbound_currency_column1 ie. Payments
if {tmp_ter_pool.Amount} > 0
then {tmp_ter_pool.Amount}
else
0.00

This is in the unbound_currency_column2 ie. Receipts
if {tmp_ter_pool.Amount} < 0
then {tmp_ter_pool.Amount}
else
0.00|||Its great u find the solution but did u try the one i told u?? :cool:

AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:Is there any locking mechanisam available in MSAS 2005

Hi All,

I am working on Analysis Server 2005 and .Net 2005 and AMO,i already worked on AS 2000 and .Net 1.1 with DSO.

I just want to comform one thing is there any thing like OlapLockTypes are there in AS 20005 or AMO.Please let me know how it works.

Thanks in Advance.

Hi vishweshwar,

This feature is already deprecated in AMO. Just want to inform that locking mechanisms can be well implemented using security features which provides good layer of abstraction.

Thanks

Subhash Subramanyam

|||

Locking in DSO does not really relate to the security features in SSAS 2005.

Locking in DSO was used to prevent mulitple uses from trying to alter an object at the same time. This is not an issue in 2005 with AMO due to the way it works with XML/A commands. Basically when you connect via AMO it reads the object definition off the server and then all the changes you make are done in memory until you issue an update command. At this point an XML/A alter command is sent to the server which contains the entire object definition. If mulitple developers happen to work on the same object at the same time, then that the second update will overwrite the first.

|||

Thanks Darren, Agree that my answer was partially wrong. Its clear to understand why Locking Mechanisms are no more implemented and how update actually works.

|||

hi Darren,

Thanks ,but one thing I coudnt understand clearly how it will handle (whether explicitly or implicitly) ,i just want to clear one point here whether we need to write explicitly by using AMO code or it will handle internally in AMO.

Please let me know.

How we can use XML/A code in .net with AMO.

Thanks in Advance.

|||

All the stuff I described is handled implicitly. You don't usually need to work about XMLA, you can just work with the AMO objects. It is just that AMO is generating XMLA under the covers.

If you want to group a whole series of updates into one batch, you can use the .CaptureLog() and .ExecuteCaptureLog() methods of the server object. I have an example in powershell on my blog here:

http://geekswithblogs.net/darrengosbell/archive/2006/06/21/GenerateXmlaByName.aspx

But you should be able to work exclusively with AMO without needing to resort to explicitly dealing with XMLA.|||

Thanks Darren.

Actually with DSO code there we are checking before doing process lock is opened , read or write.

but in AMO i am just writing code directly creating a object of server,database,cubes and dimension and then with objects we are doing process what i am doing is it the right way.Please let me know.

|||AMO is implicitly transactional. I think there are beginTransaction methods on some of the objects, but if an object is being processed you will not be able to alter it until the processing is complete. With DSO, if you did not check for the locks there was a danger of altering an object that was processing. With AMO you cannot do this.

AMO:Is there any locking mechanisam available in MSAS 2005

Hi All,

I am working on Analysis Server 2005 and .Net 2005 and AMO,i already worked on AS 2000 and .Net 1.1 with DSO.

I just want to comform one thing is there any thing like OlapLockTypes are there in AS 20005 or AMO.Please let me know how it works.

Thanks in Advance.

Hi vishweshwar,

This feature is already deprecated in AMO. Just want to inform that locking mechanisms can be well implemented using security features which provides good layer of abstraction.

Thanks

Subhash Subramanyam

|||

Locking in DSO does not really relate to the security features in SSAS 2005.

Locking in DSO was used to prevent mulitple uses from trying to alter an object at the same time. This is not an issue in 2005 with AMO due to the way it works with XML/A commands. Basically when you connect via AMO it reads the object definition off the server and then all the changes you make are done in memory until you issue an update command. At this point an XML/A alter command is sent to the server which contains the entire object definition. If mulitple developers happen to work on the same object at the same time, then that the second update will overwrite the first.

|||

Thanks Darren, Agree that my answer was partially wrong. Its clear to understand why Locking Mechanisms are no more implemented and how update actually works.

|||

hi Darren,

Thanks ,but one thing I coudnt understand clearly how it will handle (whether explicitly or implicitly) ,i just want to clear one point here whether we need to write explicitly by using AMO code or it will handle internally in AMO.

Please let me know.

How we can use XML/A code in .net with AMO.

Thanks in Advance.

|||

All the stuff I described is handled implicitly. You don't usually need to work about XMLA, you can just work with the AMO objects. It is just that AMO is generating XMLA under the covers.

If you want to group a whole series of updates into one batch, you can use the .CaptureLog() and .ExecuteCaptureLog() methods of the server object. I have an example in powershell on my blog here:

http://geekswithblogs.net/darrengosbell/archive/2006/06/21/GenerateXmlaByName.aspx

But you should be able to work exclusively with AMO without needing to resort to explicitly dealing with XMLA.|||

Thanks Darren.

Actually with DSO code there we are checking before doing process lock is opened , read or write.

but in AMO i am just writing code directly creating a object of server,database,cubes and dimension and then with objects we are doing process what i am doing is it the right way.Please let me know.

|||AMO is implicitly transactional. I think there are beginTransaction methods on some of the objects, but if an object is being processed you will not be able to alter it until the processing is complete. With DSO, if you did not check for the locks there was a danger of altering an object that was processing. With AMO you cannot do this.

AMO:Is there any locking mechanisam available in MSAS 2005

Hi All,

I am working on Analysis Server 2005 and .Net 2005 and AMO,i already worked on AS 2000 and .Net 1.1 with DSO.

I just want to comform one thing is there any thing like OlapLockTypes are there in AS 20005 or AMO.Please let me know how it works.

Thanks in Advance.

Hi vishweshwar,

This feature is already deprecated in AMO. Just want to inform that locking mechanisms can be well implemented using security features which provides good layer of abstraction.

Thanks

Subhash Subramanyam

|||

Locking in DSO does not really relate to the security features in SSAS 2005.

Locking in DSO was used to prevent mulitple uses from trying to alter an object at the same time. This is not an issue in 2005 with AMO due to the way it works with XML/A commands. Basically when you connect via AMO it reads the object definition off the server and then all the changes you make are done in memory until you issue an update command. At this point an XML/A alter command is sent to the server which contains the entire object definition. If mulitple developers happen to work on the same object at the same time, then that the second update will overwrite the first.

|||

Thanks Darren, Agree that my answer was partially wrong. Its clear to understand why Locking Mechanisms are no more implemented and how update actually works.

|||

hi Darren,

Thanks ,but one thing I coudnt understand clearly how it will handle (whether explicitly or implicitly) ,i just want to clear one point here whether we need to write explicitly by using AMO code or it will handle internally in AMO.

Please let me know.

How we can use XML/A code in .net with AMO.

Thanks in Advance.

|||

All the stuff I described is handled implicitly. You don't usually need to work about XMLA, you can just work with the AMO objects. It is just that AMO is generating XMLA under the covers.

If you want to group a whole series of updates into one batch, you can use the .CaptureLog() and .ExecuteCaptureLog() methods of the server object. I have an example in powershell on my blog here:

http://geekswithblogs.net/darrengosbell/archive/2006/06/21/GenerateXmlaByName.aspx

But you should be able to work exclusively with AMO without needing to resort to explicitly dealing with XMLA.|||

Thanks Darren.

Actually with DSO code there we are checking before doing process lock is opened , read or write.

but in AMO i am just writing code directly creating a object of server,database,cubes and dimension and then with objects we are doing process what i am doing is it the right way.Please let me know.

|||AMO is implicitly transactional. I think there are beginTransaction methods on some of the objects, but if an object is being processed you will not be able to alter it until the processing is complete. With DSO, if you did not check for the locks there was a danger of altering an object that was processing. With AMO you cannot do this.

AMO:How we can handle the transactions in AMO

HI All,

I am working with AMO and .Net i have few queries,

1) how to handle the transactions in AMO

2)if one user is processing the cube then how to prevent other user to access it.

Please let me know.

Your suggestions are valueable to me.

Thanks in advance.

vish.

AMO is inherently transactional. If an object is being processed changes that other users submit will not be committed until after the transaction has completed.

If you need to do a series of operations, like changing a cubes structure and then processing it, but you want either both or neither of these operations to suceed, then you can use explicit transactions. The server object has beginTransaction, commitTransaction and rollbackTransaction methods.

AMO:How we can handle the transactions in AMO

HI All,

I am working with AMO and .Net i have few queries,

1) how to handle the transactions in AMO

2)if one user is processing the cube then how to prevent other user to access it.

Please let me know.

Your suggestions are valueable to me.

Thanks in advance.

vish.

AMO is inherently transactional. If an object is being processed changes that other users submit will not be committed until after the transaction has completed.

If you need to do a series of operations, like changing a cubes structure and then processing it, but you want either both or neither of these operations to suceed, then you can use explicit transactions. The server object has beginTransaction, commitTransaction and rollbackTransaction methods.

AMO:Can any one knows how to drop the permissions

HI all,

I am working on AMO and .NET.In my application i have to create a role and that i am able to do it.When i am creating a role i provided some Database and cube permissions.while dropping the role i am getting some error message is like permissions are exist on that object(ROLE).So what i did is i dropped permissions first after that i am tried to drop the role.

Still i am getting the same problem when i am dropping the permissions.I am sending that part of code which i am using in my application.

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)
Newrole.Drop(DropOptions.IgnoreFailures)

after dropping the role i am commiting the transaction.and i am getting below error message while deleting.

Errors in the metadata manager. The cube permission with the ID of 'CubePermission 3', Name of 'CubePermission 3' was invalidated by operations in the transaction. Errors in the metadata manager. The transaction cannot be committed because one or more objects were invalidated during the transaction

Please help me on this and correct my code.

Regards,

vishwesh

Set Newrole.Drop(DropOptions.IgnoreFailures) to Newrole.Drop(DropOptions.AlterOrDeleteDependents).

I believe you can remove the following:

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)

B.

|||

Hi Bryan,

I tried with the above code and executes but i am getting some strange error see below.

Errors in the metadata manager. Impact analysis inside a pending transaction cannot support CREATE, DELETE, or ALTER commands.

could you please let me know what could be the problem.

Regards,

vishweshwar

|||

Could you post your revised code?

Thanks,
B.

|||

Hi Bryan,

Please find the revised code.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else
Newrole.Drop()
amodatabase.Update()
amoserver.CommitTransaction()
End If

Please help me on this how to sort it out.

|||

Bryan's suggestion was to use the AlterOrDeleteDependants option on the Drop method.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else

Newrole.Drop(DropOptions.AlterOrDeleteDependents).

amodatabase.Update()
amoserver.CommitTransaction()
End If

AMO:Can any one knows hoe to rename a role and permissions to that role

Hi All,

I am working on AMO and vb.net.Here with this I am managing Analysis Server 2005 with AMO.

My problem is , am able to create a role with role members . but when i try to rename a existing role i couldnt see any direct methods available with AMO.So what i did is first i created a role with normal procedure and then after , i am planning to drop the existing one.

here when i did this i am getting some errors while dropping the role like meta data permissions are not able to drop.

so could any one come across this kind of situation please help me.

your help is valuable to the go further.

Regards,

vishweshwar.P

Renaming a role is simply a matter of getting a reference to the role, changing the name property and calling the update method.

role = db.Roles.GetByName("Old Name");

role.Name = "New Name";

role.Update();

|||

Thanks Darren.

AMO:Can any one knows hoe to rename a role and permissions to that role

Hi All,

I am working on AMO and vb.net.Here with this I am managing Analysis Server 2005 with AMO.

My problem is , am able to create a role with role members . but when i try to rename a existing role i couldnt see any direct methods available with AMO.So what i did is first i created a role with normal procedure and then after , i am planning to drop the existing one.

here when i did this i am getting some errors while dropping the role like meta data permissions are not able to drop.

so could any one come across this kind of situation please help me.

your help is valuable to the go further.

Regards,

vishweshwar.P

Renaming a role is simply a matter of getting a reference to the role, changing the name property and calling the update method.

role = db.Roles.GetByName("Old Name");

role.Name = "New Name";

role.Update();

|||

Thanks Darren.