Comparing two dimensions

Hi, I'm writing an MDX query in VS2003 (using RS2000) with AS2000 and I want to know how can I just display the mesaures for the members of a dimension that are equal to another dimension. For example, If I display dimension1 and dimension2 I get (not all of the members in both dimensions are equal):

Cat - Cat
Cat - Dog
Cat - Car
Cat - Horse
Dog - Cat
Dog - Dog
Dog - Car
Dog - Horse
Car- Cat
Car- Dog
Car- Car
Car- Horse
Horse - Cat
Horse - Dog
Horse - Car
Horse - Horse

But I really want to get:

Cat - Cat
Dog - Dog
Car - Car
Horse - Horse

I've tryed using FILTER and I can't make it work, need help here. When I try to use INTERSECT I get the following error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Calculation error - unknown error (urn:schemas-microsoft-com:xml-analysis)

BUTTONS:

OK

Filter is the right way to go. somethign like the following should work

Select Filter( {<dim1.hier1.level1>.Member * <dim2.hier2.level2>.Members}, <dim1.hier1.CurrentMember.Name = dim2.hier2.CurrentMember.Name ) on 0 from <cube>

Note that this is a bit of an unusual request and won't perform well on large dimensions as joins are normally built into the structure of the cube rather than done at run time.

|||It worked, thanks a lot man. I'm starting to learn MDX and it's quite amazing.

compare two string in SQL Server

Hi,
I am writing a Store Procedure for Login, as following:
@.p_sUsername, @.p_sPassword are parameters
...
SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
username = @.p_sUsername AND Passwort = @.p_sPassword
...
but the SELECT Statement can not differ Uppercase and Lowercase, that means,
"Martin" = "martin"
Then I check explicitly:
if @.BName != @.p_sUsername
Login = 0
but this comparing works the same.
What can I do?
Thanks
Martinif you SQL Server database is not case sensitive you have to convert to to a
comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS) Collatio
n
for it:
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Martin" wrote:

> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that mean
s,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>
>|||Case-sensitivity is determined by the column collation. So choose a
case-sensitive collation. For example:
ALTER TABLE benutzer
ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
David Portas
SQL Server MVP
--|||Thanks
Martin
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1125571037.540712.199690@.g43g2000cwa.googlegroups.com...
> Case-sensitivity is determined by the column collation. So choose a
> case-sensitive collation. For example:
> ALTER TABLE benutzer
> ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
> --
> David Portas
> SQL Server MVP
> --
>|||thanks
Martin
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
schrieb im Newsbeitrag
news:877CE479-F0DF-4CDA-8720-A14908BFB207@.microsoft.com...
> if you SQL Server database is not case sensitive you have to convert to to
a
> comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS)
Collation
> for it:
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Martin" wrote:
>
means,|||To add to the other responses, if you force a case-sensitive compare in your
SQL statement, consider also including the normal compare in your WHERE
clause. This will allow SQL Server to efficiently use indexes on those
columns and thereby improve performance.
SELECT
@.BName = B.username,
@.BPW = Password
FROM BENUTZER as B
WHERE
username COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sUsername AND
username = @.p_sUsername AND
password COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sPassword AND
Password = @.p_sPassword
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <martinsm@.freenet.de> wrote in message
news:Okeml6trFHA.304@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that
> means,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>

Compare Queries

I am creating an SP. I want to compare alternative ways of writing the query
to see which one is the most efficient. I am trying to use the execution
plan but it doesn't give an overall cost of the query so I can compare it to
the alternative query. Rather I get a break down of the various steps (this
query uses functions which produce aggregates and these steps are included
in the execution plan) . I want to explore the steps later, at this point I
just want to compare the two queries as a whole. What is the best way of
going about this? Regards, Chris.What I'd do to start is to fire up SQL Server Profiler, turn on the
SQL:BatchCompleted event, and collect the Reads, Writes, CPU, and Duration
columns. Run your queries and compare the output...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||The wording is a bit unclear. My SP could potentially contain a variety of
variables obtained from sub queries etc. This means there will be more than
just two queries to compare. How would you deal with a more complex scenario
with SP's that use temp tables or subqueries etc.
Regards, Chris.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Chris,
> Put both queries in the same query window and run them. Then the
> percentiles will reflect both queries. Therefore, if query1 and its
> functions use 25% and query2 and its functions use 75%, that suggests that
> query1 is more efficient. However, UDFs (and some other functions) do
> degrade the reliability of these numbers. Still, it is a good first take.
> However, running both queries repeatly and getting a set of actual
> execution times for each will, in the final analysis, be a more accurate
> measure of the queries.
> RLF
> "Chris" <nospam@.nospam.com> wrote in message
> news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>>I am creating an SP. I want to compare alternative ways of writing the
>>query to see which one is the most efficient. I am trying to use the
>>execution plan but it doesn't give an overall cost of the query so I can
>>compare it to the alternative query. Rather I get a break down of the
>>various steps (this query uses functions which produce aggregates and
>>these steps are included in the execution plan) . I want to explore the
>>steps later, at this point I just want to compare the two queries as a
>>whole. What is the best way of going about this? Regards, Chris.
>|||On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more than
> just two queries to compare. How would you deal with a more complex scenario
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
> > Chris,
> > Put both queries in the same query window and run them. Then the
> > percentiles will reflect both queries. Therefore, if query1 and its
> > functions use 25% and query2 and its functions use 75%, that suggests that
> > query1 is more efficient. However, UDFs (and some other functions) do
> > degrade the reliability of these numbers. Still, it is a good first take.
> > However, running both queries repeatly and getting a set of actual
> > execution times for each will, in the final analysis, be a more accurate
> > measure of the queries.
> > RLF
> > "Chris" <nos...@.nospam.com> wrote in message
> >news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
> >>I am creating an SP. I want to compare alternative ways of writing the
> >>query to see which one is the most efficient. I am trying to use the
> >>execution plan but it doesn't give an overall cost of the query so I can
> >>compare it to the alternative query. Rather I get a break down of the
> >>various steps (this query uses functions which produce aggregates and
> >>these steps are included in the execution plan) . I want to explore the
> >>steps later, at this point I just want to compare the two queries as a
> >>whole. What is the best way of going about this? Regards, Chris.- Hide quoted text -
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Compare Queries

I am creating an SP. I want to compare alternative ways of writing the query
to see which one is the most efficient. I am trying to use the execution
plan but it doesn't give an overall cost of the query so I can compare it to
the alternative query. Rather I get a break down of the various steps (this
query uses functions which produce aggregates and these steps are included
in the execution plan) . I want to explore the steps later, at this point I
just want to compare the two queries as a whole. What is the best way of
going about this? Regards, Chris.What I'd do to start is to fire up SQL Server Profiler, turn on the
SQL:BatchCompleted event, and collect the Reads, Writes, CPU, and Duration
columns. Run your queries and compare the output...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||The wording is a bit unclear. My SP could potentially contain a variety of
variables obtained from sub queries etc. This means there will be more than
just two queries to compare. How would you deal with a more complex scenario
with SP's that use temp tables or subqueries etc.
Regards, Chris.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Chris,
> Put both queries in the same query window and run them. Then the
> percentiles will reflect both queries. Therefore, if query1 and its
> functions use 25% and query2 and its functions use 75%, that suggests that
> query1 is more efficient. However, UDFs (and some other functions) do
> degrade the reliability of these numbers. Still, it is a good first take.
> However, running both queries repeatly and getting a set of actual
> execution times for each will, in the final analysis, be a more accurate
> measure of the queries.
> RLF
> "Chris" <nospam@.nospam.com> wrote in message
> news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>|||On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more tha
n
> just two queries to compare. How would you deal with a more complex scenar
io
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
>
>
>
>
>
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Command Type for dataset using Custom Data Processing Extension

Hi,
I'm writing a custom data processing extension, but I cannot get
anything other than a command type of "Text" in the dataset for the
report I have setup to use the Data extension I have written.
Does anyone know what or where the available command types are derived
from?
GregI still haven't had any luck resolving this, hoping someone can help :)

command line uninstall of sql express 2005

Another developer is writing the install/uninstall for our app and we're not
sure about the best way to uninstall the database component when the app is
uninstalled. Basically our app installs sql express 2005 ( + other .net stuff
) during install using the command line options and that works great. When it
comes to uninstall we have only one option at the moment - have the user go
to the control panel and manually uninstall the db. Is there an automated way
to remove the db from a machine or a way to issue a batchfile command w/
params? The user doesn't need to maintain this special db instance so want to
remove it automatically.
thanks,
Paul.
hi Paul,
paul wrote:
> Another developer is writing the install/uninstall for our app and
> we're not sure about the best way to uninstall the database component
> when the app is uninstalled. Basically our app installs sql express
> 2005 ( + other .net stuff ) during install using the command line
> options and that works great. When it comes to uninstall we have only
> one option at the moment - have the user go to the control panel and
> manually uninstall the db. Is there an automated way to remove the db
> from a machine or a way to issue a batchfile command w/ params? The
> user doesn't need to maintain this special db instance so want to
> remove it automatically.
> thanks,
> Paul.
http://msdn2.microsoft.com/en-us/lib...installscripts
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply