SQL Server Performance

Ever changing named AS400 file to SQL Server

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by hjyoungii, Feb 12, 2007.

  1. hjyoungii New Member

    We have files on the AS400 that are named as atl0107, atl1206 and etc.

    We need to be able to retrieve the appropriate file to be ran in a stored procedure or DTS package.

    The problem is that when I attempt to retrieve the file in the following format, it states not found.

    '**********************************************************************
    ' Visual Basic ActiveX Script
    '************************************************************************
    Function Main()

    DIM oConn

    Dim cn

    set oConn = CreateObject("ADODB.Connection")

    cn.Open "Provider=MSDASQL;Driver=SQL Server;server=(local);Trusted_Connection=Yes"
    set rs.ActiveConnection = cn





    'Dim cn As New Connection
    'Dim rs As New Recordset

    'cn.CursorLocation = adUseClient
    'cn.Open "Provider=MSDASQL;Driver=SQL Server;server=(local);Trusted_Connection=Yes"
    'Set rs.ActiveConnection = cn
    'rs.Source = "Select * from sysobjects"
    'rs.Open , , adOpenKeyset, adLockOptimistic
    'rs.Close
    '












    Set fso = CreateObject("Scripting.FileSystemObject")
    strCurrentYear=DatePart("yyyy",Date)
    strCurrentMonth=DatePart("m",Date)-1

    strPreviousYear=CStr(DatePart("yyyy",Date)-1)
    StrCMSFile=CSTR("CMSOHIO.S10FA72E.XJLUTZ.STKBATL")
    if strCurrentMonth = 1 then
    strCurrentyear2 = right(left(strPreviousyear,4),2)
    else
    STRCURRENTYEAR2=right(left(strCurrentyear,4),2)
    end if
    strfile2=strcmsfile&strcurrentmonth&strcurrentyear2
    strfilea=cstr("CMSOHIO")
    strfileb=cstr("S10FA72E")
    STRFILEC=CSTR("XJLUTZ")
    STRFILEP=CSTR(".")
    STRFILED=CSTR("STKBATL")
    MsgBox "The End Date is: " &strcurrentyear2
    msgbox "file is: " &strcmsfile&strcurrentmonth&strcurrentyear2
    msgbox "file being used is: " &STRFILEA&STRFILEP&STRFILEB&STRFILEP&STRFILEC&STRFILEP&STRFILED
    ' MsgBox "The End Date is: " & DTSGlobalVariables("EndDate").value
    '\As400MachineNameCMSOHIO.S10FA72E.XLUTZ.STKBATL

    fso.CopyFile "CMSOHIO"+"."+"S10FA72E.XJLUTZ.STKBAT"+StrCurrentMonth+StrCurrentyear,"C:STKBSTKBATL"

    rs.Close

    Main = DTSTaskExecResult_Success
    End Function




  2. Chappy New Member

    So which bit is failing, and what do your debug messages show?<br /><br />All this code is irrelevant until the point where it tries to copy <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  3. hjyoungii New Member

    It is stating that the file CMSOHIO.S10FA72E.XJLUTZ.STKBAT1006 does not exitst. It does however exist.
  4. Adriaan New Member

    What happens if you issue a COPY or XCOPY command on the Windows command prompt, spelling out the file names in full?

    Other points ...

    Your month numbering apparently ranges from 0 to 11:
    strCurrentMonth=DatePart("m",Date)-1

    And your script is not always applying CStr() for the number-to-string conversion - although this is probably no big deal.

    Bigger problem could be that you might get leading blanks in the string, so you probably need to Trim() as well.
    And check if the month format is perhaps 2-digit, like 01 for January, instead of just 1.

    It is also a little weird that the & concatenation operators in your script do not have a blank space before and after them, and that your script has both + and & for concatenation operators.

    Finally, it may help to concatenate the file paths before executing the fso.CopyFile command (use variables).
  5. Chappy New Member

    What about the source directory? At the moment you specify a destination path, but only a source filename

    The file may exist but does it exist in the location where CopyFile is looking?

    It would default to 'current directory' but its not always easy to predict what that might be

Share This Page