Means to access SQL w/o EM

Hey folks,

Here's my situation. My web host has enabled SQL 2000 for my account but has left it to me to connect to it. I don't have the money to spend to buy the SQL Server 2000 software myself so I can get Enterprise MAnager. Is there some way, perhaps open source, that would allow me to interface even through a command line with SQL? I am a bred and bled MySQL man but I am trying to get a hang of SQL so I will be more adequetly prepared for a potential job I may have in the near future.

SketchI know that you can download the trial version of SQL Server 2000
Trial Version (http://www.microsoft.com/sql/evaluation/trial/2000/default.asp)
You only need the client tools, so just install them. The trial version is limited to a 120 days, but I wonder if that is on the Server/Database? You could try and install the client tools only.

I've downloaded and installed this product, because it works with all ADO supported RDMS.
Query Tool (using ADO) (http://gpoulose.home.att.net/)|||Yes, 120 days trial version of SQL Server might be your answer for now, however keep in mind, that you need server type of O/S (2000 Server or NT Server) to install instance of SQL Server itself.
You can download it or buy it on CD with many SQL Server books and then just download SQL S. Service Pack 2 from http://www.microsoft.com/downloads/search.asp?

Workstation such as Win 2000 Pro allows for client tools only and yes, you get Enterprise Manager with it.
With this option you need permission from the actuall SQL Server to connect to it.

means of tracing query plans

Hi,
aside from maybe using profiler, is there another means of displaying the qu
ery plans of procs being executed on the server over a period of time. ie. i
f a proc is being recompiled during execution is there a way for me to see t
he query plan of that proc
for each execution?
thanks.>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

means of tracing query plans

Hi
aside from maybe using profiler, is there another means of displaying the query plans of procs being executed on the server over a period of time. ie. if a proc is being recompiled during execution is there a way for me to see the query plan of that proc for each execution
thanks.>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

means of tracing query plans

Hi,
aside from maybe using profiler, is there another means of displaying the query plans of procs being executed on the server over a period of time. ie. if a proc is being recompiled during execution is there a way for me to see the query plan of that proc
for each execution?
thanks.
>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.
|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

MDX to locate data on a higher aggregation from a lower aggregation

I think I have a strange problem with a cube. The problem does come from the source data provider, but since there are no means to change that, I have to live with it and make the best of it.

Let me try to describe my situation: I gather data on two levels, two different grades of aggregation (for me: Groups and individual lines). On the higher level, I see an exact amount of time (<-my target), on the finer one I only have a sum of my target value and another time span. I also have counts of members on each level.

What I need to do is "split up" the sum value I have on the fine grain level according to distribution on the higher granularity level.

Does MDX provide a way to "look up" one aggregation level from a cell and use that value as a base for calculation? what would be the typical commands to step up along one aggregation line and request a value from there?

This is a more detailed explanation of the situation in case you are interested:

I have two measures on a group level, like this:

Group 1 50 (a) 1000 (b)

Group 2 some numbers as well

The 50 (a) means 50 seconds average worktime that happened by 1000 (b) jobs that were handled in the group.

Each group consists of 2 to 5 lines. For each line I also have a counting of jobs and a time, but this time is a total time, consisting of a waiting period and the worktime, like this:

Group 1

Line 1 80 750

Line 2 120 250

So, what that means is Group 1 handled 1000 jobs with 50 seconds average worktime, and that resulted out of 750 jobs handled in line 1 with an average (waiting + working time) of 80 seconds and 250 jobs that had 120 seconds work and wait time.

What I need to do is find out how long the work time was in each line, how the 80 seconds distributes between work and wait time. The only way to do that is to estimate the the waiting time in both lines is equal (in my real project it really is, or I do estimate it to be equal as there is not reason not to think so)

If you resolve this little riddle, you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120.

Hi Ralf_From_Europe:

Perhaps I'm simplifying this too much. If I am simplifying too much please correct me and I can look at the problem again. I think you can get the value you want by using the .PARENT function. You can reference the <dim>.<hierarchy>.CURRENTMEMBER.PARENT and get the value from the group level for your calculation. To illustrate I included an MDX query in this reply which references the CurrentMember.Parent sales value, shows the value, and uses it in a calculation. The sample works against the AdventureWorks DW sample from Microsoft.

Hope this helps.

PaulG

WITH MEMBER Measures.ProductParentSales AS
'
([Product].[Product Categories].CurrentMember.Parent, [Measures].[Internet Sales Amount])
'

MEMBER Measures.MyNewCalc AS
'
Measures.ProductParentSales/[Measures].[Internet Sales Amount]
', FORMAT_STRING = "#,0.00"

SELECT
{[Measures].[Internet Sales Amount], Measures.ProductParentSales, Measures.MyNewCalc} ON COLUMNS,
{[Product].[Product Categories].[Subcategory].[Bottles and Cages]
,[Product].[Product Categories].[Subcategory].[Bottles and Cages].Children
,[Product].[Product Categories].[Subcategory].[Helmets]
,[Product].[Product Categories].[Subcategory].[Helmets].Children} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003])

|||

"..you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120". Shouldn't it be 80, not 120?

Anyway, if you look at the Adventure Works MDX Calculation Script section for [Measures].[Sales Amount Quota], you'll find allocations like:

/*-- Allocate equally to months in FY 2002 --*/

Scope

(

[Date].[Fiscal Year].&[2002],

[Date].[Fiscal].[Month].Members

);

This = [Date].[Fiscal].CurrentMember.Parent / 3;

End Scope;

The Sales Targets Measure Group relates to Date at the Calendar Quarter granularity, so monthly [Measures].[Sales Amount Quota] aren't directly loaded from fact data. Instead, they are allocated - is this along the lines of what you want to do?

|||

Thanks both of you for your responses. I think I do understand a bit better now, I think I understand what the MDX parts in each answer is trying to do, but... it turns out I do not know how to "use" MDX in my case, I do not know where to "put it" in my SSAS project. There is measured columns, unary operators, semiadditive calculations and finally custom rollups. I think I need to do a lot of reading in the "advanced design" chapters of my books.

It seems I left out a crucial detail in my first description as well, the data on the two different levels is in different tables and therefore in different measuregroups. I hope that does not mean this makes it impossible. The book I try to learn from does work with adventure works as well and uses several measure groups at once, so I dare say it is possible.

But if I may, let me take out the first step of what I need to and see if someone can help me with it. This is not really different from the original question, it is only the very first step to calculate an intermediate result needed later to solve the "real wanted" value. Say my cube looks like this:

Group Line Counter Value

G1 L1 750 80 750x 80=60,000

G1 L2 250 120 250x120=30,000

G1Total 1000 200 90,000 / 1,000 = 90

The 200 marked in red is just summed up, which is wrong. The correct calculation is shown on the right, and the result is 90. In words you would say: For all children (L1, L2) of a Group (G1) sum up all the products of counter x value and divide by the sum of counter.

Now: Can that be put into MDX? And if it can, where does it have to be "installed" into the cube? I would go with custom rollup, which requires me to put the MDX into a cell of the table the dimension is based on, correct?

I'd really appreciate any hint or website or book recommendation! Thanks in advance.

Ralf

P.S. Deepak:You are of course absolutly right, I wrote down the result wrong. Thanks for pointing it out!

|||

Assuming that you have a dimension like [DimGroupLine], with [Line] attribute rolling upto [Group] attribute, you could add such a calculation statement to your cube MDX script:

([Measures].[Value], [DimGroupLine].[Group].Members)

= Sum(Existing [DimGroupLine].[Line].[Line].Members,

[Measures].[Counter] * [Measures].[Value]) /

[Measures].[Counter];