scopeing problem in an aggregate function

I have a matrix with two column groups. I need to be able have the user
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.

Scope of License, SQL Server 2005 Express

I have designed an application that spread their data over several
databases.
Actually, it spread its data over several servers, each one with
several dozens of databases each. There's just one single
server/database that acts as a root directory of where the information
(server/database) is stored.
Someone pointed me in other place, that I might be violating the scope
of license respecting this:
4. SCOPE OF LICENSE. The software is licensed, not sold. This
agreement only gives you some rights to use the software. blah, blah,
blahblah, blah, blah
* disclose the results of any benchmark blah, blah, blah
* work around any technical limitations in the software;
<<<<--This is the point!!
Do you think by this design I am workarounding the technical
limitations of the software !?
I thought that section was refering to something like hacking or
cracking the software, but not to a clever design ...
What do you think ?Its questionable if the design you described is really clever. But I
guess, the sentence of MS is just about telling that you are not
allowed to break the limitation in terms of hacking the software.
HTH, Jens K. Suessmeyer.
http://www.sqlserver20095.de
--|||Thanks for your opinion about the terms of the software.
Respecting how clever or not, what potential problems do you see with
it ?
Or what would you do with the need of storage 2TB without budget other
than renting servers? mysql ?
Regards,
Jens wrote:
> Its questionable if the design you described is really clever. But I
> guess, the sentence of MS is just about telling that you are not
> allowed to break the limitation in terms of hacking the software.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver20095.de
> --|||craigkenisston@.hotmail.com wrote:
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?
> Or what would you do with the need of storage 2TB without budget other
> than renting servers? mysql ?
>
craigkenisston@.hotmail.com wrote:
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?

> Or what would you do with the need of storage 2TB without budget other
> than renting servers? mysql ?
Renting servers won't get you far. Not unless you plan to develop,
administer and operate the system for free.
Apparently you don't have a budget for software, perhaps because your
budget holder is naive enough to think that "free" software must cost
less than software you have to pay for. Maybe you are about to prove
how wrong he is. Surely the cost of creating and supporting 500+
databases (2TB/4GB) and multiple servers will far exceed the cost of a
server licence (Workgroup Edition starts at around $700). I suspect the
1GB memory constraint of Express Edition might also become a problem
for you. Of course I can't say whether you are right or wrong, much
less propose alternatives because I know nothing of your business or
your requirements.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||A lot of people use multiple databases to get around the 4GB limit and as
far as I'm aware that doesn't violate the license. You're not exceeding the
limit that the license specifies, you're just scaling out by writing some
front end code.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<craigkenisston@.hotmail.com> wrote in message
news:1158866031.368894.280280@.h48g2000cwc.googlegroups.com...
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?
> Or what would you do with the need of storage 2TB without budget other
> than renting servers? mysql ?
> Regards,
>
> Jens wrote:
>|||Thanks for clarifying this !
Roger Wolter[MSFT] wrote:
> A lot of people use multiple databases to get around the 4GB limit and as
> far as I'm aware that doesn't violate the license. You're not exceeding t
he
> limit that the license specifies, you're just scaling out by writing some
> front end code.
>|||David:
Thanks a lot for your comment. You're right we may be are completely
wrong and the memory limitation is a big show stopper. Put 60 DB of 4Gb
each on on a 250GB Server was the idea.
Now, I have to admit that I don't understans and never understood the
SQL Server license. The workgroup edition or the server + CALs licenses
are unexpensive.
But all this data is to power a website. As far as I remember, I need
to get another kind of license, the expensive one, right ?
Regards,

> Apparently you don't have a budget for software, perhaps because your
> budget holder is naive enough to think that "free" software must cost
> less than software you have to pay for. Maybe you are about to prove
> how wrong he is. Surely the cost of creating and supporting 500+
> databases (2TB/4GB) and multiple servers will far exceed the cost of a
> server licence (Workgroup Edition starts at around $700). I suspect the
> 1GB memory constraint of Express Edition might also become a problem
> for you.|||On 21 Sep 2006 12:13:51 -0700, craigkenisston@.hotmail.com wrote:

>Thanks for your opinion about the terms of the software.
Hi craigkenisston,
<IANAL> I agree with Jens' assessment. However, if you really want to be
sure, check with a MS representative. </IANAL>
I once saw a description on Internet that detailed how to use a hex
editor to change some bytes in an MSDE executable to overcome the 5
concurrent workload limit - now THAT would be a violation of the scope
of license!!

>Respecting how clever or not, what potential problems do you see with
>it ?
* Slowness because data has to travel across the network from server to
server.
* More slowness because it's hard for the SQL optimizer to decide which
server is best equipped to execute a distributed query.
* Very complicated backup and recovery scenario's. It's impossible to
backup all databases and all servers on the exact same moment, so if you
ever need to restore, your databases and server will be out of synch and
you'll have a helll of a job restoring everything into a consistant
state.
* Extra complicated application code

>Or what would you do with the need of storage 2TB without budget other
>than renting servers? mysql ?
Even with the current pricing of hardware, I am surprised to read that
you don't have budget for a SQL Server license whereas you apparently do
have budget for "several servers", hard disks to store 2TB, backup media
to hold the same amount of data (you *DO* have a good backup strategy, I
hope - as compared to the cost of _losing_ 2TB of data, the price for a
SQL Server license is really peanuts...)
Hugo Kornelis, SQL Server MVP|||Data is redundancy in a RAID-like fashion. I can lost a server and the
system still up and running !
So
> * Slowness because data has to travel across the network from server to
> server.
Well, actually, more than going from server to server, it simply
implicates two queries, one to the allocation database and then another
to get the data from the source.

> * More slowness because it's hard for the SQL optimizer to decide which
> server is best equipped to execute a distributed query.
Sql Optimizer don't need to know where to look for data. It is not a
distribuited query, the Express edition don't have that capability ;).
I'll look for where the data is and then look for the data itself.

> * Very complicated backup and recovery scenario's.
We were not planning to tape or NAS backup. We were(are?) going to
setup full mirors of the system and it will help to serve more clients
request as well.

> * Extra complicated application code
Yes, totally agree.

> Even with the current pricing of hardware, I am surprised to read that
> you don't have budget for a SQL Server license whereas you apparently do
> have budget for "several servers",
The software is really database independent using full and pure SQL-92.
I think I can switch to mysql with a day of work. It is just that we
don't want to use mysql for "religious" reasons.
Now, what do you think?|||On 21 Sep 2006 13:43:57 -0700, craigkenisston@.hotmail.com wrote:

>But all this data is to power a website. As far as I remember, I need
>to get another kind of license, the expensive one, right ?
Hi craigkenisston,
Since a web site can have virtuallly unlimited clients accessing it,
you'll need to go for "per-processor" licensing.
Prices start at $3,899 for a single-processor license with Workgroup
Edition. (And since actual sockets are counted for the license, you can
stick a dual-core processor in the machine and have the additional power
of a second core without additional cost).
Compared to the prices of "several" computers, each with an OS license,
you'll probably save money.
Hugo Kornelis, SQL Server MVP

Scoped Calculations and Aggregate

I have a measure which is defined as 0 and then Scope'd for certain dimensions to be a percent of the total as below:

CREATE MEMBER [Measures].[Revenue Percent] AS 0;

SCOPE ([Measures].[Revenue Percent]);

THIS = ([Measures].[Gross Revenue]) / ([Measures].[Gross Revenue], [Area].[Sales Area].[All], [Direct Response].[DRS].[All])

SCOPE (Descendants([Area].[Sales Area].&[2], 1, SELF_AND_AFTER));

THIS = ([Measures].[Gross Revenue])

/ ([Measures].[Gross Revenue], [Area].[Sales Area].&[2], [Direct Response].[DRS].[All]);

END SCOPE;

END SCOPE;

This calculation works fine when I am browsing the cube as I would expect. I can create calculated members on my date dimension and the measure is calculated correctly using Aggregate({date1, date2, date3, etc}) function. However as soon as I change this calculation to use "relative" sets of members it simply returns 0.

So if I have the calcuation,

MEMEBER [Measures].[YTD Revenue Percent] AS AGGREGATE(YTD(date4), [Measures].[Revenue Percent])

it will always return 0. Even if I create the YTD portion as a set first and then aggregate that it still returns 0. However change the ytd(Date4) to something like {date1ate4} and the calculation will work.

Has anyone else seen this sort of behaviour, or can point me in the direction of information as to why it doesn't work?

Thanks in advance

Mike

PS: The date dimension is correctly figured and the member names date1 etc are just syntax for shortening the full date in the post.

{date1ate4} Should read {date1 : date4} - Sorry just seen that once it is posted.

scope_identity and sessions

I found in bol that I can use scope_identity to get the last identity in a specific session and for a the current scope. Now what I would like to know is what is a session? We usually create one user for our software in the database and create an application users table. This means that all users actually connect as the same database user to the database. Does SQL Server see this as different sessions or just one?True what you have mentioned above sessions, check with BOL for sessions then.|||Even if you log in using same username, you can open multiple sessions, like in Query Analyzer.

scope statement in the session scope?

How to write [scope] statements in the session cope,not in the cube-mdx-script?

In the book called "Microsoft Analysis Services 2005" written by developers from microsft's ssas2005 team, there is a senctence:

"because scope statements and assignment operators work ony in the context of a cube or session..."

How to do that ?

Thanx.

You can't do a scope statement from Management Studio because Management Studio doesn't set the current cube on the connection string.

If you look at the MDX Script Performance tool (http://www.codeplex.com/mdxscriptperf) that Chris Webb wrote you will see an example of running a scope statement within a session. It specifies the cube in the connection string. And it replays the calc script one statement at a time which is an example of using "scope" in the context of a session.

Hope that helps.

|||

Thanx for your answer,which is right absolutely.

But I doubt about the Performance Analyzer according the inner working rule , which can be found in the book called "Microsoft Analysis Services 2005" that is written by developers from SSAS2005.

Scope Problem

I'm having trouble with the following issue under RS for SQL 2005. I've spent about 6+ hours researching it trying to find a way to handle it correctly.

I have a report that pulls data from individual line items on contracts. I'm using a table group to group the records into monthly summaries. I'd like to divide the report.total quantity/number of months to get an average monthly sales quantity.

Here is a sample of the last few rows of report data. Under each monthly group is detail data (hidden). There may be one row or 20 in a given month.

... ... ...

+ June 2005

12

$4,300.00

+ July 2005

16

$4,675.00

+ August 2005

28

$10,375.00

Grand Total:

265

$98,212.50

However, I can only seem to get a count of the individual line items. I can't figure out how to get a count of monthly summaries which would logically be something like =countrows("Monthly Summary Group").

There are currently 140 detail items so the average is calculated as (265/140=1.9) when in fact, the average should be much higher. The number of months in the report is 28. The average value should be (265/28=9.5).

If I put this aggregate statement outside of the Montly Summary Group, I'm told that it's out of scope. If I put it within the group, I get a count of contracts within each month.

1. I'm not sure how to make the SQL query group on monthly values so I was doing it in a RS group. Below is a sample of the report detail data which shows that there are multiple detail lines in a given month. I'd like to summarize these for each month/year (Sept 2003, Oct 2003...)

OrderID Date Price Qty ExtPrice
PWSOR136 9/16/2003 5:00:00 PM 425 1 425
PWSOR137 9/16/2003 5:00:00 PM 425 1 425
PWSOR164 9/27/2003 9:00:00 AM 425 1 425
PWSOR179 10/15/2003 5:00:00 PM 425 1 425
PWSOR183 10/18/2003 5:00:00 PM 425 4 1700
PWSOR201 10/24/2003 5:00:00 PM 425 2 850
PWSOR201 10/24/2003 5:00:00 PM 425 1 425

2. I've tried adding a textbox to a blank cell in the group with a value of =1 and then trying to sum that value at the end of the report. I tried using the reportitems!textbox6.value to reference the textbox, but it shows as out of scope.

3. I've tried using custom code with a global variable as shown below:
public dim LineCount as integer = 0

function LineInc(n as integer) as integer
LineCount = LineCount+n
lineinc =linecount
end function

Within the group header, I use a textbox with the value =code.lineinc(1) and this returned an incrementing line count correctly. But when I try to use a textbox at the end of the report with a value of =code.LineCount, the value is 0. I'm assuming that this is part of the same scope problem.

Questions:
1. Is there a way to group records into 'month/year' groups based on a date field using SQL GROUP BY?

2. How can I structure a report so that I can use groups that aggregate in a way that hides the individual line items and gives me access to the Months summary.

3. Can anyone point me towards a good discussion of the programming model of reports in RS, how custom code behaves in RS or a discussion of how scope operates in RS.

Thanks,

Jason

=CountRows("Monthly Summary Group") will count the number of detail rows in the current Monthly Summary Group. That's not what you want.

What you're looking for is a count of the distinct number of months in your entire data set.
So you want something like this:
=CountDistinct(Fields!Month.Value+Fields!Year.Value*100, "DataSet1")

Regarding your additional questions:
1. Yes, you can use the DatePart function to extract the month and year (and group by them) in your SQL query
2. Yes, you can delete the detail rows from your table. However, if you don't want to let the user drill down to see the details, I'd recommend doing the grouping/aggregation by month in your query so that you're not sending a bunch of data into RS that you're just going to throw away.
3. The only thing I can point you at is Books Online/MSDN.

|||Chris,

Thanks much for the pointer to CountDistinct. I hadn't considered it before.

The actual implementation that I used is:

=Sum(Fields!QUANTITY.Value)/countdistinct(format(Fields!ENDDATE.Value,"MM yyyy") )

By using the format function to format the single date value (I didn't have seperate month and year fields) I got the result that I needed.

Also, thanks for the pointer to the DatePart function within the SQL statement. I'll look into that further later as I'm sure it's going to be usefull. I wanted to avoid grouping in the SQL statement if possible because that would have affected my drilldown features.

Later,

Jason

Scope problem

Hello,
I have a single table with 1 detail and 2 groups.
The detail contains data per year, the first group is the card which sums
all years, and the second the family of the card.
Year Objective Realized
2006 100
2007 200
2008 0
TOTAL CARD 1 1000 300
TOTAL FAMILY A 3000 (!!) 300
But the "objective" column isn't a detail data. I would like to return TOTAL
FAMILY A = 1000, but it returns 3000 (it sums 1000 for each year).
I try to use scope for the sum objective of family, but it doesn't seems to
work fine...
If someone can help me...
Thanks,
Drix.Since you want 1000 it sounds like you dont want Sum(). You want
First() or Max() or Min().|||No, if I take my example, the 1000 isn't linked to a year, but to a card.
There is nothing on the year lines for objective, the value is on the line
card, and the sum in the family line.
So, I would like to sum all the objectives cards for the family.
"FatOaf" wrote:
> Since you want 1000 it sounds like you dont want Sum(). You want
> First() or Max() or Min().
>|||I understand what you want now.
Is your resultset returning a row per year and then grouping on card
and family?
and the objective is repeated for each year in the same card?
I dont think you can do what you are asking with that resultset since
the aggregate functions operate on the underlying rows in the resultset
(years) - the scoping just determines which years are included in the
aggregate.
Maybe you can try altering the resultset so each year contains only the
average objective per card or only the first year in a card contains
the objective. Then Sum() would work at any level. The SQL to do this
would be a bit hairy but possible.
Another approach would be to use UNION to have 2 row types in the
resultset - one for years and one for cards. Then display cards from
the second row type instead of by grouping.

Scope parameter

Hi
I have a report which includes a table as follows
table 1
table1_Group1
table1_Group2
In the footer of Group 1, I have a formula
=Sum(Fields!INVOICED.Value,"table1_Group2")
When I try and run the report, I keep getting the error
"The value expression for the textbox â'textbox21â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set."
As far as I can tell, table1_Group2 is a containing group?
The only thing that seems to work is putting the dataset name (or leaving it
out completely). The table name or group get this error.
Am I being stupid?
MacI think the problem is that table1_Group2 does not contain table1_Group1.
So a group can refer to something outside itself. But why not use this...?
=Sum(Fields!INVOICED.Value,"table1_Group1")
The sums should be the same, right? The multiple instances of table1_Group2
should correspond to the instance of table1_Group1 you're displaying.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> Hi
> I have a report which includes a table as follows
> table 1
> table1_Group1
> table1_Group2
> In the footer of Group 1, I have a formula
> =Sum(Fields!INVOICED.Value,"table1_Group2")
> When I try and run the report, I keep getting the error
> "The value expression for the textbox 'textbox21' has a scope parameter
> that
> is not valid for an aggregate function. The scope parameter must be set
> to a
> string constant that is equal to either the name of a containing group,
> the
> name of a containing data region, or the name of a data set."
> As far as I can tell, table1_Group2 is a containing group?
> The only thing that seems to work is putting the dataset name (or leaving
> it
> out completely). The table name or group get this error.
> Am I being stupid?
> Mac|||Ok that works although what I am trying to is this
I have a dataset which returns the amount invoiced by client
What I want the report to show is the breakdown of the top 20 clients and
then summarise the rest into a single line e.g.
Client 1 100,000
Client 2 90,000
.
.
.
Total top 20 190,000
Other Clients 60,000
Total Income 250,000
As group 2 holds the top 20 clients by using a filter, I wanted (hoping) to
sum the clients in that group only.
Using scope 1, I get the total for the entire dataset
"Jeff A. Stucker" wrote:
> I think the problem is that table1_Group2 does not contain table1_Group1.
> So a group can refer to something outside itself. But why not use this...?
> =Sum(Fields!INVOICED.Value,"table1_Group1")
> The sums should be the same, right? The multiple instances of table1_Group2
> should correspond to the instance of table1_Group1 you're displaying.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Mac" <Mac@.discussions.microsoft.com> wrote in message
> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> > Hi
> >
> > I have a report which includes a table as follows
> >
> > table 1
> > table1_Group1
> > table1_Group2
> >
> > In the footer of Group 1, I have a formula
> >
> > =Sum(Fields!INVOICED.Value,"table1_Group2")
> >
> > When I try and run the report, I keep getting the error
> >
> > "The value expression for the textbox 'textbox21' has a scope parameter
> > that
> > is not valid for an aggregate function. The scope parameter must be set
> > to a
> > string constant that is equal to either the name of a containing group,
> > the
> > name of a containing data region, or the name of a data set."
> >
> > As far as I can tell, table1_Group2 is a containing group?
> >
> > The only thing that seems to work is putting the dataset name (or leaving
> > it
> > out completely). The table name or group get this error.
> >
> > Am I being stupid?
> >
> > Mac
>
>|||Try playing with RunningValue in a formula or maybe a code block. Coupled
with the row count, you may be able to sum them up that way.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:CF9ED55F-C444-4342-B58F-E1863BF28BE3@.microsoft.com...
> Ok that works although what I am trying to is this
> I have a dataset which returns the amount invoiced by client
> What I want the report to show is the breakdown of the top 20 clients and
> then summarise the rest into a single line e.g.
> Client 1 100,000
> Client 2 90,000
> .
> .
> .
> Total top 20 190,000
> Other Clients 60,000
> Total Income 250,000
> As group 2 holds the top 20 clients by using a filter, I wanted (hoping)
> to
> sum the clients in that group only.
> Using scope 1, I get the total for the entire dataset
>
> "Jeff A. Stucker" wrote:
>> I think the problem is that table1_Group2 does not contain table1_Group1.
>> So a group can refer to something outside itself. But why not use
>> this...?
>> =Sum(Fields!INVOICED.Value,"table1_Group1")
>> The sums should be the same, right? The multiple instances of
>> table1_Group2
>> should correspond to the instance of table1_Group1 you're displaying.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Mac" <Mac@.discussions.microsoft.com> wrote in message
>> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
>> > Hi
>> >
>> > I have a report which includes a table as follows
>> >
>> > table 1
>> > table1_Group1
>> > table1_Group2
>> >
>> > In the footer of Group 1, I have a formula
>> >
>> > =Sum(Fields!INVOICED.Value,"table1_Group2")
>> >
>> > When I try and run the report, I keep getting the error
>> >
>> > "The value expression for the textbox 'textbox21' has a scope parameter
>> > that
>> > is not valid for an aggregate function. The scope parameter must be
>> > set
>> > to a
>> > string constant that is equal to either the name of a containing group,
>> > the
>> > name of a containing data region, or the name of a data set."
>> >
>> > As far as I can tell, table1_Group2 is a containing group?
>> >
>> > The only thing that seems to work is putting the dataset name (or
>> > leaving
>> > it
>> > out completely). The table name or group get this error.
>> >
>> > Am I being stupid?
>> >
>> > Mac
>>

Scope of License, SQL Server 2005 Express

I have designed an application that spread their data over several
databases.
Actually, it spread its data over several servers, each one with
several dozens of databases each. There's just one single
server/database that acts as a root directory of where the information
(server/database) is stored.
Someone pointed me in other place, that I might be violating the scope
of license respecting this:
4. SCOPE OF LICENSE. The software is licensed, not sold. This
agreement only gives you some rights to use the software. blah, blah,
blahblah, blah, blah
* disclose the results of any benchmark blah, blah, blah
* work around any technical limitations in the software;
<<<<--This is the point!!
Do you think by this design I am workarounding the technical
limitations of the software !?
I thought that section was refering to something like hacking or
cracking the software, but not to a clever design ...
What do you think ?Its questionable if the design you described is really clever. But I
guess, the sentence of MS is just about telling that you are not
allowed to break the limitation in terms of hacking the software.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver20095.de
--|||Thanks for your opinion about the terms of the software.
Respecting how clever or not, what potential problems do you see with
it ?
Or what would you do with the need of storage 2TB without budget other
than renting servers? MySQL ?
Regards,
Jens wrote:
> Its questionable if the design you described is really clever. But I
> guess, the sentence of MS is just about telling that you are not
> allowed to break the limitation in terms of hacking the software.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver20095.de
> --|||craigkenisston@.hotmail.com wrote:
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?
> Or what would you do with the need of storage 2TB without budget other
> than renting servers? MySQL ?
>
craigkenisston@.hotmail.com wrote:
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?
> Or what would you do with the need of storage 2TB without budget other
> than renting servers? MySQL ?
Renting servers won't get you far. Not unless you plan to develop,
administer and operate the system for free.
Apparently you don't have a budget for software, perhaps because your
budget holder is naive enough to think that "free" software must cost
less than software you have to pay for. Maybe you are about to prove
how wrong he is. Surely the cost of creating and supporting 500+
databases (2TB/4GB) and multiple servers will far exceed the cost of a
server licence (Workgroup Edition starts at around $700). I suspect the
1GB memory constraint of Express Edition might also become a problem
for you. Of course I can't say whether you are right or wrong, much
less propose alternatives because I know nothing of your business or
your requirements.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||A lot of people use multiple databases to get around the 4GB limit and as
far as I'm aware that doesn't violate the license. You're not exceeding the
limit that the license specifies, you're just scaling out by writing some
front end code.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<craigkenisston@.hotmail.com> wrote in message
news:1158866031.368894.280280@.h48g2000cwc.googlegroups.com...
> Thanks for your opinion about the terms of the software.
> Respecting how clever or not, what potential problems do you see with
> it ?
> Or what would you do with the need of storage 2TB without budget other
> than renting servers? MySQL ?
> Regards,
>
> Jens wrote:
>> Its questionable if the design you described is really clever. But I
>> guess, the sentence of MS is just about telling that you are not
>> allowed to break the limitation in terms of hacking the software.
>>
>> HTH, Jens K. Suessmeyer.
>> --
>> http://www.sqlserver20095.de
>> --
>|||Thanks for clarifying this !
Roger Wolter[MSFT] wrote:
> A lot of people use multiple databases to get around the 4GB limit and as
> far as I'm aware that doesn't violate the license. You're not exceeding the
> limit that the license specifies, you're just scaling out by writing some
> front end code.
>|||David:
Thanks a lot for your comment. You're right we may be are completely
wrong and the memory limitation is a big show stopper. Put 60 DB of 4Gb
each on on a 250GB Server was the idea.
Now, I have to admit that I don't understans and never understood the
SQL Server license. The workgroup edition or the server + CALs licenses
are unexpensive.
But all this data is to power a website. As far as I remember, I need
to get another kind of license, the expensive one, right ?
Regards,
> Apparently you don't have a budget for software, perhaps because your
> budget holder is naive enough to think that "free" software must cost
> less than software you have to pay for. Maybe you are about to prove
> how wrong he is. Surely the cost of creating and supporting 500+
> databases (2TB/4GB) and multiple servers will far exceed the cost of a
> server licence (Workgroup Edition starts at around $700). I suspect the
> 1GB memory constraint of Express Edition might also become a problem
> for you.|||On 21 Sep 2006 12:13:51 -0700, craigkenisston@.hotmail.com wrote:
>Thanks for your opinion about the terms of the software.
Hi craigkenisston,
<IANAL> I agree with Jens' assessment. However, if you really want to be
sure, check with a MS representative. </IANAL>
I once saw a description on Internet that detailed how to use a hex
editor to change some bytes in an MSDE executable to overcome the 5
concurrent workload limit - now THAT would be a violation of the scope
of license!!
>Respecting how clever or not, what potential problems do you see with
>it ?
* Slowness because data has to travel across the network from server to
server.
* More slowness because it's hard for the SQL optimizer to decide which
server is best equipped to execute a distributed query.
* Very complicated backup and recovery scenario's. It's impossible to
backup all databases and all servers on the exact same moment, so if you
ever need to restore, your databases and server will be out of synch and
you'll have a helll of a job restoring everything into a consistant
state.
* Extra complicated application code
>Or what would you do with the need of storage 2TB without budget other
>than renting servers? MySQL ?
Even with the current pricing of hardware, I am surprised to read that
you don't have budget for a SQL Server license whereas you apparently do
have budget for "several servers", hard disks to store 2TB, backup media
to hold the same amount of data (you *DO* have a good backup strategy, I
hope - as compared to the cost of _losing_ 2TB of data, the price for a
SQL Server license is really peanuts...)
--
Hugo Kornelis, SQL Server MVP|||Data is redundancy in a RAID-like fashion. I can lost a server and the
system still up and running !
So
> * Slowness because data has to travel across the network from server to
> server.
Well, actually, more than going from server to server, it simply
implicates two queries, one to the allocation database and then another
to get the data from the source.
> * More slowness because it's hard for the SQL optimizer to decide which
> server is best equipped to execute a distributed query.
Sql Optimizer don't need to know where to look for data. It is not a
distribuited query, the Express edition don't have that capability ;).
I'll look for where the data is and then look for the data itself.
> * Very complicated backup and recovery scenario's.
We were not planning to tape or NAS backup. We were(are?) going to
setup full mirors of the system and it will help to serve more clients
request as well.
> * Extra complicated application code
Yes, totally agree.
> Even with the current pricing of hardware, I am surprised to read that
> you don't have budget for a SQL Server license whereas you apparently do
> have budget for "several servers",
The software is really database independent using full and pure SQL-92.
I think I can switch to MySQL with a day of work. It is just that we
don't want to use MySQL for "religious" reasons.
Now, what do you think?|||On 21 Sep 2006 13:43:57 -0700, craigkenisston@.hotmail.com wrote:
>But all this data is to power a website. As far as I remember, I need
>to get another kind of license, the expensive one, right ?
Hi craigkenisston,
Since a web site can have virtuallly unlimited clients accessing it,
you'll need to go for "per-processor" licensing.
Prices start at $3,899 for a single-processor license with Workgroup
Edition. (And since actual sockets are counted for the license, you can
stick a dual-core processor in the machine and have the additional power
of a second core without additional cost).
Compared to the prices of "several" computers, each with an OS license,
you'll probably save money.
--
Hugo Kornelis, SQL Server MVP