Marking Multiple Records?

I have a crystal report that I have been asked to modify for a new process.

This report will take a look at our scheduling system and display which Dies are needed for each order. The Problem I am running into is I need a way to be able to denote that a Die is at another operation if the die number is previously listed in the report. This would be fine if I could sort by Die number, but the report has to be sorted by time so our Die room staff knows when a die is due to a machine and are able to just run through the list without having to search through the report for the next order.

This report is currently grouped by run date (so we can keep shifts straight) and sorted by Run Start Time and then by machine number.
This is the order is has to be in, and I cannot deviate from that.

Attached is an example of the current layout of the report

My issue is that I need some way to Denote that the die for the last order 278381-1 is at another machine (the First 2 orders 278385-1) so our Die Room staff are not searching for the Die on Rack 51 when they go to get the dies for the the last order.

I have thought long and hard about this, and I cannot figure out a way to get through this issue without reordering the report, which I cant do.

Any assistance or direction would be greatly appreciatedProblem has been resolved
The solution involved creating a Sub-Report and filtering out the extra records that did not match and then adding a Formatting to the Field to denote a Duplicate record.

Marking copied records

I have a big problem now. I need to write a SQL statement to copy some
records from a table to another table. At the process of copying, I want to
update a field in both table. The field is to identify whether the record is
the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in runnin
g
sequence. There should be repeating numbers. The reason for doing this is if
a user modifies a record in the new table in the future, I will still know
how it originally was by referring back by that number.
Do you get what I mean? I have no idea whether SQL can do that. And whether
it can be settled in a statement. Can someone help me? Give me some guide?
Thank you.You could do this by adding a DateCopied (datetime -default getdate() )
column to the tables -perhaps even adding a WhoChanged (varchar(50) -default
system_user) column.
Other options include a Sequence (timeStamp datatype) Column.
Either of these choices would allow you to always restructure the sequence
of data changes.
Then you just add a TRIGGER to the primary table to copy the old version to
the archive table whenever there is a data change.
You might google "SQL Server" and "Audit Trail". Here's an article to get
you started:
http://expertanswercenter.techtarge...i980058,00.html
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:8D4B4729-8170-436E-BE05-D87933758E59@.microsoft.com...
>I have a big problem now. I need to write a SQL statement to copy some
> records from a table to another table. At the process of copying, I want
> to
> update a field in both table. The field is to identify whether the record
> is
> the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in
> running
> sequence. There should be repeating numbers. The reason for doing this is
> if
> a user modifies a record in the new table in the future, I will still know
> how it originally was by referring back by that number.
> Do you get what I mean? I have no idea whether SQL can do that. And
> whether
> it can be settled in a statement. Can someone help me? Give me some guide?
> Thank you.

Mark duplicate records in a Select statement ?

I have a requirement to mark duplicate records when I pull them from the database.

However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.

The code I have below creates a column called Dupes but marks all the duplicates - including the first one.

Is there a way to only mark the 2nd, 3rd, 4th etc record ?

SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,

Dupes = (select count(id)
from CallLogs
where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)
OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
AND DealerId= 'hdsh'
AND CSRStatus IS NULL
and datediff(d, logdate, getdate()) <= 21),

FROM CallLogs cl
left Join CallStatus cs on cs.Id = cl.CSRstatus
left Join CallStatus cs2 on cs2.Id = cl.Customerstatus
Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)
And DealerId='hdsh'
And Logdate Between '08/01/2007' And '08/31/2007'

Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?

1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1

That way it just accounts for itself by subtracting 1 from the total 

|||

The problem is that both records get the same Dupe number.

I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Many to One Data Migration

I want to move (copy + delete) data collected on many (~100) identitcal systems into one worker repository. The records all have UNIQUEIDENTIFIER primary keys. I need this as automated as possible so that when I make table changes or transfer changes, I don't need to constantly update all 100+ worker systems.

I currently have a DTS job on each worker. I can script the copy of the DTS job so that DTS updates are fairly easy to automatically propagate. However, I'd like a more automated and centralized method.

Replication jumpts to mind but wouldn't I have to individually configure a publication on each worker system and manually update that publication when necessary?you discussing 2 things...Data and Objects

1st you want to centralize data...why not just use a sproc?

2nd you need to propogate object changes...I'd think I'd use a sproc as well...

Both a scheduled jobs...

3rd...why do you have 100+ instances of sql server?

Consolidate!|||Use a sproc? How do you transfer data across a network via just a stored procedure? AFAIK, you need DTS or replication or a programmatic SELECT/INSERT (which would be too slow).

Currently, I use many stored procedures for many things. The DTS package uses a few internally.

I don't see how this issue can be resolved with a stored procedure.

Thanks for replying.

many DISTINCT queries

Hi,

I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.

Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:

select distinct CATEGORY from table TEST

Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.

I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.

I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.

Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?

Thanks for any hints!"Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> Hi,
> I have a table that contains log data, usually around a million records.
The
> table has about 10 columns with various attributes of the logged data,
> nothing special. We're using SQL Server 2000.
> Some of the columns (for example "category") have duplicate values
> throughout the records. We have a web page that queries the table to show
> all the unique columns, for example:
> select distinct CATEGORY from table TEST
> Obviously the server has to scan all rows in order to get all unique
columns
> which takes quite a while, especially since that web page contains several
> of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
that
> also add to the load.
> I already created indexes on the CATEGORY (actually on all categories)
> column which might help a little but I'm pretty sure that there has got to
> be a better way.
> I also create a view (select distinct CATEGORY from table TEST) and tried
to
> index it, but it won't let me index a query that contains a DISTINCT
> statement.
> Isn't there a way to create an index that contains only the distinct
values?
> Is there another way to speed this up?
>
> Thanks for any hints!

If only you load/update the data relatively infrequently, then you could
create a 'lookup' table for each attribute, and populate them from the main
table after loading it (rather like the dimensions in a star schema):

insert into dbo.Categories (Category)
select distinct Category
from dbo.Test

Your client code could then query the lookup tables instead of the log
table. If you want to use indexed views, then you could create a view like
this:

create view dbo.Categories
with schemabinding
as
select Category, count_big(*) as 'Occurrences'
from dbo.Test
group by Category

That should be indexable, although there are quite a few other restrictions,
so you would need to check them out. But having multiple indexed views on
the table would make data modifications much slower, so if the data changes
frequently you might have to use some sort of lookup table approach anyway.

Simon|||> select distinct CATEGORY from table TEST
Try experimenting with group by instead of distinct. Also in query
analyzer, enable view execution plan, to get an idea what mssql is
doing. "select category from mytable group by category"|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4023d837$1_1@.news.bluewin.ch...
> "Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
> news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> > Hi,
> > I have a table that contains log data, usually around a million records.
> The
> > table has about 10 columns with various attributes of the logged data,
> > nothing special. We're using SQL Server 2000.
> > Some of the columns (for example "category") have duplicate values
> > throughout the records. We have a web page that queries the table to
show
> > all the unique columns, for example:
> > select distinct CATEGORY from table TEST
> > Obviously the server has to scan all rows in order to get all unique
> columns
> > which takes quite a while, especially since that web page contains
several
> > of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
> that
> > also add to the load.
> > I already created indexes on the CATEGORY (actually on all categories)
> > column which might help a little but I'm pretty sure that there has got
to
> > be a better way.
> > I also create a view (select distinct CATEGORY from table TEST) and
tried
> to
> > index it, but it won't let me index a query that contains a DISTINCT
> > statement.
> > Isn't there a way to create an index that contains only the distinct
> values?
> > Is there another way to speed this up?
> > Thanks for any hints!
> If only you load/update the data relatively infrequently, then you could
> create a 'lookup' table for each attribute, and populate them from the
main
> table after loading it (rather like the dimensions in a star schema):
> insert into dbo.Categories (Category)
> select distinct Category
> from dbo.Test
> Your client code could then query the lookup tables instead of the log
> table. If you want to use indexed views, then you could create a view like
> this:
> create view dbo.Categories
> with schemabinding
> as
> select Category, count_big(*) as 'Occurrences'
> from dbo.Test
> group by Category
> That should be indexable, although there are quite a few other
restrictions,
> so you would need to check them out. But having multiple indexed views on
> the table would make data modifications much slower, so if the data
changes
> frequently you might have to use some sort of lookup table approach
anyway.

Thanks, I tried the indexed view and that seems to work OK now, pretty
fast - can't complain. Data shouldn't be updated that often so that should
be OK. Otherwise I might have to go with a lookup table - but it's not a
real good solution for our scenario for reasons I'm not going to bore
anybody with :)

Thanks!|||"louis nguyen" <louisducnguyen@.hotmail.com> wrote in message
news:b0e9d53.0402061216.5d4f6e56@.posting.google.co m...
> > select distinct CATEGORY from table TEST
> Try experimenting with group by instead of distinct. Also in query
> analyzer, enable view execution plan, to get an idea what mssql is
> doing. "select category from mytable group by category"

Yes, the group thing worked great - I also analyzed the execution plan and
now it's only returning the actual number of rows I'm getting - not the
whole table anymore.

Thanks.

Many Detail Record to One

I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the help
http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help

Many Detail Record to One

I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the helphttp://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help

Many data entry tables - which ones hold records?

We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form's data is persisted in
a corresponding sql table. When data entry is complete, it needs to be
processed. Here's where the questions start.

How can we easily determine in which tables a customer has data and how
best to select that data?

We're not opposed to putting all the data in a single table. This
table would wind up having ~15 million records and constantly have CRUD
operations performed against it by up to 5000 users simultaneously.
With sufficient hardware, is this too much to ask of the db?heromull (heromull@.gmail.com) writes:
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Whether the 200 tables should be 1, 10, 74, or 200 is impossible to tell
from without knowledge about what's in them.

But from a performance point of view, it would not really matter whether
it's one or two hundred tables. Provided, that is, the the single table
has proper indexing.

There are a couple a ways of finding to find data to process:

1) Timestamp column. A timestamp column is automatically updated by SQL
Server with a database-unique value that is monotonically increasing.
(Binary, completely unrelated to date and time). The process that looks
for data would keep track of the most recent timestamp per table, and
retrieve the rows with higher timestamp value. If the process updates the
rows itself, it needs to combine the lookup with a status column. The
drawback with this solution is that the timestamp column must be indexed,
and since it's updated each time the row is updated, there will be a lot
of shuffling around in that index.

2) IDENTITY column. All tables would have an identity column, and then
the process would keep track of the most recently processed value. With
this solution you can only handle inserts, not if users update existing
data.

3) Having triggers on the that enters data about rows to process into a
table. Again, you may need a mechanism to differentiate between user-entered
changes and changes from your processing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, the data to select for processing is not time based, it's based
on whatever tables a customer has data in at the time of processing,
which is determined by the customer. So, option three would be the
best and is similar to our current process. Also, the data is not
updated by the processing, only selected. Even with a table that tells
us which tables have data, how would we build the select statement(s)
to only include those tables with data?|||heromull (heromull@.gmail.com) writes:
> Thanks, the data to select for processing is not time based, it's based
> on whatever tables a customer has data in at the time of processing,
> which is determined by the customer. So, option three would be the
> best and is similar to our current process.

Not that see what time has to do with it. I did say timestamp, but
the timestamp datatype has nothing to do with time. Then again, if you
already have a process similar to option three, then go with that.

> Also, the data is not updated by the processing, only selected. Even
> with a table that tells us which tables have data, how would we build
> the select statement(s) to only include those tables with data?

I can not say that. I don't know your tables. I don't know in which context
this process runs etc. I would assume that since there are 200 tables,
that you would have a stored procedure or a SELECT statement for that
table, as I would assume that all tables would generally have a different
set of columns. If they all have the same schema, then there is a strong
indication of that you should have one single table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"heromull" <heromull@.gmail.com> wrote in message
news:1130029056.070783.59510@.g14g2000cwa.googlegro ups.com...
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Don't design a database schema around a user interface. Design your database
schema around your DATA and BUSINESS RULES, then build a data access layer
that supports the UI. You say "Each form's data is persisted in a
corresponding sql table". This make little sense to me as a description of a
data model. I suspect (admittedly on the basis of too little information)
that with a better design your perceived problems would disappear. The
stataement "We're not opposed to putting all the data in a single table"
also suggests a very arbitrary approach to database design. Is your current
design a normalized one? If so, I don't understand your confusion about "How
can we easily determine in which tables a customer has data". The answer is
presumably that you use the Customer key - whatever that is in your model.

--
David Portas
SQL Server MVP
--|||Erland Sommarskog wrote:
...
> If they all have the same schema, then there is a strong
> indication of that you should have one single table.
...

Yes, this is the case. They share the same schema.

David Portas wrote:
> Don't design a database schema around a user interface.

I think this is our first mistake.

> Design your database
> schema around your DATA and BUSINESS RULES, then build a data access layer
> that supports the UI. You say "Each form's data is persisted in a
> corresponding sql table". This make little sense to me as a description of a
> data model. I suspect (admittedly on the basis of too little information)
> that with a better design your perceived problems would disappear.

I think you're right. It's that "better design" that we're
researching.

> The
> stataement "We're not opposed to putting all the data in a single table"
> also suggests a very arbitrary approach to database design.

It does and I say that only to mean that it's not too late for us to
implement a better design.

> Is your current
> design a normalized one? If so, I don't understand your confusion about "How
> can we easily determine in which tables a customer has data". The answer is
> presumably that you use the Customer key - whatever that is in your model.

Here's DDL that describes our current "model" (I hear you laughing
already). Notice that all 200 talbes have the same schema. If we
determine a single table approach would be a better design, what would
be some areas of concern? Obviously indexing is on the list.

CREATE TABLE [Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (100),
CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED
(
[CustomerId]
)
)
--we have ~200 tables with this table's schema
CREATE TABLE [Table1] ( --Table1 thru Table200
[Table1Id] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NOT NULL ,
[Value] [nchar] (1024),
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
[Table1Id]
) ,
CONSTRAINT [FK_Table1_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
--each of the 200 tables have a trigger
--similar to this (i'll omit the delete trigger from the DDL)
create trigger
Table1CreateInterview
on
Table1
for insert
as
begin
declare @.CustomerId int
declare @.RecordId int
select
@.RecordId = Table1Id,
@.CustomerId = CustomerId
from
inserted

insert into
interview
(
CustomerId,

TableId,--represents the table caused the trigger eg. 1=Table1
RecId--the value of the primary key of the record causing the trigger
)
values
(
@.CustomerId,
1,
@.RecordId
)
end
GO
--a record is inserted into this table each
--time a record is written to any of the 200 tables
--a record is deleted from this table each time
--a record is deleted from any of the 200 tables
CREATE TABLE [Interview] (
[InterviewId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NULL ,
[TableId] [int] NOT NULL ,
[RecId] [int] NULL
CONSTRAINT [PK_Interview] PRIMARY KEY NONCLUSTERED
(
[InterviewId]
),
CONSTRAINT [FK_Interview_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
insert into customer(name) values ('some name')
insert into table1(customerid, value) values (1, 'my value in table1')|||heromull (heromull@.gmail.com) writes:
>> Don't design a database schema around a user interface.
> I think this is our first mistake.

An unusually candid confession!

>> If they all have the same schema, then there is a strong
>> indication of that you should have one single table.
> ...
> Yes, this is the case. They share the same schema.

You should make them one table, adding one more column that specifies
the entity that is now hidden in the table name. That table would look
like:

CREATE TABLE [Answers] ( --Table1 thru Table200
[CustomerId] [int] NOT NULL ,
TableNo int NOT NULL,
RowNo smallint NOT NULL,
[Value] [nchar] (1024),
PRIMARY KEY (CustomerID, TableNo, RowNo)

I've added RowNo, beause I don't know if one customer can add more
than one value in the same form. If he can't RowNo should not be
there. I've very deliberate taken out the IDENTITY column, because
this table should have a composite key. My RowNo is indeed a surrogate,
but only in the sub-realm of CustomerId, TableNo. (And TableNo is just
a name I use, in lack of knowledge about the business domain.)

I put the key on CustomerID, TableNo, but depending how you use the
table, you may also have a need for an index on (TableNo, CustomerID).
CustomerID first is good for queries like "what is customer 1233 up to"?,
but not for "What do we have in table 12?".

> It does and I say that only to mean that it's not too late for us to
> implement a better design.

That's great to hear!

> begin
> declare @.CustomerId int
> declare @.RecordId int
> select
> @.RecordId = Table1Id,
> @.CustomerId = CustomerId
> from
> inserted

Uh-uh, classic mistake. A trigger fires once per statement, not once
per row. Yes, as long as data through that form, it will only come
one by one, but then suddenly there is a batch processing loading lots
of data at the same time. So write your trigger as:

INSERT interview (...)
SELECT ...
FROM inserted

> insert into
> interview

And INSERT without a column list is very poor practice in production code.
Someone adds a column to the table, and the statement blows up. That's
bad.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> An unusually candid confession!

Acceptance is the first step right?

Anyway, thank you so much for the support! I think the suggestions are
going to help us out a lot. A lightbulb went off over my head when I
read your composit key explaination.|||Thank you so much for the support! I think this will work for us. My
only other question would be about managing the surrogate column
(RowNo). During Inserts, is there a way to manage it within SQL Server
or would my client apps need to select max(RowNo) and increase it by 1?

Again, thanks!|||On 26 Oct 2005 07:55:17 -0700, heromull wrote:

>Thank you so much for the support! I think this will work for us. My
>only other question would be about managing the surrogate column
>(RowNo). During Inserts, is there a way to manage it within SQL Server
>or would my client apps need to select max(RowNo) and increase it by 1?

Hi heromull,

A typical INSERT statement would roughly look like this:

INSERT INTO Answers (CustomerId, TableNo, RowNo, [Value])
SELECT @.CustomerId, @.TableNo, COALESCE(MAX(RowNo),0) + 1, @.Value
FROM Answers
WHERE CustomerId = @.CustomerId
AND TableNo = @.TableNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Works perfectly.

manually update a table

i have 2 tables in an sql db. table1 has 2400 records and table2 has 1400 records. if i open the table and select all rows in table2 and try to manually edit the contents of a field i receive the following error:

communication link failure.

if i open the table and select top 1000 i can manually edit any value with out any problem.

if i open table1 and select all the rows i can edit any value i want with out any issues.

can it be that the size of the table prevents me from editing when i select all the rows?

extra info:
table1 has more records and more fields but the fields in table2 are larger (navchar 50 compared to navchar20)

Thank You,
ThomasDo you have a primary or unique key on your tables?|||Try to use the OLE DB provider for SQL Server instead an ODBC driver, and see whether you problem persists.|||Are you doing this in EM? If so, it is highly reccomended you do not edit data this way as it creates a big overhead with locks. Writing appropriate update statements would be better. (again, if you are doing it from EM)

HTH

Manipulate Page-Numbers

Hi,

I need to manipulate the page-numbers, I've a dataset with for example 8 Records, where each record has its own page (page break at end) but some Records have 2Pages. So I want that every "first" page of a record gets page-number 1 and for those reports that need two pages the next page should have page-number 2. So in a PDF the page numbers look be like this: 1,1,1,2,1,1,2,3,1,1,1,2...

I tried a custom assembly with a static variable m_page on it which is resettet to 1 in a textfield at the recordbegin (=MyLib.MyClass.resetPage()) and is shown and incrementet in each page footer (=MyLib.MyClass.nextPage()). But when I print that to PDF it seems that the page footers are alltogether generated at the end, so I get numbers from for example 20 to 30 (my report has 10pages).

Is there any possibility? In Access this was quite easy ;(

Sorry bothering you, I should have used the search with the right keywords ;)

The solution is:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

Managing Table Size

I have a table in my DB that I would like to restrict to recent records
only. Recent being the last 4 months. Can someone help me determine the
easiest and most maintenance free way to accomplish this?
Thank You"Shawn" <skfabc@.yahoo.com> wrote in message
news:ezb1obNHEHA.3556@.TK2MSFTNGP10.phx.gbl...
> I have a table in my DB that I would like to restrict to recent records
> only. Recent being the last 4 months. Can someone help me determine the
> easiest and most maintenance free way to accomplish this?
> Thank You
Presumably you have a column with creation date included in the table
schema. In which case create a SQL Server Agent TSQL job that contains the
SQL:
DELETE *
FROM tablename
WHERE dateCol < GETDATE() - 120.
Schedule it to run regularly (e.g. every night). You might even want to add
it to the backup job.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||I agree with Bobs solution, but here is some SQL that will
find values greater than 4 months rather than 120 days
(although I am being very picky)
delete
FROM order
WHERE DATEDIFF(month, orderdate, getdate()) > 4
J
>--Original Message--
>I have a table in my DB that I would like to restrict to
recent records
>only. Recent being the last 4 months. Can someone help me
determine the
>easiest and most maintenance free way to accomplish this?
>Thank You
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a22a01c41d4d$1f22acf0$a101280a@.phx.gbl...
> I agree with Bobs solution, but here is some SQL that will
> find values greater than 4 months rather than 120 days
> (although I am being very picky)
> delete
> FROM order
> WHERE DATEDIFF(month, orderdate, getdate()) > 4
LOL I was going to post that, but had a sudden doubt as to whether DATEDIFF
was TSQL or VB, couldn't check, so I chickened out.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004