sql Timeout

[Bill Cue]

1) sending the code below always times out for the current day
2) sending the code below never times out for any other day except the
current day

------------------------------------------ code snippet
--------------------

' Create a connection to SQL database located on the
strConnection = ConfigurationSettings.AppSettings("ConnectionString")myConnection
= New SqlConnection(strConnection)
' Connect to the SQL database using a SQL SELECT query to get all
' the data from the "Daily Attendance VIEW & other tables" table.

myStr = "SELECT dbo.vRepDAILYATTENDANCE.homName as 'School',
dbo.vRepDAILYATTENDANCE.cliLName as 'Last',
dbo.vRepDAILYATTENDANCE.cliFName as 'First',
dbo.vRepDAILYATTENDANCE.cliMInitial as 'Init', "

myStr = myStr & "dbo.vRepDAILYATTENDANCE.courseCode as 'Course Code',
dbo.vRepDAILYATTENDANCE.couTitle as 'Course Title' "

myStr = myStr & "FROM ((dbo.vRepDAILYATTENDANCE INNER JOIN
dbo.HOMESCHOOL ON dbo.vRepDAILYATTENDANCE.homID = "

myStr = myStr & "dbo.HOMESCHOOL.homID) INNER JOIN
dbo.HOMESCHOOLCONTACT ON dbo.HOMESCHOOL.homID = "

myStr = myStr & "dbo.HOMESCHOOLCONTACT.homID) INNER JOIN
dbo.ATTENDANCECODE ON dbo.vRepDAILYATTENDANCE.codID = "

myStr = myStr & "dbo.ATTENDANCECODE.codID "

myStr = myStr & "WHERE dbo.vRepDAILYATTENDANCE.attDate = '" & myDate &
"' AND dbo.vRepDAILYATTENDANCE.BranchID=1 "
myStr = myStr & "and (dbo.vRepDAILYATTENDANCE.codCode='XU' or
dbo.vRepDAILYATTENDANCE.codCode='XE') "

If mySession <> "X" Then ' ALL sessions or one session
myStr = myStr & "AND Right(dbo.vRepDAILYATTENDANCE.courseCode,1) =
'" & mySession & "' "
End If

If mySchFlag = 1 Then
myStr = myStr & "AND dbo.HOMESCHOOL.homName = '" & mySchool & "' "
End If

myStr = myStr & "ORDER BY dbo.vRepDAILYATTENDANCE.attDate,
dbo.vRepDAILYATTENDANCE.homName, dbo.vRepDAILYATTENDANCE.cliLName,
dbo.vRepDAILYATTENDANCE.cliFName,
dbo.vRepDAILYATTENDANCE.cliMInitial;"
myCommand = new SqlDataAdapter(myStr,myConnection)
' Create and fill a DataSet.
Dim ds As DataSet = new DataSet()
myCommand.Fill(ds)
------------------------------------ code snippet

 

[Guest]

How active is the CRUD on the system. If you are constantly getting new rows,
you might have a lot of information still in the transaction log, which would
potentially kill a query. That is my first thought.

Consider checkpointing the data occasionally and see if that helps.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************