Sqlserver DTS

 

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Private Sub Form_Load()
Main
End Sub
Function Main()
   'On Error Resume Next

'aussql2 overtime
Set Connection = CreateObject("ADODB.Connection")
Connection.open "provider=sqloledb;server=test;database=test;uid=test;pwd=test"
'aussql5 FlowER test
Set Connection1 = CreateObject("ADODB.Connection")
Connection1.open "provider=sqloledb;server=10.5.13.83;database=test;uid=sa;pwd=test$"
strsql = "delete from auoform11_dept  where org_id  not like 'sg%'   "
strsql = strsql & " and org_id not like 'sj%' and  org_id not like 'sq%' and  org_id not like 'sp%' and  org_id not like 'sm01%'  "
Connection1.execute (strsql)

strsql = "select a.org_id,b.org_cname as p1_org_cname,b.org_ename as p1_org_ename,c.org_cname as p2_org_cname,c.org_ename as p2_org_ename  "
strsql = strsql & " from org_data a,org_data b,org_data c  "
strsql = strsql & " where left(a.org_id,2)+'00'=b.org_id and left(a.org_id,3)+'0'=c.org_id  and a.org_id not in('SG10','SG00')  "
strsql = strsql & " and (a.org_id not like 'sg01%'  and a.org_id not like 'sg02%'  and a.org_id not like 'sg03%'and  a.org_id not like 'sm01%'  "
strsql = strsql & " and a.org_id  not like 'sQ01%'  and a.org_id not like 'sQ02%'  and a.org_id not like 'sQ03%' and a.org_id  not like 'sg10%') "

Set rs = Connection.execute(strsql)

While Not rs.EOF

strsql = "SELECT * FROM DEPT where org_id='" & Trim(rs(0)) & "' "
Set rs1 = Connection1.execute(strsql)
If Not rs1.EOF Then
strsql = " update DEPT set p1_org_cname='" & Trim(rs(1)) & "',  p1_org_ename='" & Trim(rs(2)) & "', p2_org_cname='" & Trim(rs(3)) & "', p2_org_ename='" & Trim(rs(4)) & "' where org_id='" & Trim(rs(0)) & "'"
Connection1.execute (strsql)
Else
strsql = "insert into DEPT values('" & Trim(rs(0)) & "','" & Trim(rs(1)) & "','" & Trim(rs(2)) & "','" & Trim(rs(3)) & "','" & Trim(rs(4)) & "')"
Connection1.execute (strsql)
End If
rs.movenext

Wend

 Err = 0
 If Err <> 0 Then
   Main = DTSTaskExecResult_Failure
 Else
   Main = DTSTaskExecResult_Success
 End If
End Function

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

 

Function Main()
   'On Error Resume Next

 

'ausnetdev
Set Connection = CreateObject("ADODB.Connection")
Connection.Open "provider=sqloledb;server=10.5.13.83;database=a;uid=sa;pwd=$"

 

'aussql5 FlowER test
Set Connection1 = CreateObject("ADODB.Connection")
Connection1.Open "provider=sqloledb;server=10.5.13.83;database=s;uid=sa;pwd=s$"

 

strsql = "select form_no from test where a='a'  and test in('wp','ap')  "
Set rsformno = Connection1.Execute(strsql)

 

While Not rsformno.EOF

 

strsql = "update OAHAOCAI set test_Status ='AP'  WHERE test='" & rsformno(0) & "'"
Connection.Execute (strsql)

 

rsformno.movenext

 

Wend

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

             Set conn = CreateObject("adodb.connection")
 conn.open  "Provider=SQLOLEDB;server=t;uid=sa;pwd=flower;database=center"

             strsql0= "select emp_no, boss_no from emp_data_manually"

             set rs0=conn.execute(strsql0)
           
             while not rs0.eof

                  strsql1="update emp_data_all set boss_no='" & rs0("boss_no") & "' where emp_no='" & rs0("emp_no") & "'"
                  conn.execute strsql1
                  rs0.movenext

            wend

 Main = DTSTaskExecResult_Success
End Function