Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards."Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000
,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>sql

Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards.
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.
|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>
|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>

Multiple Joins

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
ColinIf there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

If I'm understanding your intention, the following (untested) should work :

select h.*,
d.*,
s.*,
de.*
from Header h left outer join
Detail on h.product = d.product and h.year = d.year inner join
sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week

Perhaps I'm misunderstanding what you're wanting to see...

MULTIPLE INTERSECT in MDX Query - Reg

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

DimUser 101

102

103

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

Transact SQL Query:

Select Count(*) from

(

Select userid from FactPlayer where productId = 01

INTERSECT

Select userid from FactPlayer where productId= 02

)

PlayerCount

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

Hi Vijay,

You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):

select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]

What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:

select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]

HTH,

Chris

|||

Hi Chris,

Thank you very much. It is working perfectly.

Vijay

|||

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

03

DimUser 101

102

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

200601 03 101

Transact SQL Query:

Select Count(*) from

(

Select user from FactPlayer where productId = 01

INTERSECT

Select user from FactPlayer where productId= 02

INTERSECT

Select user from FactPlayer where productId= 03

)

PlayerCount

RESULT: 1 (UserId: 101)

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

|||

I found the solution below.

SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

INTERSECT

(

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[3])

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[11])

)

)

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[12])

)

)

ON ROWS

FROM [DSV KPI]

Please reply me if there any changes in the query.

Thank You

Vijay

sql

multiple inserts in transaction

I am trying to insert into multiple tables and if any of the inserts fail, I
would like to rollback the entire thing.
This approach works but is it a good design'
Please let me know.
declare @.err int
declare @.id1 int
declare @.id2 int
set @.err = -1
begin tran
insert into tbl1(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
SELECT @.id1 = SCOPE_IDENTITY()
if @.err = 0
begin
insert into tbl2(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
SELECT @.id2 = SCOPE_IDENTITY()
end
if @.err = 0
begin
insert into tbl3(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
end
if @.err = 0
begin
commit tran
end
else
begin
rollback tran
RAISERROR('Please check bla bla bla',16,1)
endWhy continue attempting to do work if you know you are going to roll back?
BEGIN TRAN;
INSERT INTO foo(a) SELECT 1;
IF @.@.ERROR <> 0
BEGIN
RAISERROR('foo', 11, 1);
ROLLBACK;
RETURN;
END;
INSERT INTO bar(a) SELECT 2;
IF @.@.ERROR <> 0
BEGIN
RAISERROR('bar', 11, 1);
ROLLBACK;
RETURN;
END;
COMMIT TRAN;
Some people use labels and GOTO, but I'm not overly fond of that structure,
personally... reminds me too much of QBasic.
For a much more thorough treatment of the topic, Erland has a couple of
great articles:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
"sqlster" <nospam@.nospam.com> wrote in message
news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>I am trying to insert into multiple tables and if any of the inserts fail,
>I
> would like to rollback the entire thing.
> This approach works but is it a good design'
> Please let me know.
> declare @.err int
> declare @.id1 int
> declare @.id2 int
> set @.err = -1
>
> begin tran
> insert into tbl1(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
> SELECT @.id1 = SCOPE_IDENTITY()
> if @.err = 0
> begin
> insert into tbl2(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
> SELECT @.id2 = SCOPE_IDENTITY()
> end
>
> if @.err = 0
> begin
> insert into tbl3(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
>
> end
> if @.err = 0
> begin
> commit tran
> end
> else
> begin
> rollback tran
> RAISERROR('Please check bla bla bla',16,1)
> end|||Aaron,
Thank you VERY MUCH..
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why continue attempting to do work if you know you are going to roll back?
> BEGIN TRAN;
> INSERT INTO foo(a) SELECT 1;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('foo', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> INSERT INTO bar(a) SELECT 2;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('bar', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> COMMIT TRAN;
> Some people use labels and GOTO, but I'm not overly fond of that structure
,
> personally... reminds me too much of QBasic.
> For a much more thorough treatment of the topic, Erland has a couple of
> great articles:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>
>|||I agree that GOTO isn't the most elegant solution, but SQL 2000 doesn't have
TRY/CATCH logic, so that's what we're stuck with. I dislike the solution
you've suggested because the error handling code is distributed throughout
the procedure. For two or three inserts, that may not seem to be a problem,
but for a complicated process like posting a batch of inventory
transactions, distributing the error handling introduces a lot of redundant
code. Another reason to consolidate the error handling is that it's much
simpler to unit test the procedure--that is, to step through the error
handling logic--if it exists in a single place. Finally, consistency
simplifies development and maintenance and reduces bugs, so it's better to
use the same mechanism for all procedures--even those with only two or three
inserts.
By the way, there are a couple problems with your code:
(1) change "ROLLBACK" to "IF @.@.TRANCOUNT > 0 ROLLBACK" Of course, this
doesn't take into account transaction savepoints.
(2) change "RETURN" to "RETURN -1"
It should be clear even with this simple example that the changes I
mentioned must be applied throughout the procedure, illustrating the
maintenance problems that can and will occur.
The bottom line is that it cost less to code, test, and maintain procedures
with consolidated error handling code. I use GOTO because it yields better
code for less time and money. I haven't done any production code in SQL
2005 yet, but the TRY/CATCH logic sounds like a promising alternative.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:usuTkajEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> Why continue attempting to do work if you know you are going to roll back?
> BEGIN TRAN;
> INSERT INTO foo(a) SELECT 1;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('foo', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> INSERT INTO bar(a) SELECT 2;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('bar', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> COMMIT TRAN;
> Some people use labels and GOTO, but I'm not overly fond of that
> structure, personally... reminds me too much of QBasic.
> For a much more thorough treatment of the topic, Erland has a couple of
> great articles:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>|||>I agree that GOTO isn't the most elegant solution, but SQL 2000 doesn't
>have TRY/CATCH logic, so that's what we're stuck with. I dislike the
>solution you've suggested because the error handling code is distributed
>throughout the procedure. For two or three inserts, that may not seem to
>be a problem, but for a complicated process like posting a batch of
>inventory transactions, distributing the error handling introduces a lot of
>redundant code.
In my defense, my suggestion was based on the code provided, not for every
situation you could come up with, and was only meant as pseudo-code. My
main thrust was to go read the articles, because Erland has shed a lot more
light on this topic than you or I could in a single thread.
A|||I didn't think your suggestion needed defending. Your code provided a
convenient example to illustrate the benefits of using GOTO. If it's any
consolation, most of the examples in Erland's articles don't include "IF
@.@.TRANCOUNT > 0" either, and the error handling code is also distributed.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23$srncmEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> In my defense, my suggestion was based on the code provided, not for every
> situation you could come up with, and was only meant as pseudo-code. My
> main thrust was to go read the articles, because Erland has shed a lot
> more light on this topic than you or I could in a single thread.
> A
>|||>I didn't think your suggestion needed defending.
Well, you said you disliked it, and attacked it. So... to avoid that
perception in the future, my advice to you is to reply to the original
poster and just provide the helpful part of the advice, instead of adding
the beating to the respondant(s) and peppering that with your own take.

> Your code provided a convenient example to illustrate the benefits of
> using GOTO.
I don't think my code used GOTO at all...

> Erland's articles don't include "IF @.@.TRANCOUNT > 0" either
Well, to be honest, the only time I've ever seen the following type of
example require a @.@.TRANCOUNT check:
BEGIN TRAN
INSERT ...
IF @.@.ERROR <> 0
..
Is if the act of beginning a transaction itself had failed. In which case,
won't the batch abort anyway (at least under default conditions)? How do
you force a BEGIN TRAN to fail anyway? Aside from trying to commit a
transaction involving a linked server, where DTC is not running or
misconfigured, I can't think of one off the top of my head (and I don't
think that would fail on the BEGIN TRAN anyway, but it's too late for me to
bother checking right now anyway). The only time I have ever seen the above
kind of example error out due to a bad @.@.TRANCOUNT is if there are logic
errors in the code (e.g. redundant BEGIN TRAN statements).
I've seen this @.@.TRANCOUNT check used so heavily, and in such simplistic
examples, in the code of a very pedantic co-worker which, ly, I no longer
have to maintain -- because we replaced his slow and bloated codebase with a
completely revamped version (the @.@.TRANCOUNT checks weren't the slow and
bloated part I'm talking about). This anal retentive guy spent so much time
triple-bulletproofing every single line of code that, come delivery time,
much of the functionality was either broken, missing, or so horribly scraped
together that it was useless. He left us with little alternative except a
complete re-write. But he sure checked @.@.TRANCOUNT right after every BEGIN
TRAN, ROLLBACK, and COMMIT, even though the statements surrounding them were
rubbish! :-(
In any event, TRY/CATCH is a very welcome addition to SQL Server 2005,
though it still leads to what you complain about, distributed error
handling. Personally, I think it makes more sense to place error handling
right with the part of the code that errors out, instead of trying to shovel
it off to some common location at the bottom of the code. For one, when I
call a raiserror, the resulting line number lets me go right to the line of
code, instead of havint to retrace my steps. But that's just a preference
and there is no point in attacking it.
A|||Look, if you really want to turn this into a slug fest, then I'm game.

> I don't think my code used GOTO at all...
I didn't say that it did. The distributed nature of the error handling in
your code provided the perfect example of what NOT to do. Any error
handling change requires modifying every block of error handling code
throughout the procedure. Each of those modifications must then be tested.
MY point is that without TRY/CATCH, GOTO is the next best thing because it
serves to consolidate error handling into a single block of code.
I didn't say that you needed to check @.@.TRANCOUNT AFTER any statement. You
SHOULD, however, check it BEFORE a ROLLBACK to avoid raising the error:
Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
I most certainly didn't make the asinine suggestion that @.@.TRANCOUNT should
be checked after a BEGIN TRAN. Where did that come from?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvXE%23ZpEGHA.2912@.tk2msftngp13.phx.gbl...
> Well, you said you disliked it, and attacked it. So... to avoid that
> perception in the future, my advice to you is to reply to the original
> poster and just provide the helpful part of the advice, instead of adding
> the beating to the respondant(s) and peppering that with your own take.
>
> I don't think my code used GOTO at all...
>
> Well, to be honest, the only time I've ever seen the following type of
> example require a @.@.TRANCOUNT check:
> BEGIN TRAN
> INSERT ...
> IF @.@.ERROR <> 0
> ...
> Is if the act of beginning a transaction itself had failed. In which
> case, won't the batch abort anyway (at least under default conditions)?
> How do you force a BEGIN TRAN to fail anyway? Aside from trying to commit
> a transaction involving a linked server, where DTC is not running or
> misconfigured, I can't think of one off the top of my head (and I don't
> think that would fail on the BEGIN TRAN anyway, but it's too late for me
> to bother checking right now anyway). The only time I have ever seen the
> above kind of example error out due to a bad @.@.TRANCOUNT is if there are
> logic errors in the code (e.g. redundant BEGIN TRAN statements).
> I've seen this @.@.TRANCOUNT check used so heavily, and in such simplistic
> examples, in the code of a very pedantic co-worker which, ly, I no
> longer have to maintain -- because we replaced his slow and bloated
> codebase with a completely revamped version (the @.@.TRANCOUNT checks
> weren't the slow and bloated part I'm talking about). This anal retentive
> guy spent so much time triple-bulletproofing every single line of code
> that, come delivery time, much of the functionality was either broken,
> missing, or so horribly scraped together that it was useless. He left us
> with little alternative except a complete re-write. But he sure checked
> @.@.TRANCOUNT right after every BEGIN TRAN, ROLLBACK, and COMMIT, even
> though the statements surrounding them were rubbish! :-(
> In any event, TRY/CATCH is a very welcome addition to SQL Server 2005,
> though it still leads to what you complain about, distributed error
> handling. Personally, I think it makes more sense to place error handling
> right with the part of the code that errors out, instead of trying to
> shovel it off to some common location at the bottom of the code. For one,
> when I call a raiserror, the resulting line number lets me go right to the
> line of code, instead of havint to retrace my steps. But that's just a
> preference and there is no point in attacking it.
> A
>|||> I didn't say that it did. The distributed nature of the error handling in
> your code provided the perfect example of what NOT to do.
I think you're talking about a preference here, not an everyone-must-do-it
best practice.

> I didn't say that you needed to check @.@.TRANCOUNT AFTER any statement.
> You SHOULD, however, check it BEFORE a ROLLBACK to avoid raising the
> error:
> Server: Msg 3903, Level 16, State 1, Line 1
> The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
In the example I provided, please tell me how you can reach that path.
Because remember, you said you didn't like *my* example because I didn't use
@.@.TRANCOUNT. The logic in the piece of code I posted was so simple that the
above error message would be impossible to reach.|||You may be free to do as you please. I have to minimize development and
maintenance cost. Since development projects generally have budgets
attached, there are few who are free to do as they please. I think that
we'll both agree that it is a universal BAD practice to release code that
has not been thoroughly tested. This means that every path in the code must
be touched during testing. Your solution--distributing error handling
code--requires more test cases, more programming time, and more testing
time. In fact, the amount of development time for distributed error
handling code increases linearly along with the number of instances of
duplicate code, whereas the amount of development time when GOTO is used
remains flat. In addition, a change to the error handling code (for
example, if the need arises to rollback to a savepoint) requires that every
instance of duplicate code must be modified and tested--and again cost
increases linearly. The bottom line is that it costs less to use GOTO.
Because it costs less, I would consider that an everyone-must-do-it best
practice.
I said that I dislike your SOLUTION: distributing error handling code. I
acknowledge the point that "IF @.@.TRANCOUNT > 0" may not be needed in your
simplistic example. Since I consolidate error handling code, I always check
@.@.TRANCOUNT before issuing a ROLLBACK. It may add a few unnecessary CPU
cycles, but if I later add a call to another stored procedure, I don't have
to worry about forgetting to change the block of error handling code. All I
need to do is to make sure that control is redirected there whenever an
error occurs. That's one of the benefits of code reuse.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueOfQx$EGHA.472@.TK2MSFTNGP12.phx.gbl...
> I think you're talking about a preference here, not an everyone-must-do-it
> best practice.
>
> In the example I provided, please tell me how you can reach that path.
> Because remember, you said you didn't like *my* example because I didn't
> use @.@.TRANCOUNT. The logic in the piece of code I posted was so simple
> that the above error message would be impossible to reach.
>

Multiple Inserts in the Same Stored Procedure

Hey,
I have a project that I have been working on and I need to insert a
record into multiple tables and I if any one of the inserts fails I
need to rollback all of the previous inserts that were done. To
illustrate, I have ten tables that need to have a record inserted into
them and if it errors out on table six, then I want to rollback the
previous five inserts. I'd appreciate any advice I can get. Thanks.bradley.d.walker@.gmail.com wrote:
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>
BEGIN TRANSACTION
INSERT Table1 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table2 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table3 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table4 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
...
...
...
...
...
...
COMMIT TRANSACTION|||With SQL 2000 -SQL 2005 is a bit 'simplier' in manifestation - but the idea
is the same.
BEGIN TRANSACTION
INSERT INTO into table1 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
INSERT INTO into table2 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
{etc.}
COMMIT TRANSACTION
RETURN
There are variations to the idea, using GOTO to the end of the sproc and ROL
LBACK from there.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<bradley.d.walker@.gmail.com> wrote in message news:1151077294.100001.43410@.g10g2000cwb.goog
legroups.com...
> Hey,
>
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||To add to the other responses, consider adding SET XACT_ABORT ON to the
beginning of your proc. This will ensure a transaction rollback occurs in
the case of a client-initiated attention event (e.g. query timeout or
cancel).
Hope this helps.
Dan Guzman
SQL Server MVP
<bradley.d.walker@.gmail.com> wrote in message
news:1151077294.100001.43410@.g10g2000cwb.googlegroups.com...
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||>> I have a project that I have been working on and I need to insert a record [si
c] into multiple tables and I if any one of the inserts fails Ineed to rollb
ack all of the previous inserts that were done. <<
Easy enough; set up a series of INSERT INTO's in a single transaction,
trap each insertion's error and do a ROLLBACK and return if you have a
failure. Do not commit until the end of the whole thing.
The scope of transactions in T-SQL is independent of the block
structure of the language. Think of a "transaction guy" with a bucket
of data looking at a house. The house pumps data into his bucket. He
does not care what is happening inside; he is waiting to see a COMMIT
or ROLLBACK flag come out of the window of the house. At that point,
he either throws the data out or throws it in the database.
But a better question why do you want to store the same data in
multiple tables? The major reason we moved from files to RDBMS was to
get rid of redundancy -- the mantra is "one fact, one time, one way,
one place!" and not "Let's make ten copies and try to keep them all the
same!" Instead of making ten copies of a mag tape with the same data,
we use VIEWs, CTE, and derived tables in SQL.
You did know that a row is not a record in your posting or understand
transactions, makes me wonder if your schema is messed up because you
are mimicing files.|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> To add to the other responses, consider adding SET XACT_ABORT ON to the
> beginning of your proc. This will ensure a transaction rollback occurs in
> the case of a client-initiated attention event (e.g. query timeout or
> cancel).
Very interesting! I did not know about this. This can be a quick fix
for applications that suffers from unhandled query timeouts.
Why did you not tell me this before? :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
We did exactly that recently for one of our ADO middle-tier database
application with persistent db connections. All transactions were done in
stored procedure code (no nested procs) and the application performed no
explicit transaction handling and no special database exception handling.
Database errors were logged but the app moved on, oblivious to the
ramifications.
Everything was fine until a nightly job was accidentally run during the day
and that caused blocking and subsequent command timeouts. Because the app
performed no connection cleanup after the exception, a transaction that was
in progress remained open after the timeout. All subsequent work done on a
problem connection was done in the context the open transaction and was
never committed!
In addition to adding XACT_ABORT ON quick fix, I asked the developers to
close and re-open persistent connections following any type of database
exception.

> Why did you not tell me this before? :-)
I got the idea to try XACT_ABORT ON after perusing your error handling
articles. Somehow, I got it in my mind that this technique was covered
there ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97ED135AF6FCYazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
> Why did you not tell me this before? :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Everything was fine until a nightly job was accidentally run during the
> day and that caused blocking and subsequent command timeouts. Because
> the app performed no connection cleanup after the exception, a
> transaction that was in progress remained open after the timeout. All
> subsequent work done on a problem connection was done in the context the
> open transaction and was never committed!
A really fine mess! Yes, we have experienced this in our application as
well, although that's many years behind us now. I wonder how much the
default timeout of 30 seconds have cost enterprises over the worldin
money and misery.

> In addition to adding XACT_ABORT ON quick fix, I asked the developers to
> close and re-open persistent connections following any type of database
> exception.
While better than nothing, it's not optimal, unless you already have
turned off connection pooling. Of course, if you close and reconnect
directly, and get back the same physical connection directly, everything
will be cleaned up on the spot. But if the pool gives you a different
connection, it could take 60 seconds before the rollback occurs, when
the API actually closes the connection.

> I got the idea to try XACT_ABORT ON after perusing your error handling
> articles. Somehow, I got it in my mind that this technique was covered
> there ;-)
In that case, I don't know that I write in my articles myself. But I
will have to add it!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

multiple insert

hi friends,

i am having a problem here. I am using msde and i have a database with 3 tables.

Here is what i want to do.

1- I want to insert posted data to table 1 ( i can do this step)

2- I want to insert posted data to table 2 ( i can do this step, too)

3- I want to insert the primary keys of the rows inserted to table 1 and table 2 , to table 3 . I dont know anything about stored procedures . Can i do this in my code-behind using C# ? If you can show me the way i will be glad.
Thanks in advance

Using a stored procedure would be best. Assuming that you'reusing IDENTITY columns for your primary keys, your basic logic will besomething along the lines of:

DECLARE @.tbl1ID INT
DECLARE @.tbl2ID INT
INSERT Tbl1
(column list)
VALUES
(values)
SET @.tbl1ID = SCOPE_IDENTITY()

INSERT Tbl2
(column list)
VALUES
(values)
SET @.tbl2ID = SCOPE_IDENTITY()
INSERT Tbl3
(Tbl1ID, Tbl2ID)
VALUES
(@.tbl1ID, @.tbl2ID)
|||

thank you adam for you quick reply,
stored procedure way seems not that difficult but i dont know anythink about them. Where do you write the stored procedures, how do you run them , how do you call them from aspx.cs file, how do you reach the output variables from aspx.cs file ?
if you can tell me the steps i will be glad.
thanks in advance

|||I think you need to Google for some SQL Server introductoryarticles. Try www.sqlteam.com and www.sqlservercentral.com. It's a bit much to discuss in a forum post.

Multiple flat files to multiple tables

Hi,

I have searched but not found quite the best way to look at this so far..

I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.

In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.

Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?

Just wondering if there is a neater way of doing this?

It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.

Hope that explains ok and thanks in advance.

Mike

Since each source file and each destination table has its own schema, I don't know of any better way than what you describe.

Personally I would rather see 30 packages, each with one data flow, rather than one package with 30 data flows, but that's largely a personal preference, not a hard and fast rule or anything...

|||Thanks for the response - hadn't considered a package for each so will take a look at that|||The advantage that I see in the "one package per destination table" approach is that it makes ongoing maintenance much simpler. For smaller projects it seems like overkill, but it serves me well...

Multiple flat file import to corresponding SQL server tables

We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.

Thank you very much,

Regards,

Srinivas Raveendra

I am also facing the same problem.|||The simplest way I can see that is to create a package per every file-table destination pair. Each package could have a ForEach loop to process all files that have the same structure.|||

The way we have accomplished this task is to have a table that holds the names of all the files and the names of the tables each file is loaded into.

The SSIS package reads all the rows from this table and runs a foreach loop container for each record; The looping process firstly truncates the staging table and then bulk inserts based on the variable values for each iteration of the loop container. the syntax for the Bulk Insert would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv''')

If the files you are importing are not exactly the same format as the tables you are importing them into. ie the columns are in a different order or there are more/less columns in the file than in the table, you will need to have format files set up and the syntax with the format file would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv'' WITH (FORMATFILE=''' + @.strFormatDir + @.strFormat + '.fmt'')')

As a note we have the variables @.strFileDir and @.strFormatDir set at the package level, and dynamically controlled through a package configuration.

For more information on Bulk Insert see Books Online: http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Hopefully this helps.

sql

Multiple FKs of the same PK problem!

Hi SQL folks,

I have a fuzzy problem with my application,

I'm building a small internal messaging system. I have two tables: SysUsers and mail tables.

I need to select both the "mailFrom" and "mailTo" from the mail table - both of them is a uniqueidenifier and are FK from the UserId in the SysUsers table- , and then get the correspoding UserName from the SysUsers table, how can I do that?

Here is my query:

/*Get the UserName of both the Sender and Reciever*/
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo, SysUsers.UserName
FROM mail INNER JOIN
SysUsers ON mail.mailTo= SysUsers.UserId
AND mail.mailFrom= SysUsers.UserId

/*Get only the current users mails*/
WHERE (SysUsers.UserId = @.UserId)

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

|||

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

|||

jeroenm:

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

Hi jepenm,

Thanks for you responce :)

|||

TATWORTH:

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

Hi TATWORTH,

Thanks for your response

Multiple Files -- DTS

I have a rather large sale transaction DB. Basic header, and detail tables. I am providing a third party company with daily sales information, and I need to give them back data from about 8 or 9 months ago. I currently have a DTS package that gets sales for the current day, but since I have to go back, I have to manually edit the query in the DTS package, and change the date range...UNLESS ...

Blah, blah, blah. The problem is that they can only take the data in Daily files. So, there would be ONE file for each day. I really don't need to be manually running these jobs, so I'm wondering if someone could point me to a way of writing a package (maybe ActiveX, not sure) that would run through a loop, basically, of dates, and create a seperate file for each day. Versus having to edit a generic DTS package, and changing the date range 350 times...would it be an option to select the current daterange from a table. Then, as a last step, update daterange to the next (or have a daterange column and another column that acts as a 'done this one' and update that column)? If the package simply exports the table contents you could also consider creating a dynamic sp but I am not sure if that's an option for you.|||That does make sense, but I am not sure how I would go about "looping" through the Daterange, and then creating a file for each day based on the query that is in the one DTS package.|||This is what I've done, and it's led me to another question:

@.Date1=(SELECT BeginDate FROM DateRangeTable)
@.Date2=(SELECT EndDate FROM DateRangeTable)

...SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2

UPDATE DateRange SET Loaded='YES' WHERE BeginDate=@.Date1

EXEC master..xp_cmdshell 'REN I:\DailySales ' + @.Date1

--

By itself, the EXEC xp_cmdshell runs just fine. However, when I include The Main query, It doesn't work at all with NO errors...anyone know what gives?|||Does it have to be formatted a certain way? You could certainly do it in ActiveX, or simply use xp_cmdshell to execute BCP:

EXEC master..xp_cmdshell 'bcp ' + 'SELECT BLAH, BLAH...WHERE Date BETWEEN @.Date1 AND @.Date2'|||Yes, the DTS Package has a certain format that I haven't been able to figure out how to match with bcp.|||I haven't worked with BCP for sometime, but it does have a parameter for a "format file". If you can't get BCP to format it the way you want, you can do it in ActiveX.

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors you
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:
> I have another question. I took a Full Backup of the database before adding
> the secondary filegroup(single filegroup database) , the backup file size is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. The
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> > Hi Everyone,
> >
> > I added a secondary filegroup to a production database and moved some
> > tables to it from the Primary Filegroup.In the process I changed the
> > database recovery model to Simple and back to Full,backed up the
> > Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> > database recovery model is Full at the moment. I have noticed 2 oddities:
> >
> > 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> > MB a day,the database is being used as usual)
> >
> > 2. sp_spaceused and Enterprise Manager Console report strange values for
> > database available space:
> >
> > db_name size unallocated_space
> > DB_LIVE 55812.06 MB -4257.63 MB
> >
> > reserved data index_size
> > unused
> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >
> >
> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> > or is there somethig else I have to do?
> >
> > Best Regards.
> >
> >
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>> I have another question. I took a Full Backup of the database before
>> adding
>> the secondary filegroup(single filegroup database) , the backup file size
>> is
>> about 55,4 GB.
>> Today I backed up the Primary Filegroup and the newly added Filegroup1.
>> The
>> backup file size for the Primary filegroup is about 45 GB and backup file
>> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
>> GB.
>> As far as I know no data has been deleted. Is there any explanation
>> regarding the cause of the total size reduction ?
>> Regards.
>> Sezgin Rafet
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>> > Hi Everyone,
>> >
>> > I added a secondary filegroup to a production database and moved some
>> > tables to it from the Primary Filegroup.In the process I changed the
>> > database recovery model to Simple and back to Full,backed up the
>> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
>> > The
>> > database recovery model is Full at the moment. I have noticed 2
>> > oddities:
>> >
>> > 1. The Transaction Log appears to grow much slower than before(only 2 -
>> > 5
>> > MB a day,the database is being used as usual)
>> >
>> > 2. sp_spaceused and Enterprise Manager Console report strange values
>> > for
>> > database available space:
>> >
>> > db_name size unallocated_space
>> > DB_LIVE 55812.06 MB -4257.63 MB
>> >
>> > reserved data index_size
>> > unused
>> > 61507528 KB 61203424 KB 291632 KB 12472 KB
>> >
>> >
>> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
>> > or is there somethig else I have to do?
>> >
>> > Best Regards.
>> >
>> >
>>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is hard
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you then
have the choice of having different indexes on the load table to the archived
data; you would also be able to build the indexes on the archived data at the
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:
> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties ->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older data
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This may
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> > Hi
> >
> > You don't say how you moved the tables, I assumed that you re-created the
> > clustered index in the new filegroup, in which case it would have been
> > re-built. If you had not defragged the index for a while then this may be
> > where the space has been found. You may want to look at what fill factors
> > you
> > are using or whether the clustered index is suitable.
> >
> > John
> >
> > "Sezgin Rafet" wrote:
> >
> >> I have another question. I took a Full Backup of the database before
> >> adding
> >> the secondary filegroup(single filegroup database) , the backup file size
> >> is
> >> about 55,4 GB.
> >> Today I backed up the Primary Filegroup and the newly added Filegroup1.
> >> The
> >> backup file size for the Primary filegroup is about 45 GB and backup file
> >> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
> >> GB.
> >> As far as I know no data has been deleted. Is there any explanation
> >> regarding the cause of the total size reduction ?
> >>
> >> Regards.
> >>
> >> Sezgin Rafet
> >>
> >>
> >>
> >> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> >> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> >> > Hi Everyone,
> >> >
> >> > I added a secondary filegroup to a production database and moved some
> >> > tables to it from the Primary Filegroup.In the process I changed the
> >> > database recovery model to Simple and back to Full,backed up the
> >> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
> >> > The
> >> > database recovery model is Full at the moment. I have noticed 2
> >> > oddities:
> >> >
> >> > 1. The Transaction Log appears to grow much slower than before(only 2 -
> >> > 5
> >> > MB a day,the database is being used as usual)
> >> >
> >> > 2. sp_spaceused and Enterprise Manager Console report strange values
> >> > for
> >> > database available space:
> >> >
> >> > db_name size unallocated_space
> >> > DB_LIVE 55812.06 MB -4257.63 MB
> >> >
> >> > reserved data index_size
> >> > unused
> >> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >> >
> >> >
> >> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> >> > or is there somethig else I have to do?
> >> >
> >> > Best Regards.
> >> >
> >> >
> >>
> >>
> >>
>
>

multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

Multiple fact tables in SSAS 2005

Can someone please provide me with a link to an article, which explains the concept of multiple fact tables in SSAS 2005?

Thanks in advance,

Saurav

Are you talking about multiple fact tables as base for partitions or different measure group. In other words, are your fact tables have exactly the same structure? Or the structure is different and every fact table has it's own set of key columns?

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

|||

I do not have a link to an article.

If you are aware of the design in AS2000 you know that you can use one fact table in each cube. This is referred as a physical cube because it has an fact table tied to it.

On the other hand you can join two or more cubes(physical) in virtual cubes.

In AS2005(or SSAS2005) a cube can include one or many fact tables and each fact table becomes a measure group in this cube.

The concept of virtual cubes have disappeared in SSAS2005 but you can say that a cube with many fact tables(more than one) is a virtual cube in the old AS2000 meaning.

Most recent findings and post regarding using more than one fact table / measure group in SSAS2005 say that you should be careful about this.

Regards

Thomas Ivarsson

Multiple Fact Tables

I have two fact tables in a cube...Both fact tables have plenty of measures, some even have the same measures but are name differently (ex. Bad Phone Number, Number of Bad Phones). When I process the cube, only the measures in the most recent fact table (A) show up. The oldest fact table does have some measures to show up in the cube but I was expecting that all the measure in both fact tables would show up. Both fact tables are connected to the same dimensions. Any ideas?

So when you open up the cube in BIDS, can you see all the measures you expect in the list on the top left hand side? If not you can drag the columns you want from the fact tables in the diagram in the centre pane to the measures list. If you cannot even see these columns in the diagram then you must have altered your table structures after building your DSV and you will need to open up your DSV and refresh it before going back into the cube designer to create the measures.

Hope this helps.

|||

Darren, thanks a bunch this worked

Multiple Deletions From Different Tables in SQL Server Trigger

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @.AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDischarge Table
DELETE FROM tblProgramDischarge
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignment Table
DELETE FROM tblRoomAssignment
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySurvey Table
DELETE FROM tblUnitedWaySurvey
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @.AdmissionIDDoes it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:
> I have written a trigger that's supposed to go out and delete
> corresponding records from multiple tables once I delete a specific
> record from a table called tblAdmissions.
> This does not work and I'm not sure why...
> Here's the code that's supposed to run, let's say, if a user (via a
VB
> 6.0 interface) decides to delete a record. If the record in the
> tblAdmissions table has the primary key (AdmissionID) of "123", then
> the code below is supposed to search other tables that have related
> information in them and also have an AdmissionID of "123" and delete
> that information as well.
> Any ideas? Here's the code:
> CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
> -- and here is the table name
> ON tblAdmissions
> -- the operation type goes here
> FOR DELETE
> AS
> -- I just need one variable this time
> DECLARE @.AdmissionID int
> -- Now I'll make use of the deleted virtual table
> SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)
> -- And now I'll use that value to delete the data in
> -- the tblASIFollowUp Table
> DELETE FROM tblASIFollowUp
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblTOADS Table
> DELETE FROM tblTOADS
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @.AdmissionID|||What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp.admissionid)

... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--|||On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

>This does not work and I'm not sure why...

Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?

>Any ideas? Here's the code:
(snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:

>SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

First, the second @. should be left out. This will simply set the variable
@.AdmissionID equal to itself.

But if you change it to

SELECT @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @.AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @.AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.AdmissionID = tblASIFollowUp.AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarges
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.

multiple deletes cross tables

Hi,
I have a hierarchy of tables, like "Company" on top, with "Offices"
underneath and "People" underneath those and "Contact info" under those,
etc.
Now, a record gets deleted in the "Company" table. This means the Offices
and People attached to them get deleted also.
I could do this via triggers to ensure consistency, or via a stored
procedure:
CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
{
-- delete other stuff first belonging to the company
DELETE FROM Stuff WHERE ParentId = @.Id
-- delete offices first
EXEC myspDeleteOffices( @.Id );
-- delete self
DELETE FROM Companies WHERE Id=@.Id
}
CREATE PROCEDURE myspDeleteOffices ( @.ParentId )
{
-- delete people first
DELETE FROM People WHERE ParentId IN (SELECT Id FROM Companies WHERE
ParentId = @.Id)
-- delete offices
DELETE FROM Companies WHERE ParentId = @.Id
}
Triggers would be better, but I need to use procedures because I am not
really deleting records, rather setting up a flag Deleted=1. So all DELETE
statements are actually UPDATE <record> SET Deleted=1. This gets tricky when
using triggers. So let's forget about it.
Questions:
- myspDeleteOffices needs to delete people. For this I have to list all
offices first and then delete people that work at that office. Would a
cursor not be better?
- More importantly, deleting people or offices could fail.. in which case
things from the Stuff table may have already been deleted. To roll these
changes back, so that either everything gets deleted (all steps succeed) or
everything gets restored, what should I do? Use transactions?
Something like this?
CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
{
BEGIN TRANSACTION
-- delete other stuff first belonging to the company
DELETE FROM Stuff WHERE ParentId = @.Id
-- delete offices first
EXEC myspDeleteOffices( @.Id );
-- delete self
DELETE FROM Companies WHERE Id=@.Id
COMMIT TRANSACTION
}
If an error occurs, does the transaction get rolled back automatically
without me calling ROLLBACK TRANSACTION ?
Usually when an error occurs, the query aborts, so I wouldn't be able to
call ROLLBACK after that.
Just have no clue how this is done in real life situations.
LisaLisa
Have you considered using INSTEAD OF DELETE Triggers?
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23twTI99SFHA.1896@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a hierarchy of tables, like "Company" on top, with "Offices"
> underneath and "People" underneath those and "Contact info" under those,
> etc.
> Now, a record gets deleted in the "Company" table. This means the Offices
> and People attached to them get deleted also.
> I could do this via triggers to ensure consistency, or via a stored
> procedure:
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> }
> CREATE PROCEDURE myspDeleteOffices ( @.ParentId )
> {
> -- delete people first
> DELETE FROM People WHERE ParentId IN (SELECT Id FROM Companies WHERE
> ParentId = @.Id)
> -- delete offices
> DELETE FROM Companies WHERE ParentId = @.Id
> }
> Triggers would be better, but I need to use procedures because I am not
> really deleting records, rather setting up a flag Deleted=1. So all DELETE
> statements are actually UPDATE <record> SET Deleted=1. This gets tricky
when
> using triggers. So let's forget about it.
> Questions:
> - myspDeleteOffices needs to delete people. For this I have to list all
> offices first and then delete people that work at that office. Would a
> cursor not be better?
> - More importantly, deleting people or offices could fail.. in which case
> things from the Stuff table may have already been deleted. To roll these
> changes back, so that either everything gets deleted (all steps succeed)
or
> everything gets restored, what should I do? Use transactions?
> Something like this?
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> BEGIN TRANSACTION
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> COMMIT TRANSACTION
> }
> If an error occurs, does the transaction get rolled back automatically
> without me calling ROLLBACK TRANSACTION ?
> Usually when an error occurs, the query aborts, so I wouldn't be able to
> call ROLLBACK after that.
> Just have no clue how this is done in real life situations.
> Lisa
>|||You can do that with DRI (declaritive referential integrity) where you can
modify (Delete,Update) the "parent" which automatically takes affect on all
"children" referenced to this.
Example:
ALTER TABLE [dbo].[Order Details] ADD
CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23twTI99SFHA.1896@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a hierarchy of tables, like "Company" on top, with "Offices"
> underneath and "People" underneath those and "Contact info" under those,
> etc.
> Now, a record gets deleted in the "Company" table. This means the Offices
> and People attached to them get deleted also.
> I could do this via triggers to ensure consistency, or via a stored
> procedure:
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> }
> CREATE PROCEDURE myspDeleteOffices ( @.ParentId )
> {
> -- delete people first
> DELETE FROM People WHERE ParentId IN (SELECT Id FROM Companies WHERE
> ParentId = @.Id)
> -- delete offices
> DELETE FROM Companies WHERE ParentId = @.Id
> }
> Triggers would be better, but I need to use procedures because I am not
> really deleting records, rather setting up a flag Deleted=1. So all DELETE
> statements are actually UPDATE <record> SET Deleted=1. This gets tricky
> when using triggers. So let's forget about it.
> Questions:
> - myspDeleteOffices needs to delete people. For this I have to list all
> offices first and then delete people that work at that office. Would a
> cursor not be better?
> - More importantly, deleting people or offices could fail.. in which case
> things from the Stuff table may have already been deleted. To roll these
> changes back, so that either everything gets deleted (all steps succeed)
> or everything gets restored, what should I do? Use transactions?
> Something like this?
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> BEGIN TRANSACTION
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> COMMIT TRANSACTION
> }
> If an error occurs, does the transaction get rolled back automatically
> without me calling ROLLBACK TRANSACTION ?
> Usually when an error occurs, the query aborts, so I wouldn't be able to
> call ROLLBACK after that.
> Just have no clue how this is done in real life situations.
> Lisa
>|||Lisa,
Its Ok if you are deleteing or Updating, you can use a trigger for the
purpose. This is what i believe.
for myspDeleteOffices, u can write a simple delete query instead of going
for Cursors. Cursors bring down the performance of the system because they
involve iterations and buffer storage
As u said, you can use transactions if u feel the batch job fails
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> Hi,
> I have a hierarchy of tables, like "Company" on top, with "Offices"
> underneath and "People" underneath those and "Contact info" under those,
> etc.
> Now, a record gets deleted in the "Company" table. This means the Offices
> and People attached to them get deleted also.
> I could do this via triggers to ensure consistency, or via a stored
> procedure:
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> }
> CREATE PROCEDURE myspDeleteOffices ( @.ParentId )
> {
> -- delete people first
> DELETE FROM People WHERE ParentId IN (SELECT Id FROM Companies WHERE
> ParentId = @.Id)
> -- delete offices
> DELETE FROM Companies WHERE ParentId = @.Id
> }
> Triggers would be better, but I need to use procedures because I am not
> really deleting records, rather setting up a flag Deleted=1. So all DELETE
> statements are actually UPDATE <record> SET Deleted=1. This gets tricky wh
en
> using triggers. So let's forget about it.
> Questions:
> - myspDeleteOffices needs to delete people. For this I have to list all
> offices first and then delete people that work at that office. Would a
> cursor not be better?
> - More importantly, deleting people or offices could fail.. in which case
> things from the Stuff table may have already been deleted. To roll these
> changes back, so that either everything gets deleted (all steps succeed) o
r
> everything gets restored, what should I do? Use transactions?
> Something like this?
> CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
> {
> BEGIN TRANSACTION
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> COMMIT TRANSACTION
> }
> If an error occurs, does the transaction get rolled back automatically
> without me calling ROLLBACK TRANSACTION ?
> Usually when an error occurs, the query aborts, so I wouldn't be able to
> call ROLLBACK after that.
> Just have no clue how this is done in real life situations.
> Lisa
>
>|||All of this seems like it would work ... however, what is the long term
impact of building a system like this? It seems like you will end up having
to write regular queries that skip over records that have the deleted flag
set to one. Over time, you will end up querying on a field that may not hav
e
good selectively which will result in scans.
If you need to maintain deleted data you may want to consider using DRI with
cascading deletes and then use delete triggers move your deleted data to
archive audit tables. This also typically has the benefit of adding additia
l
audit data such as users names, dates and times to your audit record.
"Chandra" wrote:
> Lisa,
> Its Ok if you are deleteing or Updating, you can use a trigger for the
> purpose. This is what i believe.
> for myspDeleteOffices, u can write a simple delete query instead of going
> for Cursors. Cursors bring down the performance of the system because they
> involve iterations and buffer storage
>
> As u said, you can use transactions if u feel the batch job fails
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||That's a SQL 8/2000 feature, isn't it?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Nok1$9SFHA.2756@.tk2msftngp13.phx.gbl...
> Lisa
> Have you considered using INSTEAD OF DELETE Triggers?
> "Lisa Pearlson" <no@.spam.plz> wrote in message
> news:%23twTI99SFHA.1896@.TK2MSFTNGP14.phx.gbl...
> when
> or
>|||Yes. Both cascading foreign key as well as instead of triggers were introduc
ed in 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa Pearlson" <no@.spam.plz> wrote in message news:edfskS$SFHA.3056@.TK2MSFTNGP14.phx.gbl..
.
> That's a SQL 8/2000 feature, isn't it?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23Nok1$9SFHA.2756@.t
k2msftngp13.phx.gbl...
>|||The deleted flag is used for 2 reasons:
- Deleted records can be undeleted.
- All fields with deleted flag also have updated (timestamp) flag. This way
client program can poll 'changed records' efficiently
In SQL Server 2000 I heard you can use something like service that can
notify clients in some way of changed records. Mine was developed for SQL 7.
And you can create a type of maintanance plan that executes a DELETE FROM
<tablename> WHERE Deleted=1 AND TIMEDIFF(NOW(), Updated) > 1 month (or
something like this)
This should not impact performance too bad.. but you are right that moving
it to another "deleted" table would be better, because I do have to be
consistent to always check "deleted!=1" (because deleted can be 0 or NULL)
It's a bit of a mess :)
But I can do 'deleted' automatically with cascaded updates or something?
Lisa
"Chris Stransky" <ChrisStransky@.discussions.microsoft.com> wrote in message
news:821AB7CF-FABC-4C5B-A5E5-C134E9102DCC@.microsoft.com...
> All of this seems like it would work ... however, what is the long term
> impact of building a system like this? It seems like you will end up
> having
> to write regular queries that skip over records that have the deleted flag
> set to one. Over time, you will end up querying on a field that may not
> have
> good selectively which will result in scans.
> If you need to maintain deleted data you may want to consider using DRI
> with
> cascading deletes and then use delete triggers move your deleted data to
> archive audit tables. This also typically has the benefit of adding
> additial
> audit data such as users names, dates and times to your audit record.
> "Chandra" wrote:
>|||You could undelete by moving records back into the main table. Your undelet
e
function would have to be smart enough to move child records back.
Or
If you use DRI and include the deleted field in the PK then the cascade
would handle the child updates for you in all cases.
If you are worried about always selecting records that are not deleted you
could create a view that includes the deleted column ... but limit it to onl
y
undeleted records. All you have to do then is ensure that the view permits
updates that allow records to disappear from the view on update. That's in
BOL.
"Lisa Pearlson" wrote:

> The deleted flag is used for 2 reasons:
> - Deleted records can be undeleted.
> - All fields with deleted flag also have updated (timestamp) flag. This wa
y
> client program can poll 'changed records' efficiently
> In SQL Server 2000 I heard you can use something like service that can
> notify clients in some way of changed records. Mine was developed for SQL
7.
> And you can create a type of maintanance plan that executes a DELETE FROM
> <tablename> WHERE Deleted=1 AND TIMEDIFF(NOW(), Updated) > 1 month (or
> something like this)
> This should not impact performance too bad.. but you are right that moving
> it to another "deleted" table would be better, because I do have to be
> consistent to always check "deleted!=1" (because deleted can be 0 or NULL)
> It's a bit of a mess :)
> But I can do 'deleted' automatically with cascaded updates or something?
> Lisa
>
> "Chris Stransky" <ChrisStransky@.discussions.microsoft.com> wrote in messag
e
> news:821AB7CF-FABC-4C5B-A5E5-C134E9102DCC@.microsoft.com...
>
>|||On Thu, 28 Apr 2005 12:45:12 +0200, Lisa Pearlson wrote:
(snip)
>Questions:
>- myspDeleteOffices needs to delete people. For this I have to list all
>offices first and then delete people that work at that office. Would a
>cursor not be better?
Hi Lisa,
No - cursors are almost never better. In over 99.9% of all casees, they
are actually far worse.
You'll find that the cascaded delete is lots easier to carry out if you
start at the bottom of the hierarchy - delete Contact Info for all
people in an office of the ex-company first, then all people in an
office of the ex-company, then all offices of the ex-company, and save
the deleting of the company to the last.

>- More importantly, deleting people or offices could fail.. in which case
>things from the Stuff table may have already been deleted. To roll these
>changes back, so that either everything gets deleted (all steps succeed) or
>everything gets restored, what should I do? Use transactions?
Yep.

>Something like this?
>CREATE PROCEDURE myspDeleteCompany ( @.Id INT )
>{
> BEGIN TRANSACTION
> -- delete other stuff first belonging to the company
> DELETE FROM Stuff WHERE ParentId = @.Id
> -- delete offices first
> EXEC myspDeleteOffices( @.Id );
> -- delete self
> DELETE FROM Companies WHERE Id=@.Id
> COMMIT TRANSACTION
>}
>If an error occurs, does the transaction get rolled back automatically
>without me calling ROLLBACK TRANSACTION ?
>Usually when an error occurs, the query aborts, so I wouldn't be able to
>call ROLLBACK after that.
No, you'll have to add error handling. Without that, the execution would
continue and the transaction commited, even though some of your
statements have failed. That would ruin your consistency!
Check these articles, that describe error handling in detail:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)