repeat a statement in a loop

hello,
i need to repeat the update statement for all days in the actual month
example:if i run today (19.09.2005) the job - it must run 19 times for
the value of
GETDATE() to GetDate() -19. How can be done this in a loop.
In the update statement is GetDate() used in the where condition and it must
be replaced with the values
in the first loop with GetDate() -1
in the second loop with GetDate() - 2
...
in the last GetDate()-19
The update statement looks like :
UPDATE [table1]
SET [OrderDate]=(
SELECT DISTINCT Top 1 OrderCreationDate
FROM table2
WHERE (SalesDocNr = table1.OrderNr)
)
WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
FROM table1 INNER JOIN
table2 ON table1.OrderNr = table2.SalesDocNr
WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
CONVERT(VarChar(10), GETDATE() , 104))
)
thanks
XavierWhile can use a WHILE loop or a cursor to get this done as you want, a
better approach is to use a single UPDATE statement which can update all the
rows.
Based on the sample code you posted, it is hard to work out such a solution,
so please refer to www.aspfaq.com/5006 and post relevant information for
others to reproduce your problem.
Anith|||For a looped approach try this for your basic loop. @.date should be used in
place of GETDATE() in your update query. Replace the PRINT statement with
your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
ONLY. You may want a better method than what I did.
Mike
DECLARE @.day int
DECLARE @.date datetime
SET @.day = DAY(GETDATE())
SET @.date = GETDATE()
SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
WHILE @.day > 0
BEGIN
PRINT @.date
SET @.day = @.day - 1
SET @.date = DATEADD(d, -1, @.date)
END
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it
> must
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required
results.
I'm certain it's possible to do what you want in a single UPDATE statement
without a loop. However, the UPDATE you have posted may not give reliable
results because you've used TOP without ORDER BY. For that reason it's trick
y
to guess what you intended by it (even though it may not always work as you
wanted).
I expect the solution will look like
:
UPDATE Table1
SET orderdate =
(
..
)
WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)
AND insertdate <= CURRENT_TIMESTAMP ;
David Portas
SQL Server MVP
--
"Xavier" wrote:

> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it mu
st
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||it works,
thanks Mike
"Mike Jansen" wrote:

> For a looped approach try this for your basic loop. @.date should be used
in
> place of GETDATE() in your update query. Replace the PRINT statement with
> your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
> ONLY. You may want a better method than what I did.
> Mike
>
> DECLARE @.day int
> DECLARE @.date datetime
>
> SET @.day = DAY(GETDATE())
> SET @.date = GETDATE()
> SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
> WHILE @.day > 0
> BEGIN
> PRINT @.date
> SET @.day = @.day - 1
> SET @.date = DATEADD(d, -1, @.date)
> END
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
>
>|||> While can use a WHILE loop or a cursor to get this done as you want, a
> better approach is to use a single UPDATE statement which can update all
> the rows.
I don't think that's really possible without SQL Server 2005 (or perhaps a
_really_ messed up query in SQL 2000), in which case it would be something
like this as a base:
WITH DAYS(DayValue, Remaining) AS
(
SELECT
CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,
DAY(GETDATE()) - 1
UNION ALL
SELECT
DATEADD(d, -1, r.DayValue) AS DayValue,
r.Remaining - 1
FROM
DAYS r
WHERE
r.Remaining > 0
)
SELECT * FROM DAYS;
Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.
Mike

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
Thanks
Part of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talkin
g
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
>
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks