I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.ar
Daniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
sql
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Friday, March 30, 2012
Optimizing SQL Filtering - Conceptual question
I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
Optimizing SQL Filtering - Conceptual question
I expose a simple example of what I'm trying to figure it out:
table1:
==== field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
--
Daniel E. Alvarez
IMS Soluciones Tecnológicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnológicas S.A.
> quilate@.kropol.com.ar
>
table1:
==== field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
--
Daniel E. Alvarez
IMS Soluciones Tecnológicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnológicas S.A.
> quilate@.kropol.com.ar
>
Monday, March 12, 2012
Optimal Physical Layer config SQL Server on IBM xSeries 345 server
Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
most of the SQL Servers databases on it (~40). I am trying to figure out the
best configuration for the disk subsystem for both reasonable performance and
disk cost savings and reduced down time. The server may have upto 6 X 73.4
GB SCSI disks. Can anyone suggest any ideas?
DISK 1 and
DISK 2 as RAID 1 â' System & Tlog
DISK 3 and
DISK 4 as RAID 1 - DATA
DISK 5 and
DISK 6 as RAID 1 â' DATA
---
DISK 1 and
DISK 2 as RAID 1 System & Tempdb
DISK 3 and
DISK 4 as RAID 1 â' Tlogs
DISK 5 and
DISK 6 as AID 1 â' DATA
---
Thanks."Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:493C572F-C196-4301-B2D7-4AD665B2A48C@.microsoft.com...
> Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
> most of the SQL Servers databases on it (~40). I am trying to figure out
> the
> best configuration for the disk subsystem for both reasonable performance
> and
> disk cost savings and reduced down time. The server may have upto 6 X
> 73.4
> GB SCSI disks. Can anyone suggest any ideas?
> DISK 1 and
> DISK 2 as RAID 1 - System & Tlog
> DISK 3 and
> DISK 4 as RAID 1 - DATA
> DISK 5 and
> DISK 6 as RAID 1 - DATA
> ---
> DISK 1 and
> DISK 2 as RAID 1 System & Tempdb
> DISK 3 and
> DISK 4 as RAID 1 - Tlogs
> DISK 5 and
> DISK 6 as AID 1 - DATA
> ---
>
Physical optimization like this mostly involves aranging the spindles to
maximize the number of spindes used and minimize the contention for
spindles. However with 40 databases and 40 different workloads on the
server you will probably not be able to optimize the physical layout much.
BTW, I wouldn't consolodate onto the x345 if I could help it because it's a
32-bit machine. The newer x346 can run 64bit Windows and SQL Server 2005.
This will let you use much more memory, and minimize how much your workload
utilizes the disks.
David|||I second that emotion.
I'd also like to add that many writes wind up being cached and first written
into the transaction log... the point being that you could probably do all
your data on one RAID-5 array and have more actual space as a result or keep
one remaining disk as a spare.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4b2YHzJGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
> Physical optimization like this mostly involves aranging the spindles to
> maximize the number of spindes used and minimize the contention for
> spindles. However with 40 databases and 40 different workloads on the
> server you will probably not be able to optimize the physical layout much.
>
most of the SQL Servers databases on it (~40). I am trying to figure out the
best configuration for the disk subsystem for both reasonable performance and
disk cost savings and reduced down time. The server may have upto 6 X 73.4
GB SCSI disks. Can anyone suggest any ideas?
DISK 1 and
DISK 2 as RAID 1 â' System & Tlog
DISK 3 and
DISK 4 as RAID 1 - DATA
DISK 5 and
DISK 6 as RAID 1 â' DATA
---
DISK 1 and
DISK 2 as RAID 1 System & Tempdb
DISK 3 and
DISK 4 as RAID 1 â' Tlogs
DISK 5 and
DISK 6 as AID 1 â' DATA
---
Thanks."Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:493C572F-C196-4301-B2D7-4AD665B2A48C@.microsoft.com...
> Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
> most of the SQL Servers databases on it (~40). I am trying to figure out
> the
> best configuration for the disk subsystem for both reasonable performance
> and
> disk cost savings and reduced down time. The server may have upto 6 X
> 73.4
> GB SCSI disks. Can anyone suggest any ideas?
> DISK 1 and
> DISK 2 as RAID 1 - System & Tlog
> DISK 3 and
> DISK 4 as RAID 1 - DATA
> DISK 5 and
> DISK 6 as RAID 1 - DATA
> ---
> DISK 1 and
> DISK 2 as RAID 1 System & Tempdb
> DISK 3 and
> DISK 4 as RAID 1 - Tlogs
> DISK 5 and
> DISK 6 as AID 1 - DATA
> ---
>
Physical optimization like this mostly involves aranging the spindles to
maximize the number of spindes used and minimize the contention for
spindles. However with 40 databases and 40 different workloads on the
server you will probably not be able to optimize the physical layout much.
BTW, I wouldn't consolodate onto the x345 if I could help it because it's a
32-bit machine. The newer x346 can run 64bit Windows and SQL Server 2005.
This will let you use much more memory, and minimize how much your workload
utilizes the disks.
David|||I second that emotion.
I'd also like to add that many writes wind up being cached and first written
into the transaction log... the point being that you could probably do all
your data on one RAID-5 array and have more actual space as a result or keep
one remaining disk as a spare.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4b2YHzJGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
> Physical optimization like this mostly involves aranging the spindles to
> maximize the number of spindes used and minimize the contention for
> spindles. However with 40 databases and 40 different workloads on the
> server you will probably not be able to optimize the physical layout much.
>
Friday, March 9, 2012
Opinions needed
I am somewhat new to database technologies, I only have a basic
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like!
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> Our
> delimited
> output
which
> I
are
why
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.
>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
How's your experience
us enough. :-)
have a basic
database (Tables and
create route lists for
clients customers.
>Our
tab or comma
>delimited
into SQL and then
>output
routes.
geographical area which
>I
(Since postal codes are
coverage)
with Crystal, why
features in Access?
>
>.
>
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like!
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> Our
> delimited
> output
which
> I
are
why
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.
>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
How's your experience
us enough. :-)
have a basic
database (Tables and
create route lists for
clients customers.
>Our
tab or comma
>delimited
into SQL and then
>output
routes.
geographical area which
>I
(Since postal codes are
coverage)
with Crystal, why
features in Access?
>
>.
>
Opinions needed
I am somewhat new to database technologies, I only have a basic
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like! :)
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> > If it's a multi-user application you should use SQL Server.
> > If it's just a single user import and print application, Access probably
> > will do.
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> > news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > > I am somewhat new to database technologies, I only have a basic
> > > understanding of relational databases.
> > >
> > > I need to figure out the best way to set up a database (Tables and
> > fields).
> > > Crystal reports will be used for reporting.
> > >
> > > I am in the distribution business and I need to create route lists for
> > > people who will go out and deliver catalogs to our clients customers.
> Our
> > > client will send me their database of customers via tab or comma
> delimited
> > > file. What I then need to do is import this file into SQL and then
> output
> > > some nice looking reports for our carriers delivery routes.
> > >
> > > The routes will be based on streets in a logical geographical area
which
> I
> > > hope I will be able to organize by postal code. (Since postal codes
are
> > > already organized for optimal delivery time and coverage)
> > >
> > > I should add that I'm not even sure I need to use SQL with Crystal,
why
> > > wouldn't I use Access and just use the reporting features in Access?
> > >
> > > Any help will be appreciated.
> > >
> > > Shawn Johnson
> > >
> > >
> >
> >
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.
>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
>> How many users? How much data? How's your budget?
How's your experience
>> with SQL Server and/or Access?
>> Either can do what you've told us, but you've not told
us enough. :-)
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
>> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
>> > I am somewhat new to database technologies, I only
have a basic
>> > understanding of relational databases.
>> >
>> > I need to figure out the best way to set up a
database (Tables and
>> fields).
>> > Crystal reports will be used for reporting.
>> >
>> > I am in the distribution business and I need to
create route lists for
>> > people who will go out and deliver catalogs to our
clients customers.
>Our
>> > client will send me their database of customers via
tab or comma
>delimited
>> > file. What I then need to do is import this file
into SQL and then
>output
>> > some nice looking reports for our carriers delivery
routes.
>> >
>> > The routes will be based on streets in a logical
geographical area which
>I
>> > hope I will be able to organize by postal code.
(Since postal codes are
>> > already organized for optimal delivery time and
coverage)
>> >
>> > I should add that I'm not even sure I need to use SQL
with Crystal, why
>> > wouldn't I use Access and just use the reporting
features in Access?
>> >
>> > Any help will be appreciated.
>> >
>> > Shawn Johnson
>> >
>> >
>>
>
>.
>
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like! :)
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> > If it's a multi-user application you should use SQL Server.
> > If it's just a single user import and print application, Access probably
> > will do.
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> > news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > > I am somewhat new to database technologies, I only have a basic
> > > understanding of relational databases.
> > >
> > > I need to figure out the best way to set up a database (Tables and
> > fields).
> > > Crystal reports will be used for reporting.
> > >
> > > I am in the distribution business and I need to create route lists for
> > > people who will go out and deliver catalogs to our clients customers.
> Our
> > > client will send me their database of customers via tab or comma
> delimited
> > > file. What I then need to do is import this file into SQL and then
> output
> > > some nice looking reports for our carriers delivery routes.
> > >
> > > The routes will be based on streets in a logical geographical area
which
> I
> > > hope I will be able to organize by postal code. (Since postal codes
are
> > > already organized for optimal delivery time and coverage)
> > >
> > > I should add that I'm not even sure I need to use SQL with Crystal,
why
> > > wouldn't I use Access and just use the reporting features in Access?
> > >
> > > Any help will be appreciated.
> > >
> > > Shawn Johnson
> > >
> > >
> >
> >
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.
>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
>> How many users? How much data? How's your budget?
How's your experience
>> with SQL Server and/or Access?
>> Either can do what you've told us, but you've not told
us enough. :-)
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
>> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
>> > I am somewhat new to database technologies, I only
have a basic
>> > understanding of relational databases.
>> >
>> > I need to figure out the best way to set up a
database (Tables and
>> fields).
>> > Crystal reports will be used for reporting.
>> >
>> > I am in the distribution business and I need to
create route lists for
>> > people who will go out and deliver catalogs to our
clients customers.
>Our
>> > client will send me their database of customers via
tab or comma
>delimited
>> > file. What I then need to do is import this file
into SQL and then
>output
>> > some nice looking reports for our carriers delivery
routes.
>> >
>> > The routes will be based on streets in a logical
geographical area which
>I
>> > hope I will be able to organize by postal code.
(Since postal codes are
>> > already organized for optimal delivery time and
coverage)
>> >
>> > I should add that I'm not even sure I need to use SQL
with Crystal, why
>> > wouldn't I use Access and just use the reporting
features in Access?
>> >
>> > Any help will be appreciated.
>> >
>> > Shawn Johnson
>> >
>> >
>>
>
>.
>
Wednesday, March 7, 2012
operationg system requirement
I am in the process of learning SQL from scratch and figure it would be helpful if I can run a local server and experiment with it as I move along some book reading. I have a few questions regarding the operating system requirement for running a localhost server and hope some of you can answer to my novice questions. Can I setup a server under Windows XP? when I looked thru the documentation it indicated only windows 2000 and other Microsoft server OS will support a SQL server setup. Why then it also indicate XP as compatible? Is it because it's compatible as a client? If I do have to run a server under a different operationg system, what is the best approach for setting up another server on my only PC system along with my XP. Thanks for any answers and comments in advance.
Does "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
Are you talking about SQL 2005? XP does support SQL 2005, but it depends on which edition you have. See the following link for more information about which edition is supported on which OS. http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx
|||I'm not completely clear on whether I'm covered for a developer editionDoes "Windows XP with SP 2 or later" mean that the media center edition 2005 should suffice?
|||
Short answer: Yes.
Please see topic "Hardware and Software Requirementse for Installing SQL Server 2005" for more information.
http://msdn2.microsoft.com/en-us/library/ms143506(SQL.90).aspx
|||Short Answer: Thanks|||so with an athlon 64 X2 dual Core processor 3800+ 2Ghz I should be ordering the
x64 developer version rather than win32? Or would the latter also work (and be compatible with an older processor)
|||
x86 can install on X64 machine, but you should install x64 version if you can.
Monday, February 20, 2012
OPENXML Namespace
I am new to using XML. I have been given the following structure and
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa
-msg
xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)
Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:
> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa-msg
> xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>
|||Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
[vbcol=seagreen]
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
>)
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
>)
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa
-msgxmlns
="http://www.company.com/Schema/aaa"xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa
-msg>'EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)
Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns
="http://www.company.com/Schema/aaa"xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa
-msg/aaa:body/bbb:document/bbb:service_request',2)WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa
-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:
> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa
-msg> xmlns
="http://www.company.com/Schema/aaa"> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa
-msg>'> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>
|||Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
[vbcol=seagreen]
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns
="http://www.company.com/Schema/aaa"> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa
-msg/aaa:body/bbb:document/bbb:service_request',2)>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
>)
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa
-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
>)
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:
OPENXML Namespace
I am new to using XML. I have been given the following structure and
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa:aaa-msg
xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa:aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bb
b:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value'
,
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:
> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa:aaa-msg
> xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa:aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>|||
Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
> )
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/b
bb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value
',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
> )
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:
>
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa:aaa-msg
xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa:aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bb
b:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value'
,
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:
> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa:aaa-msg
> xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa:aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>|||
Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
> )
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/b
bb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value
',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
> )
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:
>
Subscribe to:
Comments (Atom)