01-10-06, 17:39
|
|
Registered User
|
|
Join Date: Jan 2006
Location: Sydney, Australia
Posts: 4
|
|
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 17:40.
Reason: Typo!
|
dBforums
>
Stored Procedure problem in ActiveX Script
Linear Mode
