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.
killed connection
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)
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...
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:
you should get following error message:
if you get following, you constructed too simple query or killed it too late. please construct some harder one: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 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.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.)
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.
To solve this problem you can process the OnError event and close the connection manually.
sure 
that's the code
when i kill the connection, when the .ExecuteReader() is running, I get following 2 messages (the same as in sql management studio)
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;
}
}
}
}
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.