Hello...I'm trying to update a field in a SQL 2000 table with a value
that is extracted from another field within the same table based on a
specific field value ([rectype] value). This value is dependant on a
[rectype] value = 20. All the records in the table that have a
[rectype] of 20 will determine the field value that will be populated.
The code opens a recordset uses ADODB.Connection and ADODB.recordset in
a DTS package Activex Script and loops through the table and updates
the LINKID field based upon what it finds.... This code runs very
slow... is there any possible way to speed this up? I have used a SP
to update the table to make things faster and I'm trying to figure out
an even faster way to do this... Any help is much appreciated. I have
pasted the code from the Activex Task and the SP below...

Activex Task code:
'--------------------------------------------------------------
Function Main()
dim countr
dim mySourceConn
dim mySourceRecordset
dim mySQLCmdText
dim RecNumber
dim RecType
dim TotalRecs
dim TrmPreFx
dim LINKIDstr
Dim oCmd


'------Connection to SQL that is used by all recordsets below

set mySourceConn=CreateObject("ADODB.Connection")
mySourceConn.Open="Provider=SQLOLEDB;Trusted_Conne ction=yes;SERVER=CSPARPSQL001;Initial
Catalog=ADRS"
'mySourceConn.Open="Provider=SQLOLEDB;Description= CSPARPSQL001;DATABASE=ADRS;Trusted_Connection=Yes"


'------Assign each group a unique identifier to associate the detail
records with the header record.

set mySourceRecordset=CreateObject("ADODB.Recordset")
mySQLCmdText= "SELECT IN_Global.* FROM IN_Global ORDER BY RecNum"
mySourceRecordset.open mySQLCmdText, mySourceConn

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = mySourceConn
oCmd.CommandText = "usp_UpdateLINKID"
oCmd.CommandType = 4

countr = 1
mySourceRecordset.movefirst

Do Until mySourceRecordset.Fields("RecType").value = "20"
mySourceRecordset.MoveNext
Loop

Do While Not mySourceRecordset.EOF

TrmPreFx = MID(mySourceRecordset.Fields("RecDetail").value, 34, 5)
LINKIDstr = "G" & TrmPreFx &
MID(mySourceRecordset.Fields("RecDetail").value, 55, 11)

Do
'-----Call the SP
oCmd.Parameters(1) = cstr(LINKIDstr)
oCmd.Parameters(2) = mySourceRecordset.Fields("RecNum").value
oCmd.Execute
mySourceRecordset.MoveNext
If mysourceRecordset.EOF Then Exit Do
Loop Until mySourceRecordset.Fields("RecType").value = "20"

Loop

mySourceRecordset.close
mySourceConn.close
Main = DTSTaskExecResult_Success

End Function


Stored Proc CODE
'----------------------------------------------------------
CREATE PROCEDURE usp_UpdateLINKID
(
@LINKID as varchar(17),
@RecNum as varchar(15)
)
AS
UPDATE IN_Global SET LINKID = @LINKID WHERE RecNum = @RecNum
GO

You don't need to do any of this recordset stuff. A single SQL UPDATE
statement is required to perform the update you want, something like:

update IN_Global
SET LINKID = 'G' + SUBSTRING(b.RecDetail, 34, 5) + SUBSTRING(b.RecDetail,
55, 11)
FROM IN_Global as a INNER JOIN IN_Global as b ON a.RecNum = b.RecNum WHERE
a.RecType = '20'

This should take a few seconds to execute.

Hope this helps,

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk



"david.a.karpiak (AT) kp (DOT) org" wrote: