473,394 Members | 1,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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
Jul 20 '05 #1
3 1325
B Moor (mo******@yahoo.co.uk) writes:
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


Yes, you should be able to write a stored procedure for this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
B Moor (mo******@yahoo.co.uk) writes:
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


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


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>
Jul 20 '05 #3
B Moor (mo******@yahoo.co.uk) writes:
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
B Moor (mo******@yahoo.co.uk) writes:
> 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


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


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 ?


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.
[I have copied an extract from another of your posts below which seems
to be closely related ?
Sometimes thing you say are not always good advice:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

121
by: typingcat | last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so on. I've tried many PHP IDEs today, but almost non of them supported Unicode (UTF-8) file. I've found that the only Unicode...
6
by: ransoma22 | last post by:
I developing an application that receive SMS from a connected GSM handphone, e.g Siemens M55, Nokia 6230,etc through the data cable. The application(VB.NET) will receive the SMS automatically,...
21
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Uploading files from a local computer to a remote web server has many useful purposes, the most...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
2
by: Mr. Arnold | last post by:
I mean the ability to call a Web service or a WCF service with Service Broker using CLR code that's running on SQL server is very good. I am programming this in a project for a client that's being...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.