Memory Again.

It's always about the memory.
I have win 2000 advance server, SQL Server 2000 EE, 4G memory, not other
application run on this server. How can I find out the exact memory usage for
the SQL server. I guess the task manager will not show the correct number, so
I used the performance monitor, but not sure which one show the right number.
Anybody can help? Thanks a lot.Please refer to this http://support.microsoft.com/Default.aspx?id=271624
Thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:C4D15CC5-A22E-41DC-B92C-DF070CC8CB56@.microsoft.com...
> It's always about the memory.
> I have win 2000 advance server, SQL Server 2000 EE, 4G memory, not other
> application run on this server. How can I find out the exact memory usage
> for
> the SQL server. I guess the task manager will not show the correct number,
> so
> I used the performance monitor, but not sure which one show the right
> number.
> Anybody can help? Thanks a lot.

Memo field problem using Access97 with SQL2000 Backend

I have an application written in Access 97 that connects to a SQL2000
backend. One field is a description field that is a data type NTEXT in the
SQL database. In my access form, I can not enter more than 255 characters.

Before I converted the backend to SQL, the description field was a memo
field in Access.

What do I need to do to make it so I can enter more text into this field?NChar, NVarChar, and NText are double-byte unicode types. For
example, nchar(10) will hold 10 bytes of data but takes 20 bytes of
space. Convert the field's datatype to Text and you should be fine.

Look up "data types-SQL Server, described" in Books Online for more
info.

"Bob" <bobh@.wolv.tds.net> wrote in message news:<vta8f56bkredfd@.corp.supernews.com>...
> I have an application written in Access 97 that connects to a SQL2000
> backend. One field is a description field that is a data type NTEXT in the
> SQL database. In my access form, I can not enter more than 255 characters.
> Before I converted the backend to SQL, the description field was a memo
> field in Access.
> What do I need to do to make it so I can enter more text into this field?

Membership GetAllUsers SP

Hello,

I am using the Membership application for my project. In the database (MS SQL 2005) the program created alot of SPs for the Membership, Roles, Profiles ...

Is there a function to return a list of users that are online? There is this functionaspnet_Membership_GetNumberOfUsersOnline andaspnet_Membership_GetAllUsers.

What i want is a Table with the usernames of all the users that are online in my application. No page index, just a big table with all the online users. I need to get all the usernames for another SP that i just build.

Any ideas?

To my knowledge there is not, but this would be a good one to add to the library. Realize that this would be a method that would be specific to the SQL or Custom Membership provider because AD would not be able to support this.

|||

I tried this, but i cant get it to return any rows, even tho atleast 4 people is logged in.

CREATEPROCEDURE [dbo].[aspnet_Membership_GetAllOnlineUsers]
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint,
@.CurrentTimeUtcdatetime
AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), @.CurrentTimeUtc)

CREATETABLE #PageIndexForUsersNames
(
Usernamenvarchar(20)NOTNULL
)

INSERTINTO #PageIndexForUsersNames(Username)
SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
SELECT UsernameFROM #PageIndexForUsersNames
END

And then i tried to run the following:

EXEC aspnet_Membership_GetAllOnlineUsers
@.ApplicationName= N'//',
@.MinutesSinceLastInActive= 10,
@.CurrentTimeUtc='2007-11-15 18:45'

The applicationname did i take from the applications table.

Any ideas?

|||

Sorry that was the old, this is what i am trying:

aspnet_Membership_GetAllOnlineUsers
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint,
@.CurrentTimeUtcdatetime
AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), @.CurrentTimeUtc)

SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
END

|||

You've specified a UTC time that is still in the future.

@.CurrentTimeUtc='2007-11-15 18:45' won't be for another 30 minutes.

|||

To be honest, I'm still not sure why the ASP.NET team decided to do it that way, it seems against best practices to have the client application trying to send in times when the window we're looking for is normally minutes, especially when it's extremely easy to make sure that we always use a single clock for reference:

aspnet_Membership_GetAllOnlineUsers
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint

AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), GetUtcDate())

SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
END

Measureware for SQL server 2000 - any experiences?

Hi everyone,
Hope you can help me here please. We are going to stress test a VB.NET
coded application (a web service to be exact) that pulls from a SQL
server 2000 database.
I am looking for some nice free measureware that we can install easily
on the Server that will show some nice graphs on things like:
1. CPU usage
2. Memory usage
3. Read/Writes etc
4. etc/etc...
Does anyone out there have any nice
recommendations/ideas/suggestions/user experiences that thay would like
to share?
Any comments - greatly welcomed.
Cheers,
Al.
> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
The Windows perfmon utility provides these metrics and much more and is
included free with the OS. Is there something lacking?
Hope this helps.
Dan Guzman
SQL Server MVP
<almurph@.altavista.com> wrote in message
news:1140699676.730010.306530@.u72g2000cwu.googlegr oups.com...
> Hi everyone,
> Hope you can help me here please. We are going to stress test a VB.NET
> coded application (a web service to be exact) that pulls from a SQL
> server 2000 database.
> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
>
> Does anyone out there have any nice
> recommendations/ideas/suggestions/user experiences that thay would like
> to share?
> Any comments - greatly welcomed.
> Cheers,
> Al.
>

Measureware for SQL server 2000 - any experiences?

Hi everyone,
Hope you can help me here please. We are going to stress test a VB.NET
coded application (a web service to be exact) that pulls from a SQL
server 2000 database.
I am looking for some nice free measureware that we can install easily
on the Server that will show some nice graphs on things like:
1. CPU usage
2. Memory usage
3. Read/Writes etc
4. etc/etc...
Does anyone out there have any nice
recommendations/ideas/suggestions/user experiences that thay would like
to share?
Any comments - greatly welcomed.
Cheers,
Al.> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
The Windows perfmon utility provides these metrics and much more and is
included free with the OS. Is there something lacking?
Hope this helps.
Dan Guzman
SQL Server MVP
<almurph@.altavista.com> wrote in message
news:1140699676.730010.306530@.u72g2000cwu.googlegroups.com...
> Hi everyone,
> Hope you can help me here please. We are going to stress test a VB.NET
> coded application (a web service to be exact) that pulls from a SQL
> server 2000 database.
> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
>
> Does anyone out there have any nice
> recommendations/ideas/suggestions/user experiences that thay would like
> to share?
> Any comments - greatly welcomed.
> Cheers,
> Al.
>

Measureware for SQL server 2000 - any experiences?

Hi everyone,
Hope you can help me here please. We are going to stress test a VB.NET
coded application (a web service to be exact) that pulls from a SQL
server 2000 database.
I am looking for some nice free measureware that we can install easily
on the Server that will show some nice graphs on things like:
1. CPU usage
2. Memory usage
3. Read/Writes etc
4. etc/etc...
Does anyone out there have any nice
recommendations/ideas/suggestions/user experiences that thay would like
to share?
Any comments - greatly welcomed.
Cheers,
Al.> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
The Windows perfmon utility provides these metrics and much more and is
included free with the OS. Is there something lacking?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<almurph@.altavista.com> wrote in message
news:1140699676.730010.306530@.u72g2000cwu.googlegroups.com...
> Hi everyone,
> Hope you can help me here please. We are going to stress test a VB.NET
> coded application (a web service to be exact) that pulls from a SQL
> server 2000 database.
> I am looking for some nice free measureware that we can install easily
> on the Server that will show some nice graphs on things like:
> 1. CPU usage
> 2. Memory usage
> 3. Read/Writes etc
> 4. etc/etc...
>
> Does anyone out there have any nice
> recommendations/ideas/suggestions/user experiences that thay would like
> to share?
> Any comments - greatly welcomed.
> Cheers,
> Al.
>

ME TOO

HEY - I'm having that same problem too!

However im not linking Access to SQL 2005 rather I'm connecting a VB6 COM+ application to SQL 2005. This application used to run very fast under SQL 2000 however since the upgrade to 2005 we are experiencing some serious connection problems. We are using MDAC 2.6. Can anyone suggest a solution to this problem?

Hope someone can help

Nick Graham

What problem?

MdxScript error using role security

The following error is displayed when trying to open a cube...

"An error occurred in the application: MdxScript(Quota Sales)(30,24) Then dimension '[Sales]' was not found in the cube when string, [Sales], was parsed"

I am using role security and am trying to hide a base measure [Measures].[Sales] along with two calculated measures that use the base measure. The cube does not have a [Sales] dimension.

I am using the Dimension Data tab within the role designer and denying all measures then selecting only the ones I want to appear in the cube. Those are written automatically into the "allowed member set" panel on the advanced tab. I have also tried allowing all members and then unchecking the Sales measure and specifying the [Measures].[Sales] measure and the two calculated measures in the "denied member set" panel on the advanced tab. I get the error either way.

I am using SSAS 2k5 and I receive the error when trying to open the cube using the ProClarity Professional client, although I don't think ProClarity is the issue.

Thanks in advance for any help.
Jay Hotchkiss

I've heard of some problems with security and MDX Scripts, although I can't find the relevant link at the moment...

...but, can you post the section of your MDX Script that's causing the error, ie everything around line 30? Is it by any chance something (like a calculated member definition, or named set) which refers to the Sales measure as
[Sales]
rather than using the full unique name of
[Measures].[Sales]
? If so, can you try using the full unique name in the MDX Script and seeing if you still get the same error?

Chris

|||

So that's what MDXScript is referring to, the calculated measure script! Yes, I have a number of calculations (time intellligence stuff) that are checking the Sales measure for NonEmpty behavior. I missed those. I'll do some work on the script and reply back here with the results. I think I can check another measure for NonEmpty instead of having to list all those calculated measure in the "denied set" for the role's security. Thanks very much. Jay

|||Thanks Christopher, I had a few calculated sets that were referring to my Sales measure. I appreciate the help. Jay|||For each dimension, you can set the MdxMissingMemberMode property in SSAS 2005. Perhaps this could solve your problem.

MdxScript error using role security

The following error is displayed when trying to open a cube...

"An error occurred in the application: MdxScript(Quota Sales)(30,24) Then dimension '[Sales]' was not found in the cube when string, [Sales], was parsed"

I am using role security and am trying to hide a base measure [Measures].[Sales] along with two calculated measures that use the base measure. The cube does not have a [Sales] dimension.

I am using the Dimension Data tab within the role designer and denying all measures then selecting only the ones I want to appear in the cube. Those are written automatically into the "allowed member set" panel on the advanced tab. I have also tried allowing all members and then unchecking the Sales measure and specifying the [Measures].[Sales] measure and the two calculated measures in the "denied member set" panel on the advanced tab. I get the error either way.

I am using SSAS 2k5 and I receive the error when trying to open the cube using the ProClarity Professional client, although I don't think ProClarity is the issue.

Thanks in advance for any help.
Jay Hotchkiss

I've heard of some problems with security and MDX Scripts, although I can't find the relevant link at the moment...

...but, can you post the section of your MDX Script that's causing the error, ie everything around line 30? Is it by any chance something (like a calculated member definition, or named set) which refers to the Sales measure as
[Sales]
rather than using the full unique name of
[Measures].[Sales]
? If so, can you try using the full unique name in the MDX Script and seeing if you still get the same error?

Chris

|||

So that's what MDXScript is referring to, the calculated measure script! Yes, I have a number of calculations (time intellligence stuff) that are checking the Sales measure for NonEmpty behavior. I missed those. I'll do some work on the script and reply back here with the results. I think I can check another measure for NonEmpty instead of having to list all those calculated measure in the "denied set" for the role's security. Thanks very much. Jay

|||Thanks Christopher, I had a few calculated sets that were referring to my Sales measure. I appreciate the help. Jay|||For each dimension, you can set the MdxMissingMemberMode property in SSAS 2005. Perhaps this could solve your problem.

MDX: Null value to replace column in Query

I have an MDX query that is not doing what I want it to do.

Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:

i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL

Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL

I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!

My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,

NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])

) ON 1
FROM Mimir04

But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned

What exactly is the solution to returning an empty column?

Could you please clarify whether you'd like empty rows or empty columns?

Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?

Thank you

MDX Sample Application...

Does anyone know where you can download the MDX sample application from?

if you are running Analysis Services 2005, SQL Server Management Studio is the new query tool.

You can connect to Analysis Services 2005 and write MDX queries here.

HTH

Thomas Ivarsson

|||

As far as I know, MDX Sample is not available as a public download. It’s sample code that ships with our Analysis Services 2000 product.

--Artur

|||

Hi Artur

The SQL Server 2000 Samples (with MDX sample application) are still free to download

http://www.microsoft.com/downloads/details.aspx?familyid=7824ba50-3e29-45cf-8c02-5597c014a707&displaylang=en

One of the great benefits of the MDX sample application is a possibility to set connection string options.

it is impossible In SqlWb :-(

MDX query performance is poor

We have an application that queries an SSAS 2005 cube to do fast multi-dimensional calculations about customers for a scoring application. The cube has 10 dimensions and five measures. All of the dimensions are low cardinality except for the customer one.

In short, the application loops through the customers and does the calculations it needs. The application processes 50,000 customers at a time (i.e., the customer dimension has 50,000 members in it). The typical MDX query includes the customer dimension, one or two other dimensions, and a couple of the measures.

The queries are executed in VB using an ExecuteCellset, but the same performance issues are found when we copy the MDX SELECT into a query window and run it.

The queries take several minutes each to run, and we need to make it faster. We have tried user based optimizations, aggregations, no aggregations, running queries in parallel, different numbers of customers in the cube, etc. with little or no improvement. Also, the application was significantly faster in SSAS 2000.

The problem appears to be related to caching, since the process page faults quite a bit. Since we are iterating through the data, and never hitting the same cell twice, caching does not really help us at all. We are looking at converting the cellset to a data reader to eliminate the bulk of the caching, but would rather not have to go that direction since it is a major rewrite.

Any ideas about how to speed this up would be greatly appreciated. It just does not seem reasonable that a query with 3 axes against a relatively small cube should take several minutes to run.

There are quite a few things affecting query performance and I am not sure I can go around talking about all of them.

In your case you should look at serveral things.

One. Where is the bottleneck for your queries? Is your system spending majority of time reading data from the disc to answer every individual query? If this the case, you should indentify the granularity your query operates on and build exact aggregation that is going to help answer the query.

If every query causes AS to read data on the lowest level of granularity you can also take a look at partitioning your data into several partitions.

If much of the time spent calculating complex MDX, you might take look at better way to write a query.

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