Need help assigning runway using time

Hi there,
I am trying to model a simple air traffic control system and have plane, flight_info and runway tables. Flight_info is a relational table and 2 of its attributes are pointers to the plane and runway tables so that I can allocate planes and flights to runways.

The length of the runway needs to be more than the stopping distance of the plane and I have a getStoppingDistance method to use for this. I guess one part will be to compare the length of the runway with the stopping distance.

I also need to assign a runway to a flight for a fixed time for each flight. I'm not sure how I can achieve this. I am thinking that I will need to test the 'state' attribute of the runway table to see if a particular runway is allocated or not and if it isn't, assign it to a flight. But I'm not sure how I would assign it for a fixed time.

Any help or suggestions on this would be great.reset the "state" attribute of runway everytime the flight completes landing. you could write a trigger on flight_info table which shall toggle this runway.state when you update flight_info

Need HELP ASAP for Q823492

Does this hotfix go on the SQL Server system or on the
clients that are trying to connect to the SQL Server?
Thanks a lot,
JoePer the article, install it on the server. Make sure to
follow the steps in the To Activate the Update section.
-Sue
On Tue, 2 Sep 2003 07:29:48 -0700, "Joe" <jkdriscoll@.qg.com>
wrote:
>Does this hotfix go on the SQL Server system or on the
>clients that are trying to connect to the SQL Server?
>Thanks a lot,
>Joe

Need Help - Local Synchronization Between SQL Mobile and Local SQL Database

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

Can anyone please help me

|||

If you build your mobile application using VS2005 and SQL Mobile, you can use merge replication between your server-side publication and SQL Mobile. The SQL Mobile Books OnLine are the first place to start - read the section on merge replication.

-Darren Shaffer

|||

Hi Darren

Yes I got my sql mobile to synchronize successfully with the local desktop sql database called "Inventory"

However I have a new problem

I would like to have the desktop replicating with the server for database "Inventory"

I also got this to work seperately

I would now want the SQL mobile database to replicate with the local desktop database "Inventory" which inturn will replicate with the server copy called "Inventory"

I would like to have both replication scenarios joined together. I know that most people will say get the SQL mobile to talk directly to the server but this is the most expensive scenario I believe

Is this possible?

Thankyou for your earlier post

Touraj

|||

Touraj,

Sorry for not reading your post more closely - I see what you are trying to do now. You want to have a 3 tier merge replication going on. Yes, this is possible, but the database on the laptop is going to have to be SQL Server 2005, not SQL Express. The way it would have to work is this:

The laptop would have to have IIS installed and the SQL Mobile server tools. As far as the mobile app is concerned, the laptop is the publisher. On the laptop, you have a subscription to the central server in a replication relationship. You would turn around and create a publication on the laptop with the same articles that are in the subscription to the central server. You would need to be extremely careful with uniqueness in this situation - I would avoid identity columns and use GUIDs for everything in this app domain.

Is there any chance that both the laptops and the PDAs can be subscribers to the central server? That would be a much easier implementation, but it is possible to do what you suggested with the extra tier.

-Darren

|||

Hi Darren

Thankyou again for your kind assistance

Yes you are right. It would be easier and 'cheaper' to get the PDA to synch with the central server via the servers IIS process

I am tossing up between a i-Mate and iPaq 4700. I would be interested to know if you have a preference over the two

Thanks from sunny Australia.

Touraj

|||

I think both i-Mate and HP make great devices - I have not had any trouble with SQL Mobile on my JasJar or on a variety of iPaqs. My WM5 JasJar seems a little sluggish overall compared to a Dell Axim X50v (WM5), which others have noted as well. Not slow, just not instant to respond to input. I have not had specific experience with the 4700. There are some public newsgroups (microsoft.public.pocketpc.developer) where people tend to talk more about specific devices and you could use Google Advanced Groups search to get previous opinions on the 4700.

-Darren

Need Emergency Advice

Dear All,

We have a big concern in our Database system. We have 2000 transactions
daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to
abort it because of these reasons.
I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa."laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1147164897.180455.80350@.j33g2000cwa.googlegro ups.com...
> Dear All,
> We have a big concern in our Database system. We have 2000 transactions
> daily in our database. We need to replicate some how the database for
> our fail over setup. I tried transactional replication at midnight but
> our all systems locked and we had a lot of complaints from the
> customers and It was taking a lot of time to snapshot part and I had to
> abort it because of these reasons.

Why do the transactional replication only at midnight? Better off doing it
continually throughout the day.

And btw, 2000 transactions, unless they're huge, is trivial. I have systems
doing that per minute.

The other option is to look into log shipping.

There are scripts out there that let you do it even w/o Enterprise edition,
though it is then admittedly more manual.

> I need an advice how I can create a replication or is there any other
> way to replicate or is there any way to do this process without
> bothering the system? Please give me some advice and help..
> Thanks
> asa.|||Greg D. Moore (Strider) wrote:
> "laststubborn" <arafatsalih@.gmail.com> wrote in message
> news:1147164897.180455.80350@.j33g2000cwa.googlegro ups.com...
> > Dear All,
> > We have a big concern in our Database system. We have 2000 transactions
> > daily in our database. We need to replicate some how the database for
> > our fail over setup. I tried transactional replication at midnight but
> > our all systems locked and we had a lot of complaints from the
> > customers and It was taking a lot of time to snapshot part and I had to
> > abort it because of these reasons.
> Why do the transactional replication only at midnight? Better off doing it
> continually throughout the day.

The thing is snapshot taking a lot of time to do it. Even at night we
had a problem with customers.

> And btw, 2000 transactions, unless they're huge, is trivial. I have systems
> doing that per minute.
> The other option is to look into log shipping.
> There are scripts out there that let you do it even w/o Enterprise edition,
> though it is then admittedly more manual.

As you said it is manual we can not afford manual solutions.

Thanks

>
> > I need an advice how I can create a replication or is there any other
> > way to replicate or is there any way to do this process without
> > bothering the system? Please give me some advice and help..
> > Thanks
> > asa.|||"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1147185632.084129.202140@.j73g2000cwa.googlegr oups.com...
> Greg D. Moore (Strider) wrote:
> > "laststubborn" <arafatsalih@.gmail.com> wrote in message
> > news:1147164897.180455.80350@.j33g2000cwa.googlegro ups.com...
> > > Dear All,
> > > > We have a big concern in our Database system. We have 2000
transactions
> > > daily in our database. We need to replicate some how the database for
> > > our fail over setup. I tried transactional replication at midnight but
> > > our all systems locked and we had a lot of complaints from the
> > > customers and It was taking a lot of time to snapshot part and I had
to
> > > abort it because of these reasons.
> > Why do the transactional replication only at midnight? Better off doing
it
> > continually throughout the day.
> The thing is snapshot taking a lot of time to do it. Even at night we
> had a problem with customers.

I'm not following, or we're not communicating effectively. A transactional
backup should not be taking that long or have that much impact. How large
are these transactions? Like I say, I have a system doing that many a
minute (though that one we don't backup). The one we do transactional
backups on every 20 minutes probably does 2000/hour or more at peak time.
(plus a very high number of pure select only queries.)

> > And btw, 2000 transactions, unless they're huge, is trivial. I have
systems
> > doing that per minute.
> > The other option is to look into log shipping.
> > There are scripts out there that let you do it even w/o Enterprise
edition,
> > though it is then admittedly more manual.
> As you said it is manual we can not afford manual solutions.

Well, what do you consider too manual. In the case of many of these scripts
the failover itself may or may not be automatic, but can generally be
scripted. The log-shipping itself is scripted.

In our case, we do log backups from our main server every 20 minutes and
restore them to our backup server every 20 minutes, but with either a 4 hour
or 6 hour delay (I forget which). To bring the backup system "up to date" I
can do in about 15 minutes.. either manually or via running a quick script.

The bigger delay (whcih we'd have using Enterprise Server also) is updating
our front end DSNs. And we have that scripted also.

All in all, a failover would probably take us 15 minutes or less to recover
from.
> Thanks
> > > I need an advice how I can create a replication or is there any other
> > > way to replicate or is there any way to do this process without
> > > bothering the system? Please give me some advice and help..
> > > > Thanks
> > > asa.
>|||yep I think we had a communication problem. I was asking about the
replication not for a backup (I dont know whether you mean replication
when you use 'transactional backup') Back Up is not a problem as you
said we dont have problem on that however I will create failover
cluster but I will replicate the database first. That is my problem
transactional replication.. That is why I asked all these questions.

Thank you|||"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1147244663.232015.249020@.u72g2000cwu.googlegr oups.com...
> yep I think we had a communication problem. I was asking about the
> replication not for a backup (I dont know whether you mean replication
> when you use 'transactional backup') Back Up is not a problem as you
> said we dont have problem on that however I will create failover
> cluster but I will replicate the database first. That is my problem
> transactional replication.. That is why I asked all these questions.

Again, I'm still not sure what you mean here.

In my original post I asked why you weren't simply doing the transactional
replication throughout the day.

Then you brought up snap-shotting. This isn't a term usually used with
transactional replication.

A transactional replication setup really shouldn't lock your system, so I'm
missing something here.

> Thank you|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> Then you brought up snap-shotting. This isn't a term usually used with
> transactional replication.

Not that replication is my best game, but doesn't a transactional
replication scheme usually start with the replication of a snapshot?

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BFF41144A46Yazorman@.127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> > Then you brought up snap-shotting. This isn't a term usually used with
> > transactional replication.
> Not that replication is my best game, but doesn't a transactional
> replication scheme usually start with the replication of a snapshot?

Yes, but it's a one time game. And there are ways around that (not
necessarily clean ones unfortunately).

>
> --
> 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|||Thats the thing, I could not pass one time snapshot part. During the
initial snapshot I had problems, Our application was locked and It was
taking so long we had to cancel it. You said that there are ways around
that how ? If you give me some help on that I really appreciate it.

Also any of you did a failover clustering? I will do this first time
and I have some questions.

Thanks|||"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1147348989.967368.134060@.j73g2000cwa.googlegr oups.com...
> Thats the thing, I could not pass one time snapshot part. During the
> initial snapshot I had problems, Our application was locked and It was
> taking so long we had to cancel it. You said that there are ways around
> that how ? If you give me some help on that I really appreciate it.

Ah, now I better understand what you're talking about.

I'm a bit surpised with only 2000 transactions a day you can't fnid a quiet
time (perhaps on a weekend?) to make the snapshot.

Anyway, there's KB articles on MS on various ways of doing this (and I
believe SQL 2005 has options also that help.)

Anyway, if you CAN pause work for a shrot period of time, try something like
the following:

Do a full backup of DB on "Publisher".
Do a full restore WITH NORECOVERY on "subscriber"
Since this will take some time, you'll need to do the following (and up
until now you users could still use the system.)

Stop all access to the system
Do a transaction log backup from the "publisher"
Restore to the "subscriber", this time with RECOVERY

Setup replication, but say, "subscriber has data and schema"

Permit access to the system.

This is not guaranteed to solve your problem, but I've done it with success
before.

There's several catches to watch out for:

1) if you know your initial transaction log backup will take a long time, do
it w/o kicking folks out of the publisher and restore with NORECOVERY and
then do the transaction log backup a 2nd time (or 3rd, etc.) Trick is to
get that last transaction log backup/restore cycle as absolutely as short as
possible.

Setup as much of replication in advance as possible, i.e. setup the
publisher/distributer side of things before hand.

Script out as much as you can.

Finally.. be careful of IDENTITY columns. This is where things can break.

Note to, if you decide you need to make schema changes on the subscriber, do
this after replication is setup.

The above is not guaranteed to solve your problem but I've used it in
similar cases with success.

> Also any of you did a failover clustering? I will do this first time
> and I have some questions.
> Thanks

Need DTS to overwrite data

Newbie, so please bear with me.

I'm trying to automate a daily extract from our Personnel System to SQL server using DTS.

The Personnel system spits out a TXT file (containing approx 35 columns, 5000 records), which, is then picked up by DTS. I need this to overwrite the existing data, not duplicate it. However, after 1 successful load, if I try again I get a PK constraint error message.

The PK is the employee's PIN, but data in the other columns can change (e.g. Address_1) which is why I need to overwrite the data.

I have thought about importing the TXT file to a Temp Table, dropping the original table, then importing the data that way, but using DTS I cannot drop the table because of the constraints.

Any help greatly appreciated.i usually push data into a staging table, and then write the 'good records' into the fact table, and the 'bad records' get written to a exceptions table.

its pretty straight forward

you may need to fiddle with 'IDENTITY INSERT' in order to not get PK violations--

it just depends on what kindof PK you use.

i write a few views between the staging table and the fact table in order to filter out records that are already in the table, etc

it would be pretty straightforward (if you have a simple PK) in order to do this

basically i just issue a 'TRUNCATE TABLE' statement, and then i run most of my code in ActiveX script

and then when it is done, you fire a stored proc that writes the records from staging to fact.

cheers and good luck|||thanks for the info Aaron - i will give it a try.

Need assitance to Launch MSDE and create a new database.

Installed MSDE. Icon on system tray shows "not connected". Start, Stop &
Pause are greyed out. I am trying to connect to a database. Please help.
Regards,
Shah.
hi, Shah,
Shah wrote:
> Installed MSDE. Icon on system tray shows "not connected". Start,
> Stop & Pause are greyed out. I am trying to connect to a database.
> Please help.
the "problem" with the SQL Server Service Manager can be related with
http://support.microsoft.com/default...;EN-US;814132, but you
should be able to connect to your MSDE instance anyway...
please verify in the service management applet your instance is (correctly)
installed and running...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a report
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/de...es_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pro.../c07ppcsq.mspx
http://msdn.microsoft.com/library/de...tbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/is...04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pro.../anservog.mspx

> Thanks
|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks
|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter

Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a repor
t
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/d...>
_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
http://msdn.microsoft.com/library/d...ntbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/i.../04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pr...n/anservog.mspx

> Thanks|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter

Need advice. Dropdown tables and moving data

Hi,
I have the following problem. In my system, I'm going to have many
tables that use drop-down tables. For example, my 'Persons' table has a
column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
If I want to add a new person calld 'John' with black hair, I do this:
insert into haircolors (id,color) values (1,'brown')
insert into haircolors (id,color) values (2,'black')
insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
1,'john',2)
I also though that instead of doing this, I could insert the value
directly there. Something like this:
insert into haircolors (pk) values ('brown')
insert into haircolors (pk) values ('black')
insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
1',1,'john','brown')
And I would add a relation between those tables, so the the system would
check that the values that I have for the hair color in Persons are in
the HairColors table.
Obviously, the first solution is more elegant. But maybe for my problem
it will be better the second solution.
Here is my problem.
I am going to have multiple sites running this type of db. We are going
to have some processes that will transfer records from one site to
another. That's why we have that 'siteid' field there for Persons. My
question is:
Should I have siteid for the haircolors as well? (I mean to add a column
'SiteID' in the HairColors table) Does that make sense? In that case we
could have 2 'black' hair color records that came from different sites.
If not, everytime that I move a Person from one site to another, I would
have to 'reconvert' the id of the Haircolor to the one matching in the
new site. For example, if for 'site 1' the black hair color had ID=2,
and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
have to convert that ID from 2 to 200.
What do you guys think?
Thanks for reading everything. I know it's a little bit confusing, but I
hope you guys got the idea of my problem.
RegardsStar wrote:
> Hi,
> I have the following problem. In my system, I'm going to have many
> tables that use drop-down tables. For example, my 'Persons' table has a
> column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
> If I want to add a new person calld 'John' with black hair, I do this:
> insert into haircolors (id,color) values (1,'brown')
> insert into haircolors (id,color) values (2,'black')
> insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
> 1,'john',2)
> I also though that instead of doing this, I could insert the value
> directly there. Something like this:
> insert into haircolors (pk) values ('brown')
> insert into haircolors (pk) values ('black')
> insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
> 1',1,'john','brown')
> And I would add a relation between those tables, so the the system would
> check that the values that I have for the hair color in Persons are in
> the HairColors table.
> Obviously, the first solution is more elegant. But maybe for my problem
> it will be better the second solution.
> Here is my problem.
> I am going to have multiple sites running this type of db. We are going
> to have some processes that will transfer records from one site to
> another. That's why we have that 'siteid' field there for Persons. My
> question is:
> Should I have siteid for the haircolors as well? (I mean to add a column
> 'SiteID' in the HairColors table) Does that make sense? In that case we
> could have 2 'black' hair color records that came from different sites.
> If not, everytime that I move a Person from one site to another, I would
> have to 'reconvert' the id of the Haircolor to the one matching in the
> new site. For example, if for 'site 1' the black hair color had ID=2,
> and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
> have to convert that ID from 2 to 200.
> What do you guys think?
> Thanks for reading everything. I know it's a little bit confusing, but I
> hope you guys got the idea of my problem.
> Regards
"Dropdown tables"! (shudder) Do you really believe that the job of
tables is to model a user-interface for you?
Neither of your proposed solutions solves the problem you are getting
at. That is: "How do I ensure that all my sites encode hair colours in
the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
the same as "Brunette"? If you leave decisions like that to end users
then you will have a big task on your hands when you come to integrate
your data. If you have a standard set of codes that is centrally
maintained then you won't have that problem whatever method you use
(but do please choose a recognized standard coding convention if one
exists for your data).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
We cannot have a centralized site that can provide nice codes to each
one. Some sites will be working as an standalone sites without access
outside their network. We don't know if in the future they may be
interested in sharing some data. If they are later, they will provide
access to the remote sites temporarily, while the transfer is being made.
On the other hand, users want to be able to add new values to those
'dropdown tables' at any time.
In the hypothetical case that we could have a centralized site, some
sites wouldn't be interested in seeing the 'set of codes' from the
other 40 sites, when they have to select a value. Had you thought about
that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
example, some may be 'Arrest codes' for a specific police department. We
don't want to have site 1 to see the arrest codes from each one of the
sites!! (shudder)
For that reason, and before shuddering and criticizing take a look at
the real world with real customers. It's great to answer the newsgroups
with 3 o 4 things that you got from the books that you have read, but in
the real world things are not so 'standard' and cannot be made in the
most elegant way.
David Portas wrote:
> Star wrote:
>
>
> "Dropdown tables"! (shudder) Do you really believe that the job of
> tables is to model a user-interface for you?
> Neither of your proposed solutions solves the problem you are getting
> at. That is: "How do I ensure that all my sites encode hair colours in
> the same way?" For example, does "Blond"="Blonde"? Does "Brown" mean
> the same as "Brunette"? If you leave decisions like that to end users
> then you will have a big task on your hands when you come to integrate
> your data. If you have a standard set of codes that is centrally
> maintained then you won't have that problem whatever method you use
> (but do please choose a recognized standard coding convention if one
> exists for your data).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||For your hair color example I would forget using an artificial key
(HairColorID) and just use 'brown' or 'black' as the value. I do not
find using a number to be "elegent", though it is sometimes necessary.
For something more complicated, as you described in your second
message, I would be inclined to make the key two part, site and
ArrestCode. But I would have to see what sort of data went into an
ArrestCode master table before choosing the key.
Also, if you provide a simple example that is not representative of
the real problem, don't get too critical of the answers that reflect
your question as asked instead of the real problem. I also do not
know why you would assume that any particular advice is based on
examples in books. Believe it or not a lot of answers here are based
on years of hard won experience. For myself, just about any approach
I suggest avoiding is because I am trying to help others avoid my own
past mistakes. 8-)
Roy Harvey
Beacon Falls, CT
On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:

>Hi,
>I have the following problem. In my system, I'm going to have many
>tables that use drop-down tables. For example, my 'Persons' table has a
>column called 'FK_HairColors_ID' which is a FK of the table 'HairColors'
>If I want to add a new person calld 'John' with black hair, I do this:
>insert into haircolors (id,color) values (1,'brown')
>insert into haircolors (id,color) values (2,'black')
>insert into persons (siteid,id,name,fk_haircolors_id) values ('site 1'
>1,'john',2)
>I also though that instead of doing this, I could insert the value
>directly there. Something like this:
>insert into haircolors (pk) values ('brown')
>insert into haircolors (pk) values ('black')
>insert into persons (siteid,id,name,fk_haircolors_pk) values ('site
>1',1,'john','brown')
>And I would add a relation between those tables, so the the system would
>check that the values that I have for the hair color in Persons are in
>the HairColors table.
>Obviously, the first solution is more elegant. But maybe for my problem
>it will be better the second solution.
>Here is my problem.
>I am going to have multiple sites running this type of db. We are going
>to have some processes that will transfer records from one site to
>another. That's why we have that 'siteid' field there for Persons. My
>question is:
>Should I have siteid for the haircolors as well? (I mean to add a column
>'SiteID' in the HairColors table) Does that make sense? In that case we
>could have 2 'black' hair color records that came from different sites.
>If not, everytime that I move a Person from one site to another, I would
>have to 'reconvert' the id of the Haircolor to the one matching in the
>new site. For example, if for 'site 1' the black hair color had ID=2,
>and for 'site 2' has ID=200, and I moving a record from 1 to 2, I would
>have to convert that ID from 2 to 200.
>What do you guys think?
>Thanks for reading everything. I know it's a little bit confusing, but I
>hope you guys got the idea of my problem.
>Regards|||Thanks for your answer, Roy. I think I will end up using your
first suggestion.
Regarding David's answer... I understand that I could have given more
information about my problem, like I did in my second post, however
there are many ways of helping, and this was not the best way of doing it:

>"Dropdown tables"! (shudder) Do you really believe that the job of
>tables is to model a user-interface for you?
Don't you think? If he needed more information he could have asked
and I would have explained why I needed to have those shuddering
'Dropdown tables'. It's just I didn't think it was necessary to explain
all the reasons I was doing that. I just wanted to mention which one was
my problem.
Thanks again for helping, Roy.
Roy Harvey wrote:
> For your hair color example I would forget using an artificial key
> (HairColorID) and just use 'brown' or 'black' as the value. I do not
> find using a number to be "elegent", though it is sometimes necessary.
> For something more complicated, as you described in your second
> message, I would be inclined to make the key two part, site and
> ArrestCode. But I would have to see what sort of data went into an
> ArrestCode master table before choosing the key.
> Also, if you provide a simple example that is not representative of
> the real problem, don't get too critical of the answers that reflect
> your question as asked instead of the real problem. I also do not
> know why you would assume that any particular advice is based on
> examples in books. Believe it or not a lot of answers here are based
> on years of hard won experience. For myself, just about any approach
> I suggest avoiding is because I am trying to help others avoid my own
> past mistakes. 8-)
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 28 Mar 2006 22:46:58 +0200, Star <star@.nospam.com> wrote:
>|||Star wrote:
> We cannot have a centralized site that can provide nice codes to each
> one. Some sites will be working as an standalone sites without access
> outside their network. We don't know if in the future they may be
> interested in sharing some data. If they are later, they will provide
> access to the remote sites temporarily, while the transfer is being made.
> On the other hand, users want to be able to add new values to those
> 'dropdown tables' at any time.
In that case you will have integrate and conform the data at some
future date under either of your suggested solutions. I thought you
were asking whether you could avoid that but apparently you cannot.

> In the hypothetical case that we could have a centralized site, some
> sites wouldn't be interested in seeing the 'set of codes' from the
> other 40 sites, when they have to select a value. Had you thought about
> that?. Some 'drop-down tables' are not as simple as 'Hair Colors'... For
> example, some may be 'Arrest codes' for a specific police department. We
> don't want to have site 1 to see the arrest codes from each one of the
> sites!! (shudder)
That's a different question to the one you originally asked. In that
case you would presumably implement another table that mapped Arrect
Codes to Sites so you could track which codes each site could see.

> For that reason, and before shuddering and criticizing take a look at
> the real world with real customers. It's great to answer the newsgroups
> with 3 o 4 things that you got from the books that you have read, but in
> the real world things are not so 'standard' and cannot be made in the
> most elegant way.
I have been working with real customers' for 15 years.
The only criticism I meant to imply in my post was about your use of
the term "dropdown tables". What do you mean by that? Obviously it's
not a standard term. My interpretation was that you called them by that
name because they are used for dropdown lists in your UI. This is not a
good way to look at database design. In fact that mode of thinking
(database follows UI) leads to a lot of mistakes by developers in my
experience. Database design should proceed from business analysis to
conceptual modelling to logical modelling to physical modelling. In
that sense there is only one type of table.
So I'm not saying your solutions are right or wrong because your
example doesn't give us any criteria for that decision. What I was
suggesting was that your use of the term "dropdown table" indicates
that you may not have the right understanding of some database design
concepts.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||>Regarding David's answer... I understand that I could have given more
>information about my problem, like I did in my second post, however
>there are many ways of helping, and this was not the best way of doing it:
>
>Don't you think?....
As it happens, I see nothing wrong with Dave's answer. But everyone
sees things their own way.
Roy Harvey
Beacon Falls, CT|||Star skrev:

> Regarding David's answer... I understand that I could have given more
> information about my problem, like I did in my second post, however
> there are many ways of helping, and this was not the best way of doing it:
>
Just to settle this ;)
Star, frankly I think you over-reacted, don't be so sensitive, just
google for CELKO in this newsgroup and be thankful he didn't answer
your question (not yet, anyway).
Anyway, when asking for help on a newsgroup you should prepare yourself
for all sorts of possible answers, remember people are trying to answer
without getting paid or anything, and we all have our ups and downs.
Just keep your own calm, and in the end you probably get some good
answers, for free!
/impslayer, aka Birger Johansson|||impslayer,
yep... I have seen some of Celko's posts. I think he never tries to
help. In my opinion, he usually does the following:
1. Try to ridicule the person asking the question.
2. Recomend the person to buy his 'wonderful' books.
3. If he feels like helping, he will post a solution so theoretical that
will discourage the original poster. He would provide academic exercises
that have little application in the real world.
Regards|||
> The only criticism I meant to imply in my post was about your use of
> the term "dropdown tables". What do you mean by that? Obviously it's
> not a standard term. My interpretation was that you called them by that
> name because they are used for dropdown lists in your UI. This is not a
> good way to look at database design.
David,
Maybe the term 'drop-down tables' was not the best to use. I don't speak
English very well, and I didn't know a better way to say that.
Basically our customers needs to see a drop-down box in the UI with
different values to choose from for some of the fields. That information
needs to be stored in a table, don't you think?. That's why I called
like that. How would you call it?

Need Advice. Blog

Hello,

I am creating a simple blog system using SQL 2005.

I have a Blog table:
[BlogId] > PostId (PK), BlogTitle, ...

And a Posts table
[Posts] > PostId (PK), BlogId (FK), PostContent, PostLabels, ...

PostLabels would have the following format:
Label1,Label2,Label3, etc ...

I will need to perform 3 actions:
1. Get all posts in blog
2. Get all labels in a post
3. Get all unique existing labels in all posts in a blog and make a list.

I am not sure if my approach of using a simple labels column in my Posts table is a good idea.

So my other idea would be to add two more tables:
[BlogLabels] > BlogLabelId (PK), BlogId (FK), LabelName ...

[LabelsInPosts] > BlogLabelId (PK), PostId (PK)

So my idea is:

1. When creating a post one of the parameters would be a comma
separating string with all labels for the post.
Inside SQL Procedure I will need to loop through each label and
check if it exists in BlogLabels. If not then I added it.
For each label I add a records in LabelsInPosts.

How to create this loop? Am I thinking this right?

2. To get a list of all labels in a blog I would need to go to
BlogLabels and get all labels which are related with posts in
LabelsInPosts. Those posts must be only the ones that are related
with my given BlogId.

Grrr, this is getting really confusing for me.
Is this possible to to? How?

Please, give me some advice about all this.

Thanks,
MiguelThe second approach is definitely more scalable and is the standard way to go. You will be able to obtain all the labels with a single select.

Need advice on system performance

Hi,

I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!

What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.

THANKS MUCHtootsuite,

This is one link you might want to check out.

http://www.microsoft.com/downloads/...&displaylang=en
Also, this site has a wealth of performance information:

http://www.sql-server-performance.com/
-- Bill

<tootsuite@.gmail.comwrote in message
news:1170959288.304029.270560@.l53g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!
>
What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.
>
THANKS MUCH
>

|||Thank you

- this link shows how to do a performance audit:

http://www.sql-server-performance.c...icles_audit.asp

Need advice on ETL architecture...

I am about to design (I think it could be considered as) ETL system and here
are my thoughts on how it should work at a very high level.
My intention in this post is to find out if there is a better way to design
this.
Data is originally entered in an as400 system.
On nightly basis, openquery from sql server is used to get all the tables
into "extract_database" as is from as400.
"User_database" takes data on nightly basis from "extract_database"
and converts data into user friendly tables. These tables could be
used by front end programmers to write tsql to drive the front end.
If we have a table called "Sales" then on nightly basis "Sales" table would
get refreshed from as400 and if there is a change in particluar sales order
then I want "user_database" to have the latest change in Sales table.
Some how I also want to document the change in Sales history. In other words
I don't want to lose any data change from day to day.
I also would like to have "history_database" which would keep track of
record changes or deletion.
In summary, "extract_database" has the copy of what exactly got imported,
"user_database" has the converted data so that front end application could
tap into it, and "history_database" is used for reporting.
For daily data refresh, I would like to daily keep track of data imported
for each table.
Are there any white papers, articles, web sites, books that address this
nightly database refresh design and best practices'
Please excuse me in advance if I missed some thing.
TIA..Read about the audit tools that you can buy to do this.|||What you are describing is a transactional data warehouse. The ETL (Extract
Transform Load) programming is just the tip of the iceburg.
"sqlster" <nospam@.nospam.com> wrote in message
news:AB2E77C4-8B79-4CC4-8B0D-2FF079239E1C@.microsoft.com...
>I am about to design (I think it could be considered as) ETL system and
>here
> are my thoughts on how it should work at a very high level.
> My intention in this post is to find out if there is a better way to
> design
> this.
> Data is originally entered in an as400 system.
> On nightly basis, openquery from sql server is used to get all the tables
> into "extract_database" as is from as400.
> "User_database" takes data on nightly basis from "extract_database"
> and converts data into user friendly tables. These tables could be
> used by front end programmers to write tsql to drive the front end.
> If we have a table called "Sales" then on nightly basis "Sales" table
> would
> get refreshed from as400 and if there is a change in particluar sales
> order
> then I want "user_database" to have the latest change in Sales table.
> Some how I also want to document the change in Sales history. In other
> words
> I don't want to lose any data change from day to day.
> I also would like to have "history_database" which would keep track of
> record changes or deletion.
> In summary, "extract_database" has the copy of what exactly got imported,
> "user_database" has the converted data so that front end application could
> tap into it, and "history_database" is used for reporting.
> For daily data refresh, I would like to daily keep track of data imported
> for each table.
> Are there any white papers, articles, web sites, books that address this
> nightly database refresh design and best practices'
> Please excuse me in advance if I missed some thing.
> TIA..
>

Need advice on a Data Import Routine

Hello,
I have a system that I am writing to automaticly import Credit Transaction
data into a SQL Server 2000 Database. I am using a VB.Net application to
detect when the file arives and prep it for parsing. The file is
aproximately 10Mb of relatively complex hierarchal data that is defined by 2
character tokens at the begining of each data line. The structure breaks ou
t
into aproximately 6 parent-child related tables with numerous lookup tables
on each primary table.
The previous aproach loaded each line of raw data into an Import table and
used a Stored Procedure to break down the information into the tables. The
Stored Procedure is large and very complex. It also has the problem that th
e
Parsing takes so long I need to implement a very long timeout on my
connection or I get errors. It is also difficult to track progress through
the Import for the user to see.
The options I see at this point are;
1. Use the current approach of loading the raw data into an Import table and
Parsing with a Stored Procedure.
2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data into
the apropriate tables.
3. Use a mixed aproach of breaking the massive Stored Procedure into smaller
procedures and managing the steps with VB.Net.
I am looking for any tips and advice that the guru's out there are willing
to give. Especially a comparision of the effiency of doing ADO.NET bulk
inserts vs. Stored Procedure parsing.
Thanks for any help given.
Doug
PS - Sorry for the cross posting in dotnet.general but I am hoping to get a
balanced set of views on this issue.Doug,
I have heard that sql server is not good for parsing routines, so this can
lead you to do it in your client app.
What about using XML to represent this complex hierarchal data?
AMB
"Doug R" wrote:

> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup table
s
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables. Th
e
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress throug
h
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table a
nd
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data in
to
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into small
er
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||I have done the same type of thing in the recent past. The methodology I
have used is to execute a BCP statement into a properly structured table.
BCP is native SQL Server, much faster than a Bulk Insert statement, deals
with security in-line (you specify user.password in the command) and if the
data is consistent (the type of data appears in the same position in each
line) the BCP command will import into the properly formatted table without
any parsing required. The syntax for command is listed in Books On-line.
You will need to create a format file for the import to use. This is simply
executing a BCP FORMAT command against the desired SQL Server destination
table. Once the format file is built, you use that to define for the BCP IN
command the data structure you are importing. It is an extra step to create
the format file, but once you have it you place it in a location where your
process can access it will do the heavy lifting for you.
The advantages to this process are several. First, because you are
pre-defining a data structure to the import process, no additional parsing
is needed. Second, because BCP runs in a DOS environment it is very fast.
Third, because user ID and password are contained in the command line,
connection strings and other security items are unnecessary.
There is one additional caveat to using BCP in a procedure. You will have
to run it in a DOS environment using the xp_cmdshell extended procedure.
This allows SQL Server to run DOS commands from within a stored procedure.
The user ID that runs this will need privileges assigned to the
master.dbo.xp_cmdshell proc in SQL Server Enterprise Manager.
My suggestion for implementation would be to write a simple stored procedure
that builds the BCP statement as a Varchar variable and passes it to
master.dbo.xp_cmdshell to run. Call this stored procedure using the execute
nonquery option of the SQLServer.SQLCommand object in VB.Net. Doing this
will allow for full automation of the import process.
If you need any further assistance you can contact me at
groutme_alternate@.sbcglobal.net.
Good Luck:
groutme in SO Cal.
"Doug R" <DougR@.discussions.microsoft.com> wrote in message
news:D7FEFA89-E6BD-49C9-A0E5-5464130B3440@.microsoft.com...
> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup
tables
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables.
The
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress
through
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table
and
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data
into
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into
smaller
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||Thanks for the reply. Regretably I have no control over the form of the
input data or I would be using XML. The data is being sent to us from an
outside business.
Are you familiar with ADO.NET for doing bulk inserts? I would be very
interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
An alternative would possibly be something that parses the import data file
into XML before the insert. Any ideas on how this could be done efficiently
?
"Alejandro Mesa" wrote:
> Doug,
> I have heard that sql server is not good for parsing routines, so this can
> lead you to do it in your client app.
> What about using XML to represent this complex hierarchal data?
>
> AMB
> "Doug R" wrote:
>|||If your stored procedure uses cursors or some other method to processes the
data line-by-line then probably you can rewrite some or all of it as
set-based SQL code for a significant performance improvement.

> Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing
Bulk Insert is equally possible from TSQL or ADO.NET code so this doesn't
seem to me like a meaningful comparison. The real question is whether TSQL o
r
client code can transform the data efficiently. Since this is fundamentally
about data manipulation my money would be on TSQL ... but that's without
seeing the detail of the actual problem.
David Portas
SQL Server MVP
--|||Doug,
If you are subcribed to SQL Server Magazine, there is a good article about
this theme.
Bulk-Insert Options for ADO.NET
http://www.windowsitpro.com/Article...>
oad_7pv0.asp
ADO.NET 2.0 will include this feature.
http://www.windowsitpro.com/SQLServ...62.html

AMB
"Doug R" wrote:
> Thanks for the reply. Regretably I have no control over the form of the
> input data or I would be using XML. The data is being sent to us from an
> outside business.
> Are you familiar with ADO.NET for doing bulk inserts? I would be very
> interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
> An alternative would possibly be something that parses the import data fil
e
> into XML before the insert. Any ideas on how this could be done efficient
ly?
>
> "Alejandro Mesa" wrote:
>