| Author |
Topic |
|
kingroon
Starting Member
29 Posts |
 Posted - 2005-12-14 : 19:28:53
|
| Hi,I have a Stored Procedure being called from an ActiveX script. From within a While/Wend loop, I am iterating thru each row in a semi-colon delimited TXT 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,adParamInput,50,strParam) .Execute End With Set oCMD = NothingWend-- END CODE --Any help would be greatly appreciated..KingRoonChaotician Man,Slice the lines of Virgin pathways.Harmony Hero. |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-15 : 07:10:28
|
| One Question i would ask, based on the values of your scriptHow many times are you calling the SP ?I would advice you call it jsut once and pass all your values once, let your SP do work only onceAfrika |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-15 : 07:17:22
|
Oh by the way,WELCOME TO SQLTEAM  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
kingroon
Starting Member
29 Posts |
Posted - 2005-12-18 : 17:03:17
|
| Thanks for the replies guys, and thanks for the welcome message Afrika!With regards to the posting, shall I pass the entire text file [as line value params] into the/a new SP & split and loop the data there? It just seems like a heap of text to be passing in is all.. But I could be wrong. There are 5007 lines in the text file [with the potential to be more in the future]..Thanks again..KingRoonChaotician Man,Slice the lines of virgin pathways.Harmony Hero. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-19 : 05:20:52
|
| One questionwhat way are you passing the lines ? as a csv file (comma seperated value) as stated above ?Your asp code looks okplease paste your SP CODE here. My advice is to call the SP once and loop through the csv values and do an insert based on each onewould you need help with this ?Afrika |
|
|
|
kingroon
Starting Member
29 Posts |
Posted - 2005-12-20 : 22:17:25
|
| Hi Akrika,Yes basically calling the SP 5007 times >*$Here is the SP code.. It's a basic INSERT SP that is used for another process [about 3000 lines this time], I'm just re-using rather than write another one [hoist by own laziness it seems!]. There are 15 params passed into the SP for each of the table columns. I've only used one value in the code below for examples sake..-- CODE STARTS HERECreate Procedure sdnInsert @Param varchar(50) AS set nocount on Insert into tblTABLE( CulumnName )Values( @Param) set nocount offGO-- CODE ENDS HEREThe Text file is essentially comma seperated. I take each line, split it up into an array [27 values], re-arrange and format them [concatenating a few of these e.g. street number and street name for address] and then pass these into the SP.Can I do all this in a SP then? Pass the whole file into the SP [and hence call it once] and format etc. in there?Thanks for your help with this mate..KingRoonChaotician Man,Slice the lines of virgin pathways.Harmony Hero. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-21 : 02:02:07
|
quote:
Originally posted by kingroon
Hi Akrika,Yes basically calling the SP 5007 times >*$
am i getting you right ?calling your SP ? 5007 ? in one page ?are you using an ASP PAGE ? I personally dont think thats right.Its best to call it once and run a loop in your SP In my opinion thats very badAfrika |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-21 : 08:02:43
|
| If there is a good reason to loop in the ASP and not in the SQL (Afrika is right... I always loop in SQL, especially after a margarita)... but if you must loop in ASP try moving the command object create / destroy outside the loop. 50/50 that it might fix it or break something else. |
|
|
|
kingroon
Starting Member
29 Posts |
Posted - 2005-12-21 : 21:25:21
|
| Hi,Yeah I know it kinda looks bad Afrika, it's actually sitting in an ActiveX script within a DTS job. It runs at 2am to preserve server resources during the low period..I've tried to do as you suggested before SamC, but it complained that the SP had too many params, presumably because I wasn't trashing the object and starting with a clean slate so to speak.Righto, from the beginning..I have a 5007 line, comma delimited text file.Each line has 27 values.I need to format these into 15 columns and input into a Table.Can I pass the whole text file into the SP?KingRoonChaotician Man,Slice the lines of virgin pathways.Harmony Hero. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-22 : 05:48:59
|
| There are too many parameters simply because the maximum lenght of any column in SQL server is 8000, thats varchar 8000, so with a parameter volume of 5007 it certainly wont work.I am wondering really what you are trying to achieve, but you definitely are getting it wrong.there must be a better way to do itfind a simpler way of breaking it down.So what are u trying to achieve ?Afrika |
|
|
|
kingroon
Starting Member
29 Posts |
Posted - 2005-12-23 : 00:02:01
|
| Thanks for the reply Afrika,Essentially I have an ActiveX DTS job that runs daily.An ActiveX Script checks the Last Modified Date of a freely distributed listing on a website. If the date stored [dbo.tblListModified] from the last download is less recent than the file's, it downloads the file. Then, for each line within the file, I split each value on "," and assign them to string variables, then call an INSERT Stored Proc to insert the data.Am I doing this the right way??Merry Xmas anyways, I'm outta here until Wednesday ;o)Hasta Luego,KingRoonChaotician Man,Slice the lines of virgin pathways.Harmony Hero. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-23 : 07:55:37
|
| sounds ok to meI would advice then that you break down the job scheduling if its over 5007 to chunks or executable process and run batches to do your insert.We have a similar one wiht our sms gateway, when we do bulk blastsAfrika |
|
|
|
dave
Starting Member
15 Posts |
Posted - 2006-01-09 : 21:21:12
|
| I think what kingroon is asking is "is there a reason why it would die after 3151 iterations". Not, "please supply a different method" or "can you tell me how you would go about doing it".If there is no reason then there must be something else going wrong. If there is a reason then he can try to work around it from there! |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-02-13 : 23:40:56
|
| You're not closing the DB connection, by setting the command to nothing you are just orphaning an open connection.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
|
Store Procedure problem in ActiveX