Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case statement not working

I have a Case statement that is working for the first "when", but not the
second.
t1.CsxShortDesc,
CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
in the above statement the t1.CsxShortDesc works fine.
In the Case statement, where the same field is used (CsxShortDesc), it works
for the first WHEN. If the value is 'SDICA', it will display a 'D', but if
it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
part).
Both whens are looking at the same field, so why doesn't it work?
Thanks,
TomCan you provide an actual repro (DDL, sample data)?
Is CsxShortDesc CHAR or VARCHAR?
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23mk5e5gvFHA.3756@.tk2msftngp13.phx.gbl...
>I have a Case statement that is working for the first "when", but not the
>second.
> t1.CsxShortDesc,
> CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
> in the above statement the t1.CsxShortDesc works fine.
> In the Case statement, where the same field is used (CsxShortDesc), it
> works for the first WHEN. If the value is 'SDICA', it will display a 'D',
> but if it is equal to 'ST1CA', it does not display 'I'. It is Null (the
> ELSE part).
> Both whens are looking at the same field, so why doesn't it work?
> Thanks,
> Tom
>|||typo? - should it be 'STICA' instead of 'ST1CA'?
If not, do you, in fact, have rows matching the criteria being used that
have a csxshortdesc value of ST1CA?
tshad wrote:

>I have a Case statement that is working for the first "when", but not the
>second.
> t1.CsxShortDesc,
>CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
>in the above statement the t1.CsxShortDesc works fine.
>In the Case statement, where the same field is used (CsxShortDesc), it work
s
>for the first WHEN. If the value is 'SDICA', it will display a 'D', but if
>it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
>part).
>Both whens are looking at the same field, so why doesn't it work?
>Thanks,
>Tom
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eAlu88gvFHA.2504@.tk2msftngp13.phx.gbl...
> Can you provide an actual repro (DDL, sample data)?
> Is CsxShortDesc CHAR or VARCHAR?
I will look at it.
The problem is that I will need to recreate it as a test table and select
statement, because this is a large Select with 4 views being created and
reading a table of about 20,000 records.
This is a VARCHAR. What is confusing is both are 5 characters and SDICA
works why doesn't ST1CA? ST1CA is being displayed in the field before the
Case, so I know that is correct.
Tom
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23mk5e5gvFHA.3756@.tk2msftngp13.phx.gbl...
>|||Can you post table schema?
This works for me.
select
t1.CsxShortDesc,
CASE
WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I'
ELSE NULL END
from
(
select 'SDICA'
union all
select 'ST1CA'
) as t1(CsxShortDesc)
AMB
"tshad" wrote:

> I have a Case statement that is working for the first "when", but not the
> second.
> t1.CsxShortDesc,
> CASE WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I' ELSE NULL END
> in the above statement the t1.CsxShortDesc works fine.
> In the Case statement, where the same field is used (CsxShortDesc), it wor
ks
> for the first WHEN. If the value is 'SDICA', it will display a 'D', but i
f
> it is equal to 'ST1CA', it does not display 'I'. It is Null (the ELSE
> part).
> Both whens are looking at the same field, so why doesn't it work?
> Thanks,
> Tom
>
>|||"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eNpYd%23gvFHA.2556@.TK2MSFTNGP15.phx.gbl...
> typo? - should it be 'STICA' instead of 'ST1CA'?
> If not, do you, in fact, have rows matching the criteria being used that
> have a csxshortdesc value of ST1CA?
Checked that out.
Here is part of the results pane:
ST1CA NULL 94.43 SDICA D 22.19
Tom
> tshad wrote:
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C6CB3220-A533-4FB7-9738-D61964128AE9@.microsoft.com...
> Can you post table schema?
Not really.
This is a foreign system with a lot of tables. I am creating creating 4
Views and joining them with 3 other tables to get it.
I will have to create a test table and try to recreate it there.

> This works for me.
> select
> t1.CsxShortDesc,
> CASE
> WHEN t1.CsxShortDesc = 'SDICA' THEN 'D'
> WHEN t1.CsxShortDesc = 'ST1CA' THEN 'I'
> ELSE NULL END
> from
> (
> select 'SDICA'
> union all
> select 'ST1CA'
> ) as t1(CsxShortDesc)
Right.
But in my case, it isn't. As you can see from my results in the other post,
it isn't doing the 'I', even though it is obviously ST1CA.
I am going to try reversing case and put ST1CA and see if I get the same
results.
Thanks,
Tom
>
> AMB
>
> "tshad" wrote:
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OImCiLhvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:C6CB3220-A533-4FB7-9738-D61964128AE9@.microsoft.com...
> Not really.
> This is a foreign system with a lot of tables. I am creating creating 4
> Views and joining them with 3 other tables to get it.
> I will have to create a test table and try to recreate it there.
>
> Right.
> But in my case, it isn't. As you can see from my results in the other
> post, it isn't doing the 'I', even though it is obviously ST1CA.
> I am going to try reversing case and put ST1CA and see if I get the same
> results.
OK, I tried that and it definately has to do with the position of the WHEN
statement and not the Data. I found the error in my code. The problem is I
have 20 of this statements (for 30 columns in a CSV file). The first one
works fine. Here is the 2nd one. It turns out 2-20 all refer to the table
t1 instead of their respective tables t2 -> t2, t3 -> t3.
t2.CsxShortDesc,CASE WHEN t2.CsxShortDesc = 'ST1CA' THEN 'I'
WHEN t1.CsxShortDesc = 'SDICA' THEN 'D' ELSE NULL END,t2.CsxCurrTax,
I did a lot of copying and pasting here and just missed this one - no matter
how much I stared at it.
Thanks,
Tom

> Thanks,
> Tom
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)endThere are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
--
David Portas
SQL Server MVP
--|||Thanks. That worked good
"David Portas" wrote:
> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)end
There are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
David Portas
SQL Server MVP
|||Thanks. That worked good
"David Portas" wrote:

> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

CASE Statement not working

Anyone have any idea why the following case statement I am getting a syntax
error near 'when':
, case(m.units when not like '%U%' then m.matl_qty-m.qty_issued
else (m.matl_qty*j.qty_released)-m.qty_issued)endThere are two versions of the CASE syntax, called the "simple" and
"searched" CASEs. For your LIKE expression you need to use the searched
CASE: WHEN has to come before the LIKE expression. See Books Online for
details.
CASE
WHEN m.units NOT LIKE '%U%'
THEN m.matl_qty - m.qty_issued
ELSE (m.matl_qty*j.qty_released)-m.qty_issued
END
David Portas
SQL Server MVP
--|||Thanks. That worked good
"David Portas" wrote:

> There are two versions of the CASE syntax, called the "simple" and
> "searched" CASEs. For your LIKE expression you need to use the searched
> CASE: WHEN has to come before the LIKE expression. See Books Online for
> details.
> CASE
> WHEN m.units NOT LIKE '%U%'
> THEN m.matl_qty - m.qty_issued
> ELSE (m.matl_qty*j.qty_released)-m.qty_issued
> END
> --
> David Portas
> SQL Server MVP
> --
>
>

case statement in where clause

Hello

I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this

Thanks very much

declare @.param varchar (100)
select @.param = 'mytext

select
colA
,colB
,colC
from
mytable
where
(case
when @.param is null then colA = 'group'
else colA = 'single'
end)I'd suggest using WHERE colA = CASE WHEN @.param IS NULL THEN 'group' ELSE 'single' END-PatP|||No I can't do it like that

I've rewritten what I want, maybe you can hlp with this

declare @.param varchar (100)
select @.param = 'mytext'

select
colA
,colB
,colC
from
mytable
where
(case
when @.param is null then colB = 'group' and colC = 'something'
else colB = 'group' and colC = @.param
end)|||Ok, let's do that dance and move on to:SELECT
colA
, colB
, colC
FROM mytable
WHERE (@.param IS NULL AND colB = 'group' AND colC = 'something')
OR (@.param = colC AND colB = 'group')-PatP

Case sensitive problem

Hi,
I'm working on a SQL 2000 server that was not set up by me. I tried creating
stored procedure on it and found all variables are case-sensitive. How do I
change it to case insensitive?
For example, if I use QueryAnalyzer and enter in the following two lines:
DECLARE @.VAR1 int
SET @.var1 = 3
I would get the following error,
Must declare the variable '@.var1'.
Any help would be greatly appreciatedYou would change the database's collation to a case-insensitive collation.
You may want to find out why it's using a case-sensitive collation before
changing it though; there could be a method to the madness.
"Ming" wrote:

> Hi,
> I'm working on a SQL 2000 server that was not set up by me. I tried creati
ng
> stored procedure on it and found all variables are case-sensitive. How do
I
> change it to case insensitive?
> For example, if I use QueryAnalyzer and enter in the following two lines:
> DECLARE @.VAR1 int
> SET @.var1 = 3
> I would get the following error,
> Must declare the variable '@.var1'.
> Any help would be greatly appreciated|||I used EM and right clicked on that database and clicked the property page,
the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I believ
e
is case insensitive. Any idea?
"KH" wrote:
> You would change the database's collation to a case-insensitive collation.
> You may want to find out why it's using a case-sensitive collation before
> changing it though; there could be a method to the madness.
>
> "Ming" wrote:
>|||I think that variable name sensitivity is determined by the server's
collation, not the local database's collation.
The server's collation is chosen in the installation, and is the collation
set for all system databases.
You can check the server's collation by running sp_helpsort, or select
serverproperty ('collation').
If the server's collation is case sensitive, that would explain your issues.
Changing the server's collation is not a simple task, even if you don't have
any applications that rely on or require case sensitivity.
You can try to enforce a policy where all variable names are, say, always
lower case.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ming" <Ming@.discussions.microsoft.com> wrote in message
news:7B241490-F50A-41C1-B269-AB4F18EA778F@.microsoft.com...
>I used EM and right clicked on that database and clicked the property page,
> the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I
> believe
> is case insensitive. Any idea?
> "KH" wrote:
>

Cascading Parameters, Drop down lists & the use of "ALL"

I have a report I'm working on (data dictionary) where the team wants to be
able to do the following:
1) Choose "All Tables" and get a report listing all tables with each tables
column names & properties underneath the proper table (ie, the columnName
param would be greyed out if this is chosen).
OR
2) Choose a single table by name, then choose "All Columns" and get a report
with ONLY that table and only the columns belonging to that table underneath
it with their properties.
OR
3) Choose a single table by name, then choose a single column by name and
only get that single column's properites.
Obviously, 3 is the easiest and what I was able to get done first. I have a
seperate report (my first report) where I've got a list of all tables with
drilldown enabled that shows each table's columns (all of them) when
expanded. Of course, once I got that report done, I was asked to add this
additional functionality.
Can this be done in one report or will I need to resort to multiple,
seperate reports to accomplish this?
I've got the code written for "All Tables" and "All Columns", but when I try
to pick a single table, I still get a ColumnList that lists all columns in
the entire DB, not the columns specific for that table.
Can someone give me advice on how to accomplish what I need? Thanks!!!
CatadminWe do something similar (although no cascading parameters). For "All", we
pass in the value zero, and the query is written like this:
Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
If you're doing actual database objects and are using the sysobjects and
syscolumns tables, I would imagine your syscolumns query would include a
similar clause:
Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid =@.ColumnID)
I don't use cascading parameters, so I may be completely missing the point,
and if so, I apologize...
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
>I have a report I'm working on (data dictionary) where the team wants to be
> able to do the following:
> 1) Choose "All Tables" and get a report listing all tables with each
> tables
> column names & properties underneath the proper table (ie, the columnName
> param would be greyed out if this is chosen).
> OR
> 2) Choose a single table by name, then choose "All Columns" and get a
> report
> with ONLY that table and only the columns belonging to that table
> underneath
> it with their properties.
> OR
> 3) Choose a single table by name, then choose a single column by name and
> only get that single column's properites.
> Obviously, 3 is the easiest and what I was able to get done first. I have
> a
> seperate report (my first report) where I've got a list of all tables with
> drilldown enabled that shows each table's columns (all of them) when
> expanded. Of course, once I got that report done, I was asked to add this
> additional functionality.
> Can this be done in one report or will I need to resort to multiple,
> seperate reports to accomplish this?
> I've got the code written for "All Tables" and "All Columns", but when I
> try
> to pick a single table, I still get a ColumnList that lists all columns in
> the entire DB, not the columns specific for that table.
> Can someone give me advice on how to accomplish what I need? Thanks!!!
> Catadmin
>|||I appreciate the suggestion, but I'm not sure it will work.
I want to be able to grey out the column parameter if "All Tables" is
chosen. Make it not accessible. Then, if a single table is chosen, not
print the other table on the report. My first problem is, that even when I
choose a single table, it prints out ALL the tables with the single column I
chose & all associated properties underneath all tables, even the tables that
have no such column. My second problem is creating the second parameter's
drop down list based only the columns associated with a single table picked
in the first parameter. My third problem is forcing all columns to
automatically be chosen, blocking out the second parameter, if "All Tables"
is chosen, so that all tables print, in order, with all of the columns
associated with that table and only that table.
Thank you for your time, though.
Catadmin
"DJM" wrote:
> We do something similar (although no cascading parameters). For "All", we
> pass in the value zero, and the query is written like this:
> Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
> If you're doing actual database objects and are using the sysobjects and
> syscolumns tables, I would imagine your syscolumns query would include a
> similar clause:
> Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid => @.ColumnID)
> I don't use cascading parameters, so I may be completely missing the point,
> and if so, I apologize...
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
> >I have a report I'm working on (data dictionary) where the team wants to be
> > able to do the following:
> >
> > 1) Choose "All Tables" and get a report listing all tables with each
> > tables
> > column names & properties underneath the proper table (ie, the columnName
> > param would be greyed out if this is chosen).
> >
> > OR
> >
> > 2) Choose a single table by name, then choose "All Columns" and get a
> > report
> > with ONLY that table and only the columns belonging to that table
> > underneath
> > it with their properties.
> >
> > OR
> >
> > 3) Choose a single table by name, then choose a single column by name and
> > only get that single column's properites.
> >
> > Obviously, 3 is the easiest and what I was able to get done first. I have
> > a
> > seperate report (my first report) where I've got a list of all tables with
> > drilldown enabled that shows each table's columns (all of them) when
> > expanded. Of course, once I got that report done, I was asked to add this
> > additional functionality.
> >
> > Can this be done in one report or will I need to resort to multiple,
> > seperate reports to accomplish this?
> >
> > I've got the code written for "All Tables" and "All Columns", but when I
> > try
> > to pick a single table, I still get a ColumnList that lists all columns in
> > the entire DB, not the columns specific for that table.
> >
> > Can someone give me advice on how to accomplish what I need? Thanks!!!
> >
> > Catadmin
> >
>
>

Cascading Parameters

All of a sudden I am having problems with cascading parameters. This report,
under development, was working fine earlier in the week. I've started over
and each time I get the same problem. I've been working with cascding
parameters for some time and nver seen this. I've tried everything but can't
get it back working correctly.
I have a cascading parameter that rlies on a date parameter. When I key in
the parameter, the cascading parameter populates the dropdown on the report
but when I click in the next parameter text box to set focus, Visual studio
has a seizure and starts flashing and by cpu maxes out. When I run the same
report in reporting services, I get an error that the next parameter has an
inconsistent data type.
I'm confused. Can anyone help."Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||More information. If I tab between the parameter text boxes, I can set the
dependent parameter and the report runs. Any subsequent click leads to Visual
Studion convulsions. WEIRD
"Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||Tested further on my other laptop. It's Reporting Services sp1 versus sp2 on
the problem machine. The SP1 machine is more stable. No Visual Studio
convusling.
SH**
"Mardy" wrote:
> More information. If I tab between the parameter text boxes, I can set the
> dependent parameter and the report runs. Any subsequent click leads to Visual
> Studion convulsions. WEIRD
> "Mardy" wrote:
> > All of a sudden I am having problems with cascading parameters. This report,
> > under development, was working fine earlier in the week. I've started over
> > and each time I get the same problem. I've been working with cascding
> > parameters for some time and nver seen this. I've tried everything but can't
> > get it back working correctly.
> >
> > I have a cascading parameter that rlies on a date parameter. When I key in
> > the parameter, the cascading parameter populates the dropdown on the report
> > but when I click in the next parameter text box to set focus, Visual studio
> > has a seizure and starts flashing and by cpu maxes out. When I run the same
> > report in reporting services, I get an error that the next parameter has an
> > inconsistent data type.
> >
> > I'm confused. Can anyone help.|||I have found that it depends on my cascading parameters whether this
happens. It doesn't always happened. I haven't been developing on a report
with this problem in SP2 so I can't say if it is better or worse. What I can
say is that it only occurs when developing. If you deploy it, it will work
just fine. So, for a report that does this I would just deploy it for
testing. A bit of a pain but it is a workaround and the report will work
fine in production.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:938D29C2-34FA-4843-B74F-D0C95B09BFC2@.microsoft.com...
> Tested further on my other laptop. It's Reporting Services sp1 versus sp2
> on
> the problem machine. The SP1 machine is more stable. No Visual Studio
> convusling.
> SH**
> "Mardy" wrote:
>> More information. If I tab between the parameter text boxes, I can set
>> the
>> dependent parameter and the report runs. Any subsequent click leads to
>> Visual
>> Studion convulsions. WEIRD
>> "Mardy" wrote:
>> > All of a sudden I am having problems with cascading parameters. This
>> > report,
>> > under development, was working fine earlier in the week. I've started
>> > over
>> > and each time I get the same problem. I've been working with cascding
>> > parameters for some time and nver seen this. I've tried everything but
>> > can't
>> > get it back working correctly.
>> >
>> > I have a cascading parameter that rlies on a date parameter. When I key
>> > in
>> > the parameter, the cascading parameter populates the dropdown on the
>> > report
>> > but when I click in the next parameter text box to set focus, Visual
>> > studio
>> > has a seizure and starts flashing and by cpu maxes out. When I run the
>> > same
>> > report in reporting services, I get an error that the next parameter
>> > has an
>> > inconsistent data type.
>> >
>> > I'm confused. Can anyone help.|||I found this issue occurs after applying MS patch 05-014 to my XP desktop
client and using IE browser. I have not found the solution yet, but we are
running Report Services SP1 as well.
"Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||I am having the same issue. I found by putting in a default value for the
parameter, I can bypass the dropdown selection. I have this issue both in
SP1 and SP2.
Looking forward to a patch,
andy
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:1A67CB60-5CB8-4AD7-9B2E-F42530880DD2@.microsoft.com...
> All of a sudden I am having problems with cascading parameters. This
> report,
> under development, was working fine earlier in the week. I've started
> over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but
> can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the
> report
> but when I click in the next parameter text box to set focus, Visual
> studio
> has a seizure and starts flashing and by cpu maxes out. When I run the
> same
> report in reporting services, I get an error that the next parameter has
> an
> inconsistent data type.
> I'm confused. Can anyone help.

Cascading Parameter not working

Hi Friends,

I have 2 parameter One is Office (Listbox) another Account(list box) , where Account list box filling is dependent on the Office selection.

In published report when I select the office from list box the page got refreshed but it not updating the account list, the account list box looks diabled?

I have define the two parameter and for the second parameter here is the query

="SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT " &
"where OFFICE_ID =" & Parameters!paramOffice.Value &
"ORDER BY ACCOUNT_ID "

Can any one help me.

Thanks

Novin

Hi Novin

I am not sure you are doing this in the most straight forward manner.
In your situation I would define another dataset e.g. dsAccount with the following query:

SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT
where OFFICE_ID = @.paramOffice
ORDER BY ACCOUNT_ID

Then set up the Account parameter to get its data from the dsAccount dataset.

This should refresh correctly.

Cheers
Mark
|||Hi Mark,

Thanks for your reply.

I know the sytext , I m using ODBC driver which does not support @.param ,

To access parameter i need to use Parameters!paramOffice.Value with ODBC.

Thanks
Novin
|||

Hi

The implementation is working it was a silly mistake from my side i have assign wrong data type the the paremeter.

Thanks,

Novin

Cascading Parameter not working

Hi Friends,

I have 2 parameter One is Office (Listbox) another Account(list box) , where Account list box filling is dependent on the Office selection.

In published report when I select the office from list box the page got refreshed but it not updating the account list, the account list box looks diabled?

I have define the two parameter and for the second parameter here is the query

="SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT " &
"where OFFICE_ID =" & Parameters!paramOffice.Value &
"ORDER BY ACCOUNT_ID "

Can any one help me.

Thanks

Novin

Hi Novin

I am not sure you are doing this in the most straight forward manner.
In your situation I would define another dataset e.g. dsAccount with the following query:

SELECT ACCOUNT_ID, ACCOUNT_NUMBER FROM MLGDB2.A_ACCOUNT
where OFFICE_ID = @.paramOffice
ORDER BY ACCOUNT_ID

Then set up the Account parameter to get its data from the dsAccount dataset.

This should refresh correctly.

Cheers
Mark
|||Hi Mark,

Thanks for your reply.

I know the sytext , I m using ODBC driver which does not support @.param ,

To access parameter i need to use Parameters!paramOffice.Value with ODBC.

Thanks
Novin
|||

Hi

The implementation is working it was a silly mistake from my side i have assign wrong data type the the paremeter.

Thanks,

Novin

Cascading Deletes

I have to SQL 2000 Server setups. It seems I have different builds on them.
The problem one is (told to me from SQL Analyzer) 8.00.194 the working one
is 8.00.760. In the problem one I can not setup a cascading delete from
Enterprise Manager. Anyone know why? Getting the problem updated is going
to be a bear. Anyone know a work around to add my Cascading Delete?
ChrisJust create your foreign key in T-SQL:
ALTER TABLE your_table DROP CONSTRAINT your_constraint
GO
ALTER TABLE your_table ADD CONSTRAINT your_constraint
FOREIGN KEY (referencing_column)
REFERENCES referred_table (primary_key_column)
ON CASCADE DELETE
Jacco Schalkwijk
SQL Server MVP
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>I have to SQL 2000 Server setups. It seems I have different builds on
>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>working one is 8.00.760. In the problem one I can not setup a cascading
>delete from Enterprise Manager. Anyone know why? Getting the problem
>updated is going to be a bear. Anyone know a work around to add my
>Cascading Delete?
> Chris
>|||Ya I tried that too:
ALTER TABLE Holiday ADD CONSTRAINT TestContraint
FOREIGN KEY (FK_CallFlow)
REFERENCES CallFlows (PRI_ID)
ON CASCADE DELETE
And get this error:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ON'.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Just create your foreign key in T-SQL:
> ALTER TABLE your_table DROP CONSTRAINT your_constraint
> GO
> ALTER TABLE your_table ADD CONSTRAINT your_constraint
> FOREIGN KEY (referencing_column)
> REFERENCES referred_table (primary_key_column)
> ON CASCADE DELETE
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>>I have to SQL 2000 Server setups. It seems I have different builds on
>>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>>working one is 8.00.760. In the problem one I can not setup a cascading
>>delete from Enterprise Manager. Anyone know why? Getting the problem
>>updated is going to be a bear. Anyone know a work around to add my
>>Cascading Delete?
>> Chris
>|||> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
...
on delete cascade
AMB
"Chris, Master of All Things Insignifican" wrote:
> Ya I tried that too:
> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
> And get this error:
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'ON'.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> > Just create your foreign key in T-SQL:
> >
> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
> > GO
> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
> > FOREIGN KEY (referencing_column)
> > REFERENCES referred_table (primary_key_column)
> > ON CASCADE DELETE
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
> >>I have to SQL 2000 Server setups. It seems I have different builds on
> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
> >>working one is 8.00.760. In the problem one I can not setup a cascading
> >>delete from Enterprise Manager. Anyone know why? Getting the problem
> >>updated is going to be a bear. Anyone know a work around to add my
> >>Cascading Delete?
> >>
> >> Chris
> >>
> >
> >
>
>|||Nope, same error message? Anyone know what's going on?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
> ...
> on delete cascade
>
> AMB
> "Chris, Master of All Things Insignifican" wrote:
>> Ya I tried that too:
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> And get this error:
>> Server: Msg 156, Level 15, State 1, Line 4
>> Incorrect syntax near the keyword 'ON'.
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote
>> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Just create your foreign key in T-SQL:
>> >
>> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
>> > GO
>> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
>> > FOREIGN KEY (referencing_column)
>> > REFERENCES referred_table (primary_key_column)
>> > ON CASCADE DELETE
>> >
>> >
>> > --
>> > Jacco Schalkwijk
>> > SQL Server MVP
>> >
>> >
>> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
>> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> >>I have to SQL 2000 Server setups. It seems I have different builds on
>> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>> >>working one is 8.00.760. In the problem one I can not setup a
>> >>cascading
>> >>delete from Enterprise Manager. Anyone know why? Getting the problem
>> >>updated is going to be a bear. Anyone know a work around to add my
>> >>Cascading Delete?
>> >>
>> >> Chris
>> >>
>> >
>> >
>>|||Perhaps the database has compatibility level lower than 80?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote in message
news:%23HU5f$yAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Nope, same error message? Anyone know what's going on?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> ...
>> on delete cascade
>>
>> AMB
>> "Chris, Master of All Things Insignifican" wrote:
>> Ya I tried that too:
>> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
>> FOREIGN KEY (FK_CallFlow)
>> REFERENCES CallFlows (PRI_ID)
>> ON CASCADE DELETE
>> And get this error:
>> Server: Msg 156, Level 15, State 1, Line 4
>> Incorrect syntax near the keyword 'ON'.
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
>> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Just create your foreign key in T-SQL:
>> >
>> > ALTER TABLE your_table DROP CONSTRAINT your_constraint
>> > GO
>> > ALTER TABLE your_table ADD CONSTRAINT your_constraint
>> > FOREIGN KEY (referencing_column)
>> > REFERENCES referred_table (primary_key_column)
>> > ON CASCADE DELETE
>> >
>> >
>> > --
>> > Jacco Schalkwijk
>> > SQL Server MVP
>> >
>> >
>> > "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
>> > wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> >>I have to SQL 2000 Server setups. It seems I have different builds on
>> >>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>> >>working one is 8.00.760. In the problem one I can not setup a cascading
>> >>delete from Enterprise Manager. Anyone know why? Getting the problem
>> >>updated is going to be a bear. Anyone know a work around to add my
>> >>Cascading Delete?
>> >>
>> >> Chris
>> >>
>> >
>> >
>>
>

Cascading Deletes

I have to SQL 2000 Server setups. It seems I have different builds on them.
The problem one is (told to me from SQL Analyzer) 8.00.194 the working one
is 8.00.760. In the problem one I can not setup a cascading delete from
Enterprise Manager. Anyone know why? Getting the problem updated is going
to be a bear. Anyone know a work around to add my Cascading Delete?
Chris
Just create your foreign key in T-SQL:
ALTER TABLE your_table DROP CONSTRAINT your_constraint
GO
ALTER TABLE your_table ADD CONSTRAINT your_constraint
FOREIGN KEY (referencing_column)
REFERENCES referred_table (primary_key_column)
ON CASCADE DELETE
Jacco Schalkwijk
SQL Server MVP
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>I have to SQL 2000 Server setups. It seems I have different builds on
>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>working one is 8.00.760. In the problem one I can not setup a cascading
>delete from Enterprise Manager. Anyone know why? Getting the problem
>updated is going to be a bear. Anyone know a work around to add my
>Cascading Delete?
> Chris
>
|||Ya I tried that too:
ALTER TABLE Holiday ADD CONSTRAINT TestContraint
FOREIGN KEY (FK_CallFlow)
REFERENCES CallFlows (PRI_ID)
ON CASCADE DELETE
And get this error:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ON'.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Just create your foreign key in T-SQL:
> ALTER TABLE your_table DROP CONSTRAINT your_constraint
> GO
> ALTER TABLE your_table ADD CONSTRAINT your_constraint
> FOREIGN KEY (referencing_column)
> REFERENCES referred_table (primary_key_column)
> ON CASCADE DELETE
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>
|||> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
...
on delete cascade
AMB
"Chris, Master of All Things Insignifican" wrote:

> Ya I tried that too:
> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
> And get this error:
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'ON'.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
>
|||Nope, same error message? Anyone know what's going on?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...[vbcol=seagreen]
> ...
> on delete cascade
>
> AMB
> "Chris, Master of All Things Insignifican" wrote:
|||Perhaps the database has compatibility level lower than 80?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote in message
news:%23HU5f$yAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Nope, same error message? Anyone know what's going on?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>

Cascading Deletes

I have to SQL 2000 Server setups. It seems I have different builds on them.
The problem one is (told to me from SQL Analyzer) 8.00.194 the working one
is 8.00.760. In the problem one I can not setup a cascading delete from
Enterprise Manager. Anyone know why? Getting the problem updated is going
to be a bear. Anyone know a work around to add my Cascading Delete?
ChrisJust create your foreign key in T-SQL:
ALTER TABLE your_table DROP CONSTRAINT your_constraint
GO
ALTER TABLE your_table ADD CONSTRAINT your_constraint
FOREIGN KEY (referencing_column)
REFERENCES referred_table (primary_key_column)
ON CASCADE DELETE
Jacco Schalkwijk
SQL Server MVP
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>I have to SQL 2000 Server setups. It seems I have different builds on
>them. The problem one is (told to me from SQL Analyzer) 8.00.194 the
>working one is 8.00.760. In the problem one I can not setup a cascading
>delete from Enterprise Manager. Anyone know why? Getting the problem
>updated is going to be a bear. Anyone know a work around to add my
>Cascading Delete?
> Chris
>|||Ya I tried that too:
ALTER TABLE Holiday ADD CONSTRAINT TestContraint
FOREIGN KEY (FK_CallFlow)
REFERENCES CallFlows (PRI_ID)
ON CASCADE DELETE
And get this error:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ON'.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Just create your foreign key in T-SQL:
> ALTER TABLE your_table DROP CONSTRAINT your_constraint
> GO
> ALTER TABLE your_table ADD CONSTRAINT your_constraint
> FOREIGN KEY (referencing_column)
> REFERENCES referred_table (primary_key_column)
> ON CASCADE DELETE
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com>
> wrote in message news:OXzfERwAFHA.2788@.TK2MSFTNGP15.phx.gbl...
>|||> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
...
on delete cascade
AMB
"Chris, Master of All Things Insignifican" wrote:

> Ya I tried that too:
> ALTER TABLE Holiday ADD CONSTRAINT TestContraint
> FOREIGN KEY (FK_CallFlow)
> REFERENCES CallFlows (PRI_ID)
> ON CASCADE DELETE
> And get this error:
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'ON'.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te
> in message news:evqjAqwAFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
>|||Nope, same error message? Anyone know what's going on?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...[vbcol=seagreen]
> ...
> on delete cascade
>
> AMB
> "Chris, Master of All Things Insignifican" wrote:
>|||Perhaps the database has compatibility level lower than 80?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Chris, Master of All Things Insignificant" <chris@.No_Spam_Please.com> wrote
in message
news:%23HU5f$yAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Nope, same error message? Anyone know what's going on?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:7750E15C-8940-41A5-A88C-6446146737A0@.microsoft.com...
>

Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

Capturing ODBC user name at runtime

Hi:
We are using SRS to report from a DB2 database running on AS400's. It is
working very well.
Pushing ahead, we want to capture information on when certain reports
(actually letters) are generated. The reports prompt for login credentials
at run time. These DB2 database credentials are passed to the database
server and the report returns the data just fine. Works great right out of
the box.
What we would like to do is capture the user name that is passed to the
database. This is not the Windows AD username that can be obtained by the
global field
User!UserID, but the DB2 specific user name that is passed by the ODBC
connection.
Can this be captured at runtime? We have reviewed the RS object model and
haven't found what we are looking for. Are we barking up the wrong tree?
Thanks in advance.
Bruce.
swgAre the credentials parameters?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
> Hi:
> We are using SRS to report from a DB2 database running on AS400's. It is
> working very well.
> Pushing ahead, we want to capture information on when certain reports
> (actually letters) are generated. The reports prompt for login
> credentials
> at run time. These DB2 database credentials are passed to the database
> server and the report returns the data just fine. Works great right out
> of
> the box.
> What we would like to do is capture the user name that is passed to the
> database. This is not the Windows AD username that can be obtained by the
> global field
> User!UserID, but the DB2 specific user name that is passed by the ODBC
> connection.
> Can this be captured at runtime? We have reviewed the RS object model and
> haven't found what we are looking for. Are we barking up the wrong tree?
> Thanks in advance.
> Bruce.
> swg|||Hi:
They are not parameters in the sense that they are built into the report as
parameters.
Instead of saving credentials securely on the report server, I selected the
"The credentials supplied by the user running the report." under the "Connect
Using:" portion of the datasource Properties page.
It is that username that the user enters at runtime that I need to capture.
Thanks.
Bruce.
"Bruce L-C [MVP]" wrote:
> Are the credentials parameters?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
> > Hi:
> >
> > We are using SRS to report from a DB2 database running on AS400's. It is
> > working very well.
> >
> > Pushing ahead, we want to capture information on when certain reports
> > (actually letters) are generated. The reports prompt for login
> > credentials
> > at run time. These DB2 database credentials are passed to the database
> > server and the report returns the data just fine. Works great right out
> > of
> > the box.
> >
> > What we would like to do is capture the user name that is passed to the
> > database. This is not the Windows AD username that can be obtained by the
> > global field
> > User!UserID, but the DB2 specific user name that is passed by the ODBC
> > connection.
> >
> > Can this be captured at runtime? We have reviewed the RS object model and
> > haven't found what we are looking for. Are we barking up the wrong tree?
> >
> > Thanks in advance.
> >
> > Bruce.
> >
> > swg
>
>|||Oh, I see. Sorry. I am not aware of a way to get this via built in
functionality but how about this. It depends on your database but SQL Server
has a function that returns the user:
SELECT 'The current user is: '+ convert(char(30), CURRENT_USER)
If your database has something similar then have a second dataset that uses
the same data source and get the information that way.-- Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:FB08FB96-06B5-4E39-A9DA-4929CFC3629E@.microsoft.com...
> Hi:
> They are not parameters in the sense that they are built into the report
> as
> parameters.
> Instead of saving credentials securely on the report server, I selected
> the
> "The credentials supplied by the user running the report." under the
> "Connect
> Using:" portion of the datasource Properties page.
> It is that username that the user enters at runtime that I need to
> capture.
> Thanks.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> Are the credentials parameters?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:CC286D82-B3E9-48F4-AD5A-ACAB49A19C8B@.microsoft.com...
>> > Hi:
>> >
>> > We are using SRS to report from a DB2 database running on AS400's. It
>> > is
>> > working very well.
>> >
>> > Pushing ahead, we want to capture information on when certain reports
>> > (actually letters) are generated. The reports prompt for login
>> > credentials
>> > at run time. These DB2 database credentials are passed to the database
>> > server and the report returns the data just fine. Works great right
>> > out
>> > of
>> > the box.
>> >
>> > What we would like to do is capture the user name that is passed to the
>> > database. This is not the Windows AD username that can be obtained by
>> > the
>> > global field
>> > User!UserID, but the DB2 specific user name that is passed by the ODBC
>> > connection.
>> >
>> > Can this be captured at runtime? We have reviewed the RS object model
>> > and
>> > haven't found what we are looking for. Are we barking up the wrong
>> > tree?
>> >
>> > Thanks in advance.
>> >
>> > Bruce.
>> >
>> > swg
>>

Capture Return Value from Stored Procedure, Use Same in Code Behind Page

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@.myERROR" then display in the label I have that a duplicate entry was attempted.

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50)

AS

DECLARE @.myERROR int -- local @.@.ERROR
, @.myRowCount int --local @.@.rowcount
BEGIN
-- See if a contact with the same name and zip code exists
IF EXISTS (Select * FROM Info
WHERE ID = @.ID)

BEGIN
RETURN 1
END
ELSE
BEGIN TRAN

INSERT INTO Info(IDNumber, ID, LName,
FName) VALUES (@.IDNumber, @.ID, @.LName,
@.FName)
SELECT @.myERROR = @.@.ERROR, @.myRowCount = @.@.ROWCOUNT
If @.myERROR !=0 GOTO HANDLE_ERROR

COMMIT TRAN
RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @.myERROR

END
GO

asp.net page
<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"
SelectCommand="SELECT * FROM TABLE_One"

UpdateCommand="UPDATE TABLE_One WHERE ID = @.ID"

InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />
</SelectParameters>

</asp:SqlDataSource>

You have to declare it as output parameter in your stored procedure

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50),
@.myERROR int OUTPUT

and just read value of this parameter after you close connection in which you call you SP.

remember to set parameter type as INPUTOUTPUT or OUTPUT when you define parameter in you VB code.

|||

Hi,

I included "@.myERROR" in the stored procedure, but have no idea where or how to read its value. As far as closing the connection in which I call the sp; my sp is called within the SqlDataSource, so how would I read/write the Return Value.

Thank you.

Ayomide

capacity was exceeded

I am getting the following error when I try to run a
remote query, it was working correctly for a while and
Suddenly I started getting this error below:
cannot create new transaction because capacity was exceeded
Why is this happening?
Thanks,
JohnThat doesn't sound like a SQL Server error to me. So, the error is probably
generated by your application/dev tool. You should check that documentation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John|||John,
Is this the message? Are you getting it when calling ADO.Con.BeginTrans()?
Microsoft OLE DB Provider for SQL Server error '8004d01d'
Cannot create new transaction because capacity was exceeded
This appears to be an ADO / OLEDB problem. (One post (from a few years ago)
on Google http://tinyurl.com/ven4 refers to the problem being triggered by
calling a class module which had created an implicit transaction.)
Sorry that I am not able to help more, but perhaps this clue will help.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John

Capacity planning question

I am working on a RFI for a SQL Server 2000 database apllication, I am
looking for some general answer the question below:
Capacity and resource planning for SQL Server 2000
1) resource requirements for 500, 1000, 2000 concurrent users (database,
memory, CPU, etc.)
2) deployment requirements for 500, 1000, 2000 concurrent users (server
configuration, architecture model, etc.)You will probably get very little, except it depends on the transaction, are
they reads, or writes? .. Do they use transaction control or not, how long
are the transactions, etc.
Other than that.
SQL loves memory.
More processors are better (Generally even if they are slower) than fewer
faster processors.
Multi-core processors are good
More on-board cache is good.
Keep your transaction logs mirrored on different drives than your data
Configure disk not only for space but for throughput - you might need more
disk heads to carry the volume, even if you have enough space with fewer
drives.
Just some general guidelines.
--
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.
"George Kwong" wrote:
> I am working on a RFI for a SQL Server 2000 database apllication, I am
> looking for some general answer the question below:
> Capacity and resource planning for SQL Server 2000
> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
> memory, CPU, etc.)
> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> configuration, architecture model, etc.)
>
>|||I will add, that for an installation with those projected sizes and issues,
if you do not bring in someone with adequate experience to assist in the
design, planning, and deployment, you will be making a major mistake.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:O15VKVplGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am working on a RFI for a SQL Server 2000 database apllication, I am
> looking for some general answer the question below:
> Capacity and resource planning for SQL Server 2000
> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
> memory, CPU, etc.)
> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> configuration, architecture model, etc.)
>
>|||We developed the applcation under VB, we are trying to bid on a customer's
job. is there a way to do some test to find out the resource usage?
No, we use very minimum transaction controls. transaction are relative
small, we do both read and writes.
thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> You will probably get very little, except it depends on the transaction,
> are
> they reads, or writes? .. Do they use transaction control or not, how long
> are the transactions, etc.
> Other than that.
> SQL loves memory.
> More processors are better (Generally even if they are slower) than fewer
> faster processors.
> Multi-core processors are good
> More on-board cache is good.
> Keep your transaction logs mirrored on different drives than your data
> Configure disk not only for space but for throughput - you might need more
> disk heads to carry the volume, even if you have enough space with fewer
> drives.
> Just some general guidelines.
> --
> 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.
>
> "George Kwong" wrote:
>> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> looking for some general answer the question below:
>> Capacity and resource planning for SQL Server 2000
>> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
>> memory, CPU, etc.)
>> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
>> configuration, architecture model, etc.)
>>
>>|||Hi George
Are you able to benchmark other customers' installations of your application
& project the performance characteristics from those installations against
the one you're bidding on?
I'd be tracking various perfmon counters & SQL diagnostics for this,
including at least:
Perfmon:
SQLBufferManager counter object, especially Buffer Page Life Expectancy to
determine memory characteristics
CPU Utilisation - collect system wide counter & also the sqlservr process'
CPU utilisation counter
Physical & Logical disk counters - expecially disk bytes read / write p/sec
& disk queues
There are other useful counters, but these are fundamental to pulling
together an informative picture on how your existing installations are
operating under specific hardware specs.
I'd also be taking a close look at how SQL Server is using memory
internally, using dbcc memorystatus to ensure you understand how your
system's using memory.
Performing some SQL Traces might also help you to ensure your application is
well tuned, which is important when drawing benchmark conclusions.
HTH
Regards,
Greg Linwood
SQL Server MVP
"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> We developed the applcation under VB, we are trying to bid on a customer's
> job. is there a way to do some test to find out the resource usage?
> No, we use very minimum transaction controls. transaction are relative
> small, we do both read and writes.
> thanks.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
>> You will probably get very little, except it depends on the transaction,
>> are
>> they reads, or writes? .. Do they use transaction control or not, how
>> long
>> are the transactions, etc.
>> Other than that.
>> SQL loves memory.
>> More processors are better (Generally even if they are slower) than fewer
>> faster processors.
>> Multi-core processors are good
>> More on-board cache is good.
>> Keep your transaction logs mirrored on different drives than your data
>> Configure disk not only for space but for throughput - you might need
>> more
>> disk heads to carry the volume, even if you have enough space with fewer
>> drives.
>> Just some general guidelines.
>> --
>> 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.
>>
>> "George Kwong" wrote:
>> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> looking for some general answer the question below:
>> Capacity and resource planning for SQL Server 2000
>> 1) resource requirements for 500, 1000, 2000 concurrent users (database,
>> memory, CPU, etc.)
>> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
>> configuration, architecture model, etc.)
>>
>>
>|||"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> We developed the applcation under VB, we are trying to bid on a customer's
> job. is there a way to do some test to find out the resource usage?
>
Yes. MS Press had a book on this for SQL 2000 and I assume there is one for
SQL 2005.
> No, we use very minimum transaction controls. transaction are relative
> small, we do both read and writes.
>
Well, fisrt pass, figure, "how many bytes will be read and written" for each
transaction.
How many transactions/sec do you need to cover?
Things like indices may greatly impact that. As will caching.
But first pass, it can give you a sense of stuff like disk I/o which is
generally the slowest part of a system.
If you're reading/writing say 100 bytes/transaction and doing 100/sec, well
you need 10,000 byte throughput on your disks.
This ain't much.
If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
another kettle of fish.
> thanks.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> > You will probably get very little, except it depends on the transaction,
> > are
> > they reads, or writes? .. Do they use transaction control or not, how
long
> > are the transactions, etc.
> >
> > Other than that.
> >
> > SQL loves memory.
> > More processors are better (Generally even if they are slower) than
fewer
> > faster processors.
> > Multi-core processors are good
> > More on-board cache is good.
> > Keep your transaction logs mirrored on different drives than your data
> > Configure disk not only for space but for throughput - you might need
more
> > disk heads to carry the volume, even if you have enough space with fewer
> > drives.
> >
> > Just some general guidelines.
> > --
> > 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.
> >
> >
> > "George Kwong" wrote:
> >
> >> I am working on a RFI for a SQL Server 2000 database apllication, I am
> >> looking for some general answer the question below:
> >>
> >> Capacity and resource planning for SQL Server 2000
> >>
> >> 1) resource requirements for 500, 1000, 2000 concurrent users
(database,
> >> memory, CPU, etc.)
> >> 2) deployment requirements for 500, 1000, 2000 concurrent users (server
> >> configuration, architecture model, etc.)
> >>
> >>
> >>
> >>
>|||it is actually the nature of my program worrys me. because, my program does
not have a complex transaction requirement, but the most significant part of
my program is writing and reading binary data, namely, a photo graph, it is
typically at about 35- 50 k each binary file (it is a jpeg image). this in
term will make all my other data type not significant by comparison
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uw1ZHaylGHA.3752@.TK2MSFTNGP02.phx.gbl...
> "George Kwong" <geokwo@.Lexingtontech.com> wrote in message
> news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
>> We developed the applcation under VB, we are trying to bid on a
>> customer's
>> job. is there a way to do some test to find out the resource usage?
> Yes. MS Press had a book on this for SQL 2000 and I assume there is one
> for
> SQL 2005.
>> No, we use very minimum transaction controls. transaction are relative
>> small, we do both read and writes.
> Well, fisrt pass, figure, "how many bytes will be read and written" for
> each
> transaction.
> How many transactions/sec do you need to cover?
> Things like indices may greatly impact that. As will caching.
> But first pass, it can give you a sense of stuff like disk I/o which is
> generally the slowest part of a system.
> If you're reading/writing say 100 bytes/transaction and doing 100/sec,
> well
> you need 10,000 byte throughput on your disks.
> This ain't much.
> If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
> another kettle of fish.
>
>> thanks.
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
>> > You will probably get very little, except it depends on the
>> > transaction,
>> > are
>> > they reads, or writes? .. Do they use transaction control or not, how
> long
>> > are the transactions, etc.
>> >
>> > Other than that.
>> >
>> > SQL loves memory.
>> > More processors are better (Generally even if they are slower) than
> fewer
>> > faster processors.
>> > Multi-core processors are good
>> > More on-board cache is good.
>> > Keep your transaction logs mirrored on different drives than your data
>> > Configure disk not only for space but for throughput - you might need
> more
>> > disk heads to carry the volume, even if you have enough space with
>> > fewer
>> > drives.
>> >
>> > Just some general guidelines.
>> > --
>> > 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.
>> >
>> >
>> > "George Kwong" wrote:
>> >
>> >> I am working on a RFI for a SQL Server 2000 database apllication, I am
>> >> looking for some general answer the question below:
>> >>
>> >> Capacity and resource planning for SQL Server 2000
>> >>
>> >> 1) resource requirements for 500, 1000, 2000 concurrent users
> (database,
>> >> memory, CPU, etc.)
>> >> 2) deployment requirements for 500, 1000, 2000 concurrent users
>> >> (server
>> >> configuration, architecture model, etc.)
>> >>
>> >>
>> >>
>> >>
>>
>|||"George Kwong" <geokwo@.Lexingtontech.com> wrote in message
news:%23oAEynOmGHA.492@.TK2MSFTNGP05.phx.gbl...
> it is actually the nature of my program worrys me. because, my program
does
> not have a complex transaction requirement, but the most significant part
of
> my program is writing and reading binary data, namely, a photo graph, it
is
> typically at about 35- 50 k each binary file (it is a jpeg image). this in
> term will make all my other data type not significant by comparison
Well, still basically the same. Figure out how often you'll read/write
those images and calculate from there.
BTW, many people prefer to store images in the file system, not the DB.
There's arguments either way.
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:uw1ZHaylGHA.3752@.TK2MSFTNGP02.phx.gbl...
> >
> > "George Kwong" <geokwo@.Lexingtontech.com> wrote in message
> > news:uagkjdtlGHA.4512@.TK2MSFTNGP04.phx.gbl...
> >> We developed the applcation under VB, we are trying to bid on a
> >> customer's
> >> job. is there a way to do some test to find out the resource usage?
> >>
> >
> > Yes. MS Press had a book on this for SQL 2000 and I assume there is one
> > for
> > SQL 2005.
> >
> >> No, we use very minimum transaction controls. transaction are relative
> >> small, we do both read and writes.
> >>
> >
> > Well, fisrt pass, figure, "how many bytes will be read and written" for
> > each
> > transaction.
> >
> > How many transactions/sec do you need to cover?
> >
> > Things like indices may greatly impact that. As will caching.
> >
> > But first pass, it can give you a sense of stuff like disk I/o which is
> > generally the slowest part of a system.
> >
> > If you're reading/writing say 100 bytes/transaction and doing 100/sec,
> > well
> > you need 10,000 byte throughput on your disks.
> >
> > This ain't much.
> >
> > If you're diong 1,000 bytes/transaction and doing 1,000sec, well that's
> > another kettle of fish.
> >
> >
> >> thanks.
> >>
> >>
> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> news:10F4EC48-C9AD-45E3-938B-460A95708CB2@.microsoft.com...
> >> > You will probably get very little, except it depends on the
> >> > transaction,
> >> > are
> >> > they reads, or writes? .. Do they use transaction control or not, how
> > long
> >> > are the transactions, etc.
> >> >
> >> > Other than that.
> >> >
> >> > SQL loves memory.
> >> > More processors are better (Generally even if they are slower) than
> > fewer
> >> > faster processors.
> >> > Multi-core processors are good
> >> > More on-board cache is good.
> >> > Keep your transaction logs mirrored on different drives than your
data
> >> > Configure disk not only for space but for throughput - you might need
> > more
> >> > disk heads to carry the volume, even if you have enough space with
> >> > fewer
> >> > drives.
> >> >
> >> > Just some general guidelines.
> >> > --
> >> > 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.
> >> >
> >> >
> >> > "George Kwong" wrote:
> >> >
> >> >> I am working on a RFI for a SQL Server 2000 database apllication, I
am
> >> >> looking for some general answer the question below:
> >> >>
> >> >> Capacity and resource planning for SQL Server 2000
> >> >>
> >> >> 1) resource requirements for 500, 1000, 2000 concurrent users
> > (database,
> >> >> memory, CPU, etc.)
> >> >> 2) deployment requirements for 500, 1000, 2000 concurrent users
> >> >> (server
> >> >> configuration, architecture model, etc.)
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>