800a0cb3 Error in DTS Package

I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

As of last night, this process started throwing the following error:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= ADODB.Recordset
Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.

Error on Line 110
(ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

I was curious if anyone else has had an experience with a DTS package throwing a similar error.

Any ideas?I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly... amazing....|||I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post... very very troubling...|||Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?

64-bit SQL Server, DTS and Mail

Greetings al
Any feedback on 64-bit SQL Server? Is anyone using it? Any positive results or problems experienced
I read in the BOL that DTS componenets are not included in the current release. I see this as a major hurdle to overcome - yes packages can be run from a 32-bit server, but this would have an assoicated downside.
Also, SQL Mail is not supported. Can anyone at MS explain why SQL Agent Mail is supported but not SQL Mail? And why is Outlook Express the mail client and not Outlook
Finally, will DTS and SQL Mail be fully supported in future releases on the 64-bit platformThe primary reason for no SQLMail support is that SQLMail requires Extended
MAPI which was not ported to 64bit.
Agent Mail is supported because we did some work on it to support Simple
MAPI which is included with Outlook Express. It is not possible to run
Simple MAPI inside SQL Server as an XP hence this solution does not work
for SQLMail.
The reasons for Outlook Express and not Outlook are:
1/ There is no 64bit version of Outlook, hence no 64bit Simple/Extended MAPI
included with it.
2/ Even under 32bit once Extended MAPI is installed Simple MAPI no longer
works in a manner which allows it to be used by Agent Mail.
We aim to resolve these in a future release.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Elliot Wien" <anonymous@.discussions.microsoft.com> wrote in message
news:B0C44188-50B4-4461-9E54-D53AE943B019@.microsoft.com...
> Greetings all
> Any feedback on 64-bit SQL Server? Is anyone using it? Any positive
results or problems experienced?
> I read in the BOL that DTS componenets are not included in the current
release. I see this as a major hurdle to overcome - yes packages can be run
from a 32-bit server, but this would have an assoicated downside.
> Also, SQL Mail is not supported. Can anyone at MS explain why SQL Agent
Mail is supported but not SQL Mail? And why is Outlook Express the mail
client and not Outlook?
> Finally, will DTS and SQL Mail be fully supported in future releases on
the 64-bit platform?
>

400 million record update

Hi,
I have a sql2k table with 400 Million records. All records need to be
updated (one field.).
1. What id tested was that DTS transfer(updating the field during the
transferring) to a diff table in the same db. It took 67 hours.
2. The second try I did was Select into the the same db. same thing -- 70
hours.
Is there any faster method taht I could use the accomplish this?
ThanksWith an index on a very specific field such as identity or spread-out date,
you can do this in a batch mode, something like this:
declare @.i int
set @.i = 1 (or min from your table)
get max id from table
while @.i < maxid
begin
begin tran
delete from table where id between @.i and @.i + somenumber (50K-00K')
check for error
commit tran
set @.i = @.i + somenumber
end
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Hi,
What you are testing is not really an update but the copy of a huge table.
Could you give more details about exactly what are you going to update, field
data type, operation.
Could you restore a copy of this database to some other place and test
running a simple UPDATE statement? Note that since this will be one
transaction you will need enough disk space for the transaction log to grow.
Hope this helps,
Ben Nevarez
"Mecn" wrote:
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Just so I can be clear , when you say UPDATE , I think you mean tarnsfer of
data (copy).
Some things you could try is:
use TABLOCK
If possible, use Simple Recovery Mode
Possibly disable indices and rebuild at the end .
These are just some ideas, but is dependant on the exact table structures,
code etc
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||67hours for 400millions?
its really really slow... (around 1600rows/sec)
are you sure that you use the bulk insert option and make sure you setup the
batch size value (to 10 000 or something like this)
your disk subsystem as an impact too, but you should be able to load the
table in 1 to 2hours. (depends on the size of 1 row)
also make sure you drop the indexes before the loading process, then
recreate the indexes. (recreating the indexes will add some processing time
after the loading, so estimate it between 15min to 1h regarding the number
of indexes)
the idea is to cut the big table into small batchs.
in our developments we reach 200 000rows loaded by second.
"Mecn" <mecn@.yahoo.com> wrote in message
news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>|||Thanks for all the responses.
1. You are right I was doing transfer instead of updating the field. I think
that uase DTS with 400,000 batch size/fast load(same as bulk insert?) to a a
non-index table or Insert into (bulk insert?) will be fast than undate the
field. -- 67 Hours....too long.
Any faster ways?
Agree?
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||The table datasize = 80GB, total rows = 375million
"Jeje" <willgart@.hotmail.com> wrote in message
news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
> 67hours for 400millions?
> its really really slow... (around 1600rows/sec)
> are you sure that you use the bulk insert option and make sure you setup
> the batch size value (to 10 000 or something like this)
> your disk subsystem as an impact too, but you should be able to load the
> table in 1 to 2hours. (depends on the size of 1 row)
> also make sure you drop the indexes before the loading process, then
> recreate the indexes. (recreating the indexes will add some processing
> time after the loading, so estimate it between 15min to 1h regarding the
> number of indexes)
> the idea is to cut the big table into small batchs.
> in our developments we reach 200 000rows loaded by second.
> "Mecn" <mecn@.yahoo.com> wrote in message
> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>|||yes, fast load is the bulk insert.
but a batch of 400 000 is too large, try to reduce it (to 10 000) and see
the difference.
each batch is a transaction, if the batch is big the transaction will take
more time to be commited.
you have to check the disk, memory & cpu activity during the load to
identify the bottleneck.
also insure that the database recovery is set to "simple", and make sure the
log files are on a separate controller and disks.
and validate that the source query used is correctly optimized.
and if everything is on the same server, try to spread the source database
or files on a different disks then the targeted database / table
If you read & write on the same disks this can cause performance issues.
"Mecn" <mecn@.yahoo.com> wrote in message
news:Okn4KT2XIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Thanks for all the responses.
> 1. You are right I was doing transfer instead of updating the field. I
> think that uase DTS with 400,000 batch size/fast load(same as bulk
> insert?) to a a non-index table or Insert into (bulk insert?) will be fast
> than undate the field. -- 67 Hours....too long.
> Any faster ways?
> Agree?
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:FB0540B8-0A9B-4603-AD86-01742FFC7DD7@.microsoft.com...
>> 67hours for 400millions?
>> its really really slow... (around 1600rows/sec)
>> are you sure that you use the bulk insert option and make sure you setup
>> the batch size value (to 10 000 or something like this)
>> your disk subsystem as an impact too, but you should be able to load the
>> table in 1 to 2hours. (depends on the size of 1 row)
>> also make sure you drop the indexes before the loading process, then
>> recreate the indexes. (recreating the indexes will add some processing
>> time after the loading, so estimate it between 15min to 1h regarding the
>> number of indexes)
>> the idea is to cut the big table into small batchs.
>> in our developments we reach 200 000rows loaded by second.
>> "Mecn" <mecn@.yahoo.com> wrote in message
>> news:#bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a sql2k table with 400 Million records. All records need to be
>> updated (one field.).
>> 1. What id tested was that DTS transfer(updating the field during the
>> transferring) to a diff table in the same db. It took 67 hours.
>> 2. The second try I did was Select into the the same db. same thing --
>> 70 hours.
>> Is there any faster method taht I could use the accomplish this?
>> Thanks
>>
>|||Hi
SET ROWCOUNT 10000
update_rows:
UPDATE tbakle SET ...
WHERE <condition>
IF @.@.ROWCOUNT > 0 GOTO update_rows
SET ROWCOUNT 0
"Mecn" <mecn@.yahoo.com> wrote in message
news:%23bek6QtXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a sql2k table with 400 Million records. All records need to be
> updated (one field.).
> 1. What id tested was that DTS transfer(updating the field during the
> transferring) to a diff table in the same db. It took 67 hours.
> 2. The second try I did was Select into the the same db. same thing -- 70
> hours.
> Is there any faster method taht I could use the accomplish this?
> Thanks
>
>