db backup simple vs. full recovery mode

When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

When you do a backup, markers are written to the Transaction Log file, however, the backup process does not change anything about the datafiles -therefore the 'trn' file gets a new datetime and the data file does not.

The Transaction Log file does not shrink UNLESS specifically so instructed. See Books Online for DBCC 'Shrinkfile'.

|||

Hi,

You can schedule half/hourly t-log backup to keep it in shape, how ever if its growing unpexctingly refer below thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221599&SiteID=1

Hemantgiri S. Goswami

|||

What we are seeing are current timestamps on the trn file, current timestamps on the ldf, but about a six month old modified date on the mdf. I would assume that the trn file would have the most recent transactions, the ldf the intermediate, and then the mdf.

With the truncate command on the trn file, do the transactions immediately hit the mdf file or the ldf (I would think the ldf)? however when does the mdf get updated by the ldf file?

Am I completely lost--I thought that the ldf (a locked mdf file, correct?) would eventually post the edits/updates to the mdf.

|||

The ldf is the transaction log file. Data changes are moved to the mdf (data file) on a regular basis -usually within seconds.

The OS stamps the file date. SQL Server has a data file (mdf) open with a, perhaps, large, amount of empty space. The OS does not know what is happening inside the mdf file unless there are specific interactions between SQL Server and the OS regarding the file.

It seems like you are confused because the mdf file date is not changing. It most likely will not change unless one of the following actions occur: Filegrowth, Fileshrink, Detach/Attach.

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas?
Thanks
What's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus
|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M
|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:

> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps on
> executing (status). There is no backup file created for any of the database
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas?
> Thanks
>
>
|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
[vbcol=seagreen]
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas'
ThanksWhat's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:
> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps on
> executing (status). There is no backup file created for any of the database
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas'
> Thanks
>
>|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:
> > Hello,
> >
> > I am trying to schedule complete backups of some of the databases that I
> > have on my database server using the Database Maintenance Plan. However,
> > when the DB Backup Job is run for backing up the database, it just keeps on
> > executing (status). There is no backup file created for any of the database
> > that I have chosen.
> >
> > However, if I can manually backup each of the database by Right-clicking
> > each database in Enterprise Manager and choosing 'Backup Database'.
> >
> > For some reason, the same backup process fails / takes very very long time
> > when I use a scheduled job created from the Database Maintenance Plan.
> >
> > Any ideas'
> >
> > Thanks
> >
> >
> >

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas'
ThanksWhat's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:

> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps o
n
> executing (status). There is no backup file created for any of the databas
e
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas'
> Thanks
>
>|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
[vbcol=seagreen]
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have
to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:
>

DB Backup & Checkpoint

Hello,
My question is when you backup a db is a checkpoint issued at that time?
Are 'dirty' pages flushed to disk right before the actual backup begins?
Thank you.Yes, checkpoint is automatically done before a backup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MedDBA" <MedDBA@.discussions.microsoft.com> wrote in message
news:CF8E6A4E-2F3C-4577-B210-8CB4D2B0B655@.microsoft.com...
> Hello,
> My question is when you backup a db is a checkpoint issued at that time?
> Are 'dirty' pages flushed to disk right before the actual backup begins?
>
> Thank you.
>sql

DB - DDL - how much time each operation takes - a doc needed

Is there a doc describing how much time each DDL can take,
e.g. (n is the number of records in the table):
add nullable column = 0(1)
add column with default falue = o(n)
etc'...


Tal Olier
otal@.mercury.co.ilI haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?|||Originally posted by Paul Young
I haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?

No it hasn't, I am looking for something like:

# Operation DB Type Example Time
1 Rename a table Oracle Alter table x rename .. o(1)
MS-SQL Sp_rename o(1)
2 Rename a index Oracle Alter index x rename.. o(1)
MS-SQL Sp_rename t.x.. o(1)
3 Add column Oracle Alter table x add y NULL o(1)
Oracle Alter table x add y default (10)/default(null) o(n)
MS-SQL Alter table x add o(1)

DB

Hi
You may want to check out
http://www.microsoft.com/sql/evalua...ibm/default.asp
My very little experience of DB2 was some time ago, but if you have a strong
relational database background then the regardless of the RDBMS a large
amount of what you know is directly transferable, the engine/tool specific
elements are usually available it is a matter of finding out how to do it in
the new environment.
If you are wanting to get a head start you may want to consider downloading
the trial edition.
John
"CG" wrote:

> Hi,
> I come from a strong SQL Server background.
> I am moving into a new role where the company use DB2.
> Is there much difference in terms of syntax between DB2 and SQL Server etc
?
> What is DB2 like to work with (environment, reliability etc)?
> Any feedback is much appreciated.
> Thanks.Also, check out Kevin Kline's book - "SQL in a Nutshell". It gives you
comparative syntaxes across several flavours of SQL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:679AB20E-B81B-41DC-88B3-B27FBBE70598@.microsoft.com...
Hi
You may want to check out
http://www.microsoft.com/sql/evalua...ibm/default.asp
My very little experience of DB2 was some time ago, but if you have a strong
relational database background then the regardless of the RDBMS a large
amount of what you know is directly transferable, the engine/tool specific
elements are usually available it is a matter of finding out how to do it in
the new environment.
If you are wanting to get a head start you may want to consider downloading
the trial edition.
John
"CG" wrote:

> Hi,
> I come from a strong SQL Server background.
> I am moving into a new role where the company use DB2.
> Is there much difference in terms of syntax between DB2 and SQL Server
> etc?
> What is DB2 like to work with (environment, reliability etc)?
> Any feedback is much appreciated.
> Thanks.sql

Daylite saving time problem

Hello
I am using SQL Server 2000, SP4
I am calculating number of hours passed between two dates. Both dates have
time set to 00:00:00. I use datediff function it works ok unless the time
interval I pass includes date when time is changed due to Daylite Saving Tim
e
(DST) issue. Instead of one hour more or one hour less datediff keeps
returning constant number of hours.
Does SQL Server 2000 internally support DST depending on a regional settings
in OS?
Thanks in advance.we don't that feature in SQL Server to my knowledge. You can write a UDF to
do the conversion.
Check out this link
http://www.planet-source-code.com/U...cripts/ShowCode!asp/txtCodeId!9
11/lngWid!5/anyname.htm|||Thanks a lot|||Some ideas here maybe:
http://www.aspfaq.com/2218
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||Oh, and also the calendar table.
http://www.aspfaq.com/2519
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||or how about rather than using getdate() to get the two dates in the
first place, use getutcdate() function?
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal
Time Coordinate or Greenwich Mean Time). The current UTC time is
derived from the current local time and the time zone setting in the
operating system of the computer on which SQL Server is running.
Mel|||> or how about rather than using getdate() to get the two dates in the
> first place, use getutcdate() function?
> GETUTCDATE
> Returns the datetime value representing the current UTC time (Universal
> Time Coordinate or Greenwich Mean Time). The current UTC time is
> derived from the current local time and the time zone setting in the
> operating system of the computer on which SQL Server is running.
Well, if you're comparing two datetime values:
2005-12-31
2006-06-01
If you're in a timezone that observes daylight savings time, your
calculation is going to be an hour off (which way depends on what is
currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
an hour off in the other direction the next time the daylight savings time
goes on or off.
The calendar table can help solve this problem by giving you the offset on
each of the dates in question, allowing you to adjust each date accordingly.|||You also have to take into account that different areas change their clocks
on different dates, so you may need to create a second table with each time
zone and the date/time that they change their clocks.
That, and some areas (Arizona for example) do not use daylight savings time
at all.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OES60M7YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Well, if you're comparing two datetime values:
> 2005-12-31
> 2006-06-01
> If you're in a timezone that observes daylight savings time, your
> calculation is going to be an hour off (which way depends on what is
> currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
> an hour off in the other direction the next time the daylight savings time
> goes on or off.
> The calendar table can help solve this problem by giving you the offset on
> each of the dates in question, allowing you to adjust each date
accordingly.
>|||> You also have to take into account that different areas change their
> clocks
> on different dates, so you may need to create a second table with each
> time
> zone and the date/time that they change their clocks.
Or an extra column for each timezone (reproduce the tinyints instead of the
wider date values).

> That, and some areas (Arizona for example) do not use daylight savings
> time
> at all.
Right, Indiana just changed. Next year, the formula for determining the
dates changed in the US, so I think a lot of people who hav used an inline
calculation for this are either already working on fixing it or have plenty
of work to do over the winter. Since we used a calendar table in all of our
implementations, we don't have to worry about it... a simple update
statement corrects all future data until they waffle again.|||a column for each timezone seems much more complex than a single table with
one row each.
However, the benefit to doing it with columns is that you don't run into
problems when the timezone rules change. In the case of Indiana, you would
update the Indiana column in the calendar table for those date ranges. With
a separate table you would need to store the date that the rules changed and
always make sure you are joining to the correct row. I think I like your
idea of multiple columns better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O%23NxvE8YGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Or an extra column for each timezone (reproduce the tinyints instead of
the
> wider date values).
>
> Right, Indiana just changed. Next year, the formula for determining the
> dates changed in the US, so I think a lot of people who hav used an inline
> calculation for this are either already working on fixing it or have
plenty
> of work to do over the winter. Since we used a calendar table in all of
our
> implementations, we don't have to worry about it... a simple update
> statement corrects all future data until they waffle again.
>

Daylight Savings Time error SQL SERVER 2000

Hi all.
I used tzedit to update the daylight savings time setting on my windows 2000
server.. Now, when I run any of my reports, I get this error:
"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name:
date "
Does anyone have or heard of a solution?Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
encounter this issue with my SQL 2005.
"jereviscious" <here@.there.com> wrote in message
news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi all.
> I used tzedit to update the daylight savings time setting on my windows
> 2000 server.. Now, when I run any of my reports, I get this error:
> "An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) Get Online Help
> Specified argument was out of the range of valid values. Parameter name:
> date "
> Does anyone have or heard of a solution?
>|||Since Windows 2000 server isn't officially supported any more, microsoft
didn't release a patch. They did, however, issue this knowledge base
article, which I followed..
http://support.microsoft.com/kb/914387
and everything else appears to be working fine. All of my servers are
reporting the correct time and date. However, SQL SERVER is giving me this
problem, and I have no idea how to go about fixing it...
"Julien Bonnier" <julien@.m0851.com> wrote in message
news:OM3DXKwaHHA.3584@.TK2MSFTNGP02.phx.gbl...
> Maybe there's a patch for sql 2000, i fixed my win 2003 server and didn't
> encounter this issue with my SQL 2005.
>
> "jereviscious" <here@.there.com> wrote in message
> news:eEmN81vaHHA.4396@.TK2MSFTNGP06.phx.gbl...
>> Hi all.
>> I used tzedit to update the daylight savings time setting on my windows
>> 2000 server.. Now, when I run any of my reports, I get this error:
>> "An internal error occurred on the report server. See the error log for
>> more details. (rsInternalError) Get Online Help
>> Specified argument was out of the range of valid values. Parameter name:
>> date "
>> Does anyone have or heard of a solution?
>

Daylight Savings Time error SQL SERVER 2000

Hi all.

After DST change (US/Canada), when I run any of my reports, I get this error:

"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name:
date "

Does anyone have or heard of a solution?

Thank you!

What is your date format, MM/DD/YYYY or DD/MM/YYYY?|||

Try installing SQL Server 2005 (and restore the databases on it) and run the reports.

Shyam

|||mm/dd/yyyy|||

I cannot give up using SQL Server 2000 just because of this issue

|||

I do not think you need to give up on SQL 2000 for this.

Can you share the script where you pass in the parameters?

|||<Value>=Globals!ReportName &amp;" between " &amp; Parameters!startdate.Value &amp; " &amp; " &amp; Parameters!enddate.Value</Value>
...
<ReportParameters>
<ReportParameter Name="startdate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(cstr(year(Now())) + "/" + "01" + "/" + "01" + " 00:00:01")</Value>
</Values>
</DefaultValue>
<Prompt>Start Date-Time (MM/DD/YYYY HH:MM:SS AM or PM)</Prompt>
</ReportParameter>
<ReportParameter Name="enddate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(cstr(year(Now())) + "/" + cstr(month(now())) + "/" + cstr(day(now())) + " 23:59:59")</Value>
</Values>
</DefaultValue>
<Prompt>End Date-Time (MM/DD/YYYY HH:MM:SS AM or PM)</Prompt>
</ReportParameter>
<ReportParameter Name="Team">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>%</Value>
</Values>
</DefaultValue>
<Prompt>Enter a Team with % as a wildcard character i.e. %Cust%</Prompt>
</ReportParameter>
</ReportParameters>|||

I do not see any problems in the script.

Please take a look at the server log and here's something for reference. If possible, please post what you can find from the log. HTH.

http://blogs.msdn.com/jgalla/archive/2006/12/05/diagnosing-rsinternalcatalogexception.aspx

|||

Problem was resolved after power outage ;) and server restart.

|||Reboot is always the "best" solution for all MS issues.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.sql

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time Change in 2007

Is there a patch available for SQL Server 2000 to address the 2007 change in
daylight savings time?
Thank you very much in advance for your help.
Thanks,
Sally
Sally wrote:
> Is there a patch available for SQL Server 2000 to address the 2007 change in
> daylight savings time?
> Thank you very much in advance for your help.
> Thanks,
> Sally
SQL Server takes the current date and time from the OS. For details
see:
http://www.microsoft.com/windows/timezone/dst2007.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
sql

Daylight Savings Time Change in 2007

Is there a patch available for SQL Server 2000 to address the 2007 change in
daylight savings time?
Thank you very much in advance for your help.
Thanks,
SallySally wrote:
> Is there a patch available for SQL Server 2000 to address the 2007 change
in
> daylight savings time?
> Thank you very much in advance for your help.
> Thanks,
> Sally
SQL Server takes the current date and time from the OS. For details
see:
http://www.microsoft.com/windows/timezone/dst2007.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
ThanksSQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operati
ng
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
Thanks
SQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks
|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operating
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
ThanksSQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operating
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi