June 5, 2007 at 12:58 pm
I am a newbie to sql server but trying very hard to master it, so please excuse my ignorance in advance.
I am connecting to SQL db and checking a directory to see if files are in the db, if not then put them in the db. I use a text box to display the folder i am on, also use a counter to see my progress. Everything works fine up until it reads about 2500 records then it becomes slow and eventually i get the:
SQL server does not exist
any advice is appreciated thanks
zach
June 6, 2007 at 2:51 am
We had a similiar problem before. It was solved when we enabled connection pooling. The connection(s) are closed when they are no longer needed?
June 6, 2007 at 1:06 pm
I close the connection after i have looped though everything... O by the way this is old shcool vb6 here is my code if that helps:
count = 0
'On Error Resume Next
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={SQL Server};SERVER=myserv\mydb;UID=id;PWD=pwd;DATABASE=db"
conn.ConnectionTimeout = 15
conn.Open
sql = "select databasename, manufacturerid from emmanufacturerdetails where type = 1 and em = 1"
Set rs2 = conn.Execute(sql)
While Not rs2.EOF
folder = rs2(0)
manid = rs2(1)
path = Me.path.Text 'App.path & "\" '"d:\emweb2\www\emweb\"
If Dir(path & folder & "_\VerifyOldThenDelete", vbDirectory) = "" Then
MkDir path & folder & "_\VerifyOldThenDelete"
End If
folderpath = path & folder & "_\css\"
x = Dir(folderpath, vbNormal)
Do Until x = ""
If InStr(1, x, ".htm") Then
sql = "select path from " & manid & "data where path= '" & x & "'"
Set rs3 = conn.Execute(sql)
If rs3.EOF Then
sqlinsert = "insert into emweberrors values ('" & folder & "', '" & folderpath & x & "', 'NotInDatabase')"
conn.Execute (sqlinsert)
DoEvents
count = count + 1
counter.Text = count
FileCopy (folderpath & x), (path & folder & "_\VerifyOldThenDelete\" & x)
' Kill (path & folder & "_\css\" & x)
End If
End If
x = Dir()
txtfolder.Text = rs2(0)
Loop
'End If
rs2.MoveNext
Wend
rs2.Close
conn.Close
MsgBox "Done"
End Sub
June 7, 2007 at 1:34 am
I would change 2 things
*Set rs2 = conn.Execute(sql)
While Not rs2.EOF
... This will hold locks on non-fetched data
to
set rs2=conn.execute(sql)
if rs2.recordcount>0 then
rs2.movelast
rs2.movefirst
While Not rs2.EOF
... all data retrieved, no remaining locks
* rs3 isn't closed explicitly after the test if a record exists, only closed at the next loop. Close recordsets as soon you don't need them any longer.
* perhaps use select top 1 ... for rs3 since you only need 1 record.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply