sql server dts:
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_Connection=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
