MS SQL Server - Stored procedures do not logout

Author

Stored procedures do not logout

andrew

2006-02-07, 3:23 am

I noticed that when I call a stored proc, sqlserver create a session. When I
finish executing stored procedures, the session do not logout. As a result,
I have to manually kill hundreds of session every day from enterprise
manager. May I know anyone encounter this problem? In my stored proc, I
only do 'begin', 'sql statement', 'end'.

Michael Hotek

2006-02-07, 3:23 am

I don't understand. Calling a stored proc doesn't create a SPID (unless you
are executing xp_cmdshell or sp_executesql). A SPID is created when you
create a connection to the instance.

--
Mike
http://www. solidqualitylearning
.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

"andrew" <andrew@discussions.microsoft.com> wrote in message
news:5E0A369A-4608-4AD8-88E1- A89591C88B34@microso
ft.com...
>I noticed that when I call a stored proc, sqlserver create a session. When
>I
> finish executing stored procedures, the session do not logout. As a
> result,
> I have to manually kill hundreds of session every day from enterprise
> manager. May I know anyone encounter this problem? In my stored proc, I
> only do 'begin', 'sql statement', 'end'.

Uri Dimant

2006-02-07, 3:23 am

andrew
what is the status in master.dbo.sysprocesses table?
also did you run sp_who2 stored procedure?

"andrew" <andrew@discussions.microsoft.com> wrote in message
news:5E0A369A-4608-4AD8-88E1- A89591C88B34@microso
ft.com...
>I noticed that when I call a stored proc, sqlserver create a session. When
>I
> finish executing stored procedures, the session do not logout. As a
> result,
> I have to manually kill hundreds of session every day from enterprise
> manager. May I know anyone encounter this problem? In my stored proc, I
> only do 'begin', 'sql statement', 'end'.

Andrew J. Kelly

2006-02-07, 9:23 am

Exactly how are you executing this stored procedure?

--
Andrew J. Kelly SQL MVP

"andrew" <andrew@discussions.microsoft.com> wrote in message
news:5E0A369A-4608-4AD8-88E1- A89591C88B34@microso
ft.com...
>I noticed that when I call a stored proc, sqlserver create a session. When
>I
> finish executing stored procedures, the session do not logout. As a
> result,
> I have to manually kill hundreds of session every day from enterprise
> manager. May I know anyone encounter this problem? In my stored proc, I
> only do 'begin', 'sql statement', 'end'.

andrew

2006-02-10, 3:23 am

I am using .Net. In the .Net, I just call the stored procedures directly.

"Andrew J. Kelly" wrote:

> Exactly how are you executing this stored procedure?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "andrew" <andrew@discussions.microsoft.com> wrote in message
> news:5E0A369A-4608-4AD8-88E1- A89591C88B34@microso
ft.com...
>
>
>

Andrew J. Kelly

2006-02-10, 9:23 am

That doesn't really tell me much. Can we see the code where you are calling
it? And are you using connection pooling? Do you close the connection
after you are done executing the sp?

--
Andrew J. Kelly SQL MVP

"andrew" <andrew@discussions.microsoft.com> wrote in message
news:076D97B0-600C-43D8-810A- 9D53AFF6FB64@microso
ft.com...[color=darkred]
>I am using .Net. In the .Net, I just call the stored procedures directly.
>
> "Andrew J. Kelly" wrote:
>

andrew

2006-02-13, 7:23 am

This is my code:

SqlParameter[] arrParm = new SqlParameter[1];
string strSQL = "sp_name";
arrParm[1] = new SqlParameter("@APPL_TYPE",System.Data.SqlDbType.VarChar ,15);
arrParm[1].Value =ApplType ;
SqlConnection cnn = new SqlConnection(connec
tingstring)
SqlCommand cmd = new SqlCommand(strSQL,cn
n)
cmd.CommandType = CommandType.StoredProcedure;
cnn.Open()
SqlTransaction myTrans = cnn. BeginTransactio(Isol
ationLevel.ReadCommitted)
cmd.Transaction = myTrans;
foreach(SqlParameter
param in arrParam)
{
cmd.Parameters.Add(param);
}
cmd.ExecuteNonQuery();
myTrans.Commit();
myTrans.Dispose();
while(cmd.Parameters.Count >0) cmd.Parameters.RemoveAt(0);
cnn.Close();

"Andrew J. Kelly" wrote:

> That doesn't really tell me much. Can we see the code where you are calling
> it? And are you using connection pooling? Do you close the connection
> after you are done executing the sp?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "andrew" <andrew@discussions.microsoft.com> wrote in message
> news:076D97B0-600C-43D8-810A- 9D53AFF6FB64@microso
ft.com...
>
>
>

Andrew J. Kelly

2006-02-13, 9:23 am

I don't really see anything wrong per say with the code that would case the
connection to remain open. Again I ask are you using connection pooling?
Have you traced these to see if the sp_reset connection is actually getting
called? Is there something in the way you set up the pooling that may be
the issue? Maybe these will help:

http://msdn.microsoft.com/library/d...ml/pooling2.asp
http://support.microsoft.com/kb/q191572/

--
Andrew J. Kelly SQL MVP

"andrew" <andrew@discussions.microsoft.com> wrote in message
news:1A72A1C4-FBD6-4737-BCC6- FC886CAC6A1F@microso
ft.com...[color=darkred]
> This is my code:
>
> SqlParameter[] arrParm = new SqlParameter[1];
> string strSQL = "sp_name";
> arrParm[1] = new SqlParameter("@APPL_TYPE",System.Data.SqlDbType.VarChar
> ,15);
> arrParm[1].Value =ApplType ;
> SqlConnection cnn = new SqlConnection(connec
tingstring)
> SqlCommand cmd = new SqlCommand(strSQL,cn
n)
> cmd.CommandType = CommandType.StoredProcedure;
> cnn.Open()
> SqlTransaction myTrans = cnn. BeginTransactio(Isol
ationLevel.ReadCommitted)
> cmd.Transaction = myTrans;
> foreach(SqlParameter
param in arrParam)
> {
> cmd.Parameters.Add(param);
> }
> cmd.ExecuteNonQuery();
> myTrans.Commit();
> myTrans.Dispose();
> while(cmd.Parameters.Count >0) cmd.Parameters.RemoveAt(0);
> cnn.Close();
>
>
> "Andrew J. Kelly" wrote:
>

Sponsored Links