Iteration in SQL

I have an application that needs to create invoices on a daily basis to multiple clients based on orders shipped that day. This is easy to do on the front-end. But how can I do this on the back end in SQL Server.

I want to sort orders by Client ID and put all orders belonging to one customer on one invoice. When customer id changes, I change the Invoice ID. Is this possible in SQL?

Xcog

Asked and answered in the microsoft.public.sqlserver.programming newsgroup.
|||

Yes it is possible, install AdventureWorks in you development box in Enterprise manager click on stored procedures and you can get close to what you need. I would also check Northwind database but it was for mail order while AdventureWorks is for Ecommerce. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en

sql

Iterate result set inside stored procedure

Hello,

I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).

How can I process result rows inside a stored procedure?

This is what I have so far:

CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)
/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID
/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)
PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)
/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO

Hi,
You can declare a variable and append the results onto it for each row with:

DECLARE @.emails varchar(1000)

SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx

This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"

The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:

SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery

|||

Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?

Also, I get a leading comma - how do I avoid that on the first entry?

Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"

|||

I got it! Needed to assign a derived table.

Still have the comma issue, though.

/* Get email addresses of Service Responsible Parties */
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1

|||

The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''

The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email

if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.

As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)

DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail

FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
 ) subquery

|||

Just seen your post after I did one!
Excellent news that you got it working.

The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with

SET @.emails = 'recip: ' + @.emails

|||

Instead of

SELECT @.email=ISNULL(@.email,'') + ',' + {your field}

use

SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}

|||

I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).

Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail

And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.

Either way, thanks a lot for the help - it's good to go now!

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete
it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||Hi Amir
Use Row level locks before inserting a row into the Table.
best regards
Chandra
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
the
> same time - which causes an error, since the SET IDENTITY_INERT ON
statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
David Portas
SQL Server MVP
--|||"David Portas" wrote:

> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:

> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust yo
ur
> queries to include only Archive = 'Y'. The way you're doing it now, you'r
e
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before
).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
David Portas
SQL Server MVP
--

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> > When I delete a row, I first copy it to another table and afterwards
> > delete it
> > from the original table.
> > When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> > in
> > order to avoid getting a new value for the identity column.
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>> > When I delete a row, I first copy it to another table and afterwards
>> > delete it
>> > from the original table.
>> > When I want to restore the row, I use the SET IDENTITY_INSERT
>> > statement,
>> > in
>> > order to avoid getting a new value for the identity column.
>> This makes no sense to me. Why not just assign a new IDENTITY value,
>> since
>> you apparently don't have dependent rows referencing the data in the
> > original table?
>> IDENTITY should be used only as an artificial key - if you care about
>> what
>> the value is then don't use IDENTITY.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
> queries to include only Archive = 'Y'. The way you're doing it now, you're
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
> >
> >
> > "David Portas" wrote:
> >
> >> > When I delete a row, I first copy it to another table and afterwards
> >> > delete it
> >> > from the original table.
> >> > When I want to restore the row, I use the SET IDENTITY_INSERT
> >> > statement,
> >> > in
> >> > order to avoid getting a new value for the identity column.
> >>
> >> This makes no sense to me. Why not just assign a new IDENTITY value,
> >> since
> >> you apparently don't have dependent rows referencing the data in the
> > > original table?
> >>
> >> IDENTITY should be used only as an artificial key - if you care about
> >> what
> >> the value is then don't use IDENTITY.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >
> > Well, I do have related records, thus I can't insert the row with a new
> > IDENTITY if I want to keep the relations.
> > It goes like this: I have a table full of Customers rows and another table
> > with CustomersReports rows (which is a child table of the Customers
> > table).
> > When I delete a customer, I don't really delete it, but rather move it to
> > an
> > archive table along with it's related CustomersReports child rows.
> > In another scenario, I might want to restore the Customer row to it's
> > original table, and restore it's related CustomersReports rows as well.
> > If I will restore the customer by assigning it a new ID, I will be
> > compelled
> > to modify the foreign key in the child table as well.
> > I prefer to avoid it if possible.
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
--
David Portas
SQL Server MVP
--|||On Sun, 08 May 2005 11:25:15 -0700, Amir Shitrit wrote:
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to the
> archive, I move all of it's related child rows as well (as I explaind before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
Although I fully agree with Michael and David - this is just a bad idea,
as far as I can see, you could do it using application locks. Like this:
CREATE PROCEDURE NeverRunsInParallel
AS
DECLARE @.result int
EXEC @.result = sp_getapplock @.Resource = 'myLock', @.LockMode = 'Exclusive'
IF @.result => 0
BEGIN
-- Do your thing, secure in the fact that
-- no other connection will do it at the same
-- time.
END
EXEC sp_releaseapplock @.Resource = 'myLock1'
<<
HTH,
Andrés Taylor

Issue with the ForEachLoop Task in SSIS

Hi,

I am using a SQL task to execute a stored procedure which returns a single field with multiple records. I want the records returned by the stored procedure to be processed one by one within a ForEachLoop container. How do I assign the records one by one to one variable and use it in a Script task running inside the ForEachLoop container.

I am using 2 tasks in my package.

In my first task I call a SQL task that executes a stored procedure which returns a list of reference numbers (TrackData). This works perfectly.

However, in my second task I must use the ForEachLoop task to loop through the above list and set the value of var_TrackData (a user variable declared by me) with the value of the TrackData present in it during that particular loop.

I am not sure how to go about the second task. Any help would be greatly appreciated.

Create an object variable.

In the execute sql task set the resultset to the variable name (set result name to 0).

Create a variable to hold the reference numbers (int or string?)

Create a for each loop task

Set the collection to for each ADO enumerator

Set the ADO object source variable to your object variable name (which contains the resultset).

In variable mappings set the variable to the variable name you created to hold the reference numbers

Set the precednce so that the execute sql runs before the for each llop.

Now when this runs the object variable will be set to the resultset the the for each loop will itterate through it setting the variable to the reference number for each loop.

|||

See if this post gives you some idea of how you can do it:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Issue with Report Manager after SP 2 install

I'm trying to delete reports using Report Manager from the folder
level by checking multiple files and hitting the delete button. It
use to work but after SP2, the script behind the button no longer
works.
Is anyone experiencing the same issue?
Thanks,
TWe have the same problem - actually, we can't delete any reports, not just
multiple reports.
I found a post on a web site that says that it is a known problem with the
SP2 CTP and will be fixed in the final SP2 release.
<tuong.k.lam@.gmail.com> wrote in message
news:1170293384.531044.128600@.k78g2000cwa.googlegroups.com...
> I'm trying to delete reports using Report Manager from the folder
> level by checking multiple files and hitting the delete button. It
> use to work but after SP2, the script behind the button no longer
> works.
> Is anyone experiencing the same issue?
> Thanks,
> T
>|||Thanks DJX,
Can you post the website where you saw the problem, if you can
remember.

Issue with Multiple Fact Tables

Hi,

I am having an issue with Multiple Fact Tables..

I have 2 fact tables with some common dimensions and some independent to each fact table.

When I execute a mdx query which fetches data only from the 1^st fact table, for some reason it look’s to the second fact table and display the results accordingly.

For Example

Record-Id Fact table 1 Fact table 2

Measure -[Cut In] Measure -[Zro Cut In]

1 1 -

2 12 10

3 10 5

4 2 -

5 20 -

6 0 -

7 Null -

8 0 -

If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?

Query 1

SELECT

{

[Measures].[Cut In]

} ON COLUMNS,

{

ORDER(

nonempty([Dim Product].[Principal Id].children)

,[Measures].[Cut In],

desc

)

} ON ROWS

FROM [SIFDW]

WHERE (

{ [Dim Period].[Period].&[200601] },

{ [Dim Period].[Period Type].&[M] },

{ [Dim Store].[organization name].&[CORERP]},

{ [Dim Product].[Team ID].&[1]}

)

Query 2

SELECT

{

[Measures].[Cut In]

} ON COLUMNS,

{

ORDER (

[Dim Product].[Principal Id].children

,[Measures].[Cut In],

desc

)

} ON ROWS

FROM [SIFDW]

WHERE (

{ [Dim Period].[Period].&[200601] },

{ [Dim Period].[Period Type].&[M] },

{ [Dim Store].[organization name].&[CORERP]},

{ [Dim Product].[Team ID].&[1]}

)

Thank you,

Manish

ManishNShah wrote:

If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?

Since you didn't explictly specify a measure for the NonEmpty(), it may have defaulted to a measure in the other measure, group, so you could try adding the measure in Query 1:

Code Snippet

SELECT

{

[Measures].[Cut In]

} ON COLUMNS,

{

ORDER(

nonempty([Dim Product].[Principal Id].children,

{[Measures].[Cut In]})

,[Measures].[Cut In],

desc

)

} ON ROWS

FROM [SIFDW]

WHERE (

{ [Dim Period].[Period].&[200601] },

{ [Dim Period].[Period Type].&[M] },

{ [Dim Store].[organization name].&[CORERP]},

{ [Dim Product].[Team ID].&[1]}

|||

Hi Deepak,

Thank you for your quick response, but still have couple of issues with it.

If a cube has multiple measure groups, and each group has few measures; but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group which is not mentioned in the MDX Query?

Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?

Thank you,

Manish

|||

ManishNShah wrote:

but still have couple of issues with it.

But did it work - that would be useful to know, as well?

ManishNShah wrote:

but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group

The query applies to the cube, not just to a specific measure group. So if a measure is not specified, explicitly or by context, the default cube measure would be used, which could be from another measure group:

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

...

The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).

...

ManishNShah wrote:

Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?

You might want to start a separate thread for this - but keep in mind that extrapolating from SQL may not always be the best approach in MDX.

Issue selecting two rows from two columns

I'm trying to select two rows from two columns and while I can select the columns, the selection of multiple rows is not falling into place. I can pull one row into the report, but not two.

This is what I'm writing

SELECT name, number FROM table
WHERE name="variable1',''variable2'

Something is occurring around the , between variable1 and variable2. I know I doing something wrong, but can't figure it out. I can pull variable1 by itself just fine.

The specific error is:
sql: errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'variable2' at line 2
number: 1064

Any ideas would be greatly appreciated. I tried searching and could not find an answer to this problem.Found the answer

SELECT name, number FROM table
WHERE name IN('var1', 'var2', 'var3')|||SELECT name, number FROM table
WHERE name in('variable1','variable2')