DB architecture

Hi there.
We are building a new mission critical application in our company, using SQL
server 2000 as the RDBMS. The new database is replacing a legacy system that
used to run in two platforms: the day to day operations (OLTP) was
maintained in a small DB2 database running on a OS/2 PC (only 1 week of
data) and the rest was moved periodically to an iseries IBM server (DB2).
all the OLTP was done on the PC, and most of the reporting was done against
the iseries DB2 database.
So far we only have one database to replace the two systems mentioned. We
are planning to either separate the data and have the reporting done in
another SQL server machine (with the same schema,and using log shipping) or
create a set of tables that would pre-process the information and would be
used by the reporting tools. When planning for theses, we created a set of
views tha are being use by our current reports (this layering protects us if
we need to change the underlying schema). The current DB is highly
normalized and I don't think would operate well for OLAP. Is the log
shipping approach recommended? Is there a better alternative? I wouldn'
like to have to maintain two different schemas.
Thank you,
Pedro."PeyoQuintero" <pedroquintero@.earthlink.net> wrote in message
news:u73cc.10784$NL4.2990@.newsread3.news.atl.earthlink.net...
> Hi there.
> We are building a new mission critical application in our company, using
SQL
> server 2000 as the RDBMS. The new database is replacing a legacy system
that
> used to run in two platforms: the day to day operations (OLTP) was
> maintained in a small DB2 database running on a OS/2 PC (only 1 week of
> data) and the rest was moved periodically to an iseries IBM server (DB2).
> all the OLTP was done on the PC, and most of the reporting was done
against
> the iseries DB2 database.
> So far we only have one database to replace the two systems mentioned. We
> are planning to either separate the data and have the reporting done in
> another SQL server machine (with the same schema,and using log shipping)
or
> create a set of tables that would pre-process the information and would be
> used by the reporting tools. When planning for theses, we created a set of
> views tha are being use by our current reports (this layering protects us
if
> we need to change the underlying schema). The current DB is highly
> normalized and I don't think would operate well for OLAP. Is the log
> shipping approach recommended? Is there a better alternative? I wouldn'
> like to have to maintain two different schemas.
> Thank you,
>
The answer, as so often with these sorts of questions, is "It depends". If
you want the maximum performance then on the OLTP database have a highly
normalized schema and few indexes. Normalized data means the integrity of
your data is easily maintained.
On the OLAP database, if no-one is updating the data directly, then you
already know the data is correct. Normalization is no longer required, and
the speed of your queries can be improved by creating tables that reflect
your views. No messy or slow joins for SQL to deal with. Bung in your
indexes to speed the filtering and grouping of your data. Include
calculated and aggragated data directly in your tables.
To maintain these different schemas create DTS jobs to transform and move
your data from one database to the other.
Of course, the downside of this approach, as you've identified, is
maintaining two schemas, but the OLAP database (apart from the automated DTS
jobs) is a read-only database, and therefore should not need that much
maintaining once up and running. Also, log shipping will typically have
less latency, but in your old model you imply that there was a weekly
upload, so that would not be an issue.
Log Shipping works only if both databases have the same schema. It is much
simpler than DTS, but less flexible as well.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,- 30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112
))
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.droptable.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it c
ame back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.droptable.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of[vbcol=seagreen]
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
James
Try:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
David Portas
SQL Server MVP
|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.sqlmonster.com
|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.sqlmonster.com
|||Thank you both.
|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>
|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>
|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...[vbcol=seagreen]
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
--
Message posted via http://www.sqlmonster.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
--
Message posted via http://www.sqlmonster.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
--
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
--
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> >I have a view that I'm building in which I need to get all records that
> >have
> > an InvoicedDate in in the past 30 days. So basically I set up part of
the
> > where clause as:
> >
> > (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> >
> > Which doesn't work because the function returns a date and a time,
whereas
> > I'm only storing dates in the InvoicedDate field. So, long story short,
> > it
> > doesn't get the dates from exactly 30 days ago. I could roll some kind
of
> > hack where I add one I guess, but that seems really hokey. What's a
> > better
> > way to accomplish this?
> >
> > Thanks!
> > James
> >
> >
>

Dates overlow

Dear All,
When I enter a date with the year before 1950, it is rejected with an
"overflow" message.
I'm building an application that uses dates that ranges from 1200 till 2004.
So, does any body know how to solve this problem?
Regards,
Mohamed El Wakil
Teaching Assistant
Information Systems Department,
Faculty of Computers and Information,
Cairo University - Cairo
http://mohamedelwakil.tripod.com
Please, reply to mohamed.elwakil@.omeldonia.com
SQL Server accepts date from 1753 to 9999 for the datetime datatype. If you need to go out of this
span, you need to use some other representation. A string representation is one option. A number of
integer columns (one for each element) is another. One integer column counting seconds from a
certain reference date is yet another option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till 2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||The exact "overflow" error would be helpful. Does it come from SQL Server
or from whatever application you are using to enter the date?
Are you using two digits for the year? I recommend that you use more...
There are easier ways to enter dates than typing them all in...a WHILE loop
might be easier!
You may have some problems entering dates earlier than 1753.
From Books Online:
datetime and smalldatetime
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Keith
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||Date outside the range 1753-9999 is not accepted at datetime type. Look for
datetime and smalldatetime in BOL for more detail.
You can come up with your own definition, not as datetime, but such as a
char column, to accommodate your needs.
Quentin
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>

datepart

I'm pretty new to this VB Scripting, I'm building a webpage where I'd like to be able to pick out month, day or year from a date variable, I understand this can be done by the datepart function, but it seems like I need to include a certain file or something to make it work?
It generates error '800a0005' when I try to run the code as below

d=datepart(mm,date)

You are asking in the wrong forum!!