How to make this join select statement works

Here is a select statement of table A:

select user01_id, user02_id from A where ...

I also need to have the names of users which is in the table user. How to join the table user to have the name field and yeild two fields with the same name?

select a.user01_id,u.name, a.user02_id, u.name from a join user u on (?)

table A

user01_id int,
user02_id int,
...
primary (user01_id, user02_id)

table user

userid int primary key,
name varchar(80),
...

Thans for your advise.select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/|||Originally posted by r937
select user01_id, u01.name as name01
, user02_id, u02.name as name02
from A
left outer
join user u01
on user01_id = u01.userid
left outer
join user u02
on user02_id = u02.useridrudy
http://r937.com/

Hi, Rudy,

The query works well. I didn't know the alias also can be appled on a field in addition of a table. I guess I can use inner join instead when the both user01_id and user02_id of table A refer to the userid in the table user.

Thanks very much for your help.

Vernon|||yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy|||Originally posted by r937
yes, you could use inner joins, if you are guaranteed that the userids will exist

i guess i use left outer simply because it is "defensive sql" -- if either of the userids doesn't match, the A row disappears...

rudy

In fact, the two IDs of table A are foreign keys of the tabe user primary key, userid. So existence of the userid is guaranteed.

A good usage of "defensive". That is the difference between a master and a regular craftsman.

How to make sure no other table writes happen between 2 SQL statements ?

Ok, here is my situation....

When someone navigates to a user's profile page on my site, I present them with a slideshow of the user's photos using the AJAX slideshow extender. I obtain the querystring value in the URL (to determine which user's page I'm on) and feed that into a webservice via a context value where an array of photos is created for the slideshow. Now, in order to create the array's size, I do a COUNT of all of that specific user's photos. Then, I run another SQL statement to obtain the path of those photos in the file system. However, during the time of that first SQL query's execution (the COUNT statement) to the time of the second SQL query (getting the paths of the photos), the owner of that profile may upload or delete a photo from his profile. I understand this would be a very rare occurrence since SQL statements 1 and 2 will be executed within milliseconds of each other, but it is still possible I suppose. When this happens, when I try to populate the array, either the array will be too small or too large. I'm using SqlDataReader for this as it seems to be less memory and resource intensive than datasets, but I could be wrong since I'm a relative beginner and newbie. This is what I have in my vb file for the webservice....

PublicFunction GetSlides(ByVal contextKeyAsString)As AjaxControlToolkit.Slide()
Dim dbConnectionAsNew SqlConnection("string for the data source, etc.")

Try
dbConnection.Open()

Dim memberId =CInt(contextKey)
Dim photoCountLookupCmdAsNew SqlCommand _
("SELECT COUNT(*) FROM Photo WHERE memberId = " & memberId, dbConnection)
Dim thisReaderAs SqlDataReader = photoCountLookupCmd.ExecuteReader()

Dim photoCountAsInteger
While (thisReader.Read())
photoCount = thisReader.GetInt32(0)
EndWhile
thisReader.Close()

Dim MySlides(photoCount - 1)As AjaxControlToolkit.Slide

Dim photoLookupCmdAsNew SqlCommand _
("SELECT fullPath FROM Photo WHERE memberId = " & memberId, dbConnection)
thisReader = photoLookupCmd.ExecuteReader()

Dim iAsInteger
For i = 0To 2
thisReader.Read()
Dim photoUrlAsString = thisReader.GetString(0)
MySlides(i) =New AjaxControlToolkit.Slide(photoUrl,"","")
Next i
thisReader.Close()

Return MySlides

Catch exAs SqlException

Finally
dbConnection.Close()

EndTry

EndFunction

I'm trying to use the most efficient method to interact with the database since I don't have unlimited hardware and there may be moderate traffic on the site. Is SqlDataReader the way to go or do I use something else? If I do use SqlDataReader, can someone show me how I can run those 2 SQL statements in best practice? Would I have to somehow lock writing to that table when I start the first SQL statement, then release the lock after I execute the second SQL statement? What's the best practice in this kind of scenario.

Thanks in advance.

Hello S2KDriver,

Why don't you use a SqlDataAdapter and fill a DataTable with your second SELECT statement (e.g. the photoLookupCommand). You can use the rowcount property to get the number of rows retrieved.

In this scenario you still have the chance that when Slides are added to the AjaxControlToolkit, the photo is deleted and not available.

|||

Hi jeroenm,

Yes, now that I think about it, you're right. Since the photo's filepath is contained in the database and not the photo itself, there can be more or less photos by the time the aspx page receives the array of photo filepaths.

Maybe this is an ignorant and a beginner question, but if the dataset doesn't solve this problem of making sure things are in sync, what is its benefit over the datareader? The only time I've used the dataset is when implementing custom paging with datalists (since you can't page forwards AND backwards with a datareader). In all other instances, I've used the datareader and then I'd always make sure to close the reader right after each reading. I'm trying to be as stingy as possible in terms of resource utilization... is my approach wrong?

And also, to the issue at hand, I guess the only way to keep things in sync, is to somehow lock writing to the Photo table right before I execute my first SQL statement (to prevent the owner of the profile from uploading or deleting his photos during those few milliseconds), then release the write lock after I return the array of photo filepaths back to the requesting aspx page. Am I correct? And if so, how would I do this?

Thanks.

|||

The dataset was my suggestion to get ride of the first select statement. In your situation a datareader gives probably less resource utilization.

Maybe you can use the following scenario:

- open a transaction with the appropriate isolation level.

- update photo records with the matching memberid. (locking the set of records, you want to read locked).

- you will get the number of records updated by the command in return (given you the count).

- select the rows with the datareader and process the result set.

- rollback the transaction (releasing the lock).

I don't know when the AjaxControlToolkit reads the photo's. But if they are read just before showed to the user, you still have a synchronization issue.

how to make a 'my reports' folder

Hi,
I am having some trouble getting the 'My Reports' folder to appear for all
users. The docs just say that as long as the user exists as a windows user
and the 'My Reports' option is ticked in 'Site Settings', ReportServer
should just create a 'My Reports' folder the first time a user logs onto the
ReportServer. But this is not working for all users. About half of the
users have the 'My Reports' folder created. So I need to know how to create
them manually, or at least some workaround to get each user a 'My Reports'
folder!
I don't see any difference between the user accts (other than the names).
Thanks,
LanceLance,
Have you checked to ensure all users are are included in the "My Reports"
role - or in some custom role that includes the required permissions.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11849vnplj1n02@.corp.supernews.com...
> Hi,
> I am having some trouble getting the 'My Reports' folder to appear for all
> users. The docs just say that as long as the user exists as a windows
> user
> and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> should just create a 'My Reports' folder the first time a user logs onto
> the
> ReportServer. But this is not working for all users. About half of the
> users have the 'My Reports' folder created. So I need to know how to
> create
> them manually, or at least some workaround to get each user a 'My Reports'
> folder!
> I don't see any difference between the user accts (other than the names).
> Thanks,
> Lance
>|||Hi,
Thanks for the reply, but I am a bit confused about it! Maybe if I explain
a bit more about the setup: The ReportServer is set up with no common
reports. Each user can *only* view the reports in their own 'My Reports'
folder. As such each user *must* have a 'My Reports' folder! (obviously)
Here is how the server is set up:
* There are the standard 'Item-level' roles and an additional one,
'Viewer' - which is a limited acct, that just allows the user to 'view
folders' and 'view reports'. None of the non-admin users can create
reports.
* The 'System Roles' are the standard 'System Administrator' and 'System
User' - the 'System User' has the 'View report server properties' and 'View
shared schedules' tasks.
* The 'Enable My Reports to support user-owned folders for publishing and
running personalized reports.' checkbox is checked in the 'Reporst Server'
'Site Settings'. The 'Choose the role to apply to each user's My Reports
folder' dropdown is 'Viewer'
To set up for a new user:
* Each new user is added to the 'Users' group on the server (windows 2000
server).
* The user logs in, and a 'My Reports' folder should be created.
But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
shown. I don't know how to check whether there is a 'My Reports' folder for
the user - I am thinking that there is a value in the DB somewhere that
tells me...
I was experimenting with a dozen new users, and the docs say that as soon as
a windows user logs in, a 'My Reports' folder should be created. I tried
both creating a 'System Role' for the user and *not* creating a system role,
but the creation of the 'My Reports' folder seems to occur randomly. I say
*seem*, as I am sure that I am missing a step or something, as I confess
that I find the role assignment somewhat confusing. But I'm new, and I am
sure I'll get used to it...
In response to your kindly answer:
So, from my limited understanding, the 'My Reports' role is an 'item-level'
role; I can only seem to assign to this at the report level (ie an 'item')
*not* to a user. But if the 'My Reports' folder is not being created, I
can't assign this 'tiem-level' role to it!
In short: I am still stuck with some users having a 'My Reports' folder and
most users *not* having one!
Thanks,
Lance
> Lance,
> Have you checked to ensure all users are are included in the "My Reports"
> role - or in some custom role that includes the required permissions.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:11849vnplj1n02@.corp.supernews.com...
> > Hi,
> > I am having some trouble getting the 'My Reports' folder to appear for
all
> > users. The docs just say that as long as the user exists as a windows
> > user
> > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > should just create a 'My Reports' folder the first time a user logs onto
> > the
> > ReportServer. But this is not working for all users. About half of the
> > users have the 'My Reports' folder created. So I need to know how to
> > create
> > them manually, or at least some workaround to get each user a 'My
Reports'
> > folder!
> >
> > I don't see any difference between the user accts (other than the
names).
> >
> > Thanks,
> > Lance
> >
> >
>|||Hi,
I was just nosing around the DB, and I noticed that all the users are in the
'users' table, but the user accounts don't appear in the 'PolicyUserRole'
table, and hence there are not any policies for them.
I don't know if this helps any or just confuses...
And there aren't any application events in the log on the server, either.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11869vtmjhs8i08@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I am a bit confused about it! Maybe if I
explain
> a bit more about the setup: The ReportServer is set up with no common
> reports. Each user can *only* view the reports in their own 'My Reports'
> folder. As such each user *must* have a 'My Reports' folder! (obviously)
> Here is how the server is set up:
> * There are the standard 'Item-level' roles and an additional one,
> 'Viewer' - which is a limited acct, that just allows the user to 'view
> folders' and 'view reports'. None of the non-admin users can create
> reports.
> * The 'System Roles' are the standard 'System Administrator' and 'System
> User' - the 'System User' has the 'View report server properties' and
'View
> shared schedules' tasks.
> * The 'Enable My Reports to support user-owned folders for publishing and
> running personalized reports.' checkbox is checked in the 'Reporst Server'
> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
> folder' dropdown is 'Viewer'
> To set up for a new user:
> * Each new user is added to the 'Users' group on the server (windows 2000
> server).
> * The user logs in, and a 'My Reports' folder should be created.
> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
> shown. I don't know how to check whether there is a 'My Reports' folder
for
> the user - I am thinking that there is a value in the DB somewhere that
> tells me...
> I was experimenting with a dozen new users, and the docs say that as soon
as
> a windows user logs in, a 'My Reports' folder should be created. I tried
> both creating a 'System Role' for the user and *not* creating a system
role,
> but the creation of the 'My Reports' folder seems to occur randomly. I
say
> *seem*, as I am sure that I am missing a step or something, as I confess
> that I find the role assignment somewhat confusing. But I'm new, and I am
> sure I'll get used to it...
>
> In response to your kindly answer:
> So, from my limited understanding, the 'My Reports' role is an
'item-level'
> role; I can only seem to assign to this at the report level (ie an 'item')
> *not* to a user. But if the 'My Reports' folder is not being created, I
> can't assign this 'tiem-level' role to it!
>
> In short: I am still stuck with some users having a 'My Reports' folder
and
> most users *not* having one!
> Thanks,
> Lance
>
>
> > Lance,
> >
> > Have you checked to ensure all users are are included in the "My
Reports"
> > role - or in some custom role that includes the required permissions.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11849vnplj1n02@.corp.supernews.com...
> > > Hi,
> > > I am having some trouble getting the 'My Reports' folder to appear for
> all
> > > users. The docs just say that as long as the user exists as a windows
> > > user
> > > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > > should just create a 'My Reports' folder the first time a user logs
onto
> > > the
> > > ReportServer. But this is not working for all users. About half of
the
> > > users have the 'My Reports' folder created. So I need to know how to
> > > create
> > > them manually, or at least some workaround to get each user a 'My
> Reports'
> > > folder!
> > >
> > > I don't see any difference between the user accts (other than the
> names).
> > >
> > > Thanks,
> > > Lance
> > >
> > >
> >
> >
>sql

How to MAKE @@LOCK_TIMEOUT effect to every session

Example,I want to let any lock be cancel if the lock keep 2 minute.
Or is there has any other parametes about user's lock and how to use?
thanks
I'm afraid that there is no such setting. you have to set it from the client applications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fx_man" <welcome_you@.sina.com> wrote in message news:ejbP7$KQEHA.3532@.TK2MSFTNGP12.phx.gbl...
> Example,I want to let any lock be cancel if the lock keep 2 minute.
> Or is there has any other parametes about user's lock and how to use?
> thanks
>

How to maintain users permission and access level

Hi guys,

We have a scenario where there are about 50 tables in our database and we want to build an intranet web application for users to with the office to access those tables.

Users ability to access tables falls into diferent category:

Some users can NOT view some tables at allSome users can ONLY view some tables but not insert/update any fieldSome users can view and also insert/update some tables (in the same time they might not have view(select) permision on some other tables)

Now, what is the right way to implement this.

I say we have to have a Role, RolePermission, User, UserPermission inside our database to implement this (something which would look like the Roles and Users inside MSSQL) and we only have one user for our Database (MachineName/ASPUSER) to access the database and all the tables within

My colleague says NO, instead of creating all these tables and implement this, we add every user of our application as a Database user inside MSSQL in the Databse Users.

All the web application I have seen so far, DNN, CommunityServer, ... the have tables to implement all these and they don't add users inside the MSSQL.

Now which way is the way to go with, and what problem might we fall into if we use SQL users, is this possible at all. How can I convince him that we have to make and use our own tables to manage this.

Thanks for any help,
Mehdi

If you are using asp.net 2.0 you can use login controls and restrict the users to access particular forms.

Other way is to use Windows based authentication. As you are creating Intranet based application...every user will have windows ID. You can restrict the user to access the database.

To learn about Windows Based Authentication, visit the following link:

http://msdn2.microsoft.com/en-us/library/aa480475.aspx

To learb about login controls, watch the video tutorial for Membership and Roles

http://www.asp.net/learn/videos/default.aspx?tabid=63#howdoi

|||go to http://www.codeproject.com/csharp/cgsecurity3.aspit was presented a library for managing user-level credentials

How to loop through in sql server 2000

I have a scenario where I have to keep track of users last three passwords,

if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."

Basically I can do : select top 3 passwords from MyTable order by createdDate desc

But how to do comparision ?

I was googling for if anything like array exist in sql server 2000?

Please suggest, any pointer......

Thanks

You can use the IN keyword to see if a scalar value is within a set of values.

Here's how I would do this. I'd have a stored procedure that accepts the @.NewPassword and @.UserID as input parameters and then returns either a 1 or a 0, and in .NET code I'd do an ExecuteScalar() and cast the result to a Boolean.

The T-SQL in the stored procedure would look something like:

IF @.NewPassword IN (SELECT TOP 3 Passwords FROM PasswordHistory WHERE UserID = @.UserID ORDER BY CreatedDate DESC)
SELECT 1
ELSE
SELECT 0

|||

Hi Scott,

Thanks for your reply, its surely something which i wanted , let me try it .

will let you know how it works out.

|||

It does work !!

Thanks a lot for your time.

|||

gridview:

It does work !!

Thanks a lot for your time.

Great! Happy Programming!

|||

gridview:

It does work !!

Thanks a lot for your time.

Great! Happy Programming!

How to log in to SQL with a user other than sa

my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||

here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

|||Are you sure that the password for user paul is correct? Have you tried resetting it to make sure?
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||

i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?

|||

I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?

Paul

|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.
|||

I knew it had to be me doing something stupid....

thanks all

How to log in to SQL with a user other than sa

my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||

here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

|||Are you sure that the password for user paul is correct? Have you tried resetting it to make sure?
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||

i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?

|||

I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?

Paul

|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.
|||

I knew it had to be me doing something stupid....

thanks all

How to log each database access (SQL 2000 / 2005)

Is it possible to log each database access in SQL 2000?
We use a third-party application with a limited number of licences. Some
users add / modify / delete data in the database, some just read it. As we
are running out of licences we would like to eliminate those users, who
havn't access the database for a certain time. The question is: how to find
out when the last activity of the user occured in the database.
Since the application is third-party we cannot modify it, so the solution
must based solely on SQL Server mechanisms.
If the above is not possible in SQL Server 2000, does SQL 2005 offer more
(please note that we need to log all accesses to the database including
reads)?
Thank you very much for any help!
Best regards,
AndrewYou can run Profiler Trace in sql server 2000 and save the result in
table. Then query the table for specific user.
NJ
Andrew Drake wrote:
> Is it possible to log each database access in SQL 2000?
> We use a third-party application with a limited number of licences. Some
> users add / modify / delete data in the database, some just read it. As we
> are running out of licences we would like to eliminate those users, who
> havn't access the database for a certain time. The question is: how to find
> out when the last activity of the user occured in the database.
> Since the application is third-party we cannot modify it, so the solution
> must based solely on SQL Server mechanisms.
> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
> (please note that we need to log all accesses to the database including
> reads)?
> Thank you very much for any help!
> Best regards,
> Andrew|||Thank you! It is a very good idea, the only problem could be that I have to
collect data over a long period, that is a couple of months, and I am not
sure if I can run Profiler Trace as a service on an SQL Server machine
(runing an interactive (desktop) application for several months and locking
down the server wouldn't work in our environment).
But I will definitely give it a try.
Thanks again!
Best regards,
Andrew
> You can run Profiler Trace in sql server 2000 and save the result in
> table. Then query the table for specific user.
> NJ
> Andrew Drake wrote:
>> Is it possible to log each database access in SQL 2000?
>> We use a third-party application with a limited number of licences. Some
>> users add / modify / delete data in the database, some just read it. As
>> we
>> are running out of licences we would like to eliminate those users, who
>> havn't access the database for a certain time. The question is: how to
>> find
>> out when the last activity of the user occured in the database.
>> Since the application is third-party we cannot modify it, so the solution
>> must based solely on SQL Server mechanisms.
>> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
>> (please note that we need to log all accesses to the database including
>> reads)?
>> Thank you very much for any help!
>> Best regards,
>> Andrew
>

How to list all history/snapshots of a given report

We are running SSRS2005 and produce multiple reports.

Our users need history report data so we set up snapshots executions to run weekly.

How do we display the list of these historical snapshots to the users for selection?

Currently we code:

http://myserver/Reports/Pages/Report.aspx?ItemPath=/myreports/Available+Floor+Space&rs:Command=Render&SelectedTabId=SnapshotsTab

That is not preferred as the user is presented the Report Manager interface and can go use other functionality such as subscriptions etc... that we do not support.

Is there (a) a way to suppress the Report Manager controls? or (b)another way to list the snapshots executed given a report name( maybe by querying the report server database?) or (c) code http://myserver/Reportserver/?/myreports/Available+Floor+Space&rs:Command=Render&? to get the history listing?

Thanks

Hi,

The best way is to use Reporting Services Web Services.

You need to add a Web Reference to your Reporting Server and name it <ReferenceName>. Once it is setup you can call the web services using the <ReferenceName>.

In your class first you need to add the "using" statement....

using <NameSpace>.<ReferenceName>.

then in the code you can use...

ReportingService rs = new ReportingService();

rs.Credentials = <your credentials>;

ReportHistorySnapshot[] historySnapshotList = rs.ListReportHistory(<report name>);

Hope this will help you.

Thanks.

Hammad

How to limit number of connections in Analysis Services

Hi All,

Our users accesses the OLAP database using excel with large data reporting. When the number of connections reaches to 20-30, the generation of reports becomes very slow. My question is, is there a way in order to limit the number of connections that can access the olap, in this way we can use this approach to let user create their reports by batch with only let say 5 connections.

Thanks...

There is no property or knob allowing you to limit number of connections.

You can use different ways to scale your application. One: is to install several machines and configure them to run as NLB cluster. This way your users will be connecting to the same IP and NLB will distribute the load between several physical machines.

You can try and implement simple application based on the DISCOVER_CONNECTIONS request ( see ActivityViewer sample application for example of it) and starts cancelling any new connections after reaching some threashold.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to limit concurrent users when using pooled connections?

Hi,
I've been out of the loop for a while but when I learnt about db access from
VB etc we were told to use the same connection credentials such as username
and password in order to speed up access by recycling connections. Nowadays
I want to host a web site with some sort of database at the back end and I
would like to use MSDE but there is a 25 concurrent users restraint on the
license.
How would I limit the users if I'm using connection pooling? Is this
something I'd have to do in code?
Thanks in advance.
hi,
meadensi wrote:
> Hi,
> I've been out of the loop for a while but when I learnt about db
> access from VB etc we were told to use the same connection
> credentials such as username and password in order to speed up access
> by recycling connections. Nowadays I want to host a web site with
> some sort of database at the back end and I would like to use MSDE
> but there is a 25 concurrent users restraint on the license.
there is no restrincions on licences at all..this 25 magic number is just a
general "guess" about the "potential" limit of MSDE, as it allows up to
32765 theoretical concurrent connections (as all SQL Server editions) but it
has a built in Query Governor ( more at
http://msdn.microsoft.com/library/?u...asp?frame=true )
that kicks in when 8 concurrent workloads (AKA batches and not connections)
are executing, slowing down all active workloads...

> How would I limit the users if I'm using connection pooling? Is this
> something I'd have to do in code?
as you are writing a web base application, probably built on IIS, you can
for sure use the connection pooling features, as the application server
probably uses the very same connection string each time (and this is enougth
for reusing pooled connections), but disabling the pooled connections
(providing the "OLE DB Services = -2" parameter in the connection string,
which disables connection pooling only...) could not increase your
performance/access strategy, as the wall still is at 8 concurrent workloads,
with no regard to the actual connection owner... and querying your
master..sysprocesses table for the actual current count only adds additional
workloads to your (scarce) resources...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. Very good and helpful answer which has reminded me of why the
MVP programme is a good idea (I once pondered trying to earn MVP status
myself, in Excel!)
Cheers,
meadensi
"Andrea Montanari" wrote:

> hi,
> meadensi wrote:
> there is no restrincions on licences at all..this 25 magic number is just a
> general "guess" about the "potential" limit of MSDE, as it allows up to
> 32765 theoretical concurrent connections (as all SQL Server editions) but it
> has a built in Query Governor ( more at
> http://msdn.microsoft.com/library/?u...asp?frame=true )
> that kicks in when 8 concurrent workloads (AKA batches and not connections)
> are executing, slowing down all active workloads...
>
> as you are writing a web base application, probably built on IIS, you can
> for sure use the connection pooling features, as the application server
> probably uses the very same connection string each time (and this is enougth
> for reusing pooled connections), but disabling the pooled connections
> (providing the "OLE DB Services = -2" parameter in the connection string,
> which disables connection pooling only...) could not increase your
> performance/access strategy, as the wall still is at 8 concurrent workloads,
> with no regard to the actual connection owner... and querying your
> master..sysprocesses table for the actual current count only adds additional
> workloads to your (scarce) resources...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

How to let users modifiy this ...?

Then you'll have to have then define what is a correct status and change it
accordingly. If all they want to do is change the name from Pending to
Standby, that's just a quick update to the Status table, with no impact to
the Orders table.
Alternatively, you can add the new status to the status table and then
update the orders table:
update Orders
set
StatusID = 3
where
StatusID = 2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:C8737DE7-4B39-4C66-B3BD-C9494205158C@.microsoft.com...
1. Order must have a Status,
2. Order table is linked to the Status table via a FK (PK in Status table),
3. Users want to be able to change (add [no problem], modify [no problem]
and delete [big problem]) definition of what Status is.
4. For example (Status table and then Order table)
StatusID =1 StatusName = New
StatusID =2 StatusName = Pending
OrderID=3 StatusID=1
OrderID=4 StatusID=1
OrderID=5 StatusID=2
What users want to do is this "'Pending' order status is not a valid status
anymore, I want to delete it from the Status table". Deletiing StatusID=2
from the Status table is problematic since each order must have some sort of
status + behavior (what other Status to default to in this case) is not even
defined by the client.
Is there a way to deal with this?Tom,
You are right, it can be done the way and that's what I was trying to
explain to the users but the insist on "deleting" a status and in their worl
d
this means that if today there are 5 different order statuses available and
they want one removed then (physically) there are going to be only 4
available after removal.
When I ask them "what do you want to do with all the orders that now point
to a status which doesn't even exist in your system" they say "well, you
figure it out". Round and round we go.
There is no way one could even remove it since this would violate the
integrity and database would never allow this transaction to complete.
"Tom Moreau" wrote:

> Then you'll have to have then define what is a correct status and change i
t
> accordingly. If all they want to do is change the name from Pending to
> Standby, that's just a quick update to the Status table, with no impact to
> the Orders table.
> Alternatively, you can add the new status to the status table and then
> update the orders table:
> update Orders
> set
> StatusID = 3
> where
> StatusID = 2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:C8737DE7-4B39-4C66-B3BD-C9494205158C@.microsoft.com...
> 1. Order must have a Status,
> 2. Order table is linked to the Status table via a FK (PK in Status table)
,
> 3. Users want to be able to change (add [no problem], modify [no problem]
> and delete [big problem]) definition of what Status is.
> 4. For example (Status table and then Order table)
> StatusID =1 StatusName = New
> StatusID =2 StatusName = Pending
> OrderID=3 StatusID=1
> OrderID=4 StatusID=1
> OrderID=5 StatusID=2
> What users want to do is this "'Pending' order status is not a valid statu
s
> anymore, I want to delete it from the Status table". Deletiing StatusID=2
> from the Status table is problematic since each order must have some sort
of
> status + behavior (what other Status to default to in this case) is not ev
en
> defined by the client.
> Is there a way to deal with this?
>|||Add a status column to the table with values of "Active" and "Inactive".
Since your table is called Status already, lets call this new column
ActiveInactive
Allow them to select active rows in the application, but not innactive rows.
The existing inactive rows will still be joined in queries, and can show up
on reports so you don't lose that fact that the code was valid at one point.
You can take it to a next level and put a date in your table indicating when
the ActiveInactive flag (or the description) changed.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:0A001470-14A7-43FB-AC7E-1805AA425FB6@.microsoft.com...
> Tom,
> You are right, it can be done the way and that's what I was trying to
> explain to the users but the insist on "deleting" a status and in their
world
> this means that if today there are 5 different order statuses available
and
> they want one removed then (physically) there are going to be only 4
> available after removal.
> When I ask them "what do you want to do with all the orders that now point
> to a status which doesn't even exist in your system" they say "well, you
> figure it out". Round and round we go.
> There is no way one could even remove it since this would violate the
> integrity and database would never allow this transaction to complete.
> "Tom Moreau" wrote:
>
it
to
table),
problem]
status
StatusID=2
sort of
even|||And who did it - that'll be the next question...
"Jim Underwood" wrote:

> Add a status column to the table with values of "Active" and "Inactive".
> Since your table is called Status already, lets call this new column
> ActiveInactive
> Allow them to select active rows in the application, but not innactive row
s.
> The existing inactive rows will still be joined in queries, and can show u
p
> on reports so you don't lose that fact that the code was valid at one poin
t.
> You can take it to a next level and put a date in your table indicating wh
en
> the ActiveInactive flag (or the description) changed.
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:0A001470-14A7-43FB-AC7E-1805AA425FB6@.microsoft.com...
> world
> and
> it
> to
> table),
> problem]
> status
> StatusID=2
> sort of
> even
>
>|||very true.
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:C35DFC91-892D-40D5-8DA9-1EC2FE6A305E@.microsoft.com...
> And who did it - that'll be the next question...
> "Jim Underwood" wrote:
>
"Inactive".
rows.
up
point.
when
their
available
point
you
change
to
impact
then
not

How to know that which table i have updated?

Hi,
I have one sql server and many users access the sql
server in the same time. How i can know which table has
been recently updated? How i can know someone has update
the some data into which table?
Can i keep track what tables or data have been
updated to sql server today? The data inside which table
has been modified?
Thank you very much.
regards,
florenceEasiest to implement would be setup a SQL Profiler Trace.
Alternitavly you could create UPDATE triggers on the tables in question
--
HTH
Ryan Waight, MCDBA, MCSE
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:0f5101c3a863$91345320$a501280a@.phx.gbl...
> Hi,
> I have one sql server and many users access the sql
> server in the same time. How i can know which table has
> been recently updated? How i can know someone has update
> the some data into which table?
> Can i keep track what tables or data have been
> updated to sql server today? The data inside which table
> has been modified?
> Thank you very much.
> regards,
> florence
>

How to know how many users are connected to my sql server 2000 and how many resources are

how can i know it? can i do it with sql 2000 tools or i need other kind of
tool?
Regards
Rick
EXEC master.dbo.sp_who2;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>
|||Hello,
For SQL 2000 query the MASTER..SYPROCESSES table. Use the filtering and
group based on your requirement.
For SQL 2005 you could the dynamic management view (DMV)
sys.dm_exec_sessions
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 - All user SessionsSee the below URL for more
details:-
http://www.codeproject.com/useritems/Dynamic_Management_Views.asp
Thanks
Hari
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick

>

How to know how many users are connected to my sql server 2000 and how many resources are

how can i know it? can i do it with sql 2000 tools or i need other kind of
tool?
Regards
RickEXEC master.dbo.sp_who2;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>|||Hello,
For SQL 2000 query the MASTER..SYPROCESSES table. Use the filtering and
group based on your requirement.
For SQL 2005 you could the dynamic management view (DMV)
sys.dm_exec_sessions
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 - All user SessionsSee the below URL for more
details:-
http://www.codeproject.com/useritems/Dynamic_Management_Views.asp
Thanks
Hari
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>

How to kill running query?

Hi!
Our users often run malicious queries which could take up to quarter of CPU power for couple of hours. If there is more that one such query, the whole environment is severely impacted with no other recovery that restarting the service.

Is there any (easy) way how to kill unwanted connections and related queries? If not, could someone please give some explanation for that (is it design issue or what?). Otherwise I'd like to see that functionality in SP2-3. Someone with similar experience out there?Could you tell us please, what a client do you use?|||The mechanism of cancelling queries in Yukon is based on establishing another connection with the server passing the same session id as part of the connection string. If that session is currently executing a query it is possible to send query cancel request. Microsoft.AnalysisServices.Server class has CancelCommand method, for instance.

To determine which sessions are running it is possible to send sessions discover schema request.

I am not sure how you are planning to do it - automatically or manually.|||

Could own Command be cancelled without AMO. Only using AdomMd.NET?

What a XMLA Execute schould be ussued?

Who has permittion to cancel command in another session?

|||

Absolutly.

You can use any client, not only AMO to send a Cancel command.

For instance, you can use ActivityViewer sample application to cancel user sessions, or user connections.

Connect to Analysis Server with Profiler to see exact syntax of the Cancel command. Then you can use any client object model to submit the command.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Just a few days ago, we discovered that when a user cancels his or her query from Excel (that's our front end to our AS2005 cube), it's only canceling on the client side and that the query is still running on the server and will keep on running until it completes (yes, "canceling" takes just as long as if the user had let the query run until completion). As a result, our server's memory and CPU gets consumed entirely and requires us to restart the service at least once or twice a day.

I believe if one cancels a query from within Management Studio, the query will also not be canceled immediately and will essentially run to completion.

Basically, we discovered that canceling is simply asking for the results to not be shown as the query will still run to completion.

Is there a way we can kill the query when the user hits escape in Excel?|||

For Excel case this needs to be investigated. If this is not about Office 12 Beta Excel then the client probably executes the queries on non user interface thread and when user tries to cancel the query the client simply unblocks the UI thread, while the worker thread keeps running.

In SQL Server Management Studio we do not do that. We send Cancel request to the server, server notices it and as soon as algorithms are ready to break the server produces "query cancelled" response. If for some queries the server does not break reasonably soon then this should be logged as a bug.

|||Thank you, Edward! I complied ActivityViewer and it is what I was looking for.

Personally I think this is very important feature and it should be internal part of Management Studio, the same way as SQL has Activity Monitor. But at least there's a way how you can do it.

Cheers!
Radim|||UPDATE:

Using Excel 2003 SP2, I connected to a local AS2005 cube. I wrote a report and saved it. I opened up the workbook and hit "refresh data". After a few minutes, the query is done. I then went into view the code of the sheet so I could get the MDX. Using activesheet.pivottable.mdx, I got the following query:

SELECT NON EMPTY CROSSJOIN(CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Customer].[Customer Id].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Customer].[Customer Name].[All]})}))), HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownLevel({[Customer].[Region Code].[All]}))}, {[Customer].[Region Code].&[ZUG], [Customer].[Region Code].&[ZRCH], [Customer].[Region Code].&[ZHJG], [Customer].[Region Code].&[ZDHD], [Customer].[Region Code].&[ZAC], [Customer].[Region Code].&[YUNN], [Customer].[Region Code].&[WYO], [Customer].[Region Code].&[WY], [Customer].[Region Code].&[WV], [Customer].[Region Code].&[WTRF], [Customer].[Region Code].&[WSX], [Customer].[Region Code].&[WRW], [Customer].[Region Code].&[WRSW], [Customer].[Region Code].&[WOR], [Customer].[Region Code].&[WMD], [Customer].[Region Code].&[WISL], [Customer].[Region Code].&[WIL], [Customer].[Region Code].&[WI], [Customer].[Region Code].&[WFLN], [Customer].[Region Code].&[WBNG], [Customer]
.[Region Code].&[WA], [Customer].[Region Code].&[VT], [Customer].[Region Code].&[VRLB], [Customer].[Region Code].&[VNTO], [Customer].[Region Code].&[VNNA], [Customer].[Region Code].&[VLNC], [Customer].[Region Code].&[VJLE], [Customer].[Region Code].&[VIC], [Customer].[Region Code].&[VI], [Customer].[Region Code].&[VEST], [Customer].[Region Code].&[VER], [Customer].[Region Code].&[VA], [Customer].[Region Code].&[UTRC], [Customer].[Region Code].&[UT], [Customer].[Region Code].&[USMA], [Customer].[Region Code].&[UPPS], [Customer].[Region Code].&[ULPR], [Customer].[Region Code].&[TYRO], [Customer].[Region Code].&[TYN], [Customer].[Region Code].&[TX], [Customer].[Region Code].&[TSCA], [Customer].[Region Code].&[TORF], [Customer].[Region Code].&[TOKY], [Customer].[Region Code].&[TNDU], [Customer].[Region Code].&[TN], [Customer].[Region Code].&[TLAX], [Customer].[Region Code].&[TIPN], [Customer].[Region Code].&[TIAN], [Customer].[Region Code].&[THUR], [Customer].[Region Code].&[TAMS], [Customer].[Region Code].&[TA]
, [Customer].[Region Code].&[SYO], [Customer].[Region Code].&[SXNY], [Customer].[Region Code].&[SXAN], [Customer].[Region Code].&[SUR], [Customer].[Region Code].&[SUF], [Customer].[Region Code].&[STKH], [Customer].[Region Code].&[STIR], [Customer].[Region Code].&[STA], [Customer].[Region Code].&[SP], [Customer].[Region Code].&[SON], [Customer].[Region Code].&[SOM], [Customer].[Region Code].&[SNDJ], [Customer].[Region Code].&[SLTH], [Customer].[Region Code].&[SLP], [Customer].[Region Code].&[SLG], [Customer].[Region Code].&[SK], [Customer].[Region Code].&[SICH], [Customer].[Region Code].&[SHR], [Customer].[Region Code].&[SHNG], [Customer].[Region Code].&[SHND], [Customer].[Region Code].&[SEOU], [Customer].[Region Code].&[SELA], [Customer].[Region Code].&[SD], [Customer].[Region Code].&[SCRZ], [Customer].[Region Code].&[SCLA], [Customer].[Region Code].&[SCHH], [Customer].[Region Code].&[SCBO], [Customer].[Region Code].&[SC], [Customer].[Region Code].&[SARA], [Customer].[Region Code].&[SAAR], [Customer].[Region
Code].&[RSCM], [Customer].[Region Code].&[RNAL], [Customer].[Region Code].&[RI], [Customer].[Region Code].&[RHPF], [Customer].[Region Code].&[RGLN], [Customer].[Region Code].&[RENF], [Customer].[Region Code].&[QRO], [Customer].[Region Code].&[QR], [Customer].[Region Code].&[QLD], [Customer].[Region Code].&[QC], [Customer].[Region Code].&[PULA], [Customer].[Region Code].&[PUE], [Customer].[Region Code].&[PTCH], [Customer].[Region Code].&[PRTO], [Customer].[Region Code].&[PRHV], [Customer].[Region Code].&[PR], [Customer].[Region Code].&[POW], [Customer].[Region Code].&[PNDC], [Customer].[Region Code].&[PLPN], [Customer].[Region Code].&[PIEM], [Customer].[Region Code].&[PGLA], [Customer].[Region Code].&[PEMB], [Customer].[Region Code].&[PE], [Customer].[Region Code].&[PDLL], [Customer].[Region Code].&[PCRD], [Customer].[Region Code].&[PACD], [Customer].[Region Code].&[PA], [Customer].[Region Code].&[OXO], [Customer].[Region Code].&[OVJS], [Customer].[Region Code].&[OTH], [Customer].[Region Code].&[OSLO], [Cust
omer].[Region Code].&[ON], [Customer].[Region Code].&[OK], [Customer].[Region Code].&[OH], [Customer].[Region Code].&[OBER], [Customer].[Region Code].&[OAX], [Customer].[Region Code].&[NYO], [Customer].[Region Code].&[NY], [Customer].[Region Code].&[NXI], [Customer].[Region Code].&[NVRA], [Customer].[Region Code].&[NV], [Customer].[Region Code].&[NSW], [Customer].[Region Code].&[NS], [Customer].[Region Code].&[NRW], [Customer].[Region Code].&[NRDH], [Customer].[Region Code].&[NRDB], [Customer].[Region Code].&[NPDC], [Customer].[Region Code].&[NOT], [Customer].[Region Code].&[NOR], [Customer].[Region Code].&[NMR], [Customer].[Region Code].&[NMP], [Customer].[Region Code].&[NM], [Customer].[Region Code].&[NL], [Customer].[Region Code].&[NJ], [Customer].[Region Code].&[NH], [Customer].[Region Code].&[NGR], [Customer].[Region Code].&[NF], [Customer].[Region Code].&[NEL], [Customer].[Region Code].&[NEID], [Customer].[Region Code].&[NE], [Customer].[Region Code].&[ND], [Customer].[Region Code].&[NC], [Customer].[R
egion Code].&[NBL], [Customer].[Region Code].&[NB], [Customer].[Region Code].&[MWP], [Customer].[Region Code].&[MTH], [Customer].[Region Code].&[MT], [Customer].[Region Code].&[MS], [Customer].[Region Code].&[MRCA], [Customer].[Region Code].&[MPY], [Customer].[Region Code].&[MOR], [Customer].[Region Code].&, [Customer].[Region Code].&[MNGN], [Customer].[Region Code].&[MN], [Customer].[Region Code].&[MLO], [Customer].[Region Code].&[MI], [Customer].[Region Code].&[MHRS], [Customer].[Region Code].&[MEX], [Customer].[Region Code].&[METR], [Customer].[Region Code].&[MER], [Customer].[Region Code].&[ME], [Customer].[Region Code].&[MDX], [Customer].[Region Code].&[MDRD], [Customer].[Region Code].&[MD], [Customer].[Region Code].&[MB], [Customer].[Region Code].&[MAYO], [Customer].[Region Code].&[MALM], [Customer].[Region Code].&[MA], [Customer].[Region Code].&[LWSX], [Customer].[Region Code].&[LTH], [Customer].[Region Code].&[LSBO], [Customer].[Region Code].&[LPZ], [Customer].[Region Code].&[LOND], [Customer].[R
egion Code].&[LODZ], [Customer].[Region Code].&[LMSN], [Customer].[Region Code].&[LMCK], [Customer].[Region Code].&[LMBR], [Customer].[Region Code].&[LMBD], [Customer].[Region Code].&[LINC], [Customer].[Region Code].&[LIMB], [Customer].[Region Code].&[LIMA], [Customer].[Region Code].&[LIGE], [Customer].[Region Code].&[LGRA], [Customer].[Region Code].&[LEI], [Customer].[Region Code].&[LAZO], [Customer].[Region Code].&[LAR], [Customer].[Region Code].&[LAN], [Customer].[Region Code].&[LA], [Customer].[Region Code].&[KYON], [Customer].[Region Code].&[KY], [Customer].[Region Code].&[KS], [Customer].[Region Code].&[KRLA], [Customer].[Region Code].&[KLK], [Customer].[Region Code].&[KLD], [Customer].[Region Code].&[KEN], [Customer].[Region Code].&[KELA], [Customer].[Region Code].&[JONK], [Customer].[Region Code].&[JOHO], [Customer].[Region Code].&[JNGS], [Customer].[Region Code].&[JHCS], [Customer].[Region Code].&[JAL], [Customer].[Region Code].&[IZMR], [Customer].[Region Code].&[ISTN], [Customer].[Region Code].&[IS
LE], [Customer].[Region Code].&[IN], [Customer].[Region Code].&[IL], [Customer].[Region Code].&[IDF], [Customer].[Region Code].&[ID], [Customer].[Region Code].&[IA], [Customer].[Region Code].&[HUNN], [Customer].[Region Code].&[HSSN], [Customer].[Region Code].&[HNUT], [Customer].[Region Code].&[HNN], [Customer].[Region Code].&[HN], [Customer].[Region Code].&[HMBG], [Customer].[Region Code].&[HI], [Customer].[Region Code].&[HGO], [Customer].[Region Code].&[HER], [Customer].[Region Code].&[HDLN], [Customer].[Region Code].&[HBEI], [Customer].[Region Code].&[HB], [Customer].[Region Code].&[HAW], [Customer].[Region Code].&[HAM], [Customer].[Region Code].&[GU], [Customer].[Region Code].&[GTO], [Customer].[Region Code].&[GRON], [Customer].[Region Code].&[GRLO], [Customer].[Region Code].&[GRCC], [Customer].[Region Code].&[GNGD], [Customer].[Region Code].&[GM], [Customer].[Region Code].&[GLO], [Customer].[Region Code].&[GLA], [Customer].[Region Code].&[GJT], [Customer].[Region Code].&[GELD], [Customer].[Region Code].&
[GAB], [Customer].[Region Code].&[GA], [Customer].[Region Code].&[FYN], [Customer].[Region Code].&[FRIE], [Customer].[Region Code].&[FLIN], [Customer].[Region Code].&[FLEV], [Customer].[Region Code].&[FL], [Customer].[Region Code].&[FIFE], [Customer].[Region Code].&[FFL], [Customer].[Region Code].&[FDSC], [Customer].[Region Code].&[EXTR], [Customer].[Region Code].&[ESX], [Customer].[Region Code].&[ESS], [Customer].[Region Code].&[ERY], [Customer].[Region Code].&[EMRM], [Customer].[Region Code].&[EFLN], [Customer].[Region Code].&[EDI], [Customer].[Region Code].&[DUR], [Customer].[Region Code].&[DUB], [Customer].[Region Code].&[DRNT], [Customer].[Region Code].&[DNGL], [Customer].[Region Code].&[DLHI], [Customer].[Region Code].&[DF], [Customer].[Region Code].&[DEV], [Customer].[Region Code].&[DER], [Customer].[Region Code].&[DE], [Customer].[Region Code].&[DC], [Customer].[Region Code].&[DADU], [Customer].[Region Code].&[CUM], [Customer].[Region Code].&[CTLN], [Customer].[Region Code].&[CT], [Customer].[Region
Code].&[CSNG], [Customer].[Region Code].&[CRK], [Customer].[Region Code].&[COL], [Customer].[Region Code].&[COAH], [Customer].[Region Code].&, [Customer].[Region Code].&[CNTR], [Customer].[Region Code].&[CNDM], [Customer].[Region Code].&[CMPN], [Customer].[Region Code].&[CLAC], [Customer].[Region Code].&[CHIS], [Customer].[Region Code].&[CHIH], [Customer].[Region Code].&[CHE], [Customer].[Region Code].&[CERE], [Customer].[Region Code].&[CDF], [Customer].[Region Code].&[CBBO], [Customer].[Region Code].&[CARM], [Customer].[Region Code].&[CANT], [Customer].[Region Code].&[CAMB], [Customer].[Region Code].&[CAIT], [Customer].[Region Code].&[CA], [Customer].[Region Code].&[BVRA], [Customer].[Region Code].&[BUC], [Customer].[Region Code].&[BTWN], [Customer].[Region Code].&[BSQU], [Customer].[Region Code].&[BSNM], [Customer].[Region Code].&[BSKR], [Customer].[Region Code].&[BRTG], [Customer].[Region Code].&[BRNE], [Customer].[Region Code].&[BRMN], [Customer].[Region Code].&[BRLN], [Customer].[Region Code].&[BRGS
], [Customer].[Region Code].&[BRGN], [Customer].[Region Code].&[BRGA], [Customer].[Region Code].&[BRAB], [Customer].[Region Code].&[BNWG], [Customer].[Region Code].&[BIOB], [Customer].[Region Code].&[BIAL], [Customer].[Region Code].&[BG], [Customer].[Region Code].&[BER], [Customer].[Region Code].&[BEIJ], [Customer].[Region Code].&[BED], [Customer].[Region Code].&[BDPS], [Customer].[Region Code].&[BC], [Customer].[Region Code].&[BA], [Customer].[Region Code].&[AZNS], [Customer].[Region Code].&[AZ], [Customer].[Region Code].&[AYRE], [Customer].[Region Code].&[AVRG], [Customer].[Region Code].&[AVN], [Customer].[Region Code].&[AS], [Customer].[Region Code].&[ARMA], [Customer].[Region Code].&[ARHS], [Customer].[Region Code].&[ARGU], [Customer].[Region Code].&[AR], [Customer].[Region Code].&[AQTN], [Customer].[Region Code].&, [Customer].[Region Code].&[AOR], [Customer].[Region Code].&[ANTW], [Customer].[Region Code].&[ANTR], [Customer].[Region Code].&[ANHI], [Customer].[Region Code].&[ANGU], [Customer].[Region
Code].&[ANDL], [Customer].[Region Code].&[AND], [Customer].[Region Code].&[ALV], [Customer].[Region Code].&[ALSC], [Customer].[Region Code].&[AL], [Customer].[Region Code].&[AKSS], [Customer].[Region Code].&[AK], [Customer].[Region Code].&[AGS], [Customer].[Region Code].&[AGNN], [Customer].[Region Code].&[ADNA], [Customer].[Region Code].&[ABRZ], [Customer].[Region Code].&[ABE], [Customer].[Region Code].&[AB], [Customer].[Region Code].&[AAGD], [Customer].[Region Code].&[AAC], [Customer].[Region Code].&[AAB], [Customer].[Region Code].&[AA], [Customer].[Region Code].&[]}))}, {[Customer].[Region Code].&[ZUG], [Customer].[Region Code].&[ZRCH], [Customer].[Region Code].&[ZHJG], [Customer].[Region Code].&[ZDHD], [Customer].[Region Code].&[ZAC], [Customer].[Region Code].&[YUNN], [Customer].[Region Code].&[WYO], [Customer].[Region Code].&[WY], [Customer].[Region Code].&[WV], [Customer].[Region Code].&[WTRF], [Customer].[Region Code].&[WSX], [Customer].[Region Code].&[WRW], [Customer].[Region Code].&[WRSW], [Customer
].[Region Code].&[WOR], [Customer].[Region Code].&[WMD], [Customer].[Region Code].&[WISL], [Customer].[Region Code].&[WIL], [Customer].[Region Code].&[WI], [Customer].[Region Code].&[WFLN], [Customer].[Region Code].&[WBNG], [Customer].[Region Code].&[WA], [Customer].[Region Code].&[VT], [Customer].[Region Code].&[VRLB], [Customer].[Region Code].&[VNTO], [Customer].[Region Code].&[VNNA], [Customer].[Region Code].&[VLNC], [Customer].[Region Code].&[VJLE], [Customer].[Region Code].&[VIC], [Customer].[Region Code].&[VI], [Customer].[Region Code].&[VEST], [Customer].[Region Code].&[VER], [Customer].[Region Code].&[VA], [Customer].[Region Code].&[UTRC], [Customer].[Region Code].&[UT], [Customer].[Region Code].&[USMA], [Customer].[Region Code].&[UPPS], [Customer].[Region Code].&[ULPR], [Customer].[Region Code].&[TYRO], [Customer].[Region Code].&[TYN], [Customer].[Region Code].&[TX], [Customer].[Region Code].&[TSCA], [Customer].[Region Code].&[TORF], [Customer].[Region Code].&[TOKY], [Customer].[Region Code].&[TNDU]
, [Customer].[Region Code].&[TN], [Customer].[Region Code].&[TLAX], [Customer].[Region Code].&[TIPN], [Customer].[Region Code].&[TIAN], [Customer].[Region Code].&[THUR], [Customer].[Region Code].&[TAMS], [Customer].[Region Code].&[TA], [Customer].[Region Code].&[SYO], [Customer].[Region Code].&[SXNY], [Customer].[Region Code].&[SXAN], [Customer].[Region Code].&[SUR], [Customer].[Region Code].&[SUF], [Customer].[Region Code].&[STKH], [Customer].[Region Code].&[STIR], [Customer].[Region Code].&[STA], [Customer].[Region Code].&[SP], [Customer].[Region Code].&[SON], [Customer].[Region Code].&[SOM], [Customer].[Region Code].&[SNDJ], [Customer].[Region Code].&[SLTH], [Customer].[Region Code].&[SLP], [Customer].[Region Code].&[SLG], [Customer].[Region Code].&[SK], [Customer].[Region Code].&[SICH], [Customer].[Region Code].&[SHR], [Customer].[Region Code].&[SHNG], [Customer].[Region Code].&[SHND], [Customer].[Region Code].&[SEOU], [Customer].[Region Code].&[SELA], [Customer].[Region Code].&[SD], [Customer].[Region C
ode].&[SCRZ], [Customer].[Region Code].&[SCLA], [Customer].[Region Code].&[SCHH], [Customer].[Region Code].&[SCBO], [Customer].[Region Code].&[SC], [Customer].[Region Code].&[SARA], [Customer].[Region Code].&[SAAR], [Customer].[Region Code].&[RSCM], [Customer].[Region Code].&[RNAL], [Customer].[Region Code].&[RI], [Customer].[Region Code].&[RHPF], [Customer].[Region Code].&[RGLN], [Customer].[Region Code].&[RENF], [Customer].[Region Code].&[QRO], [Customer].[Region Code].&[QR], [Customer].[Region Code].&[QLD], [Customer].[Region Code].&[QC], [Customer].[Region Code].&[PULA], [Customer].[Region Code].&[PUE], [Customer].[Region Code].&[PTCH], [Customer].[Region Code].&[PRTO], [Customer].[Region Code].&[PRHV], [Customer].[Region Code].&[PR], [Customer].[Region Code].&[POW], [Customer].[Region Code].&[PNDC], [Customer].[Region Code].&[PLPN], [Customer].[Region Code].&[PIEM], [Customer].[Region Code].&[PGLA], [Customer].[Region Code].&[PEMB], [Customer].[Region Code].&[PE], [Customer].[Region Code].&[PDLL], [Cust
omer].[Region Code].&[PCRD], [Customer].[Region Code].&[PACD], [Customer].[Region Code].&[PA], [Customer].[Region Code].&[OXO], [Customer].[Region Code].&[OVJS], [Customer].[Region Code].&[OTH], [Customer].[Region Code].&[OSLO], [Customer].[Region Code].&[ON], [Customer].[Region Code].&[OK], [Customer].[Region Code].&[OH], [Customer].[Region Code].&[OBER], [Customer].[Region Code].&[OAX], [Customer].[Region Code].&[NYO], [Customer].[Region Code].&[NY], [Customer].[Region Code].&[NXI], [Customer].[Region Code].&[NVRA], [Customer].[Region Code].&[NV], [Customer].[Region Code].&[NSW], [Customer].[Region Code].&[NS], [Customer].[Region Code].&[NRW], [Customer].[Region Code].&[NRDH], [Customer].[Region Code].&[NRDB], [Customer].[Region Code].&[NPDC], [Customer].[Region Code].&[NOT], [Customer].[Region Code].&[NOR], [Customer].[Region Code].&[NMR], [Customer].[Region Code].&[NMP], [Customer].[Region Code].&[NM], [Customer].[Region Code].&[NL], [Customer].[Region Code].&[NJ], [Customer].[Region Code].&[NH], [Custom
er].[Region Code].&[NGR], [Customer].[Region Code].&[NF], [Customer].[Region Code].&[NEL], [Customer].[Region Code].&[NEID], [Customer].[Region Code].&[NE], [Customer].[Region Code].&[ND], [Customer].[Region Code].&[NC], [Customer].[Region Code].&[NBL], [Customer].[Region Code].&[NB], [Customer].[Region Code].&[MWP], [Customer].[Region Code].&[MTH], [Customer].[Region Code].&[MT], [Customer].[Region Code].&[MS], [Customer].[Region Code].&[MRCA], [Customer].[Region Code].&[MPY], [Customer].[Region Code].&[MOR], [Customer].[Region Code].&, [Customer].[Region Code].&[MNGN], [Customer].[Region Code].&[MN], [Customer].[Region Code].&[MLO], [Customer].[Region Code].&[MI], [Customer].[Region Code].&[MHRS], [Customer].[Region Code].&[MEX], [Customer].[Region Code].&[METR], [Customer].[Region Code].&[MER], [Customer].[Region Code].&[ME], [Customer].[Region Code].&[MDX], [Customer].[Region Code].&[MDRD], [Customer].[Region Code].&[MD], [Customer].[Region Code].&[MB], [Customer].[Region Code].&[MAYO], [Customer].[R
egion Code].&[MALM], [Customer].[Region Code].&[MA], [Customer].[Region Code].&[LWSX], [Customer].[Region Code].&[LTH], [Customer].[Region Code].&[LSBO], [Customer].[Region Code].&[LPZ], [Customer].[Region Code].&[LOND], [Customer].[Region Code].&[LODZ], [Customer].[Region Code].&[LMSN], [Customer].[Region Code].&[LMCK], [Customer].[Region Code].&[LMBR], [Customer].[Region Code].&[LMBD], [Customer].[Region Code].&[LINC], [Customer].[Region Code].&[LIMB], [Customer].[Region Code].&[LIMA], [Customer].[Region Code].&[LIGE], [Customer].[Region Code].&[LGRA], [Customer].[Region Code].&[LEI], [Customer].[Region Code].&[LAZO], [Customer].[Region Code].&[LAR], [Customer].[Region Code].&[LAN], [Customer].[Region Code].&[LA], [Customer].[Region Code].&[KYON], [Customer].[Region Code].&[KY], [Customer].[Region Code].&[KS], [Customer].[Region Code].&[KRLA], [Customer].[Region Code].&[KLK], [Customer].[Region Code].&[KLD], [Customer].[Region Code].&[KEN], [Customer].[Region Code].&[KELA], [Customer].[Region Code].&[JONK]
, [Customer].[Region Code].&[JOHO], [Customer].[Region Code].&[JNGS], [Customer].[Region Code].&[JHCS], [Customer].[Region Code].&[JAL], [Customer].[Region Code].&[IZMR], [Customer].[Region Code].&[ISTN], [Customer].[Region Code].&[ISLE], [Customer].[Region Code].&[IN], [Customer].[Region Code].&[IL], [Customer].[Region Code].&[IDF], [Customer].[Region Code].&[ID], [Customer].[Region Code].&[IA], [Customer].[Region Code].&[HUNN], [Customer].[Region Code].&[HSSN], [Customer].[Region Code].&[HNUT], [Customer].[Region Code].&[HNN], [Customer].[Region Code].&[HN], [Customer].[Region Code].&[HMBG], [Customer].[Region Code].&[HI], [Customer].[Region Code].&[HGO], [Customer].[Region Code].&[HER], [Customer].[Region Code].&[HDLN], [Customer].[Region Code].&[HBEI], [Customer].[Region Code].&[HB], [Customer].[Region Code].&[HAW], [Customer].[Region Code].&[HAM], [Customer].[Region Code].&[GU], [Customer].[Region Code].&[GTO], [Customer].[Region Code].&[GRON], [Customer].[Region Code].&[GRLO], [Customer].[Region Code].
&[GRCC], [Customer].[Region Code].&[GNGD], [Customer].[Region Code].&[GM], [Customer].[Region Code].&[GLO], [Customer].[Region Code].&[GLA], [Customer].[Region Code].&[GJT], [Customer].[Region Code].&[GELD], [Customer].[Region Code].&[GAB], [Customer].[Region Code].&[GA], [Customer].[Region Code].&[FYN], [Customer].[Region Code].&[FRIE], [Customer].[Region Code].&[FLIN], [Customer].[Region Code].&[FLEV], [Customer].[Region Code].&[FL], [Customer].[Region Code].&[FIFE], [Customer].[Region Code].&[FFL], [Customer].[Region Code].&[FDSC], [Customer].[Region Code].&[EXTR], [Customer].[Region Code].&[ESX], [Customer].[Region Code].&[ESS], [Customer].[Region Code].&[ERY], [Customer].[Region Code].&[EMRM], [Customer].[Region Code].&[EFLN], [Customer].[Region Code].&[EDI], [Customer].[Region Code].&[DUR], [Customer].[Region Code].&[DUB], [Customer].[Region Code].&[DRNT], [Customer].[Region Code].&[DNGL], [Customer].[Region Code].&[DLHI], [Customer].[Region Code].&[DF], [Customer].[Region Code].&[DEV], [Customer].[Reg
ion Code].&[DER], [Customer].[Region Code].&[DE], [Customer].[Region Code].&[DC], [Customer].[Region Code].&[DADU], [Customer].[Region Code].&[CUM], [Customer].[Region Code].&[CTLN], [Customer].[Region Code].&[CT], [Customer].[Region Code].&[CSNG], [Customer].[Region Code].&[CRK], [Customer].[Region Code].&[COL], [Customer].[Region Code].&[COAH], [Customer].[Region Code].&, [Customer].[Region Code].&[CNTR], [Customer].[Region Code].&[CNDM], [Customer].[Region Code].&[CMPN], [Customer].[Region Code].&[CLAC], [Customer].[Region Code].&[CHIS], [Customer].[Region Code].&[CHIH], [Customer].[Region Code].&[CHE], [Customer].[Region Code].&[CERE], [Customer].[Region Code].&[CDF], [Customer].[Region Code].&[CBBO], [Customer].[Region Code].&[CARM], [Customer].[Region Code].&[CANT], [Customer].[Region Code].&[CAMB], [Customer].[Region Code].&[CAIT], [Customer].[Region Code].&[CA], [Customer].[Region Code].&[BVRA], [Customer].[Region Code].&[BUC], [Customer].[Region Code].&[BTWN], [Customer].[Region Code].&[BSQU], [
Customer].[Region Code].&[BSNM], [Customer].[Region Code].&[BSKR], [Customer].[Region Code].&[BRTG], [Customer].[Region Code].&[BRNE], [Customer].[Region Code].&[BRMN], [Customer].[Region Code].&[BRLN], [Customer].[Region Code].&[BRGS], [Customer].[Region Code].&[BRGN], [Customer].[Region Code].&[BRGA], [Customer].[Region Code].&[BRAB], [Customer].[Region Code].&[BNWG], [Customer].[Region Code].&[BIOB], [Customer].[Region Code].&[BIAL], [Customer].[Region Code].&[BG], [Customer].[Region Code].&[BER], [Customer].[Region Code].&[BEIJ], [Customer].[Region Code].&[BED], [Customer].[Region Code].&[BDPS], [Customer].[Region Code].&[BC], [Customer].[Region Code].&[BA], [Customer].[Region Code].&[AZNS], [Customer].[Region Code].&[AZ], [Customer].[Region Code].&[AYRE], [Customer].[Region Code].&[AVRG], [Customer].[Region Code].&[AVN], [Customer].[Region Code].&[AS], [Customer].[Region Code].&[ARMA], [Customer].[Region Code].&[ARHS], [Customer].[Region Code].&[ARGU], [Customer].[Region Code].&[AR], [Customer].[Region
Code].&[AQTN], [Customer].[Region Code].&, [Customer].[Region Code].&[AOR], [Customer].[Region Code].&[ANTW], [Customer].[Region Code].&[ANTR], [Customer].[Region Code].&[ANHI], [Customer].[Region Code].&[ANGU], [Customer].[Region Code].&[ANDL], [Customer].[Region Code].&[AND], [Customer].[Region Code].&[ALV], [Customer].[Region Code].&[ALSC], [Customer].[Region Code].&[AL], [Customer].[Region Code].&[AKSS], [Customer].[Region Code].&[AK], [Customer].[Region Code].&[AGS], [Customer].[Region Code].&[AGNN], [Customer].[Region Code].&[ADNA], [Customer].[Region Code].&[ABRZ], [Customer].[Region Code].&[ABE], [Customer].[Region Code].&[AB], [Customer].[Region Code].&[AAGD], [Customer].[Region Code].&[AAC], [Customer].[Region Code].&[AAB], [Customer].[Region Code].&[AA], [Customer].[Region Code].&[]}))), {[Measures].[Sales, (USD)], [Measures].[Sales, Volume (lbs)]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Apollo11Dev] WHERE ([Products].[P-Product Line].&[1,4-Butanediol], [Customer].[City].[Al
l], [Date].[Quarter].[All], [Date].[Year].[All])

I then went to run this query against the same cube, but did it as a MDX query in SQL Management Studio. My CPU instantly goes to 100% (nothing unusual), but this query seems to be taking a very long time. I then went to cancel the query in Management Studio, but that didn't seem to work. I then went into Activity Viewer, and tried to kill connections and sessions. I was able to kill the connection, but the CPU is still pegged at 100%. There is a session that I cannot kill, which I assume is the session for the above query?

It looks as if a restart is the only solution.

Any thoughts?|||

Hello,

now I'm using ActivityViewer for some time and I for most situations it works fine. But I also had couple of queries, which I simply couldn't kill. I had to wait until it was finished.

But you could also experiment with CoordinatorCancelCount, which says how often the engine would come to check if Cancel command wasn't issued. Read more about it at: http://msdn2.microsoft.com/en-us/library/ms174921.aspx

Radim

|||Unfortunately, that property (CoordinatorCancelCount) doesn't seem to be available as I don't see it.

How to kill running query?

Hi!
Our users often run malicious queries which could take up to quarter of CPU power for couple of hours. If there is more that one such query, the whole environment is severely impacted with no other recovery that restarting the service.

Is there any (easy) way how to kill unwanted connections and related queries? If not, could someone please give some explanation for that (is it design issue or what?). Otherwise I'd like to see that functionality in SP2-3. Someone with similar experience out there?Could you tell us please, what a client do you use?|||The mechanism of cancelling queries in Yukon is based on establishing another connection with the server passing the same session id as part of the connection string. If that session is currently executing a query it is possible to send query cancel request. Microsoft.AnalysisServices.Server class has CancelCommand method, for instance.

To determine which sessions are running it is possible to send sessions discover schema request.

I am not sure how you are planning to do it - automatically or manually.|||

Could own Command be cancelled without AMO. Only using AdomMd.NET?

What a XMLA Execute schould be ussued?

Who has permittion to cancel command in another session?

|||

Absolutly.

You can use any client, not only AMO to send a Cancel command.

For instance, you can use ActivityViewer sample application to cancel user sessions, or user connections.

Connect to Analysis Server with Profiler to see exact syntax of the Cancel command. Then you can use any client object model to submit the command.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Just a few days ago, we discovered that when a user cancels his or her query from Excel (that's our front end to our AS2005 cube), it's only canceling on the client side and that the query is still running on the server and will keep on running until it completes (yes, "canceling" takes just as long as if the user had let the query run until completion). As a result, our server's memory and CPU gets consumed entirely and requires us to restart the service at least once or twice a day.

I believe if one cancels a query from within Management Studio, the query will also not be canceled immediately and will essentially run to completion.

Basically, we discovered that canceling is simply asking for the results to not be shown as the query will still run to completion.

Is there a way we can kill the query when the user hits escape in Excel?|||

For Excel case this needs to be investigated. If this is not about Office 12 Beta Excel then the client probably executes the queries on non user interface thread and when user tries to cancel the query the client simply unblocks the UI thread, while the worker thread keeps running.

In SQL Server Management Studio we do not do that. We send Cancel request to the server, server notices it and as soon as algorithms are ready to break the server produces "query cancelled" response. If for some queries the server does not break reasonably soon then this should be logged as a bug.

|||Thank you, Edward! I complied ActivityViewer and it is what I was looking for.

Personally I think this is very important feature and it should be internal part of Management Studio, the same way as SQL has Activity Monitor. But at least there's a way how you can do it.

Cheers!
Radim|||UPDATE:

Using Excel 2003 SP2, I connected to a local AS2005 cube. I wrote a report and saved it. I opened up the workbook and hit "refresh data". After a few minutes, the query is done. I then went into view the code of the sheet so I could get the MDX. Using activesheet.pivottable.mdx, I got the following query:

SELECT NON EMPTY CROSSJOIN(CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Customer].[Customer Id].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Customer].[Customer Name].[All]})}))), HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownLevel({[Customer].[Region Code].[All]}))}, {[Customer].[Region Code].&[ZUG], [Customer].[Region Code].&[ZRCH], [Customer].[Region Code].&[ZHJG], [Customer].[Region Code].&[ZDHD], [Customer].[Region Code].&[ZAC], [Customer].[Region Code].&[YUNN], [Customer].[Region Code].&[WYO], [Customer].[Region Code].&[WY], [Customer].[Region Code].&[WV], [Customer].[Region Code].&[WTRF], [Customer].[Region Code].&[WSX], [Customer].[Region Code].&[WRW], [Customer].[Region Code].&[WRSW], [Customer].[Region Code].&[WOR], [Customer].[Region Code].&[WMD], [Customer].[Region Code].&[WISL], [Customer].[Region Code].&[WIL], [Customer].[Region Code].&[WI], [Customer].[Region Code].&[WFLN], [Customer].[Region Code].&[WBNG], [Customer]
.[Region Code].&[WA], [Customer].[Region Code].&[VT], [Customer].[Region Code].&[VRLB], [Customer].[Region Code].&[VNTO], [Customer].[Region Code].&[VNNA], [Customer].[Region Code].&[VLNC], [Customer].[Region Code].&[VJLE], [Customer].[Region Code].&[VIC], [Customer].[Region Code].&[VI], [Customer].[Region Code].&[VEST], [Customer].[Region Code].&[VER], [Customer].[Region Code].&[VA], [Customer].[Region Code].&[UTRC], [Customer].[Region Code].&[UT], [Customer].[Region Code].&[USMA], [Customer].[Region Code].&[UPPS], [Customer].[Region Code].&[ULPR], [Customer].[Region Code].&[TYRO], [Customer].[Region Code].&[TYN], [Customer].[Region Code].&[TX], [Customer].[Region Code].&[TSCA], [Customer].[Region Code].&[TORF], [Customer].[Region Code].&[TOKY], [Customer].[Region Code].&[TNDU], [Customer].[Region Code].&[TN], [Customer].[Region Code].&[TLAX], [Customer].[Region Code].&[TIPN], [Customer].[Region Code].&[TIAN], [Customer].[Region Code].&[THUR], [Customer].[Region Code].&[TAMS], [Customer].[Region Code].&[TA]
, [Customer].[Region Code].&[SYO], [Customer].[Region Code].&[SXNY], [Customer].[Region Code].&[SXAN], [Customer].[Region Code].&[SUR], [Customer].[Region Code].&[SUF], [Customer].[Region Code].&[STKH], [Customer].[Region Code].&[STIR], [Customer].[Region Code].&[STA], [Customer].[Region Code].&[SP], [Customer].[Region Code].&[SON], [Customer].[Region Code].&[SOM], [Customer].[Region Code].&[SNDJ], [Customer].[Region Code].&[SLTH], [Customer].[Region Code].&[SLP], [Customer].[Region Code].&[SLG], [Customer].[Region Code].&[SK], [Customer].[Region Code].&[SICH], [Customer].[Region Code].&[SHR], [Customer].[Region Code].&[SHNG], [Customer].[Region Code].&[SHND], [Customer].[Region Code].&[SEOU], [Customer].[Region Code].&[SELA], [Customer].[Region Code].&[SD], [Customer].[Region Code].&[SCRZ], [Customer].[Region Code].&[SCLA], [Customer].[Region Code].&[SCHH], [Customer].[Region Code].&[SCBO], [Customer].[Region Code].&[SC], [Customer].[Region Code].&[SARA], [Customer].[Region Code].&[SAAR], [Customer].[Region
Code].&[RSCM], [Customer].[Region Code].&[RNAL], [Customer].[Region Code].&[RI], [Customer].[Region Code].&[RHPF], [Customer].[Region Code].&[RGLN], [Customer].[Region Code].&[RENF], [Customer].[Region Code].&[QRO], [Customer].[Region Code].&[QR], [Customer].[Region Code].&[QLD], [Customer].[Region Code].&[QC], [Customer].[Region Code].&[PULA], [Customer].[Region Code].&[PUE], [Customer].[Region Code].&[PTCH], [Customer].[Region Code].&[PRTO], [Customer].[Region Code].&[PRHV], [Customer].[Region Code].&[PR], [Customer].[Region Code].&[POW], [Customer].[Region Code].&[PNDC], [Customer].[Region Code].&[PLPN], [Customer].[Region Code].&[PIEM], [Customer].[Region Code].&[PGLA], [Customer].[Region Code].&[PEMB], [Customer].[Region Code].&[PE], [Customer].[Region Code].&[PDLL], [Customer].[Region Code].&[PCRD], [Customer].[Region Code].&[PACD], [Customer].[Region Code].&[PA], [Customer].[Region Code].&[OXO], [Customer].[Region Code].&[OVJS], [Customer].[Region Code].&[OTH], [Customer].[Region Code].&[OSLO], [Cust
omer].[Region Code].&[ON], [Customer].[Region Code].&[OK], [Customer].[Region Code].&[OH], [Customer].[Region Code].&[OBER], [Customer].[Region Code].&[OAX], [Customer].[Region Code].&[NYO], [Customer].[Region Code].&[NY], [Customer].[Region Code].&[NXI], [Customer].[Region Code].&[NVRA], [Customer].[Region Code].&[NV], [Customer].[Region Code].&[NSW], [Customer].[Region Code].&[NS], [Customer].[Region Code].&[NRW], [Customer].[Region Code].&[NRDH], [Customer].[Region Code].&[NRDB], [Customer].[Region Code].&[NPDC], [Customer].[Region Code].&[NOT], [Customer].[Region Code].&[NOR], [Customer].[Region Code].&[NMR], [Customer].[Region Code].&[NMP], [Customer].[Region Code].&[NM], [Customer].[Region Code].&[NL], [Customer].[Region Code].&[NJ], [Customer].[Region Code].&[NH], [Customer].[Region Code].&[NGR], [Customer].[Region Code].&[NF], [Customer].[Region Code].&[NEL], [Customer].[Region Code].&[NEID], [Customer].[Region Code].&[NE], [Customer].[Region Code].&[ND], [Customer].[Region Code].&[NC], [Customer].[R
egion Code].&[NBL], [Customer].[Region Code].&[NB], [Customer].[Region Code].&[MWP], [Customer].[Region Code].&[MTH], [Customer].[Region Code].&[MT], [Customer].[Region Code].&[MS], [Customer].[Region Code].&[MRCA], [Customer].[Region Code].&[MPY], [Customer].[Region Code].&[MOR], [Customer].[Region Code].&, [Customer].[Region Code].&[MNGN], [Customer].[Region Code].&[MN], [Customer].[Region Code].&[MLO], [Customer].[Region Code].&[MI], [Customer].[Region Code].&[MHRS], [Customer].[Region Code].&[MEX], [Customer].[Region Code].&[METR], [Customer].[Region Code].&[MER], [Customer].[Region Code].&[ME], [Customer].[Region Code].&[MDX], [Customer].[Region Code].&[MDRD], [Customer].[Region Code].&[MD], [Customer].[Region Code].&[MB], [Customer].[Region Code].&[MAYO], [Customer].[Region Code].&[MALM], [Customer].[Region Code].&[MA], [Customer].[Region Code].&[LWSX], [Customer].[Region Code].&[LTH], [Customer].[Region Code].&[LSBO], [Customer].[Region Code].&[LPZ], [Customer].[Region Code].&[LOND], [Customer].[R
egion Code].&[LODZ], [Customer].[Region Code].&[LMSN], [Customer].[Region Code].&[LMCK], [Customer].[Region Code].&[LMBR], [Customer].[Region Code].&[LMBD], [Customer].[Region Code].&[LINC], [Customer].[Region Code].&[LIMB], [Customer].[Region Code].&[LIMA], [Customer].[Region Code].&[LIGE], [Customer].[Region Code].&[LGRA], [Customer].[Region Code].&[LEI], [Customer].[Region Code].&[LAZO], [Customer].[Region Code].&[LAR], [Customer].[Region Code].&[LAN], [Customer].[Region Code].&[LA], [Customer].[Region Code].&[KYON], [Customer].[Region Code].&[KY], [Customer].[Region Code].&[KS], [Customer].[Region Code].&[KRLA], [Customer].[Region Code].&[KLK], [Customer].[Region Code].&[KLD], [Customer].[Region Code].&[KEN], [Customer].[Region Code].&[KELA], [Customer].[Region Code].&[JONK], [Customer].[Region Code].&[JOHO], [Customer].[Region Code].&[JNGS], [Customer].[Region Code].&[JHCS], [Customer].[Region Code].&[JAL], [Customer].[Region Code].&[IZMR], [Customer].[Region Code].&[ISTN], [Customer].[Region Code].&[IS
LE], [Customer].[Region Code].&[IN], [Customer].[Region Code].&[IL], [Customer].[Region Code].&[IDF], [Customer].[Region Code].&[ID], [Customer].[Region Code].&[IA], [Customer].[Region Code].&[HUNN], [Customer].[Region Code].&[HSSN], [Customer].[Region Code].&[HNUT], [Customer].[Region Code].&[HNN], [Customer].[Region Code].&[HN], [Customer].[Region Code].&[HMBG], [Customer].[Region Code].&[HI], [Customer].[Region Code].&[HGO], [Customer].[Region Code].&[HER], [Customer].[Region Code].&[HDLN], [Customer].[Region Code].&[HBEI], [Customer].[Region Code].&[HB], [Customer].[Region Code].&[HAW], [Customer].[Region Code].&[HAM], [Customer].[Region Code].&[GU], [Customer].[Region Code].&[GTO], [Customer].[Region Code].&[GRON], [Customer].[Region Code].&[GRLO], [Customer].[Region Code].&[GRCC], [Customer].[Region Code].&[GNGD], [Customer].[Region Code].&[GM], [Customer].[Region Code].&[GLO], [Customer].[Region Code].&[GLA], [Customer].[Region Code].&[GJT], [Customer].[Region Code].&[GELD], [Customer].[Region Code].&
[GAB], [Customer].[Region Code].&[GA], [Customer].[Region Code].&[FYN], [Customer].[Region Code].&[FRIE], [Customer].[Region Code].&[FLIN], [Customer].[Region Code].&[FLEV], [Customer].[Region Code].&[FL], [Customer].[Region Code].&[FIFE], [Customer].[Region Code].&[FFL], [Customer].[Region Code].&[FDSC], [Customer].[Region Code].&[EXTR], [Customer].[Region Code].&[ESX], [Customer].[Region Code].&[ESS], [Customer].[Region Code].&[ERY], [Customer].[Region Code].&[EMRM], [Customer].[Region Code].&[EFLN], [Customer].[Region Code].&[EDI], [Customer].[Region Code].&[DUR], [Customer].[Region Code].&[DUB], [Customer].[Region Code].&[DRNT], [Customer].[Region Code].&[DNGL], [Customer].[Region Code].&[DLHI], [Customer].[Region Code].&[DF], [Customer].[Region Code].&[DEV], [Customer].[Region Code].&[DER], [Customer].[Region Code].&[DE], [Customer].[Region Code].&[DC], [Customer].[Region Code].&[DADU], [Customer].[Region Code].&[CUM], [Customer].[Region Code].&[CTLN], [Customer].[Region Code].&[CT], [Customer].[Region
Code].&[CSNG], [Customer].[Region Code].&[CRK], [Customer].[Region Code].&[COL], [Customer].[Region Code].&[COAH], [Customer].[Region Code].&, [Customer].[Region Code].&[CNTR], [Customer].[Region Code].&[CNDM], [Customer].[Region Code].&[CMPN], [Customer].[Region Code].&[CLAC], [Customer].[Region Code].&[CHIS], [Customer].[Region Code].&[CHIH], [Customer].[Region Code].&[CHE], [Customer].[Region Code].&[CERE], [Customer].[Region Code].&[CDF], [Customer].[Region Code].&[CBBO], [Customer].[Region Code].&[CARM], [Customer].[Region Code].&[CANT], [Customer].[Region Code].&[CAMB], [Customer].[Region Code].&[CAIT], [Customer].[Region Code].&[CA], [Customer].[Region Code].&[BVRA], [Customer].[Region Code].&[BUC], [Customer].[Region Code].&[BTWN], [Customer].[Region Code].&[BSQU], [Customer].[Region Code].&[BSNM], [Customer].[Region Code].&[BSKR], [Customer].[Region Code].&[BRTG], [Customer].[Region Code].&[BRNE], [Customer].[Region Code].&[BRMN], [Customer].[Region Code].&[BRLN], [Customer].[Region Code].&[BRGS
], [Customer].[Region Code].&[BRGN], [Customer].[Region Code].&[BRGA], [Customer].[Region Code].&[BRAB], [Customer].[Region Code].&[BNWG], [Customer].[Region Code].&[BIOB], [Customer].[Region Code].&[BIAL], [Customer].[Region Code].&[BG], [Customer].[Region Code].&[BER], [Customer].[Region Code].&[BEIJ], [Customer].[Region Code].&[BED], [Customer].[Region Code].&[BDPS], [Customer].[Region Code].&[BC], [Customer].[Region Code].&[BA], [Customer].[Region Code].&[AZNS], [Customer].[Region Code].&[AZ], [Customer].[Region Code].&[AYRE], [Customer].[Region Code].&[AVRG], [Customer].[Region Code].&[AVN], [Customer].[Region Code].&[AS], [Customer].[Region Code].&[ARMA], [Customer].[Region Code].&[ARHS], [Customer].[Region Code].&[ARGU], [Customer].[Region Code].&[AR], [Customer].[Region Code].&[AQTN], [Customer].[Region Code].&, [Customer].[Region Code].&[AOR], [Customer].[Region Code].&[ANTW], [Customer].[Region Code].&[ANTR], [Customer].[Region Code].&[ANHI], [Customer].[Region Code].&[ANGU], [Customer].[Region
Code].&[ANDL], [Customer].[Region Code].&[AND], [Customer].[Region Code].&[ALV], [Customer].[Region Code].&[ALSC], [Customer].[Region Code].&[AL], [Customer].[Region Code].&[AKSS], [Customer].[Region Code].&[AK], [Customer].[Region Code].&[AGS], [Customer].[Region Code].&[AGNN], [Customer].[Region Code].&[ADNA], [Customer].[Region Code].&[ABRZ], [Customer].[Region Code].&[ABE], [Customer].[Region Code].&[AB], [Customer].[Region Code].&[AAGD], [Customer].[Region Code].&[AAC], [Customer].[Region Code].&[AAB], [Customer].[Region Code].&[AA], [Customer].[Region Code].&[]}))}, {[Customer].[Region Code].&[ZUG], [Customer].[Region Code].&[ZRCH], [Customer].[Region Code].&[ZHJG], [Customer].[Region Code].&[ZDHD], [Customer].[Region Code].&[ZAC], [Customer].[Region Code].&[YUNN], [Customer].[Region Code].&[WYO], [Customer].[Region Code].&[WY], [Customer].[Region Code].&[WV], [Customer].[Region Code].&[WTRF], [Customer].[Region Code].&[WSX], [Customer].[Region Code].&[WRW], [Customer].[Region Code].&[WRSW], [Customer
].[Region Code].&[WOR], [Customer].[Region Code].&[WMD], [Customer].[Region Code].&[WISL], [Customer].[Region Code].&[WIL], [Customer].[Region Code].&[WI], [Customer].[Region Code].&[WFLN], [Customer].[Region Code].&[WBNG], [Customer].[Region Code].&[WA], [Customer].[Region Code].&[VT], [Customer].[Region Code].&[VRLB], [Customer].[Region Code].&[VNTO], [Customer].[Region Code].&[VNNA], [Customer].[Region Code].&[VLNC], [Customer].[Region Code].&[VJLE], [Customer].[Region Code].&[VIC], [Customer].[Region Code].&[VI], [Customer].[Region Code].&[VEST], [Customer].[Region Code].&[VER], [Customer].[Region Code].&[VA], [Customer].[Region Code].&[UTRC], [Customer].[Region Code].&[UT], [Customer].[Region Code].&[USMA], [Customer].[Region Code].&[UPPS], [Customer].[Region Code].&[ULPR], [Customer].[Region Code].&[TYRO], [Customer].[Region Code].&[TYN], [Customer].[Region Code].&[TX], [Customer].[Region Code].&[TSCA], [Customer].[Region Code].&[TORF], [Customer].[Region Code].&[TOKY], [Customer].[Region Code].&[TNDU]
, [Customer].[Region Code].&[TN], [Customer].[Region Code].&[TLAX], [Customer].[Region Code].&[TIPN], [Customer].[Region Code].&[TIAN], [Customer].[Region Code].&[THUR], [Customer].[Region Code].&[TAMS], [Customer].[Region Code].&[TA], [Customer].[Region Code].&[SYO], [Customer].[Region Code].&[SXNY], [Customer].[Region Code].&[SXAN], [Customer].[Region Code].&[SUR], [Customer].[Region Code].&[SUF], [Customer].[Region Code].&[STKH], [Customer].[Region Code].&[STIR], [Customer].[Region Code].&[STA], [Customer].[Region Code].&[SP], [Customer].[Region Code].&[SON], [Customer].[Region Code].&[SOM], [Customer].[Region Code].&[SNDJ], [Customer].[Region Code].&[SLTH], [Customer].[Region Code].&[SLP], [Customer].[Region Code].&[SLG], [Customer].[Region Code].&[SK], [Customer].[Region Code].&[SICH], [Customer].[Region Code].&[SHR], [Customer].[Region Code].&[SHNG], [Customer].[Region Code].&[SHND], [Customer].[Region Code].&[SEOU], [Customer].[Region Code].&[SELA], [Customer].[Region Code].&[SD], [Customer].[Region C
ode].&[SCRZ], [Customer].[Region Code].&[SCLA], [Customer].[Region Code].&[SCHH], [Customer].[Region Code].&[SCBO], [Customer].[Region Code].&[SC], [Customer].[Region Code].&[SARA], [Customer].[Region Code].&[SAAR], [Customer].[Region Code].&[RSCM], [Customer].[Region Code].&[RNAL], [Customer].[Region Code].&[RI], [Customer].[Region Code].&[RHPF], [Customer].[Region Code].&[RGLN], [Customer].[Region Code].&[RENF], [Customer].[Region Code].&[QRO], [Customer].[Region Code].&[QR], [Customer].[Region Code].&[QLD], [Customer].[Region Code].&[QC], [Customer].[Region Code].&[PULA], [Customer].[Region Code].&[PUE], [Customer].[Region Code].&[PTCH], [Customer].[Region Code].&[PRTO], [Customer].[Region Code].&[PRHV], [Customer].[Region Code].&[PR], [Customer].[Region Code].&[POW], [Customer].[Region Code].&[PNDC], [Customer].[Region Code].&[PLPN], [Customer].[Region Code].&[PIEM], [Customer].[Region Code].&[PGLA], [Customer].[Region Code].&[PEMB], [Customer].[Region Code].&[PE], [Customer].[Region Code].&[PDLL], [Cust
omer].[Region Code].&[PCRD], [Customer].[Region Code].&[PACD], [Customer].[Region Code].&[PA], [Customer].[Region Code].&[OXO], [Customer].[Region Code].&[OVJS], [Customer].[Region Code].&[OTH], [Customer].[Region Code].&[OSLO], [Customer].[Region Code].&[ON], [Customer].[Region Code].&[OK], [Customer].[Region Code].&[OH], [Customer].[Region Code].&[OBER], [Customer].[Region Code].&[OAX], [Customer].[Region Code].&[NYO], [Customer].[Region Code].&[NY], [Customer].[Region Code].&[NXI], [Customer].[Region Code].&[NVRA], [Customer].[Region Code].&[NV], [Customer].[Region Code].&[NSW], [Customer].[Region Code].&[NS], [Customer].[Region Code].&[NRW], [Customer].[Region Code].&[NRDH], [Customer].[Region Code].&[NRDB], [Customer].[Region Code].&[NPDC], [Customer].[Region Code].&[NOT], [Customer].[Region Code].&[NOR], [Customer].[Region Code].&[NMR], [Customer].[Region Code].&[NMP], [Customer].[Region Code].&[NM], [Customer].[Region Code].&[NL], [Customer].[Region Code].&[NJ], [Customer].[Region Code].&[NH], [Custom
er].[Region Code].&[NGR], [Customer].[Region Code].&[NF], [Customer].[Region Code].&[NEL], [Customer].[Region Code].&[NEID], [Customer].[Region Code].&[NE], [Customer].[Region Code].&[ND], [Customer].[Region Code].&[NC], [Customer].[Region Code].&[NBL], [Customer].[Region Code].&[NB], [Customer].[Region Code].&[MWP], [Customer].[Region Code].&[MTH], [Customer].[Region Code].&[MT], [Customer].[Region Code].&[MS], [Customer].[Region Code].&[MRCA], [Customer].[Region Code].&[MPY], [Customer].[Region Code].&[MOR], [Customer].[Region Code].&, [Customer].[Region Code].&[MNGN], [Customer].[Region Code].&[MN], [Customer].[Region Code].&[MLO], [Customer].[Region Code].&[MI], [Customer].[Region Code].&[MHRS], [Customer].[Region Code].&[MEX], [Customer].[Region Code].&[METR], [Customer].[Region Code].&[MER], [Customer].[Region Code].&[ME], [Customer].[Region Code].&[MDX], [Customer].[Region Code].&[MDRD], [Customer].[Region Code].&[MD], [Customer].[Region Code].&[MB], [Customer].[Region Code].&[MAYO], [Customer].[R
egion Code].&[MALM], [Customer].[Region Code].&[MA], [Customer].[Region Code].&[LWSX], [Customer].[Region Code].&[LTH], [Customer].[Region Code].&[LSBO], [Customer].[Region Code].&[LPZ], [Customer].[Region Code].&[LOND], [Customer].[Region Code].&[LODZ], [Customer].[Region Code].&[LMSN], [Customer].[Region Code].&[LMCK], [Customer].[Region Code].&[LMBR], [Customer].[Region Code].&[LMBD], [Customer].[Region Code].&[LINC], [Customer].[Region Code].&[LIMB], [Customer].[Region Code].&[LIMA], [Customer].[Region Code].&[LIGE], [Customer].[Region Code].&[LGRA], [Customer].[Region Code].&[LEI], [Customer].[Region Code].&[LAZO], [Customer].[Region Code].&[LAR], [Customer].[Region Code].&[LAN], [Customer].[Region Code].&[LA], [Customer].[Region Code].&[KYON], [Customer].[Region Code].&[KY], [Customer].[Region Code].&[KS], [Customer].[Region Code].&[KRLA], [Customer].[Region Code].&[KLK], [Customer].[Region Code].&[KLD], [Customer].[Region Code].&[KEN], [Customer].[Region Code].&[KELA], [Customer].[Region Code].&[JONK]
, [Customer].[Region Code].&[JOHO], [Customer].[Region Code].&[JNGS], [Customer].[Region Code].&[JHCS], [Customer].[Region Code].&[JAL], [Customer].[Region Code].&[IZMR], [Customer].[Region Code].&[ISTN], [Customer].[Region Code].&[ISLE], [Customer].[Region Code].&[IN], [Customer].[Region Code].&[IL], [Customer].[Region Code].&[IDF], [Customer].[Region Code].&[ID], [Customer].[Region Code].&[IA], [Customer].[Region Code].&[HUNN], [Customer].[Region Code].&[HSSN], [Customer].[Region Code].&[HNUT], [Customer].[Region Code].&[HNN], [Customer].[Region Code].&[HN], [Customer].[Region Code].&[HMBG], [Customer].[Region Code].&[HI], [Customer].[Region Code].&[HGO], [Customer].[Region Code].&[HER], [Customer].[Region Code].&[HDLN], [Customer].[Region Code].&[HBEI], [Customer].[Region Code].&[HB], [Customer].[Region Code].&[HAW], [Customer].[Region Code].&[HAM], [Customer].[Region Code].&[GU], [Customer].[Region Code].&[GTO], [Customer].[Region Code].&[GRON], [Customer].[Region Code].&[GRLO], [Customer].[Region Code].
&[GRCC], [Customer].[Region Code].&[GNGD], [Customer].[Region Code].&[GM], [Customer].[Region Code].&[GLO], [Customer].[Region Code].&[GLA], [Customer].[Region Code].&[GJT], [Customer].[Region Code].&[GELD], [Customer].[Region Code].&[GAB], [Customer].[Region Code].&[GA], [Customer].[Region Code].&[FYN], [Customer].[Region Code].&[FRIE], [Customer].[Region Code].&[FLIN], [Customer].[Region Code].&[FLEV], [Customer].[Region Code].&[FL], [Customer].[Region Code].&[FIFE], [Customer].[Region Code].&[FFL], [Customer].[Region Code].&[FDSC], [Customer].[Region Code].&[EXTR], [Customer].[Region Code].&[ESX], [Customer].[Region Code].&[ESS], [Customer].[Region Code].&[ERY], [Customer].[Region Code].&[EMRM], [Customer].[Region Code].&[EFLN], [Customer].[Region Code].&[EDI], [Customer].[Region Code].&[DUR], [Customer].[Region Code].&[DUB], [Customer].[Region Code].&[DRNT], [Customer].[Region Code].&[DNGL], [Customer].[Region Code].&[DLHI], [Customer].[Region Code].&[DF], [Customer].[Region Code].&[DEV], [Customer].[Reg
ion Code].&[DER], [Customer].[Region Code].&[DE], [Customer].[Region Code].&[DC], [Customer].[Region Code].&[DADU], [Customer].[Region Code].&[CUM], [Customer].[Region Code].&[CTLN], [Customer].[Region Code].&[CT], [Customer].[Region Code].&[CSNG], [Customer].[Region Code].&[CRK], [Customer].[Region Code].&[COL], [Customer].[Region Code].&[COAH], [Customer].[Region Code].&, [Customer].[Region Code].&[CNTR], [Customer].[Region Code].&[CNDM], [Customer].[Region Code].&[CMPN], [Customer].[Region Code].&[CLAC], [Customer].[Region Code].&[CHIS], [Customer].[Region Code].&[CHIH], [Customer].[Region Code].&[CHE], [Customer].[Region Code].&[CERE], [Customer].[Region Code].&[CDF], [Customer].[Region Code].&[CBBO], [Customer].[Region Code].&[CARM], [Customer].[Region Code].&[CANT], [Customer].[Region Code].&[CAMB], [Customer].[Region Code].&[CAIT], [Customer].[Region Code].&[CA], [Customer].[Region Code].&[BVRA], [Customer].[Region Code].&[BUC], [Customer].[Region Code].&[BTWN], [Customer].[Region Code].&[BSQU], [
Customer].[Region Code].&[BSNM], [Customer].[Region Code].&[BSKR], [Customer].[Region Code].&[BRTG], [Customer].[Region Code].&[BRNE], [Customer].[Region Code].&[BRMN], [Customer].[Region Code].&[BRLN], [Customer].[Region Code].&[BRGS], [Customer].[Region Code].&[BRGN], [Customer].[Region Code].&[BRGA], [Customer].[Region Code].&[BRAB], [Customer].[Region Code].&[BNWG], [Customer].[Region Code].&[BIOB], [Customer].[Region Code].&[BIAL], [Customer].[Region Code].&[BG], [Customer].[Region Code].&[BER], [Customer].[Region Code].&[BEIJ], [Customer].[Region Code].&[BED], [Customer].[Region Code].&[BDPS], [Customer].[Region Code].&[BC], [Customer].[Region Code].&[BA], [Customer].[Region Code].&[AZNS], [Customer].[Region Code].&[AZ], [Customer].[Region Code].&[AYRE], [Customer].[Region Code].&[AVRG], [Customer].[Region Code].&[AVN], [Customer].[Region Code].&[AS], [Customer].[Region Code].&[ARMA], [Customer].[Region Code].&[ARHS], [Customer].[Region Code].&[ARGU], [Customer].[Region Code].&[AR], [Customer].[Region
Code].&[AQTN], [Customer].[Region Code].&, [Customer].[Region Code].&[AOR], [Customer].[Region Code].&[ANTW], [Customer].[Region Code].&[ANTR], [Customer].[Region Code].&[ANHI], [Customer].[Region Code].&[ANGU], [Customer].[Region Code].&[ANDL], [Customer].[Region Code].&[AND], [Customer].[Region Code].&[ALV], [Customer].[Region Code].&[ALSC], [Customer].[Region Code].&[AL], [Customer].[Region Code].&[AKSS], [Customer].[Region Code].&[AK], [Customer].[Region Code].&[AGS], [Customer].[Region Code].&[AGNN], [Customer].[Region Code].&[ADNA], [Customer].[Region Code].&[ABRZ], [Customer].[Region Code].&[ABE], [Customer].[Region Code].&[AB], [Customer].[Region Code].&[AAGD], [Customer].[Region Code].&[AAC], [Customer].[Region Code].&[AAB], [Customer].[Region Code].&[AA], [Customer].[Region Code].&[]}))), {[Measures].[Sales, (USD)], [Measures].[Sales, Volume (lbs)]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Apollo11Dev] WHERE ([Products].[P-Product Line].&[1,4-Butanediol], [Customer].[City].[Al
l], [Date].[Quarter].[All], [Date].[Year].[All])

I then went to run this query against the same cube, but did it as a MDX query in SQL Management Studio. My CPU instantly goes to 100% (nothing unusual), but this query seems to be taking a very long time. I then went to cancel the query in Management Studio, but that didn't seem to work. I then went into Activity Viewer, and tried to kill connections and sessions. I was able to kill the connection, but the CPU is still pegged at 100%. There is a session that I cannot kill, which I assume is the session for the above query?

It looks as if a restart is the only solution.

Any thoughts?|||

Hello,

now I'm using ActivityViewer for some time and I for most situations it works fine. But I also had couple of queries, which I simply couldn't kill. I had to wait until it was finished.

But you could also experiment with CoordinatorCancelCount, which says how often the engine would come to check if Cancel command wasn't issued. Read more about it at: http://msdn2.microsoft.com/en-us/library/ms174921.aspx

Radim

|||Unfortunately, that property (CoordinatorCancelCount) doesn't seem to be available as I don't see it.