Information

Happy New Year to all
Question,
If i have a spid like this:
1)Sleeping
2)Last_wait type: PAGEIOLATCH_SH
3)wait ressource: 7:1:1246732
4)cmd: AWAITING COMMAND
5) it=B4s a day old and is comming from someone's pc
could it be possible that even though that spid is sleeping its holding a "lock" on the ressource 7:1:1246732?
ThanxsYou can run the sp_lock procedure and see if this spid has a lock on any
resources.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Infinite Loop in BOL code!?

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
----------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-----------------Where?|||Originally posted by Brett Kaiser
Where?

If an error occurs within the [Done:] handle then the compiler goes to [AdoError:] handle and when executing the last statement there "GoTo Done" we will end up going back to [Done:] ... then same error occurs .. then back to [AdoError:] .. etc .. that sure looks like an infinite loop!|||If a user who can not create tables runs this, yes, it could run forever. Fortunately, Microsoft makes everything run as "administrator".|||lack of permission to Insert would cause the same problem .. But it's not just lack of permission that might cause this code to run forever .. for instance a network failure might raise the same problem .. a server failure might also do the same thing .. the point is: Error handling is supposed to be very helpful but in this code it is not. Because it is not done right.