Reset Excel Destination for Error Logging

I am using sheets in an Excel spreadsheet to receive redirected error rows. The trouble is that it keeps appending the rows to the bottom of the sheet - even if I delete the rows in the spreadsheet. If I remove the spreadsheet all together then I need to set-up the Excel Desitnation Sheets again.

Each time I run the Control Flow I would like it to be removing old log entries and writing in the new ones.

There must be an easy way?

Unfortunately the Excel driver does not support anything like TRUNCATE TABLE. Even if you opened Excel and cleared the contents of those rows, the driver would still see them as "used" ... the only solution is to delete the rows themselves from within Excel. And you would want to check the range definition to make sure that you were in fact deleting all the rows in the range.

Of course the cleaner solution would be to drop and recreate the table. You could use an Execute SQL task for this purpose, and probably get one of the SSIS components to write the SQL for you to copy and paste in.

-Doug

Reset date to first of month

I receive an existing date variable, which I copy to my @.today variable. I
need to reset the day of the month to be the first of the month, leaving all
other aspects of @.today alone.
Any suggestions?
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--the following doesn't work, but it shows what I want to do
SET MONTH(@.today) = 1
SELECT @.today
Thanks
--
RandySET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
Steve Kass
Drew University
randy1200 wrote:

>I receive an existing date variable, which I copy to my @.today variable. I
>need to reset the day of the month to be the first of the month, leaving al
l
>other aspects of @.today alone.
>Any suggestions?
>-- simulate the date I get, which I have no control over
>declare @.date datetime
>set @.date = '02/08/2008'
>--assign to my date variable, which I do control
>declare @.today datetime
>SET @.today = @.date
>--the following doesn't work, but it shows what I want to do
>SET MONTH(@.today) = 1
>SELECT @.today
>Thanks
>|||You can try using:
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--If I understood correctly
set @.today = @.today - (DAY(@.today)-1)
SELECT @.today
Let me know if it helps..
"randy1200" wrote:

> I receive an existing date variable, which I copy to my @.today variable. I
> need to reset the day of the month to be the first of the month, leaving a
ll
> other aspects of @.today alone.
> Any suggestions?
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
> --
> Randy|||That did it. Many thanks.
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try using:
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --If I understood correctly
> set @.today = @.today - (DAY(@.today)-1)
> SELECT @.today
> Let me know if it helps..
> "randy1200" wrote:
>|||That did it. Many thanks.
--
Randy
"Steve Kass" wrote:

> SET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
> Steve Kass
> Drew University
> randy1200 wrote:
>
>|||This one will work for the first date as well:
set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
end)
"randy1200" wrote:
> That did it. Many thanks.
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Very . Many thanks again!
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> This one will work for the first date as well:
> set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
> end)
> "randy1200" wrote:
>|||You are very welcome!
"randy1200" wrote:
> Very . Many thanks again!
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Hi Randy,
Try:
select DATEADD(mm, DATEDIFF(mm,0,@.today), 0)
"DATEDIFF(mm,0,getdate())" calculates the number of months between the curre
nt
date and the date "1900-01-01 00:00:00.000".
Remember date and time variables are stored as the number of milliseconds
since "1900-01-01 00:00:00.000"; this is why you can specify the first datet
ime
expression of the DATEDIFF function as "0."
Now the last function call, DATEADD, adds the number of months between the
current date and '1900-01-01".
By adding the number of months between our pre-determined date '1900-01-01'
and the current date, you are able to arrive at the first day of the current
month.
In addition, the time portion of the calculated date will be "00:00:00.000."

> I receive an existing date variable, which I copy to my @.today
> variable. I
> need to reset the day of the month to be the first of the month,
> leaving all
> other aspects of @.today alone.
> Any suggestions?
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
>sql

Repost: sql7 maintenance jobs hang

Hi group,
the following has been posted and did not receive any response. In hope to
get some leads I am reposting it here.
we have a sql7 server,
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
For a while now, it has been seeing hanging db maintenance jobs. The jobs
are initiated, but never goes beyond that -- the maintenance task is not
done before the hanging of the job. EM shows that the jobs are running but
loses the ability of managing them. One needs to end the processes with the
windows task manager. When manually initiated, the jobs can run through.
The jobs are not necessarily the same(different days can have different jobs
experiencing the problem, some can be the same), but all came from db
maintenance plan. A job hung yesterday may not hang today. Recreating the
db maint plan does not solve the problem.
Any suggestions are appreciated.
QuentinQuentin
Are you using the repair minor errors option? That tends
to cause problems in maint plans.
Any other problems on the server, I had a similar problem
and it was being caused by another process clashing with
the checkpoint process, but that eventually killed the
whole system.
Regards
John|||John,
thanks for the response.
The repair minor errors option is not being used. We are aware of that
problem.
We are trying to identify other problems on the server but came out empty
handed so far.
Quentin
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:003201c3a86e$ccafad40$a301280a@.phx.gbl...
> Quentin
> Are you using the repair minor errors option? That tends
> to cause problems in maint plans.
> Any other problems on the server, I had a similar problem
> and it was being caused by another process clashing with
> the checkpoint process, but that eventually killed the
> whole system.
> Regards
> John