Cancel TSQL query

[sunaj]

Hi Guys,

Does anybody know if/how I can cancel a query to my MS SQL server?

'Open my connections here...
Dim Comm As New SqlCommand("My long haired SQL statement here",Conn) 
Dim MyReader As SqlDataReader = Comm.ExecuteReader 'if the user gets tried of waiting I want to tell the SQL server to forget about it.

I could launch a 2nd thread and run the SQL there, but that would not stop the process on the SQL server.
Should I ask the question in the MS SQL server forum instead?

Thx in advance

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

This actually seems to work....

Imports System.Threading, System.Data.SqlClient
Public Class Class1

    Public DataTableRes As DataTable
    Public Event DataTableReady()
    Dim Cmd As SqlCommand
    Dim T1 As Thread
    Dim m_ConnStr As String

    Private Sub FillSource()
        Dim Conn As New SqlConnection(m_ConnStr)
        Conn.Open()
        Cmd.Connection = Conn
        DataTableRes = New DataTable()
        Dim DA As New SqlDataAdapter(Cmd)
        DA.Fill(DataTableRes)
        Conn.Close()
        RaiseEvent DataTableReady()
    End Sub

    Public Sub PopDataTable(ByVal SQL As String, ByVal ConnStr As String)
        m_ConnStr = ConnStr
        Dim t1Start As ThreadStart = New ThreadStart(AddressOf FillSource)
        T1 = New Thread(t1Start)
        Cmd = New SqlCommand(SQL)
        T1.Start()
    End Sub

    Public WriteOnly Property Break() As Boolean
        Set(ByVal Value As Boolean)
            Cmd.Cancel()
            T1.Abort()
        End Set
    End Property

End Class

Use:

    Dim WithEvents h As New Class1()
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        h.PopDataTable("waitfor delay '00:00:03';select top 10 cast(datetime as varchar(25)) from tblstation", _
            "Data Source=MyServerName;Integrated Security=SSPI;Initial Catalog=MyDBName;")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        h.Break = True
        MessageBox.Show("User breaks")
    End Sub

    Private Sub h_DataTableReady() Handles h.DataTableReady
        MessageBox.Show(h.DataTableRes.Rows(0).Item(0).ToString)
    End Sub

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'