killed connection

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

killed connection

Post by Ludek » Tue 26 May 2009 15:32

if i kill a sql server connection using t-sql KILL command, the property "connected" of TMSConnection is still true. each attempt to execute a query requires the a login and a loout to sql server. i would like to detect a correct this bad state (very bad for me, as the @@spid value changes from command to command etc). how could i do that?
thanks, ludek.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Tue 26 May 2009 16:44

more infos: i tried also localfailover set to true, disconnected mode to false - the onconnectionlostevent does not get called - i simply can't find out, that I 'm stuck in such unhappy state :(

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Tue 26 May 2009 17:59

next info - after kill of the connection the tmsconnection behaves like disconnectedmode = true (although set to false) - the connection gets closed and opened for each query execution.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 27 May 2009 09:54

To solve this problme you should set the LocalFailover option to True and to set RetryMode to rmRaise in the OnConnectionLost event handler. In this case the AfterDisconnect event will be raised.

Also to learn if a new connection was established you can process the AfterConnect event.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Wed 27 May 2009 11:06

as i write in my second comment - i had localfailover true and the onconnectionlost did not get called... even no afterconnect/beforeconnect events were raised. the msconnection had always connected = true, but it was not connected. the connection opened and closed for each query execution (have seen in sql profiler)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 27 May 2009 12:15

The TMyConnection.AfterDisconnect event occurs after the connection is closed by calling the Disconnect or Close methods. If connection was broken, this event is not arised.
Therefore OnConnectionLost, AfterDisconnect, and AfterConnect events occur when trying to open or execute a SQL query.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Wed 27 May 2009 12:32

i understand this. but i get these events never, even on execution on next query not. the weird thing is also, i get just "unknown error" in the killed connection, no better message. in my small testing executable i already get better one. so first i must find out, where's the problem with the error message...

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 27 May 2009 13:16

Please try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Wed 27 May 2009 15:32

now i have found out, that it happens only on killing really big queries (my current query has 60 lines of sql). i'll try to find the smallest one that generates such error...

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Wed 27 May 2009 16:01

so: please construct a query with a long time of execution (not just long time of fetching the data, also long time of compilation of the query - some joins on big tables). first execute it from one tab of sql management studio and kill immediately from another one.
you should get following error message:
CORRECT:
------------
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
if you get following, you constructed too simple query or killed it too late. please construct some harder one:
WRONG
---------
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
if you have such long executin query, execute it from some delphi sdac application and, again, kill its connection. you get "unspecified error" and the connection then behaves weird - no events (no aferconnect, beforeconnect, connectionlost etc), the executable still runs, but profiler shows, that each query execution is between an "audit login" and "audit logout" - it means, the temporary tables cannot be used between query executions, @@spid changes, etc - just everything behaves like disconnected mode.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Wed 27 May 2009 16:27

just a guess: it could be because of the 2 errors - first one with relatively low severity of 11, and second, fatal one with 20. perhaps is such combination of errors wrongly interpreted somewhere :roll:

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 28 May 2009 08:26

We have reproduced the problem. Really, if 'unspecified error' is arised then the OnConnectionLost event is not arised. But in this case SDAC doesn't establish connection obviously, and OLEDB executes queries correctly, establishing connection implicitly.
To solve this problem you can process the OnError event and close the connection manually.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Thu 28 May 2009 09:34

hmmmm.... how should I do that? test the exception, if its code is $80004005?
I tried similar situation in visual studio 2008 - .net writes nice messages, just as sql management studio does. only sdac writes just "unknown error". How can you explain that?
edit: sorry, i meant "unspecified error"

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 28 May 2009 11:14

Please give a more detailed description of the way you get the required mesage in Visual Studio 2008?

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Thu 28 May 2009 14:08

sure :)

that's the code

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LudkaTest
{
  public partial class Form1 : Form
  {
    SqlConnection conn = new SqlConnection("Data Source=...;Initial Catalog=...;Persist Security Info=True;User ID=...;Password=...");
    
    public Form1()
    {
      InitializeComponent();
      conn.Open();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      button1.Enabled = false;
     
      string commandText = string.Format(
        @"
          
        ");


      SqlCommand command = new SqlCommand(commandText, conn);
      SqlDataReader reader=null;
      try
      {
        reader = command.ExecuteReader();
        while (reader.Read())
        {

        }
      }
      catch(Exception ex) 
      {
        throw new Exception(ex.Message);
      }
      finally
      {
        reader.Close();
        conn.Close();
        button1.Enabled = true;
      }
    }
  }
}
when i kill the connection, when the .ExecuteReader() is running, I get following 2 messages (the same as in sql management studio)
A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.

Post Reply