One more - SQL query

Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Hello group
My english is very poor.
I'm beginner in sql and I've problem?
How get from table only first and last record with idPerson.
example:
Tables in SQL
EventLog
idPerson LogTime IdKomp
1 9:00 1
2 9:10 2
2 9:15 4
3 10:00 1
4 11:00 3
2 13:00 2
1 15:00 3
KompTable
IdKomp KompName
1 uranus
2 jupiter
3 mars
4 saturn
PersonTable
IdPerson PersonName
1 Jon
2 Bob
3 Tom
Select ..... ('?) order by idPerson
result:
PersonName MinTime KompName MaxTime KompName
Jon 9:00 uranus 15:00 mars
Bob 9:10 jupiter 13:00 jupiter
Tom 10:00 uranus (null) (null)
...
How join this table, meybe create temporary table.
Please help.Thanks LUV SQL
This is simple and work very good.
Good for You
PawelR
LUV SQL wrote:
> Here is one solution.
> Create a in-line view / table, which has the values you are after. Called this E_List.
> Then join the EventLog table to it (twice), to get the details.
> Then it's joining the other tables off to get the dispalyable results.
> You could create the in-line view as an actual view.
> SELECT P.PersonName, E1.LogTime, K1.KompName, E2.LogTime, K2.KompName
> FROM ( SELECT EventLog.idPerson, MIN(EventLog.logtime) AS min_time, MAX(EventLog.logtime) AS max_time
> FROM EventLog
> GROUP BY EventLog.idPerson) AS E_List
> JOIN EventLog AS E1 ON (E_List.idPerson = E1.idPerson AND E_List.Min_Time = E1.LogTime)
> JOIN EventLog AS E2 ON (E_List.idPerson = E2.idPerson AND E_List.Max_Time = E2.LogTime)
> JOIN PersonTable P ON (P.idPerson = E1.idPerson)
> JOIN KompTable K1 ON (E1.idKomp = K1.IdKomp)
> JOIN KompTable K2 ON (E2.idKomp = K2.IdKomp)

One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql

On excel export

We have couple of group in table on one group when we try to export to excel
it supress the data visible to the user.
Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
as the size of it is as such.
What property can we provide to dynamically increase the row height when we
export.On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.
It sounds like you are dealing with a merged cells issue in Excel once
the report is exported. The best ways to avoid this is to do the
following:
- Make sure that, if you have multiple tables/controls/matrices above
each other, that they are all touching each other.
- Make sure that the last table/etc control on the report (in Layout
view) is touching the bottom of the report border with no extra space
available.
- Make sure that all controls touch the right-side border of the
report and if you have smaller width controls (in particular
textboxes) in the report, make sure to extend the textbox controls out
to the full width of the report and same width as the widest control
on the report (normally, the table/matrix controls).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||It didnt slove the issue,
I have list within an list and we have section 1 thru 7. We display the list
based the parameters seleted from report for some result section might start
from 3 thru 6 . In such case when try to export to excel i get huge gaps as i
am missing section 1 thru 2 in the report.
How do we avoid those spaces when exporting to excel,
"EMartinez" wrote:
> On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> > We have couple of group in table on one group when we try to export to excel
> > it supress the data visible to the user.
> >
> > Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> > as the size of it is as such.
> > What property can we provide to dynamically increase the row height when we
> > export.
>
> It sounds like you are dealing with a merged cells issue in Excel once
> the report is exported. The best ways to avoid this is to do the
> following:
> - Make sure that, if you have multiple tables/controls/matrices above
> each other, that they are all touching each other.
> - Make sure that the last table/etc control on the report (in Layout
> view) is touching the bottom of the report border with no extra space
> available.
> - Make sure that all controls touch the right-side border of the
> report and if you have smaller width controls (in particular
> textboxes) in the report, make sure to extend the textbox controls out
> to the full width of the report and same width as the widest control
> on the report (normally, the table/matrix controls).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I am getting the same issue, I have a mixture of TextBoxes and images in the
row and if I take the image cells away the row heights amend themselves
automatically but when the images are in the row they dont resize.
Any ideas? Any Help? Anything?
Anyone
"NAVIN.D" wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.|||I am having the same problem on exporting a report containing matrixes, and I
don't know what to try next. This report needs to go into production very
soon.
I inserted a rectangle containing textboxes for column headers in the left
corner of the matrix as instructed. When the matrix is exported to Excel,
there are 2 separate cells where ONE extra pixel, .08in, is inserted between
2 cells, causing a merged cell. There is no apparent reason. I have checked
the alignment and width of the columns and textboxes. I tried it with and
without borders on the textboxes. Nothing is working. When I exported the
matrix after I removed the rectangle and textboxes from the left corner the
export worked correctly. Are there any solutions?

OLTP Best Practices

Are there any best practices for high volume OLTP systems (20M trans / day)?
Such as sp_updatestats, index defrag, file group alignment, online backup,
etc...
Any advice is greatly appreciated.
Thanks
Bryan DoveI am looking for some general best practices. Such as how often should
sp_updatestats be run, how often should the t-log be backed up, etc... We
are having a debate over what is necessary, versus what is overkill for many
of the maintenance-type processes.
Thanks
Bryan Dove
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> They are all good practices for most OLTP systems but how, when etc
depends
> a lot on each individual app's needs. Maybe this is a good place to
start.
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> > Are there any best practices for high volume OLTP systems (20M trans /
> day)?
> > Such as sp_updatestats, index defrag, file group alignment, online
backup,
> > etc...
> >
> > Any advice is greatly appreciated.
> >
> > Thanks
> >
> > Bryan Dove
> >
> >
>|||Hi Bryan,
I think how often the transaction log backup should be run is dependent on
your actual requirements. For example, if you have the following backup
plan:
Time Event
8:00 A.M. Back up database
Noon Back up transaction log
4:00 P.M. Back up transaction log
6:00 P.M. Back up database
8:00 P.M. Back up transaction log
10:00 P.M. Failure occurs
Then we may lose the data between 8PM and 10PM.
In "Analyzing Availability and Recovery Requirements" topic in SQL Server
2000 Books Online, there are some basic questions to help you analyze your
availability and recovery requirements:
What are your availability requirements? What portion of each day must the
database be online?
What is the financial cost of downtime to your business?
If you experience media failure, such as a failing disk drive, what is the
acceptable downtime?
In case of a disaster, such as the loss of a server in a fire, what is the
acceptable downtime?
How important is it to never lose a change?
How easy would it be to re-create lost data?
Does your organization employ system or database administrators?
Who will be responsible for performing backup and recovery operations, and
how will they be trained?
Here are some questions to help you choose the tools, techniques, and
hardware appropriate for your site:
How large is each database?
How often does the data in each database change?
Are some tables modified more often than others?
What are your critical database production periods?
When does the database experience heavy use, resulting in frequent inserts
and updates?
Is transaction log space consumption likely to be a problem due to heavy
update activity?
Is your database subject to periodic bulk data loading?
Is your database subject to risky updates or application errors that may
not be detected immediately?
Is your database server part of a SQL Server 2000 failover cluster for high
availability?
Is your database in a multi-server environment with centralized
administration?
"Planning for Disaster Recovery"
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_7kvb.asp>
Designing a Backup and Restore Strategy
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_63eh.asp>
- Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
500/wcblurb120500.asp>
- INF: Disaster Recovery Planning for SQL Server
<http://support.microsoft.com/?kbid=169039>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
nol/sql/reskit/sql2000/part4/c1561.asp>
In addition, making some experiments and comparisons would be necessary in
a production system, as it is more effective.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Fri, 26 Sep 2003 09:28:03 -0400
| Lines: 46
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.5.227.202
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I am looking for some general best practices. Such as how often should
| sp_updatestats be run, how often should the t-log be backed up, etc... We
| are having a debate over what is necessary, versus what is overkill for
many
| of the maintenance-type processes.
|
| Thanks
|
| Bryan Dove
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > They are all good practices for most OLTP systems but how, when etc
| depends
| > a lot on each individual app's needs. Maybe this is a good place to
| start.
| >
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/maintain/operate/opsguide/default.asp
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > > Are there any best practices for high volume OLTP systems (20M trans /
| > day)?
| > > Such as sp_updatestats, index defrag, file group alignment, online
| backup,
| > > etc...
| > >
| > > Any advice is greatly appreciated.
| > >
| > > Thanks
| > >
| > > Bryan Dove
| > >
| > >
| >
| >
|
|
||||Good advice on the tlog backups.
What about the sp_updatestats. How often should that take place? Our
database is an OLTP, third normal form, about 20,000,000 trans per day.
Thanks
Bryan Dove
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
> Hi Bryan,
> I think how often the transaction log backup should be run is dependent on
> your actual requirements. For example, if you have the following backup
> plan:
> Time Event
> 8:00 A.M. Back up database
> Noon Back up transaction log
> 4:00 P.M. Back up transaction log
> 6:00 P.M. Back up database
> 8:00 P.M. Back up transaction log
> 10:00 P.M. Failure occurs
> Then we may lose the data between 8PM and 10PM.
> In "Analyzing Availability and Recovery Requirements" topic in SQL Server
> 2000 Books Online, there are some basic questions to help you analyze your
> availability and recovery requirements:
> What are your availability requirements? What portion of each day must the
> database be online?
>
> What is the financial cost of downtime to your business?
>
> If you experience media failure, such as a failing disk drive, what is the
> acceptable downtime?
>
> In case of a disaster, such as the loss of a server in a fire, what is the
> acceptable downtime?
>
> How important is it to never lose a change?
>
> How easy would it be to re-create lost data?
>
> Does your organization employ system or database administrators?
>
> Who will be responsible for performing backup and recovery operations, and
> how will they be trained?
> Here are some questions to help you choose the tools, techniques, and
> hardware appropriate for your site:
> How large is each database?
>
> How often does the data in each database change?
>
> Are some tables modified more often than others?
>
> What are your critical database production periods?
>
> When does the database experience heavy use, resulting in frequent inserts
> and updates?
>
> Is transaction log space consumption likely to be a problem due to heavy
> update activity?
>
> Is your database subject to periodic bulk data loading?
>
> Is your database subject to risky updates or application errors that may
> not be detected immediately?
>
> Is your database server part of a SQL Server 2000 failover cluster for
high
> availability?
>
> Is your database in a multi-server environment with centralized
> administration?
>
> "Planning for Disaster Recovery"
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_7kvb.asp>
> Designing a Backup and Restore Strategy
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_63eh.asp>
> - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
>
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
> 500/wcblurb120500.asp>
> - INF: Disaster Recovery Planning for SQL Server
> <http://support.microsoft.com/?kbid=169039>
>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
> nol/sql/reskit/sql2000/part4/c1561.asp>
> In addition, making some experiments and comparisons would be necessary in
> a production system, as it is more effective.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
> | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
> <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: OLTP Best Practices
> | Date: Fri, 26 Sep 2003 09:28:03 -0400
> | Lines: 46
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 12.5.227.202
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I am looking for some general best practices. Such as how often should
> | sp_updatestats be run, how often should the t-log be backed up, etc...
We
> | are having a debate over what is necessary, versus what is overkill for
> many
> | of the maintenance-type processes.
> |
> | Thanks
> |
> | Bryan Dove
> |
> |
> |
> | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> | > They are all good practices for most OLTP systems but how, when etc
> | depends
> | > a lot on each individual app's needs. Maybe this is a good place to
> | start.
> | >
> | >
> |
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
> ol/sql/maintain/operate/opsguide/default.asp
> | >
> | > --
> | >
> | > Andrew J. Kelly
> | > SQL Server MVP
> | >
> | >
> | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> | > > Are there any best practices for high volume OLTP systems (20M trans
/
> | > day)?
> | > > Such as sp_updatestats, index defrag, file group alignment, online
> | backup,
> | > > etc...
> | > >
> | > > Any advice is greatly appreciated.
> | > >
> | > > Thanks
> | > >
> | > > Bryan Dove
> | > >
> | > >
> | >
> | >
> |
> |
> |
>|||Hi Bryan,
sp_updatestats runs UPDATE STATISTICS against all user-defined tables in
the current database. In addition, the statistics update may cause stored
procedures that access the table data to be re-compiled in SQL Server. It
will reflect the up-to-date data distribution of the database data and may
let SQL Server choose better execution plans. However, re-compilation of
stored procedures can impact SQL Server performance very much.
According to my experience, if you find your stored procedures performance
degrade after long time of running and recompiling it can resolve the
problem, then it indicates that SQL Server may need the up-to-date
statistics. Then we can run sp_updatestats periodically (usually daily
update is fine.)
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
<#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
<ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Sun, 28 Sep 2003 19:26:10 -0400
| Lines: 201
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <OWK$ofhhDHA.616@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: pcp648536pcs.mkethn01.fl.comcast.net 68.35.203.37
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308722
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Good advice on the tlog backups.
|
| What about the sp_updatestats. How often should that take place? Our
| database is an OLTP, third normal form, about 20,000,000 trans per day.
|
| Thanks
|
| Bryan Dove
|
| ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
| news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
| > Hi Bryan,
| >
| > I think how often the transaction log backup should be run is dependent
on
| > your actual requirements. For example, if you have the following backup
| > plan:
| > Time Event
| > 8:00 A.M. Back up database
| > Noon Back up transaction log
| > 4:00 P.M. Back up transaction log
| > 6:00 P.M. Back up database
| > 8:00 P.M. Back up transaction log
| > 10:00 P.M. Failure occurs
| >
| > Then we may lose the data between 8PM and 10PM.
| >
| > In "Analyzing Availability and Recovery Requirements" topic in SQL
Server
| > 2000 Books Online, there are some basic questions to help you analyze
your
| > availability and recovery requirements:
| >
| > What are your availability requirements? What portion of each day must
the
| > database be online?
| >
| >
| > What is the financial cost of downtime to your business?
| >
| >
| > If you experience media failure, such as a failing disk drive, what is
the
| > acceptable downtime?
| >
| >
| > In case of a disaster, such as the loss of a server in a fire, what is
the
| > acceptable downtime?
| >
| >
| > How important is it to never lose a change?
| >
| >
| > How easy would it be to re-create lost data?
| >
| >
| > Does your organization employ system or database administrators?
| >
| >
| > Who will be responsible for performing backup and recovery operations,
and
| > how will they be trained?
| > Here are some questions to help you choose the tools, techniques, and
| > hardware appropriate for your site:
| >
| > How large is each database?
| >
| >
| > How often does the data in each database change?
| >
| >
| > Are some tables modified more often than others?
| >
| >
| > What are your critical database production periods?
| >
| >
| > When does the database experience heavy use, resulting in frequent
inserts
| > and updates?
| >
| >
| > Is transaction log space consumption likely to be a problem due to heavy
| > update activity?
| >
| >
| > Is your database subject to periodic bulk data loading?
| >
| >
| > Is your database subject to risky updates or application errors that may
| > not be detected immediately?
| >
| >
| > Is your database server part of a SQL Server 2000 failover cluster for
| high
| > availability?
| >
| >
| > Is your database in a multi-server environment with centralized
| > administration?
| >
| >
| >
| > "Planning for Disaster Recovery"
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_7kvb.asp>
| >
| > Designing a Backup and Restore Strategy
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_63eh.asp>
| >
| > - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
| >
|
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
| > 500/wcblurb120500.asp>
| >
| > - INF: Disaster Recovery Planning for SQL Server
| > <http://support.microsoft.com/?kbid=169039>
| >
| >
|
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
| > nol/sql/reskit/sql2000/part4/c1561.asp>
| >
| > In addition, making some experiments and comparisons would be necessary
in
| > a production system, as it is more effective.
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --
| > | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| > | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
| > <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| > | Subject: Re: OLTP Best Practices
| > | Date: Fri, 26 Sep 2003 09:28:03 -0400
| > | Lines: 46
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: 12.5.227.202
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I am looking for some general best practices. Such as how often should
| > | sp_updatestats be run, how often should the t-log be backed up, etc...
| We
| > | are having a debate over what is necessary, versus what is overkill
for
| > many
| > | of the maintenance-type processes.
| > |
| > | Thanks
| > |
| > | Bryan Dove
| > |
| > |
| > |
| > | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| > | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > | > They are all good practices for most OLTP systems but how, when etc
| > | depends
| > | > a lot on each individual app's needs. Maybe this is a good place to
| > | start.
| > | >
| > | >
| > |
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
| > ol/sql/maintain/operate/opsguide/default.asp
| > | >
| > | > --
| > | >
| > | > Andrew J. Kelly
| > | > SQL Server MVP
| > | >
| > | >
| > | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > | > > Are there any best practices for high volume OLTP systems (20M
trans
| /
| > | > day)?
| > | > > Such as sp_updatestats, index defrag, file group alignment, online
| > | backup,
| > | > > etc...
| > | > >
| > | > > Any advice is greatly appreciated.
| > | > >
| > | > > Thanks
| > | > >
| > | > > Bryan Dove
| > | > >
| > | > >
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|

OLEDB vs ODBC

Maybe this group is more responsive for this particular question. TIA.
-- Original Message --
From: "mason"
Newsgroups: microsoft.public.sqlserver.clients
Sent: Tuesday, February 21, 2006 5:01 PM
Subject: OLEDB vs ODBC

>I am new to SQL Server 2005. The native client supports both OLEDB and ODBC
> connections. Which one should I prefer and why? Thanks.ODBC is the older technology and is harder to use. Always use OLEDB where
possible as it is easier to implement and provides faster connections.
Ian Logan
"mason" wrote:

> Maybe this group is more responsive for this particular question. TIA.
>
> -- Original Message --
> From: "mason"
> Newsgroups: microsoft.public.sqlserver.clients
> Sent: Tuesday, February 21, 2006 5:01 PM
> Subject: OLEDB vs ODBC
>
>
>|||"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
>
While that is true, there are still quite a few legacy applications out
there that only support ODBC. As Ian said, in general use OLEDB when
possible, but you will probably still want to have the ODBC drivers loaded.
As an example, I believe (I could be wrong here) that Microsoft Visio (prior
to 2002) only used ODBC.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you very much. That's what I like hear. I am not sure about "easier"
part, but "faster connection" tells them apart.
"Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> ODBC is the older technology and is harder to use. Always use OLEDB where
> possible as it is easier to implement and provides faster connections.
> Ian Logan
> "mason" wrote:
>|||Thanks. Since the native client is a single DLL supporting both OLEDB and
ODBC connections, this shouldn't be a problem. We are still going to support
ODBC connection (DSNless), but to make OLEDB the default configuration for
MSSQL2005.
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:e3xajlIOGHA.668@.TK2MSFTNGP11.phx.gbl...
> "Ian Logan" <IanLogan@.discussions.microsoft.com> wrote in message
> news:2C8FB0CD-88BF-4532-A98A-BE75A699AA42@.microsoft.com...
> While that is true, there are still quite a few legacy applications out
> there that only support ODBC. As Ian said, in general use OLEDB when
> possible, but you will probably still want to have the ODBC drivers
> loaded. As an example, I believe (I could be wrong here) that Microsoft
> Visio (prior to 2002) only used ODBC.
>
> Rick Sawtell
> MCT, MCSD, MCDBAsql

OLE DB Command Stage: Capturing Rejects

Hello group, I have a question regarding the OLE DB Command Stage. Currently, I am reviewing a Data Flow that runs in production. This Data Flow Inserts to the various dimension tables in our warehouse. For a particular dimension table, the flow is like this:

Read Source records for Product combinations LookUp Product combinations against the current dimProduct table (cached in memory) Rows not found are then subjected to another LookUp on the dimProduct table (not cached). This is to find any rows inserted during the current run Rows not found are then Inserted to dimProduct using a Stored Procedure invoked by an OLE DB Command Successful Inserts then continue on, Rejected Inserts should be captured to a Flat File on our server for review.

Apparently, this last step has never been successful at capturing Rejects. Obviously, we would want to review these records to find the reason for failure. We get an empty file.

Currently, in the Stored Procedure we are using logic like this:

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

Questions:

Is the RAISERROR command going to give us Output? Can we implement the OUTPUT command in our Proc invocation? I have not found any documentation that says the OLE DB Command Stage supports Error logging (Although columns are available to be added in the Input/Output columns tab?) Should we be using another Stage to accomplish this?

Any thoughts are welcome, thanks for your time!

rg

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

In my experience, the error disposition on the OLE DB Command does not work. At least I haven't been able to get it to work. It will ignore RAISERROR statements, yet fail the component on a divide by zero, but never redirect a row. Even if the error redirection did work, you wouldn't be able to get the error description you're trying to raise.

Happily, output parameters DO work (which still surprises me since it isn't documented and isn't really intuitive). I recommend you use an output parameter for the error description, assign it to a column in the data flow, and put a Conditional Split right after the OLE DB Command evaluating the column to roll your own error redirection.

|||

Jay, thanks for the tip. The research was leading me in the direction that the error disposition was less than robust. Thanks for the confirmation, I will pursue using OUTPUT parameters for this data flow.

Thanks for the help! I have much more experience with a different ETL toolset, so even the little things right now are a challenge.