multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.sql

multi-select MDX issue from a client tool

Hi

This is the sample query which was getting generated by the client tool :
with member [Destination].[Destination].[Sum] as 'Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]})'
select [Measures].[Value] on columns,
nonempty([Time].[Calendar].allmembers) on rows
from [Consolidation Prototype]
where ([Destination].[Destination].[Sum]

The above query was failing in SSAS. It was failing because of the implicit use of current member somwhere.

The error was

The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set.

Mosha has written a few blogs over the network explaining the reason for the problem. My guess is that, although, I am not using the currentmember across Destination dimension explicitly in the above query, it is getting used in the MDX script while evaluating the values of the measure [Measures].[Value] .

So while evaluating the currentmember for Destination dimension, its finding 2 current values and fails, because SSAS cannot handle this scenario.

Now the interesting part :

If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember)

Would anyone be able to explain to me the reason for this kind of behaviour?

ZA

"If I replace the slicer clause with this one ({[Destination].[Destination].[Sum]}) i.e just enclosing the calculated member with {}, it works fine.

There is one more solution as well :
If I replace the aggregate statement with the following:

Aggregate({[Destination].[Destination].&[12],[Destination].[Destination].&[3]},[Time].[Calendar].currentmember) "

Just a guess, but the above tweaks may be enough to stop the replacement that Mosha describes in his blog :

Writing multiselect friendly MDX calculations

...

AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

|||I thought so. Thanks anyways, Deepak.

Multiple value parameters in SQL Server 2000 Reporting Services.

Hi all,

I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.

Thank you,

Hi,

Unfortunatelly, there is no official way to push multipy values fo parmeters in SSRS 2000, however, you can hardcode a list with multiply values behind eg: Sales Users as text and "Jim; Bob " ...etc as values and reuse them in the procedure or construct your t-sql string based on parameter with sp_executesql. There is a security risk with the last option.

Regards,

Janos

|||Thanks a lot Janos. I'll see what I can do. Maybe I can convince the client to go to SSRS 2005?

Let's hope.

Thanks anyway,

Multiple SQL Server Native Client Versions

One of the big problems with the old MDAC was different versions on different client machines. You would test your app with the latest version say, but when you deployed it, it might fail as the client has a different version.

My company develops software for Municipal Government clients. These clients use other SQL Server applications as well as ours, but they can only use one version of the client software (MDAC) on a given desktop. That means if we require a particular MDAC version, but the clients other applications from other vendors don't officially support that MDAC version, the client is in a real jam.

Our software also supports Oracle, which allows our software to specify a particular Oracle Home which points to a directory with a particular version of the Oracle client dlls (along with corresponding registry entries for that Oracle Home), such that we control the exact client version of the Oracle software that the client uses with our applications. This will not interfere with, and is completely seperate, from the default Oracle home installed when you install the Oracle client software.

What I would like to see for the Native Client is the ability to have our applications use the version of the Native Client that we wish to support and deploy without interfering with the Native Client version used by other applications. Have a default Native Client, but allow applications to somehow specify a different Native Client version/set of DLLs.

Is there any such functionality with the Native Client? (I didn't see any mention of such in the documentation, but I thought I'd ask)

If not, are there any future plans to support multiple Native Client versions on the same desktop?

At present, there is only one version of the SQL Server Native Client, so the particular issue you've raised isn't technically a concern at this point (which may explain why you didn't find any obvious discussion of the topic).

In the SQL Server Native Client blog, Chris and/or Acey discuss the history of issues that were encountered with multiple versions of MDAC and specifically discuss the issue you've brought up above (the blog posting is at: http://blogs.msdn.com/sqlnativeclient/archive/2006/09/27/774286.aspx ). They go on to specifically mention:

By wrapping the OLE DB and ODBC technologies into a single library, we are able to avoid these issues by making a clean break from MDAC so that you can effectively deploy SQL Native Client as needed, without concern about if it will “play nicely” with other versions of MDAC.

So, for you app today, you can build against the SQL Server Native Client and deploy that application (including the SQL Server Native Client redistributable) to your clients without breaking existing MDAC based applications.

As to the future, making this clean break only to commit to the same course of action with the new SQL Server Native Client would be less than ideal and we are actively considering how best to support this need in the next release that will come with future versions of Microsoft SQL Server. If you have specific scenarios or issues you would like considered, we would very much like to hear your ideas.

Multiple Servers in ODBC Client Setup

OK, something weird is happening.
I have a standard Windows XP client and I am trying to set up a simple ODBC
entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
there I create a new ODBC entry connecting to a SQL Server. I enter in the
name of my ODBC and a descripton. When I hit the drop-down button to select
a server I see my 2 SQL Server machines (local machine also named "Jim" and
sbserver) but they are listed twice or even 3 times (see below) and the local
machine has even more entries. What gives and how do I clean this up? Both
seem to work when I "test" at the last step but I have a local application
using this ODBC and it is failing to run so I think it is having "issues"
with these multiple entries.
Thanks!
-Richard
e.g.
(local)
(local)
Jim
Jim
jim
sbserver
sbserver
Make sure your MDAC installation isn't flaky - you can use
component checker to verity the MDAC version and
installation. You can download the tool from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx
How many instances do you have on each of those servers?
Do you get the same list if you execute the following from
the command prompt:
osql -L
-Sue
On Fri, 16 Mar 2007 04:47:05 -0700, Richard K
<RichardK@.discussions.microsoft.com> wrote:

>OK, something weird is happening.
>I have a standard Windows XP client and I am trying to set up a simple ODBC
>entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
>there I create a new ODBC entry connecting to a SQL Server. I enter in the
>name of my ODBC and a descripton. When I hit the drop-down button to select
>a server I see my 2 SQL Server machines (local machine also named "Jim" and
>sbserver) but they are listed twice or even 3 times (see below) and the local
>machine has even more entries. What gives and how do I clean this up? Both
>seem to work when I "test" at the last step but I have a local application
>using this ODBC and it is failing to run so I think it is having "issues"
>with these multiple entries.
>Thanks!
>-Richard
>e.g.
>(local)
>(local)
>Jim
>Jim
>jim
>sbserver
>sbserver

Multiple server connections with XP client to SQL server using Great Plains

We have 2 XP clients using Great Plains client connecting
to a Small Business Server running SQL 2000. When these
users start up Great Plains they each are using multiple
server connections/sessions which is eating up our
limited licenses. Our Windows 2000 users only use one
connection but the two XP users are taking up 4 and 7
licenses each. I'm not sure if it is an XP, Great
Plains, SQL or Small Business Server issue.
.SQL server cals are machine based, a single machine with
sql cal can open multiple connections to a sql server and
it can also connect to multiple sql servers at the same
time.
Are you getting an error for exceeding licenses ?
Use sp_who command in sql query analyzer to find out how
many connections are open.
>--Original Message--
>We have 2 XP clients using Great Plains client connecting
>to a Small Business Server running SQL 2000. When these
>users start up Great Plains they each are using multiple
>server connections/sessions which is eating up our
>limited licenses. Our Windows 2000 users only use one
>connection but the two XP users are taking up 4 and 7
>licenses each. I'm not sure if it is an XP, Great
>Plains, SQL or Small Business Server issue.
>..
>
>.
>|||We've got a 25 user Small Business Server CAL. As soon
as we get 25 connections (as seen through a NETSTAT
command) the server refuses to allow anymore. We
discovered that the two machines that are creating
multiple connections are XP and it appears to be ODBC
related.
>--Original Message--
>SQL server cals are machine based, a single machine with
>sql cal can open multiple connections to a sql server
and
>it can also connect to multiple sql servers at the same
>time.
>Are you getting an error for exceeding licenses ?
>Use sp_who command in sql query analyzer to find out how
>many connections are open.
>>--Original Message--
>>We have 2 XP clients using Great Plains client
connecting
>>to a Small Business Server running SQL 2000. When
these
>>users start up Great Plains they each are using
multiple
>>server connections/sessions which is eating up our
>>limited licenses. Our Windows 2000 users only use one
>>connection but the two XP users are taking up 4 and 7
>>licenses each. I'm not sure if it is an XP, Great
>>Plains, SQL or Small Business Server issue.
>>..
>>
>>.
>.
>

Multiple SA Sessions

As a DBA, I was just handed a SQL Server to support where the client has
install an application and all user connections are logged in as 'sa'. Does
anybody know what priority 'sa' has, would this be a huge contention problem
and would this be one of my major sources for blocking processes? There are
about 20,000 updates per day and active queries being run simultaniously.
This is SQL Server 2000 Standard Ed on windows 2000.
I am looking for any documents or whitepapers to convince client this has to
change - comments and suggestions are welcome.The login doesn't influence priorities regarding resource usage, blocking et
c. There are many
reasons why you shouldn't be using sa, but blocking problems is not one of t
hem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBAJimbo" <DBAJimbo@.discussions.microsoft.com> wrote in message
news:8540E787-D825-4931-B2F1-CAF449744D96@.microsoft.com...
> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Do
es
> anybody know what priority 'sa' has, would this be a huge contention probl
em
> and would this be one of my major sources for blocking processes? There a
re
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has
to
> change - comments and suggestions are welcome.|||As the sa account can do anything on the server, this setup raises all sorts
of security issues. Another account should be created and used, which has
only the access it needs.
Alternatively, if your users can all authenticate using their windows domain
accounts, you can create windows domain groups that you can give certain
rights to, and add users to the appropriate groups. This gives much better
control over who can do/access what on the server.
Regards
Adam
"DBAJimbo" wrote:

> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Do
es
> anybody know what priority 'sa' has, would this be a huge contention probl
em
> and would this be one of my major sources for blocking processes? There a
re
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has
to
> change - comments and suggestions are welcome.

Multiple SA Sessions

As a DBA, I was just handed a SQL Server to support where the client has
install an application and all user connections are logged in as 'sa'. Does
anybody know what priority 'sa' has, would this be a huge contention problem
and would this be one of my major sources for blocking processes? There are
about 20,000 updates per day and active queries being run simultaniously.
This is SQL Server 2000 Standard Ed on windows 2000.
I am looking for any documents or whitepapers to convince client this has to
change - comments and suggestions are welcome.The login doesn't influence priorities regarding resource usage, blocking etc. There are many
reasons why you shouldn't be using sa, but blocking problems is not one of them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBAJimbo" <DBAJimbo@.discussions.microsoft.com> wrote in message
news:8540E787-D825-4931-B2F1-CAF449744D96@.microsoft.com...
> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Does
> anybody know what priority 'sa' has, would this be a huge contention problem
> and would this be one of my major sources for blocking processes? There are
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has to
> change - comments and suggestions are welcome.|||As the sa account can do anything on the server, this setup raises all sorts
of security issues. Another account should be created and used, which has
only the access it needs.
Alternatively, if your users can all authenticate using their windows domain
accounts, you can create windows domain groups that you can give certain
rights to, and add users to the appropriate groups. This gives much better
control over who can do/access what on the server.
Regards
Adam
"DBAJimbo" wrote:
> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Does
> anybody know what priority 'sa' has, would this be a huge contention problem
> and would this be one of my major sources for blocking processes? There are
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has to
> change - comments and suggestions are welcome.

Multiple SA Sessions

As a DBA, I was just handed a SQL Server to support where the client has
install an application and all user connections are logged in as 'sa'. Does
anybody know what priority 'sa' has, would this be a huge contention problem
and would this be one of my major sources for blocking processes? There are
about 20,000 updates per day and active queries being run simultaniously.
This is SQL Server 2000 Standard Ed on windows 2000.
I am looking for any documents or whitepapers to convince client this has to
change - comments and suggestions are welcome.
The login doesn't influence priorities regarding resource usage, blocking etc. There are many
reasons why you shouldn't be using sa, but blocking problems is not one of them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBAJimbo" <DBAJimbo@.discussions.microsoft.com> wrote in message
news:8540E787-D825-4931-B2F1-CAF449744D96@.microsoft.com...
> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Does
> anybody know what priority 'sa' has, would this be a huge contention problem
> and would this be one of my major sources for blocking processes? There are
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has to
> change - comments and suggestions are welcome.
|||As the sa account can do anything on the server, this setup raises all sorts
of security issues. Another account should be created and used, which has
only the access it needs.
Alternatively, if your users can all authenticate using their windows domain
accounts, you can create windows domain groups that you can give certain
rights to, and add users to the appropriate groups. This gives much better
control over who can do/access what on the server.
Regards
Adam
"DBAJimbo" wrote:

> As a DBA, I was just handed a SQL Server to support where the client has
> install an application and all user connections are logged in as 'sa'. Does
> anybody know what priority 'sa' has, would this be a huge contention problem
> and would this be one of my major sources for blocking processes? There are
> about 20,000 updates per day and active queries being run simultaniously.
> This is SQL Server 2000 Standard Ed on windows 2000.
> I am looking for any documents or whitepapers to convince client this has to
> change - comments and suggestions are welcome.