Aliases & Columns Name w/ Spaces

Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?

-

SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq

Kinny:

Try changing your reference from [q.cactus #] to q.[cactus #]

Dave

|||

The brackets are use to encapsulate a name of a table, schema, etc, not the entire messed up name :)

You can even include dots in the name:

create table [dbo].[bad.idea]
(
[bad.idea.id] int
)

select *
from [bad.idea]

And yes, very very bad idea.

Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]

Alert doesn't work on SQL 2005

Hi,
I have trouble getting the alerts to work on SQL 2005.
Below I define a custom message and a custom alert.
When I raise the error though the alert never fires.
Any ideas why that would be?
Thanks,
Patrick
----
USE master
GO
EXEC sp_addmessage 50001, 16, N'The version of SQL Server has been
requested.'
GO
---
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @.name=N'TEST Alert',
@.message_id=50001,
@.severity=0,
@.enabled=1,
@.delay_between_responses=5,
@.include_event_description_in=5,
@.notification_message=N'TEST',
@.category_name=N'[Uncategorized]',
@.job_id=N'00000000-0000-0000-0000-000000000000'
---
RAISERROR (50001, 16, 1)
---On sp_addmessage set @.with_log to true, the default is false.
Hope this helps,
Ben Nevarez
"Patrick" wrote:
> Hi,
> I have trouble getting the alerts to work on SQL 2005.
> Below I define a custom message and a custom alert.
> When I raise the error though the alert never fires.
> Any ideas why that would be?
> Thanks,
> Patrick
> ----
> USE master
> GO
> EXEC sp_addmessage 50001, 16, N'The version of SQL Server has been
> requested.'
> GO
> ---
> USE [msdb]
> GO
> EXEC msdb.dbo.sp_add_alert @.name=N'TEST Alert',
> @.message_id=50001,
> @.severity=0,
> @.enabled=1,
> @.delay_between_responses=5,
> @.include_event_description_in=5,
> @.notification_message=N'TEST',
> @.category_name=N'[Uncategorized]',
> @.job_id=N'00000000-0000-0000-0000-000000000000'
> ---
> RAISERROR (50001, 16, 1)
> ---
>|||On Jan 29, 4:06 pm, Ben Nevarez
<bneva...@.no.spam.please.sunamerica.com> wrote:
> On sp_addmessage set @.with_log to true, the default is false.
> Hope this helps,
> Ben Nevarez
Hi Ben,
thanks that worked. Maybe you would also know why none of my
replication alerts is working.
They are all set up but when I look on the history section of each
alert none of them has ever been called (even though we are using
replication :))
Thanks,
Patricksql

Aggregations...

Here is 2 different version's of practically the same aggregation.

See images below please.

Version 1.) Smaller aggregation file size.

Version 2.) Process Index is twice as fast.

Both version's queries take the same amount of time because it seems even though I create two versions of the aggregation, SSAS creates the same aggregation data set. Any idea whats going on here, there's plus and minus' to both versions...thanks.

Version1: http://img405.imageshack.us/img405/7673/vers1og4.jpg

Version2: http://img255.imageshack.us/img255/2488/vers2mo0.jpg

Hate to bump, but no one knows whats going on here? We are using version 2...it processes much faster so we can create more aggregations.

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

aggregate question

In the below structure, if I wanted to get the Id of the comment for
each Generic record having the latest comment time, how would I do that
not using a subquery?

Table: Generic
Id
Description

Table: Comment
Id
GenericId
CommentTime

Currently I have something like the following:

Select
Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
time comment record*/
From
Generic
INNER JOIN Comment ON Generic.Id = Comment.GenericId
Group By
Generic.Id

To get it, I could do a sub query, using the above query as its source
and joining on the max comment time, but I was wondering if there was a
way to do it without a sub query. Keep in mind that I am looking for a
set of Generic records and not looking for only a single record (so
select top top 1 with order by won't work)You need to take what you have and use it as a subquery as you've
suggested. Other than the subquery, there's no way in SQL to say "give
me the id column from the right table where some other column in the
right table has it's max value".

The reason you can't do that is simple: what id would you get back
from the right table if the maximum value occured in more than one row?|||On 3 Nov 2005 08:41:10 -0800, pb648174 wrote:

>In the below structure, if I wanted to get the Id of the comment for
>each Generic record having the latest comment time, how would I do that
>not using a subquery?
>Table: Generic
>Id
>Description
>Table: Comment
>Id
>GenericId
>CommentTime
>Currently I have something like the following:
>Select
> Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
>time comment record*/
>From
> Generic
> INNER JOIN Comment ON Generic.Id = Comment.GenericId
>Group By
> Generic.Id
>To get it, I could do a sub query, using the above query as its source
>and joining on the max comment time, but I was wondering if there was a
>way to do it without a sub query. Keep in mind that I am looking for a
>set of Generic records and not looking for only a single record (so
>select top top 1 with order by won't work)

Hi pb648174,

You can do this in two ways.

1. Using a correlated subquery (probably the solution you already had in
mind, since you write: "if there was a way to do it without a sub
query", but I'll give it anyway)

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN Comment AS c
ON c.GenericId = g.Id
WHERE c.CommentTime = (SELECT MAX(c2.CommentTime)
FROM Comment AS c2
WHERE c2.GenericId = c.GenericId)

2. Using a derived table. This is a subquery as well, but it's not
correlated, and it's used in the FROM clause, in place of a table or
view name:

SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN (SELECT GenericId, MAX(CommentTime) AS MaxCommentTime
FROM Comment
GROUP BY GenericId) AS c2
ON c2.GenericId = g.Id
INNER JOIN Comment AS c
ON c.GenericId = g.Id
AND c.CommentTime = c2.MaxCommentTime

(Note: both queries are untested - see www.aspfaq.com/5006 if you prefer
a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you had done this right and realized tht ther are
no magical, universal "id' things in RDBMS, would the schema look like
this?

CREATE TABLE Generic
(generic_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);

CREATE TABLE Comments
(generic_id INTEGER NOT NULL
REFERENCES Generic (generic_id)
comment_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- where is the comment??
PRIMARY KEY (generic_id, comment_time));

>> but I was wondering if there was a way to do it without a subquery. <<

No, not unless you move comment time into Gerneric.|||Thanks, Celko. Helpful and on-topic as always.

Aggregate on a Calculated Measure

I am trying to calculate aggregate on a calculate measure. See below for code. I get an error saying I cannot aggregate over calculated member in the measures dimension. I need to calculate MTD and YTD values for a calculated member. Is there any way I can do this?

Any help will be appreciated.

Thanks

Ann

Aggregate

(

PeriodsToDate

(

[Time].[Time].[Month],

[Time].[Time].CurrentMember

),

(

SUM({[Order Type].[Order Type].&[Membership Renewal Order],[Order Type].[Order Type].&[Membership Renewal with Autoship Order],[Order Type].[Order Type].&[New Membership Order],[Order Type].[Order Type].&[New Membership with Autoship Order]},[Measures].[Sale Amount]))

Try replacing the "Aggregate" function in your statement with "SUM". Since you are not attempting to aggregate a "Distinct Count" or some other semi-additive measure this should give you the result you are looking for. If you want create MTD, YTD or other period calculations that work with multiple measures your best bet is to use a "shell dimension" that will allow the creation of members using the "Aggregate" function. You can use the Business Intelligence Wizard to add time calculations, which will automate the process of creating the shell dimension for you. There are some bugs in the current version, but there is a workaround posted at the link below and SP1 includes a fix that addresses the issue.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;912136

HTH,

- Steve

again dead lock

hi i m getting below error.

why i m getting this error...

Transaction(Process ID 61) was deadlockeds on lock resources with another process and has been
chosen as the deadlock victim.Return the transaction.82

below is procedure

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1

) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

--Added By Gopal For HDFC Bounce cases 24-Feb-07
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='83' and drcr ='D' and amount>0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in('82','83') and rtrim(ltrim(drcr)) ='C' and amount<0 and compare_status='Pending'
Update Tbl_CMS_UploadDetails Set amount =-amount where format_id='82' and rtrim(ltrim(drcr)) ='B' and amount>0 and compare_status='Pending'
--Added by Nachiket for failure

Update Tbl_CMS_UploadDetails Set amount =-amount where format_id in ('89','91','86') and amount>0 and compare_status='Pending'

Update Tbl_CMS_UploadDetails Set amount =abs(amount) where format_id in ('85','88','90') and amount<0 and compare_status='Pending'
Declare @.recCount int
select @.recCount=count(*) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set rec_count =@.recCount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id
Declare @.amount decimal
select @.amount=SUM(Amount) from tbl_cms_uploaddetails where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
update tbl_cms_uploadMaster set Amount =@.amount
Where
CMS_Upload_Master_ID=@.Cms_Upload_Master_Id

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Deadlocking occurs because 2 processes are competing for the same resources like this

Process 1 is holding Table A & is waiting for Table B to complete.

Process 2 is holding Table B & is waiting for Table A to complete.

Neither will let go off the resource it has. SQL Server detects this dead-locking and will randomly choose one to kill.

DBCC TRACEON (3650, 1205, -1) will write the details of the 2 processes of the deadlocking into the errorlog each times that it occurs but the error log can grow very fast.

An easy way to avoid deadlocking is to ensure the all processes access tables in the same order (e.g. Customer, Accounts, Transactions or maybe in alphabetical order if this works for you)

regards,

Niall.

|||hey thanx for reply...but how to solve my problem?|||

You need to run the traceon to find out what the other process (involved in the deadlocking) is doing. Then you can figure out which objects are used by both procedures/routines and how the order of access to these objects might differ.

Once you have that, it can be fixed.

Niall.

Advise on Multiple Column Updates

Using SQL2000, is there another way of optimizing the original query below.
TIA,
Bob

Update Transaction
set field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder where
tblOrder.id = Transaction.id),
field2 = (select avg(price) from tblOrder where tblOrder.id =
Transaction.id),
field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)
etc..
(there are six additional updates)

Is this a quicker way:

Update Transaction
SET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,
field3 = min(cost) , etc....
FROM tblOrder
WHERE tblOrder.id = Transaction.idThe format is:
UPDATE <table> SET (<columnlist>) = (SELECT <select_list> FROM ...)
WHERE ...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Advice on table design which will allow me to enforce integrity

Hi,
I have two tables Table A and B, below with some dummy data...

Table A (contains specific unique settings that can be requested)

Id, SettingName
1, weight
2, length

Table B (contains the setting values, here 3 values relate to weight
and 1 to length)

Id, Brand, SettingValue
1, A, 100
1, B, 200
1, null, 300
2, null, 5.3

(There is also a list of Brands available in another table). No primary
keys / referential integrity has been setup yet.

Basically depending upon the Brand requested a different setting value
will be present. If a particular brand is not present (signified by a
null in the Brand column in table B), then a default value will be
used.
Therefore if I request the weight and pass through a Brand of A, I will
get 100
If I request the weight but do not pass through a brand (i.e. null) I
will get 300.

My question is, what kind of integrity can I apply to avoid the user
specifying duplicate Ids and Brands in table B. I cannot apply a
composite key on these two fields as a null is present. Table B will
probably contain about 50 rows and probably 10 of them will be brand
specific. The reason its done like this is in the calling client code I
want to call some function e.g.
getsetting(weight) ... result = 300
Or if it is brand specific
getsetting(weight,A) .... result = 100

Any advice on integrity or table restructuring would be greatly
appreciated. Its sql 2000 sp3.
Thanks
bradAs you have already realized, TableB isn't even in First Normal Form.
Attribute Value models like this are seldom viable and you haven't
stated any excuse not to use the standard, normalized approach:

CREATE TABLE TableB (id INTEGER NOT NULL, brand CHAR(1) DEFAULT 'Z' NOT
NULL REFERENCES Brands (brand), weight INTEGER NOT NULL, length INTEGER
NOT NULL, PRIMARY KEY (id,brand))

Either add the default ("Z" here) brand to the brands table, or if you
prefer not to do that, put the brand-specific info into a separate
table:

CREATE TABLE TableB (id INTEGER NOT NULL, default_weight INTEGER NOT
NULL, default_length INTEGER NOT NULL, PRIMARY KEY (id))

CREATE TABLE TableBBrands (id INTEGER NOT NULL REFERENCES TableB (id),
brand CHAR(1) NOT NULL REFERENCES Brands (brand), weight INTEGER NOT
NULL, length INTEGER NOT NULL, PRIMARY KEY (id,brand))

Personally, I would lean toward the former design. This meets all the
requirements that you have specified as far as I can see.

--
David Portas
SQL Server MVP
--|||i would go with two tables B1(id ,brand , value) with PK (id,brand) and
B2(id,value) PK (id)
but i think that more appropriate solution is to have separate table
for each "setting type" e.g.
WEIGHTS, LENGTHS, VOLUMES where you can have appropriate data type for
each setting

HTH, Strider|||> i think that more appropriate solution is to have separate table
> for each "setting type" e.g.
> WEIGHTS, LENGTHS, VOLUMES

Do you mean separate COLUMNS? Why would you create separate tables for
each attribute?

--
David Portas
SQL Server MVP
--|||I should have also said that Table A will grow in size over time (i.e.
60 different settings), so you can have many settings, i.e. a user
could add additional settings in here, therefore making these as
columns would not be ideal. If possible I would also prefer to not have
to have a dummy brand.

David Portas wrote:
> > i think that more appropriate solution is to have separate table
> > for each "setting type" e.g.
> > WEIGHTS, LENGTHS, VOLUMES
> Do you mean separate COLUMNS? Why would you create separate tables
for
> each attribute?
> --
> David Portas
> SQL Server MVP
> --|||>Why would you create separate tables for
>each attribute?

i don't know, perhaps transient brain disorder ;)|||Why can't you implement the necessary change control procedures to add
columns as the users require them? Allowing users the unfettered
ability to add new "attributes" to a system is a recipe for creating an
unusable mass of redundant and inconsistent data. Do you really expect
users to go through the process of identifying and eliminating
functional dependencies before they add new attributes? On the whole,
users aren't good database architects. At least if they were they would
work with proper tables and columns, constraints and keys - things
which aren't possible under your model.

Your question was how to enforce integrity but I don't see any
integrity in your design at all.

--
David Portas
SQL Server MVP
--|||Yup, I agree all valid points there David. Thanks for your input on
this one.
Brad

David Portas wrote:
> Why can't you implement the necessary change control procedures to
add
> columns as the users require them? Allowing users the unfettered
> ability to add new "attributes" to a system is a recipe for creating
an
> unusable mass of redundant and inconsistent data. Do you really
expect
> users to go through the process of identifying and eliminating
> functional dependencies before they add new attributes? On the whole,
> users aren't good database architects. At least if they were they
would
> work with proper tables and columns, constraints and keys - things
> which aren't possible under your model.
> Your question was how to enforce integrity but I don't see any
> integrity in your design at all.
> --
> David Portas
> SQL Server MVP
> --|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

2) Google EAV and attribute splitting" design flaws before you destroy
your database. It is virtually impossible to have any data integrity
with this "design" becuase of the need to write a complete RDBMS engine
in SQL constraints.

3) The thing falls apart in about one year of operation. That is based
on fixing these things as a consultant.

4) You need help from someone who knows even a little bit about data
modeling. You have not even gottent o the basic normal forms yet.|||[posted and mailed, please reply in news]

(obhayes@.hotmail.com) writes:
> My question is, what kind of integrity can I apply to avoid the user
> specifying duplicate Ids and Brands in table B. I cannot apply a
> composite key on these two fields as a null is present. Table B will
> probably contain about 50 rows and probably 10 of them will be brand
> specific. The reason its done like this is in the calling client code I
> want to call some function e.g.
> getsetting(weight) ... result = 300
> Or if it is brand specific
> getsetting(weight,A) .... result = 100

I would add a dummy primary key to TableB, possibly an IDENTITY column.
When I would add a UNIQUE constraint on (Id, Brand). A UNIQUE constraint
does permit for NULL values, but only one NULL.

(It's a bit amazing that a thread with so many replies did not include
this simple and straigtforward suggestion.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp