I agree it seems to be some kind of spurious network error.

For example: The loop can correctly process 6 days of data and get a network failure when working...

If you have a table partitioned on Date partioned table and the primary key clustered index of the table is needs to have both Date and ID. If each date...

If you can make use of Partition Elimination then Yes it is. Here is a prity good article. http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx