Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > microsoft.applicationblock.data DLL (.net2.0) causing sql timeout error

Reply
Thread Tools

microsoft.applicationblock.data DLL (.net2.0) causing sql timeout error

 
 
Milsnips
Guest
Posts: n/a
 
      11-16-2006
hi there,

i;m doing a loop of a few hundred records and inserting into database using
the MS data dll, and the following line

dim sql as string = "mysql code is here..."
microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)

After a large number of records, i see its not closing the sql process, and
the process remain in "sleeping" mode.
How can i fix this?

thanks,
Paul


 
Reply With Quote
 
 
 
 
Mark Fitzpatrick
Guest
Posts: n/a
 
      11-16-2006
Paul,
Give the Enterprise Library a try. I found the data block in that to
be much more stable. It's very similar and more neutral than the older
applicationblocks.

http://practices.gotdotnet.com/projects/entlib

--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"Milsnips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> hi there,
>
> i;m doing a loop of a few hundred records and inserting into database
> using the MS data dll, and the following line
>
> dim sql as string = "mysql code is here..."
> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
>
> After a large number of records, i see its not closing the sql process,
> and the process remain in "sleeping" mode.
> How can i fix this?
>
> thanks,
> Paul
>
>



 
Reply With Quote
 
 
 
 
Milsnips
Guest
Posts: n/a
 
      11-16-2006
Ok, will give it a try

i just did another test with the my code, and instead of using the
application block, i created the connection and sqlcommand objects,
executenonquery, then closed, disposed and set both objects to nothing,

Dim cn As New SqlConnection(SharedFunctions.db)
Dim cmd As New SqlCommand(sql.ToString, cn)
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
'error occured
Finally
cmd.Dispose()
cmd = Nothing
If cn.State = Data.ConnectionState.Open Then cn.Close()
cn.Dispose()
cn = Nothing
End Try

after about approx. 150+ records, the error still occured, im trying to
import data from a CSV file via web that has up to 500-2000 records.

thanks,
Paul
"Mark Fitzpatrick" <(E-Mail Removed)> wrote in message
news:OCM$%(E-Mail Removed)...
> Paul,
> Give the Enterprise Library a try. I found the data block in that
> to be much more stable. It's very similar and more neutral than the older
> applicationblocks.
>
> http://practices.gotdotnet.com/projects/entlib
>
> --
> Hope this helps,
> Mark Fitzpatrick
> Former Microsoft FrontPage MVP 199?-2006
>
> "Milsnips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> hi there,
>>
>> i;m doing a loop of a few hundred records and inserting into database
>> using the MS data dll, and the following line
>>
>> dim sql as string = "mysql code is here..."
>> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
>>
>> After a large number of records, i see its not closing the sql process,
>> and the process remain in "sleeping" mode.
>> How can i fix this?
>>
>> thanks,
>> Paul
>>
>>

>
>



 
Reply With Quote
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      11-16-2006
Your post title is somewhat misleading - are you REALLY getting a sql
timeout, or are you just seeing the SQL process in Enterprise Manager
"Sleeping".
If #2, that is normal, and has nothing to do with timeouts.
As long as you are closing your ADO.NET SqlConnection object after each
"call" you should be fine.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com




"Milsnips" wrote:

> hi there,
>
> i;m doing a loop of a few hundred records and inserting into database using
> the MS data dll, and the following line
>
> dim sql as string = "mysql code is here..."
> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
>
> After a large number of records, i see its not closing the sql process, and
> the process remain in "sleeping" mode.
> How can i fix this?
>
> thanks,
> Paul
>
>
>

 
Reply With Quote
 
Milsnips
Guest
Posts: n/a
 
      11-17-2006
Hi Peter,

I tried a number of ways, and what happens is it creates about 150 or so sql
process, and then throws the error saying connection pool max. reached.

What i'm using is the line,
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute NonQuery, so i dont have
an object to close/dispose.

Also, i did the full code with sqlconection, sqlcommand, and so on, did
executenonquery, then closed and disposed all objects and it still appears
to be happening. Bear in mind, my web page uploads a CSV file, and on button
click, it reads in the file, and tries importing/updating about 2000 records
on one postback.

thanks,
Paul
"Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Your post title is somewhat misleading - are you REALLY getting a sql
> timeout, or are you just seeing the SQL process in Enterprise Manager
> "Sleeping".
> If #2, that is normal, and has nothing to do with timeouts.
> As long as you are closing your ADO.NET SqlConnection object after each
> "call" you should be fine.
> Peter
>
> --
> Co-founder, Eggheadcafe.com developer portal:
> http://www.eggheadcafe.com
> UnBlog:
> http://petesbloggerama.blogspot.com
>
>
>
>
> "Milsnips" wrote:
>
>> hi there,
>>
>> i;m doing a loop of a few hundred records and inserting into database
>> using
>> the MS data dll, and the following line
>>
>> dim sql as string = "mysql code is here..."
>> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
>>
>> After a large number of records, i see its not closing the sql process,
>> and
>> the process remain in "sleeping" mode.
>> How can i fix this?
>>
>> thanks,
>> Paul
>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      11-17-2006
OK. If you are doing 150, that's enough to bomb out the connection pool which
holds 100 by default. So what is happening is that you are creating new
connections but they aren't getting closed / disposed.

The SqlHelper ExecuteNonQuery method has a number of overloads, one of which
allows you to pass in your own SqlConnection. So in the line after the
ExecuteNonQuery, you should call the .Close() method on your connection, and
that should take care of it.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com




"Milsnips" wrote:

> Hi Peter,
>
> I tried a number of ways, and what happens is it creates about 150 or so sql
> process, and then throws the error saying connection pool max. reached.
>
> What i'm using is the line,
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute NonQuery, so i dont have
> an object to close/dispose.
>
> Also, i did the full code with sqlconection, sqlcommand, and so on, did
> executenonquery, then closed and disposed all objects and it still appears
> to be happening. Bear in mind, my web page uploads a CSV file, and on button
> click, it reads in the file, and tries importing/updating about 2000 records
> on one postback.
>
> thanks,
> Paul
> "Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Your post title is somewhat misleading - are you REALLY getting a sql
> > timeout, or are you just seeing the SQL process in Enterprise Manager
> > "Sleeping".
> > If #2, that is normal, and has nothing to do with timeouts.
> > As long as you are closing your ADO.NET SqlConnection object after each
> > "call" you should be fine.
> > Peter
> >
> > --
> > Co-founder, Eggheadcafe.com developer portal:
> > http://www.eggheadcafe.com
> > UnBlog:
> > http://petesbloggerama.blogspot.com
> >
> >
> >
> >
> > "Milsnips" wrote:
> >
> >> hi there,
> >>
> >> i;m doing a loop of a few hundred records and inserting into database
> >> using
> >> the MS data dll, and the following line
> >>
> >> dim sql as string = "mysql code is here..."
> >> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
> >>
> >> After a large number of records, i see its not closing the sql process,
> >> and
> >> the process remain in "sleeping" mode.
> >> How can i fix this?
> >>
> >> thanks,
> >> Paul
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Milsnips
Guest
Posts: n/a
 
      11-17-2006
Thanks for the reply peter, i'll give that a try.

regards,
Paul

"Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> OK. If you are doing 150, that's enough to bomb out the connection pool
> which
> holds 100 by default. So what is happening is that you are creating new
> connections but they aren't getting closed / disposed.
>
> The SqlHelper ExecuteNonQuery method has a number of overloads, one of
> which
> allows you to pass in your own SqlConnection. So in the line after the
> ExecuteNonQuery, you should call the .Close() method on your connection,
> and
> that should take care of it.
> Peter
>
> --
> Co-founder, Eggheadcafe.com developer portal:
> http://www.eggheadcafe.com
> UnBlog:
> http://petesbloggerama.blogspot.com
>
>
>
>
> "Milsnips" wrote:
>
>> Hi Peter,
>>
>> I tried a number of ways, and what happens is it creates about 150 or so
>> sql
>> process, and then throws the error saying connection pool max. reached.
>>
>> What i'm using is the line,
>> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute NonQuery, so i dont
>> have
>> an object to close/dispose.
>>
>> Also, i did the full code with sqlconection, sqlcommand, and so on, did
>> executenonquery, then closed and disposed all objects and it still
>> appears
>> to be happening. Bear in mind, my web page uploads a CSV file, and on
>> button
>> click, it reads in the file, and tries importing/updating about 2000
>> records
>> on one postback.
>>
>> thanks,
>> Paul
>> "Peter Bromberg [C# MVP]" <(E-Mail Removed)> wrote in
>> message
>> news:(E-Mail Removed)...
>> > Your post title is somewhat misleading - are you REALLY getting a sql
>> > timeout, or are you just seeing the SQL process in Enterprise Manager
>> > "Sleeping".
>> > If #2, that is normal, and has nothing to do with timeouts.
>> > As long as you are closing your ADO.NET SqlConnection object after each
>> > "call" you should be fine.
>> > Peter
>> >
>> > --
>> > Co-founder, Eggheadcafe.com developer portal:
>> > http://www.eggheadcafe.com
>> > UnBlog:
>> > http://petesbloggerama.blogspot.com
>> >
>> >
>> >
>> >
>> > "Milsnips" wrote:
>> >
>> >> hi there,
>> >>
>> >> i;m doing a loop of a few hundred records and inserting into database
>> >> using
>> >> the MS data dll, and the following line
>> >>
>> >> dim sql as string = "mysql code is here..."
>> >> microsoft.applicationblocks.data.sqlhelper.execute nonquery(configurationsettings.appsetting("db"),co mmandtype.text,sql)
>> >>
>> >> After a large number of records, i see its not closing the sql
>> >> process,
>> >> and
>> >> the process remain in "sleeping" mode.
>> >> How can i fix this?
>> >>
>> >> thanks,
>> >> Paul
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CGI-Timeout causing "internal server error"???? Carl Lafferty Perl Misc 7 07-22-2006 12:07 AM
Cocoon sql transformer causing squid timeout phil.a.jenkins@gmail.com XML 0 12-07-2005 04:57 PM
Timeout::timeout and Socket timeout Mark Probert Ruby 1 10-06-2004 09:30 AM
Annclist.dll is causing an error with KERNEL32.DLL gavinsmall Computer Support 2 09-04-2004 05:41 AM
msvcrt.dll, msvcirt.dll, msvcrt20.dll and msvcrt40.dll, explanation please! Snoopy NZ Computing 16 08-25-2003 12:34 PM



Advertisments