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

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 Error in an Insert Statement

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.Blimey - lots of errors :)

1) 2 opening parentheses, 1 closing
2) Fields in the select not separated by commas
3) The area where you have your case statement is where you specify the destination fields. The bit after the select clause is where yuou define your data so...
4) 7 destination fields, 6 source fields

I advise you read INSERT in BoL. Construct your SQL without the case and only include it once you have it working.

HTH

EDIT - 4 is wrong - can't count :o|||That is some pretty messed up syntax. I strongly encourage you to (re-)read the BOL sections on INSERT statements and the CASE function.

This is the basic syntax of an INSERT statement. You can't perform logic in the column list; only in the SELECT clause.

I left out the CASE statement because it is unclear how you want it to work. Do you want it to reference model_ID?
Insert into myTblA
(TblA_ID,
alt_min,
alt_max,
longitude,
latitude)
Select MTB.LocationID,
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB|||Thanks for the input. Sorry about the "incompleteness" of the sql, I was trying to edit it down, in reality it is larger than what I posted.
In ref to the parentheses, they're all there in the original, I just missed putting it in the sample.
The fields in the select are separated by commas, except for the case portion and for information purposes I put commas after each Case statement but it still errors.
I do have a matching number of Select fields for my Insert, again, it's an error on my part when trying to just give the pertinent issues.
Lastly, I did run the sql statement w/o the case statement and it works (yeah, I know, it probably shouldn't but hey, I'm not going to complain).

I'll try to clean up the query and repost it to see if that helps in identifying why the case won't work.
Thanks for the input.|||Here is the sql again, this time I tried to ensure all the basic stuff is correct (i.e. matching selected to inserted, commas, etc).
Insert into operation
(LocationID,
instance_id =
case
when instance_id = 1 then 99861
when instance_id = 2 then 99862
when instance_id = 3 then 99863
when instance_id = 4 then 99864
when instance_id = 5 then 99865
when instance_id = 6 then 99866
when instance_id = 7 then 99867
when instance_id = 8 then 99868
when instance_id = 9 then 99855
when instance_id = 10 then 99839
end,
altitude_minimum,
altitude_maximum,
longitude,
latitude)
(
Select l.Locationid,
(SELECT Equipment.ModelID
FROM Assignment INNER JOIN Equipment ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location ON Assignment.LocationID = Location.LocationID
INNER JOIN Product ON Assignment.AssignmentID = ProductDataFile.AssignmentID),
l.elevation, --alt min
null, --alt max
l.longitude, --longitude
l.latitude --latitude
from Location l
);|||Hi

Point 3 still stands :)|||Hey PootleFlump, I don't think I follow your 3rd point but would like to clarify.
I'm well aware that the case statement is in the destination fields, I don't want to insert the value coming from the Selected Records, I need to change it (hence the case statement). Are you saying to move the case statement down into the Select?

Thanks for any assistance....|||You can't have a CASE statement in an INSERT column list...it needs to be in the SELECT

And since it doesn't make any sense, I don't know how to help|||Does INSTANCE ID = MODEL ID?|||Hey Brett,
Yes it does. Thanks for the input about the Case Statement not being permitted in an Insert. I'll look for another avenue for inserting the records from one table to another.

Thanks!|||This is a flat out, shot in the dark, but it could be what you need:INSERT INTO operation (
LocationID, instance_id, altitude_minimum
, altitude_maximum, longitude, latitude)
Select l.Locationid,
, (SELECT
CASE Equipment.ModelID
WHEN 1 THEN 99861
WHEN 2 then 99862
WHEN 3 then 99863
WHEN 4 then 99864
WHEN 5 then 99865
WHEN 6 then 99866
WHEN 7 then 99867
WHEN 8 then 99868
WHEN 9 then 99855
WHEN 10 then 99839
END
FROM Assignment
INNER JOIN Equipment
ON Assignment.EquipmentID = Equipment.EquipmentID
INNER JOIN EquipmentModel
ON Equipment.ModelID = EquipmentModel.ModelID
INNER JOIN Location
ON Assignment.LocationID = Location.LocationID
INNER JOIN Product
ON Assignment.AssignmentID = ProductDataFile.AssignmentID)
, l.elevation --alt min
, null --alt max
, l.longitude --longitude
, l.latitude --latitude
FROM Location AS l
);-PatP

Case statement Error

Hi,
I don't know what i am doing wrong here, I am always getting this error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
When I am running the following query
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
Could anyone put some light on it.
Thanks
J S"John Smith" <John@.nospam.yahoo.com> wrote in news:ebqEpwfOFHA.1500
@.TK2MSFTNGP09.phx.gbl:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>
Leave out the field name between the CASE and WHEN keywords.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||There are two versions of Case Syntax, and you are "mixing" them...
Ver 1
Case <Expression>
When <ExpressionValue1> Then <OutValue1>
When <ExpressionValue2> Then <OutValue2>
When <ExpressionValue3> Then <OutValue3>
Else <ElseOutValue> End
Version 2
Case
When <BooleanExpression1> Then <OutValue1>
When <BooleanExpression2> Then <OutValue2>
When <BooleanExpression3> Then <OutValue3>
Else <ElseOutValue> End
So you need to eliminate the "price" right after the Case...
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
"John Smith" wrote:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>|||this should work:
SELECT title, price,
Budget = CASE --price <- removed
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
dean
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||Thanks a lot guys, it is working....
J S
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||I removed price from CASE. This passed syntax check for me:
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles

Case statement error

I have an sp that I am trying to run, but it keeps failing. Something like t
his
if a.one='PA' then
begin
select * from a
else
select * from b
end
I keep getting errors. I have tried a CASE statement, but it does not seems
to workIF EXISTS (SELECT one FROM a WHERE one='PA')
SELECT <column_list> FROM a
ELSE
SELECT <column_list> FROM b
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:4A5DC7F7-C8FE-4CA6-9F0D-C5108E871D65@.microsoft.com...
>I have an sp that I am trying to run, but it keeps failing. Something like
>this
> if a.one='PA' then
> begin
> select * from a
> else
> select * from b
> end
> I keep getting errors. I have tried a CASE statement, but it does not
> seems
> to work

CASE statement advice needed

It appears that this query goes into an endless loop. Can someone please identify the error? The 4 tables referenced (CPADF_DT , CPACCDEF , CPARTY, CPDELACC) have less that 2000 records each.

--CP BANK ACCOUNT DEFAULTS
SELECT
CPARTY.NAME AS "COUNTERPARTY" ,
CPACCDEF.TRANS_TYPE AS "TRANSACTION TYPE",
CPACCDEF.SECTYPE AS "INSTRUMENT TYPE",
CPACCDEF.ENTITY AS "ENTITY",
CPACCDEF.FACILITY AS "RELATIONSHIP",
CPACCDEF.CFLOW_TYPE AS "CASHFLOW TYPE",
CPACCDEF.CCY AS "CURRENCY",
CASE CPACCDEF.PAY_REC
WHEN 'B' THEN 'BOTH'
WHEN 'P' THEN 'PAYMENTS'
WHEN 'R' THEN 'RECEIPTS'
ELSE
CPACCDEF.PAY_REC END AS "PAYMENTS/RECEIPTS",
CPADF_DT.EFFECT_DT AS "EFFECTIVE DATE",
CPADF_DT.ACC_NO AS "ACCOUNT NUMBER / IDENTIFIER",
CPDELACC.ACC_NAME AS "ACCOUNT NAME/PAYMENT METHOD", CPDELACC.CCY AS "CURRENCY",
CPDELACC.BANK_NAME AS "BANK NAME"
FROM CPADF_DT , CPACCDEF , CPARTY, CPDELACCHey first of all the join happening here is cross join and hence it will result in the cartesian product of all the 4 tables and I don't think it is the intended behavior.

there should be some joining condition between the tables.

finally it is recommended to use aliases for the tables in the join so that query looks good :)|||Hi,

I think its not going into an endless loop. The thing is that, since no join condition is provided it will be a cartesian product and if there are lot of rows in each of the tables it will take a lot of time to execute the above query.

The solution to ur problem is that have join conditions so that you filter out unwanted rows.sql

CASE Statement

Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
--
Thanks in advance,
sck10You are missing END in the inner CASEs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitive query

I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,SYou'll have to do an ALTER DATABASE to change the collation on the DB.
However, you'll have to change the collation on all character columns to get
the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
I would like to execute query:
select Column1 from table
and I get an error message:
Invalid column name 'Column1'.
If I write query like this:
select column1 from table
than it works.
It looks that server is case sensitive for columns and tables. How can I
change this?
Otherwise I have to rewrite all aplication.
Regards,S|||I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||How exactly did you change the collation? What commands did you use?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
I have already changed collation.
But I still have problems.
If I declare variable in my procedure, for example @.productID and than
somewhere in my procedure I use it, like: set @.productid=1
(not the same case for id), I get an error, because "id" should be "ID".
Any idea?
regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
> You'll have to do an ALTER DATABASE to change the collation on the DB.
> However, you'll have to change the collation on all character columns to
> get
> the intended results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:ug1e6YWXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I would like to execute query:
> select Column1 from table
> and I get an error message:
> Invalid column name 'Column1'.
> If I write query like this:
> select column1 from table
> than it works.
> It looks that server is case sensitive for columns and tables. How can I
> change this?
> Otherwise I have to rewrite all aplication.
> Regards,S
>|||I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||Are you definitely inside the DB when you run the code or are you in master
or tempdb?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
I have changed collation:
ALTER DATABASE dbName COLLATE Slovenian_CI_AS
Queries now works:
select Column1 from table
or
select column1 from table
both works.
But if I declare variable in SP, like :
declare @.id int
and then set the value:
set @.ID=5
I get an error message, that I should declare variable @.ID.
This all is happening because Turkish has different letter for I or i.
But all my procedures are written case insensitive.
What should I do?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
> How exactly did you change the collation? What commands did you use?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23r197xWXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I have already changed collation.
> But I still have problems.
> If I declare variable in my procedure, for example @.productID and than
> somewhere in my procedure I use it, like: set @.productid=1
> (not the same case for id), I get an error, because "id" should be "ID".
> Any idea?
> regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O2R$dgWXGHA.4248@.TK2MSFTNGP05.phx.gbl...
>
>|||I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||How about if you create the proc inside Query Analyzer? I don't trust
Enterprise Manager for most things.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:O8Z9iFkXGHA.196@.TK2MSFTNGP04.phx.gbl...
I'm definitly inside the DB.
I go to database and click: create new stored procedure and then copy my
procedure from other server into this window and then check sintax failes
because of different letters.
Letter i is not the same as letter I on this server. On all other servers
everything works.
Any idea?
Regards,Simon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23ckyr$hXGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Are you definitely inside the DB when you run the code or are you in
> master
> or tempdb?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:%23JU5v9fXGHA.4652@.TK2MSFTNGP04.phx.gbl...
> I have changed collation:
> ALTER DATABASE dbName COLLATE Slovenian_CI_AS
> Queries now works:
> select Column1 from table
> or
> select column1 from table
> both works.
> But if I declare variable in SP, like :
> declare @.id int
> and then set the value:
> set @.ID=5
> I get an error message, that I should declare variable @.ID.
> This all is happening because Turkish has different letter for I or i.
> But all my procedures are written case insensitive.
> What should I do?
> Regards,Simon
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ukR154XXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>

Case sensitive AS server and VBA functions

I use VBA function, e.g. ABS, in an MDX statment, and try to deploy the project to a case-sensitive AS 2005 server -- got and error message "An unexpected exception occured". No such problem when server isn't case sensitive.

Any one came across this problem and has any idea how to overcome this problem?

Thanks.

Try to contact Customer support and report your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||How to connect them? I tried the feedback site, but it's seems no body in MS read it.|||

Try going through http://support.microsoft.com/oas/default.aspx?gprid=2855 .

Navigate to the edition of SQL server you are running.

Which VBA function are you trying to use? Have you tried to spell them with all capital letters?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I tried to use ABS() and VAL(). I tried to spell it ABS, abs, Abs even AbS, aBs, etc. Simply doesn't work. Microsoft doen't care, and customer support costs money. Why should I pay for their Bugs? Hello, MS, somebody at home?

|||

If you willing to share your design, I would be happy to take a look.

Feel free to contact me by removing the "noreply.online." part of my display email.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

CASE Help

I am trying to put a case statement into my Having Clause or Where Clause. Both come up with the same error "Error near '='" in the THEN statement. I'm not sure why this is not working. The idea of this statement is to look at the date and if it is the first of the month it will return last months data, if not it gives me this months data.
If someone could give me and idea as to why this doesn't work or another solution that would be great. Thanks

SELECT TOP 100 PERCENT database.Point_time
FROM database
WHERE CASE WHEN datepart(dd, getdate())=1
THEN(MONTH(database.Point_time) = MONTH(getdate())-1) AND
(YEAR(database.Point_time) = YEAR(getdate()))
ELSE (MONTH(database.Point_time) = MONTH(getdate())) AND
(YEAR(database.Point_time) = YEAR(getdate()))
END
GROUP BY database.Point_time
ORDER BY database.Point_time.SELECT TOP 100 PERCENT
CASE
WHEN datepart(dd, getdate()) = 1 THEN MONTH(databasex.Point_time)-1
ELSE MONTH(databasex.Point_time)
END, (YEAR(databasex.Point_time))

FROM databasex

GROUP BY databasex.Point_time
ORDER BY databasex.Point_time|||That didn't work. It gave me one column with 1 thru 6 multiple times and a second column of just 2006.

This problem has to do with the statement in the THEN part of the CASE statement. SQL doesn't like the = sign. If there is a way to do it without the = sign I think that would work, but don't know how...|||well you can't put the case statment in the WHERE clause - it has to go in the SELECT stmnt, so this way may give you what you want.

if datepart(dd, getdate()) = 1
select TOP 100 PERCENT databasex.Point_time where MONTH(databasex.Point_time) = MONTH(getdate())-1
and YEAR(databasex.Point_time) = YEAR(getdate())
GROUP BY databasex.Point_time
ORDER BY databasex.Point_time

else
select TOP 100 PERCENT databasex.Point_time where MONTH(databasex.Point_time) = MONTH(getdate())
and YEAR(databasex.Point_time) = YEAR(getdate())
GROUP BY databasex.Point_time
ORDER BY databasex.Point_time|||That is infact wrong!

you can put a case statement in the where clause

SELECT TOP 100 PERCENT database.Point_time
FROM database
WHERE database.Point_time =

CASE WHEN datepart(dd, getdate())=1
THEN(MONTH(getdate())-1) AND
(YEAR(getdate()))

CASE ELSE (MONTH(getdate())) AND
(YEAR(getdate()))
END
GROUP BY database.Point_time
ORDER BY database.Point_time|||Sorry I made a mistake by accident in the previouse reply, always check your work. This is the correct way
SELECT TOP 100 PERCENT database.Point_time
FROM database
WHERE database.Point_time =

CASE
WHEN datepart(dd, getdate())=1
THEN(MONTH(getdate())-1) AND
(YEAR(getdate()))
WHEN (MONTH(getdate())) AND
(YEAR(getdate()))
END

GROUP BY database.Point_time
ORDER BY database.Point_time

case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

sql

Cascading Parameters error

I am new to SQL Reporting services - (3 weeks in fact)
I have a report that uses cascading parameters down 2 levels
I select from a dropdown which populates the second dropdown.
I then select from the second drop down list.
There are three more parameters to be entered in by the user - (these
are not drop down lists)
type of test
start date
end date
This runs fine in the test environment
I deploy
then select from my first dropdown.
It crashes out because start date and end date are not entered
It appears that the page is refreshing with all the parameters even
though they are not entered.
Any help is greatly appreciated.
ThanksTry changing the order of your parameters. Put the start and end date
at the beginning.
Or put in defaults. Start and end date of yesterday and today for
example.|||How are you implementing cascading parameters. I have been needing to do
something like this, but do not see how it would be done.
Thanks
John
"Ches Weldishofer" wrote:
> Try changing the order of your parameters. Put the start and end date
> at the beginning.
> Or put in defaults. Start and end date of yesterday and today for
> example.
>|||I am not experienced enough to explain it to you correctly - RS books
online explains it quite well|||Changing the order etc as you say works fine
Thank you very much
Can you recommend a good book for a beginner?

Cascading Deletes

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...
> ...
> 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 Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Delete Error on FK

I'm getting the following error when I try to set the Delete action for a
relationship to Cascade. What I have is 4 tables. The base table, a child1
table, a child2 table, and a child1_mm_child2 table. For each Base object
(row) can have multiple child1 rows and multiple child2 rows. For a Base
object, for each child1 and child2 row there is a child1_mm_child2 row (in
other words an object with 2 child1 rows and 3 child2 rows would have 6
(2*3) child1_mm_child2 rows). What I want to do is when a Base object is
deleted, all related rows in all other 3 tables are deleted, hence the
Cascade On Delete relationships. I'm able to create all but 1 of the
relationships, the final one errors out.
"- Unable to create relationship 'FK_Sources_CIP'.
Introducing FOREIGN KEY constraint 'FK_Sources_CIP' on table 'Sources' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
Thanks for any assistance,
Ryan
Hi Ryan,
Per my understanding, you were trying to create the tables like the
following:
CREATE TABLE [dbo].[Base] (
[BaseID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1] (
[Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child2] (
[Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1_2] (
[Child1ID] [int] NOT NULL ,
[Child2ID] [int] NOT NULL
) ON [PRIMARY]
GO
You wanted to append the cascade delete on the following relations:
FK_BASE_CHILD1
FK_BASE_CHILD2
FK_CHILD1_CHILD1_2
FK_CHILD2_CHILD1_2
The problem was that the last relation failed to be created.
If I have misunderstood, please let me know.
The behavior is expected if your tables were designed like the above. A
logically reasonable design should be that remove the relations
FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the table
Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
For example:
Base:
BaseIDBaseName
1B1
2B2
3B3
Child1:
Child1IDBaseIDChild1Name
11C1
21C2
Child2:
Child2IDBaseIDChild2Name
11D1
22D2
Child1_2:
Child1IDChild2ID
11
12
21
22
This is original design, but it is not reasonable. In this case, when you
execute "delete from Base where BaseID=1", what is your expected result?
Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
deleted, and the row (1,1,D1) in Child2 should be deleted; however should
the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
two rows should not have been existed in the table?
Hope this helps.
If you have any other quesitons or concerns, pleae feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
You have it exactly right. It does look like I will need to add a FK to the
Child1_2 table for Base and apply the cascade delete relationship to that as
well. Thank you for your assistance.
Ryan
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:CJNRLDXVHHA.1580@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ryan,
> Per my understanding, you were trying to create the tables like the
> following:
> CREATE TABLE [dbo].[Base] (
> [BaseID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1] (
> [Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child2] (
> [Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1_2] (
> [Child1ID] [int] NOT NULL ,
> [Child2ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> You wanted to append the cascade delete on the following relations:
> FK_BASE_CHILD1
> FK_BASE_CHILD2
> FK_CHILD1_CHILD1_2
> FK_CHILD2_CHILD1_2
> The problem was that the last relation failed to be created.
> If I have misunderstood, please let me know.
> The behavior is expected if your tables were designed like the above. A
> logically reasonable design should be that remove the relations
> FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the
> table
> Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
> For example:
> Base:
> BaseID BaseName
> 1 B1
> 2 B2
> 3 B3
> Child1:
> Child1ID BaseID Child1Name
> 1 1 C1
> 2 1 C2
> Child2:
> Child2ID BaseID Child2Name
> 1 1 D1
> 2 2 D2
> Child1_2:
> Child1ID Child2ID
> 1 1
> 1 2
> 2 1
> 2 2
> This is original design, but it is not reasonable. In this case, when you
> execute "delete from Base where BaseID=1", what is your expected result?
> Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
> deleted, and the row (1,1,D1) in Child2 should be deleted; however should
> the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
> two rows should not have been existed in the table?
> Hope this helps.
> If you have any other quesitons or concerns, pleae feel free to let me
> know.
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>