Using SQL server to process the data

SQL Server 2000 sp3; single instance; used as backend to Access 2002:

I have 20 tables used as a temporary store, each one receiving data
from VB based remote applications that insert new data on a regular
basis.

My current Access apps are used to collate, action and summarise this
data.

The collation process involves checking the new data and adding it to
a single table, and if action is required flag this - this is done in
the "server" Access app. The other Access apps are used to filter and
view the data, reports etc.

Question:
is it possible to do this collation in SQL Server by some automated
task triggered by an event, thereby speeding things up and releiving
the need to be logged on with the "server" Access app ?

The function outline for each of the 20 tables would be:
1. Are there any new records, if so insert them into the central table
2. If the status field=1 of any these new records copy these records
to a 2nd table
3. Repeat


regards,

B Moor

re: Using SQL server to process the data

B Moor (morebyuk@yahoo.co.uk) writes:[color=blue]
> Question:
> is it possible to do this collation in SQL Server by some automated
> task triggered by an event, thereby speeding things up and releiving
> the need to be logged on with the "server" Access app ?
>
> The function outline for each of the 20 tables would be:
> 1. Are there any new records, if so insert them into the central table
> 2. If the status field=1 of any these new records copy these records
> to a 2nd table
> 3. Repeat[/color]

Yes, you should be able to write a stored procedure for this.


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

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

re: Using SQL server to process the data

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns9549EFA6D9560Yazorman@127.0.0.1>...[color=blue]
> B Moor (morebyuk@yahoo.co.uk) writes:[color=green]
> > Question:
> > is it possible to do this collation in SQL Server by some automated
> > task triggered by an event, thereby speeding things up and releiving
> > the need to be logged on with the "server" Access app ?
> >
> > The function outline for each of the 20 tables would be:
> > 1. Are there any new records, if so insert them into the central table
> > 2. If the status field=1 of any these new records copy these records
> > to a 2nd table
> > 3. Repeat[/color]
>
> Yes, you should be able to write a stored procedure for this.[/color]

Erland,

I have so far used stored procedures for insert,update & deletes and I
have just read that triggers can be used but that caution should be
taken when using them, although I think in my application they look
right for the task.

Are you referring to Triggers or a different method using stored
procedures, and if the latter can you give me a simple example of what
you mean ?
[I have copied an extract from another of your posts below which seems
to be closely related ?

<extract from another post>
There a few things you can do to optimize a trigger. If your
operations
on a table mainly are one-row operations, consider writing logic like:

SELECT @rowc = COUNT(*) FROM inserted
IF @rowc = 0
RETURN

IF @rowc = 1
BEGIN
SELECT @val1 = col1, @val2 = col2 FROM inserted
-- Use variables for real job.
END
ELSE
BEGIN
-- Multi-row operation using inserted.
END

You can also consider saying SELECT * INTO #inserted FROM inserted,
and
then use that temp table. Then you are at least scanning inserted only
once. This is something you should not do 6.5, because you will block
others from creating a temp table while the trigger is running.

</extract from another post>

re: Using SQL server to process the data

B Moor (morebyuk@yahoo.co.uk) writes:[color=blue]
> Erland Sommarskog <esquel@sommarskog.se> wrote in message[/color]
news:<Xns9549EFA6D9560Yazorman@127.0.0.1>...[color=blue][color=green]
>> B Moor (morebyuk@yahoo.co.uk) writes:[color=darkred]
>> > Question:
>> > is it possible to do this collation in SQL Server by some automated
>> > task triggered by an event, thereby speeding things up and releiving
>> > the need to be logged on with the "server" Access app ?
>> >
>> > The function outline for each of the 20 tables would be:
>> > 1. Are there any new records, if so insert them into the central table
>> > 2. If the status field=1 of any these new records copy these records
>> > to a 2nd table
>> > 3. Repeat[/color]
>>
>> Yes, you should be able to write a stored procedure for this.[/color]
>
> Erland,
>
> I have so far used stored procedures for insert,update & deletes and I
> have just read that triggers can be used but that caution should be
> taken when using them, although I think in my application they look
> right for the task.
>
> Are you referring to Triggers or a different method using stored
> procedures, and if the latter can you give me a simple example of what
> you mean ?[/color]

My answer was brief because I did not really a get a full understanding
of what you wanted to do. But it seemed that you could use stored procedures
for whatever the task was. If you plan to compile from various tables
as are are inserted, it is possible that you can use triggers as well.

It could help if you provided a more detailed example. Also then keep
in mind that not everyknow here knows Access. For instance, I don't.
[color=blue]
> [I have copied an extract from another of your posts below which seems
> to be closely related ?[/color]

Sometimes thing you say are not always good advice:
[color=blue]
> You can also consider saying SELECT * INTO #inserted FROM inserted,
> and then use that temp table. Then you are at least scanning inserted only
> once. This is something you should not do 6.5, because you will block
> others from creating a temp table while the trigger is running.[/color]

I have since found that this is not very good idea. It's better to
declare a table variable and insert inserted/deleted into that table
variable. You would only include the columns you actually need in
the table variable.

How bad SELECT INTO actually is, depends on how access the tables. I
had a case in our application where we were doing one-by-one processing
in a long transaction and a trigger with a SELECT INTO was fired many
times. With 470 reads each time only to create the table, this proved
to be expensive.

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

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