Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Location
    Sydney, Australia
    Posts
    4

    Post Unanswered: Stored Procedure problem in ActiveX Script

    Hi..

    First off, apologies if you've read this on any other forums.. I just can't seem to get an answer to the error I encounter..

    I have a Stored Procedure being called from an ActiveX script in a DTS Job that runs daily. First the DTS JOb checks the Date value of a remote file, if it is more recent than a file stored locally, it dowloads the file [a semi-colon delimited text file]. Then, from within a While/Wend loop, I am iterating thru each row in the semi-colon delimited text file, formatting the array values of each line, assigning them to Params, executing the SP and setting the command object to Nothing.

    The problem is, that after 3151 rows inserted correctly into the Table, the ActiveX script fails and with the following error reported:

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    Is this a "maximum number of connections" issue? Can it be resolved? I can see that the oCmd [and hence the connection] is trashed and then initialised for each of the 5007 records, but this doesn't seem like a inordinate amount of work really?

    I have included an abridged version of my code below.. It is all dim'd elsewhere in the ActiveX so implicit coding notwithstanding..

    -- CODE START --

    While Not oTxtStream.AtEndOfStream

    strLine = Trim(oTxtStream.ReadLine)
    i = 0
    myArr = Split(strLine, ";")

    strParam = fncCharCheck(myArr(i))

    Set oCMD = CreateObject("ADODB.Command")
    Set oCMD.ActiveConnection = oConn
    With oCMD
    .CommandType = adCmdStoredProc
    .CommandText = "sdnInsert"
    .Parameters.Append oCMD.CreateParameter("@Param",adVarChar,adParamInp ut,50,strParam)
    .Execute
    End With
    Set oCMD = Nothing

    Wend

    -- END CODE --

    What I'm after really is a reason for the connection to error on me.. The 5007 line [and up to 650 chars per line] file is too big to be dealt with by the SP directly, right? So I have to use VB to Loop through each line? Is 5007 successive connections alot? Surely this kinda thing is done elsewhere, I just wonder how..

    Any help would be greatly appreciated..

    KingRoon
    Last edited by kingroon; 01-10-06 at 18:40. Reason: Typo!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are using DTS on SQL server...

    did you try to ask the question on the SQL server forum. If you did and didn't get a response then what makes you think you will get a response on the generic SQL forum?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •