do anyone have an idea?

Hi there,

I have number of tasks in my control flow most of them are execute sql task. I want to update one of the column in my table when anyone of the task in the control get fails?

Please let me know if anyone have an idea how to do this.

Thanks and Regards

I think 'event handlers' can do that for you. I dont have specific examples now; but this forum has a lot of info on that.|||

Hi Salas,

You are right.I have done it by using event handler.

Thanks a lot.

Division by zero problem

I've got the following SQL

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / b.divider 0)

The b.divider value can be 0, but still I get the division by zero
error message.
I guess the reason is that the database performs the where statements
one at the time?

So when performing the division a.number / b.divider it could get 0 in
the divider even if b.divider 0 is also part of the where
statement??

My question is then.. How can I work around this problem? Changing the
database to set b.divider always 0 is not an optionMaybe this?

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / NULLIF(b.divider,0) 0)|||Thank you :)

Division by Zero Error

I am getting a division by zero error from the SQL below, I believe I need to utilize Cast because it could be a negative number and I need to be able to view 8 positions to the right of the dec. (0000.00000000). Any help would be greatly appreciated.

SELECT FromDate AS MonthOneDate,State,Description,SUM(Flowthru) AS CELCSumFT,SUM(TotalCount) AS CELCsumCNT,
(SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct,
RetailNum AS ARZSumFT,RetailDen AS
ARZSumCnt,(RetailNum/RetailDen) * 100 AS ARZFtPct,
((RetailNum/RetailDen) - SUM(Flowthru) /SUM(TotalCount)) / SQRT(((SUM(Flowthru) + RetailNum) / (SUM(TotalCount) +RetailDen)) * (1-(SUM(Flowthru)+RetailNum)/(SUM(TotalCount) + RetailDen)) * (1 / SUM(TotalCount)+1/RetailDen)) AS ZScoreMonth1
FROM pmMidwest33_BlueRed WHERE TotalCount <> 0 AND CLEC <>
'' AND Acna NOT IN ('TRE','TRD') AND STATE IN ('IL','IZ','TR','MI','RE')
AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
GROUP BY FromDate,State,Description,RetailNum,RetailDenDid you figure what part of your SQL query returns a divide by zero ? It could be :

sum(TotalCount),
RetailDen,
SQRT(((SUM(Flowthru) + RetailNum)
...

Before casting anything, I would look for the origin of the problem.|||for example you have

select (SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct from table

select CELCFtPct = case when SUM(TotalCount)) = 0 then 0
else (SUM(Flowthru)/SUM(TotalCount)) * 100 end
from table

You can do that for all your divisions so you do not get division by zero error. Not sure what field you needed this on but its a good ideal to do this anytime that you are dividing because at some point you are bound to divide by zero.

Hope that helps

KG|||Thank you for your help.. I figured out that my data types needed to be changed to float. I had it a varchar.

Joe

Division

Newbie Question.
While using a query/subquery to get a percentage I keep getting zero. I
assume this is becuase the number returned is less than 1. How can I make
this work right?
Example
Select 123/12345
returns 0 when it should equal 0.00996
I have tried converting using decimal(5,5) but either this is wrong, or I am
doing it wrong.
Thanks
DaveThe result of an integer division will be integer.
Example:
select 4 / 2, 4.00 / 2, 4 / 2.00, cast(2 as numeric(5, 2)) / 4
AMB
"Dave S." wrote:

> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>
>|||Select 123/(12345 * 1.0)
Keith
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uUS4OO5EFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>

dividing a large flat file into small files

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

See this post by John:
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx|||

prashant550806 wrote:

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

This should help as well:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

-Jamie

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)
What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>
|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.
|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegr oups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:
> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by Zero Error

I have a function that compiles a number of different aspects of a Select query and then creates some high-bred fields based on the result. The problem I have is that some of these values contain a zero or Null and therefore I am getting a divide by zero error. The three sums I have are as follows;

Number 1:-

SELECT........, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, BLAH BLAH FROM

Number 2 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc, BLAH BLAH FROM

Number 3 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, BLAH BLAH FROM

I have been informed that I need to use something like a CASE statement. What I want it to do is that if a ZERO or NULL is detected in any element of the source of the sum, then I want it to ignore the sum and just place the value of zero in whatever the AS xxxxxxxx dictates.

Could someone point me in a direction here or provide me with a little sample of how to go about doing this in a SELECT statement.

Thanks in advance

you can use case like this

select case when fnWTRalldata.floortotal = 0 then 0 else fnWTRalldata.floortotocc / fnWTRalldata.floortotal end as floorspaceperc from table

This should help you avoid division by zero errors.

|||

Thanks for your suggestion Andreas.

In addition to my previous post, would I be better to evaluate these conditions using a CASE statment in a second,third and fourth function and then deliver the results to the afore mention function, or could I use a nested statement within the existing SELECT statements?

Regards

|||

you can also use the iif in reporting services if that helps but it is a pain.

=iif(denominator > 0, numerator/iif(denominator >0, denominator, 1), 0)

|||

This is what I did to solve it. Found the answer elsewhere on the net

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal
else 0 end AS floorspaceperc,
case when FinalRtLsincSC <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1
else 0 end AS rentrolldiscperc,
case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal
else 0 end AS netrentpersqft, BLAH BLAH
FROM fnWTRalldata

Thanks for all your suggestions, the solution I found was down the road you were all heading in.

Thanks

Divide by number of days in the year.

How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advanceHere's how to get the number of days in the current year:
select
case
when year (getdate()) / 100 % 4 = 0 then 365
when year (getdate()) % 4 = 0 then 366
else 365
end
However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly. However, you could feed a date to
the function.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advance|||For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>|||scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)
SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+
1
Regards
R.D
"scuba79" wrote:

> How can I create a function that will divide a parameter passed into the
> stored procedure by the number of days in the current year? I know that I
> can not just use 365 since it will not take into account leap years.
> Thanks in advance|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>

Divde by Zero error

I to am getting the above error when trying to action the following
calculation.
=iif(CostValue=0,0,Profit/CostValue)
I have tried a number of the other solutions posted and these do not seem to
work in my instance.
Have tried to use Nz function but this is not included in Reporting
Services, also tried ISERROR this too failed.
Help me Obi Wan - you're my only hope....Try this:
=iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
Keep in mind that iif is a function call and therefore all arguments get
evaluated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem
to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||What is your data source? I trap divide by zero errors using a function in
SQL Server before the data is delivered to the report.
/*
Created by Vince Plaza
Last revised by Vince Plaza
Last revised on 6/29/2003
The divide by zero trap looks for a denominator of zero and skips the
division operation.
It also serves to round results to a desired number of decimal places.
*/
CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
@.DENOMINATOR AS FLOAT, @.ROUND AS INT)
RETURNS FLOAT AS
BEGIN
DECLARE @.OUTPUT AS FLOAT
IF @.DENOMINATOR = 0
SELECT @.OUTPUT = 0
ELSE IF @.ROUND = -1 --Dont Round
SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
ELSE
SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
RETURN @.OUTPUT
END
"Jules_Anime" wrote:
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||Thanks Robert. This seemed to work fine.
Although I thought I had already followed this path, maybe I was "Lost in
Translation"
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
> iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
> Keep in mind that iif is a function call and therefore all arguments get
> evaluated.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
> news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem
> to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
>
>|||Thks..I like the neatness of this solution, I think I went with a case
statment in the original SQL.
It ment however that I had to summarise the view firstly, and then report
from the summarised data.
Cheers.
"vmp_pdx" wrote:
> What is your data source? I trap divide by zero errors using a function in
> SQL Server before the data is delivered to the report.
> /*
> Created by Vince Plaza
> Last revised by Vince Plaza
> Last revised on 6/29/2003
> The divide by zero trap looks for a denominator of zero and skips the
> division operation.
> It also serves to round results to a desired number of decimal places.
> */
> CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
> @.DENOMINATOR AS FLOAT, @.ROUND AS INT)
> RETURNS FLOAT AS
> BEGIN
> DECLARE @.OUTPUT AS FLOAT
> IF @.DENOMINATOR = 0
> SELECT @.OUTPUT = 0
> ELSE IF @.ROUND = -1 --Dont Round
> SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
> ELSE
> SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
> RETURN @.OUTPUT
> END
>
> "Jules_Anime" wrote:
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....

Distribution database is growing large...

Hi!
A few days ago the number of replication commands grew up to ~6 000
000. A normal number used to be ~20 000. The replication agents are
working fine. The data on subscribers are valid. I suspect that the
history clean up agents don't remove old commands, although their
status shows successful completion.
What could be a workout for such situation?
Thanks.
P.S.: I am using Win 2003 Ent Server + MS SQL 2000 sp3 and only
transactional replication.
Do you have anonymous subscriptions? If so the commands and transactions
will remain there until the end of the retention period.
Also it could be that you merely issued transactions that affected a large
number of rows. However, if these commands have been replicated to all
subscribers the distribution clean up agent should purge them every 10
minutes.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0412020529.372a7316@.posting.google.c om...
> Hi!
> A few days ago the number of replication commands grew up to ~6 000
> 000. A normal number used to be ~20 000. The replication agents are
> working fine. The data on subscribers are valid. I suspect that the
> history clean up agents don't remove old commands, although their
> status shows successful completion.
> What could be a workout for such situation?
> Thanks.
> P.S.: I am using Win 2003 Ent Server + MS SQL 2000 sp3 and only
> transactional replication.

Distribution Agent - Replication Monitor warning

Hi,

I am replicating a large table with a number of indexes. During the initialisation phase (creating the indexes at the subscriber), I am seing the following error/warning in Replication monitor. When the initialisation is complete the error warnings dissapear.

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Is there a way to increase the 10 minute time-limit?

Thanks,
Priyanga

Hi Priyanga,

You can use the -KeepAliveMessage parameter of the distribution agent to specify a larger time interval for logging the "agent suspect" messages. However, I am a bit hesitant to recommend that you simply increase the time interval in general since you would probably want to be notified that the distribution agent may be stalled in a more timely manner when it is delivering incremental changes. On the other hand, delivery of snapshot is arguably a rare enough occurrence that the "agent suspect" messages should not become a significant source of annoyance.

In truth, the following factors had conspired to make the "agent suspect" messages almost unavoidable when delivering a large snapshot:

1) BCP API does not allow the distribution agent to log a progress message until every "BcpBatchSize" number of rows have been bulk-loaded into a subscriber table
2) We use a large "BcpBatchSize" (2^31 - delta) as the default "BcpBatchSize" in SQL2005 so the distribution agent can more readily meet the minimally-logged bulk-load requirements.
3) The time required to create an index on a large table probably exceeds the default -KeepAliveMessageInterval

Based on 1) and 2) above, distribution agent will mostly not be able to log a progress message in-between a bulk-load operation;and if the amount of data that needs to be bulk-loaded is large, the distribution agent will likely not be able to finish the bulk-load operation within the default -KeepAliveMessageInterval.

HTH

-Raymond