Connection Timeout

S

sam

When I use ASP.Net via ADO.Net to retrieve data from MS SQL Server 2000, it
is always prompt error message "Timeout" if the number record is larger than
1 million.

May I know what is the best practise for pool large records for my case,
please?

Or it is limitation on ADO.Net or ASP.Net or .Net framework 1.1?


ASP.Net Coding
-----------------
Server.ScriptTimeout = 9000

Dim constr_GL_3a_2 As String = "server='SQLSVR'; user id='sa';
password='sa'; Database='ERP_ARCHIVAL';Connect Timeout=1200"
Dim sqlcon_GL_3a_2 As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr_GL_3a_2)
Dim sqlcmd_GL_3a_2 As System.Data.SqlClient.SqlCommand = New SqlCommand()
Dim sqlda_GL_3a_2 As New system.Data.SqlClient.sqldataAdapter()
Dim sqlprm1_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
Dim sqlprm2_GL_3a_2 As New system.Data.SqlClient.SqlParameter()

Try
sqlcmd_GL_3a_2 = sqlcon_GL_3a_2.CreateCommand

sqlcmd_GL_3a_2.CommandText = "[General_Ledger]"

sqlcmd_GL_3a_2.CommandType = commandtype.StoredProcedure

sqlprm1_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateFrom",SqlDbType.Int,6)
sqlprm1_GL_3a_2.value = FlagFrDate

sqlprm2_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateTo",SqlDbType.Int,6)
sqlprm2_GL_3a_2.value = FlagToDate

sqlda_GL_3a_2.SelectCommand = sqlcmd_GL_3a_2
sqlcon_GL_3a_2.Open
sqlcmd_GL_3a_2.ExecuteNonQuery
Finally
sqlcon_GL_3a_2.Close()
sqlcon_GL_3a_2.Dispose()
End Try

constr_GL_3a_2 = Nothing
sqlcon_GL_3a_2 = Nothing
sqlcmd_GL_3a_2 = Nothing
sqlda_GL_3a_2 = Nothing
 
M

Marina

The default timeout for a command is 30 seconds. You need to set it to a
larger timeout, if you need more time to execute your query.

However, do you really need to retrieve 1 million records all at once?
 
S

sam

I had set timeout at following :
- web.config
- ISS 6.0
- ASP.Net page

But it is seem un-retrievable for millions record....

I also notice that MS SQL Server 2000 (Standard Edition) is capable to store
over millions but unable to retrieval via stored procedure or ADO.Net...

Could it possible that MS SQL Server 2000 (Standard Edition) have constraint
of retrieval millions record and only MS SQL Server 2000 (Enterprise
Edition) could do so...

Please advise...

Marina said:
The default timeout for a command is 30 seconds. You need to set it to a
larger timeout, if you need more time to execute your query.

However, do you really need to retrieve 1 million records all at once?

sam said:
When I use ASP.Net via ADO.Net to retrieve data from MS SQL Server 2000,
it is always prompt error message "Timeout" if the number record is
larger than 1 million.

May I know what is the best practise for pool large records for my case,
please?

Or it is limitation on ADO.Net or ASP.Net or .Net framework 1.1?


ASP.Net Coding
-----------------
Server.ScriptTimeout = 9000

Dim constr_GL_3a_2 As String = "server='SQLSVR'; user id='sa';
password='sa'; Database='ERP_ARCHIVAL';Connect Timeout=1200"
Dim sqlcon_GL_3a_2 As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr_GL_3a_2)
Dim sqlcmd_GL_3a_2 As System.Data.SqlClient.SqlCommand = New
SqlCommand()
Dim sqlda_GL_3a_2 As New system.Data.SqlClient.sqldataAdapter()
Dim sqlprm1_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
Dim sqlprm2_GL_3a_2 As New system.Data.SqlClient.SqlParameter()

Try
sqlcmd_GL_3a_2 = sqlcon_GL_3a_2.CreateCommand

sqlcmd_GL_3a_2.CommandText = "[General_Ledger]"

sqlcmd_GL_3a_2.CommandType = commandtype.StoredProcedure

sqlprm1_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateFrom",SqlDbType.Int,6)
sqlprm1_GL_3a_2.value = FlagFrDate

sqlprm2_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateTo",SqlDbType.Int,6)
sqlprm2_GL_3a_2.value = FlagToDate

sqlda_GL_3a_2.SelectCommand = sqlcmd_GL_3a_2
sqlcon_GL_3a_2.Open
sqlcmd_GL_3a_2.ExecuteNonQuery
Finally
sqlcon_GL_3a_2.Close()
sqlcon_GL_3a_2.Dispose()
End Try

constr_GL_3a_2 = Nothing
sqlcon_GL_3a_2 = Nothing
sqlcmd_GL_3a_2 = Nothing
sqlda_GL_3a_2 = Nothing
Click to expand...
Click to expand...
 
M

Marina

Like I said, the issue is the command timeout. So you need to set it on the
SqlCommand object. This is not related to IIS or anything like that, I would
expect the same behavior in a windows app.

So you should be able to retrieve 1 million records. But you probably want
to re-examine your requirement to get 1 million records into memory all at
once.

sam said:
I had set timeout at following :
- web.config
- ISS 6.0
- ASP.Net page

But it is seem un-retrievable for millions record....

I also notice that MS SQL Server 2000 (Standard Edition) is capable to
store over millions but unable to retrieval via stored procedure or
ADO.Net...

Could it possible that MS SQL Server 2000 (Standard Edition) have
constraint of retrieval millions record and only MS SQL Server 2000
(Enterprise Edition) could do so...

Please advise...

Marina said:
The default timeout for a command is 30 seconds. You need to set it to a
larger timeout, if you need more time to execute your query.

However, do you really need to retrieve 1 million records all at once?

sam said:
When I use ASP.Net via ADO.Net to retrieve data from MS SQL Server 2000,
it is always prompt error message "Timeout" if the number record is
larger than 1 million.

May I know what is the best practise for pool large records for my case,
please?

Or it is limitation on ADO.Net or ASP.Net or .Net framework 1.1?


ASP.Net Coding
-----------------
Server.ScriptTimeout = 9000

Dim constr_GL_3a_2 As String = "server='SQLSVR'; user id='sa';
password='sa'; Database='ERP_ARCHIVAL';Connect Timeout=1200"
Dim sqlcon_GL_3a_2 As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr_GL_3a_2)
Dim sqlcmd_GL_3a_2 As System.Data.SqlClient.SqlCommand = New
SqlCommand()
Dim sqlda_GL_3a_2 As New system.Data.SqlClient.sqldataAdapter()
Dim sqlprm1_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
Dim sqlprm2_GL_3a_2 As New system.Data.SqlClient.SqlParameter()

Try
sqlcmd_GL_3a_2 = sqlcon_GL_3a_2.CreateCommand

sqlcmd_GL_3a_2.CommandText = "[General_Ledger]"

sqlcmd_GL_3a_2.CommandType = commandtype.StoredProcedure

sqlprm1_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateFrom",SqlDbType.Int,6)
sqlprm1_GL_3a_2.value = FlagFrDate

sqlprm2_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateTo",SqlDbType.Int,6)
sqlprm2_GL_3a_2.value = FlagToDate

sqlda_GL_3a_2.SelectCommand = sqlcmd_GL_3a_2
sqlcon_GL_3a_2.Open
sqlcmd_GL_3a_2.ExecuteNonQuery
Finally
sqlcon_GL_3a_2.Close()
sqlcon_GL_3a_2.Dispose()
End Try

constr_GL_3a_2 = Nothing
sqlcon_GL_3a_2 = Nothing
sqlcmd_GL_3a_2 = Nothing
sqlda_GL_3a_2 = Nothing
Click to expand...
Click to expand...
Click to expand...
 
W

William \(Bill\) Vaughn

I agree. When you go to the library for information on a subject, do you
fetch the entire shelf of books at once--or (in your case) the entire
library, stuff it into your Volkswagen beetle and try to get home to read
them all before your library sends the cops after you?

Consider that ADO (and all of the data access interfaces) are "query"
interfaces. They are designed to let you ask questions (query) the database.
The database is designed and optimized to answer questions--not act as a
file server. If find yourself fetching a million rows to process them as a
block (and there are applications that must do this (like graphics and
cartography)), then I usually recommend that this processing go on at the
server using an Extended Stored Procedure and rolled-up data be returned to
the client via file IO. If you're simply moving data from one source to
another, ADO is the poorest possible choice short of paper tape. DTS or BCP
are designed and tuned to move bulk data. So, you have to process the data
before it's committed to the database. Never post unvalidated data to a
production table--post it to a work table and use a stored procedure to
revalidate the rules and criteria--then post the valid data to the server.
Does this put a big load on the server? Yup it does. That's why we build
servers that have multiple processors tons of RAM and fast hard drives. It's
almost always faster to process bulk data in this way--orders of magnitude
faster.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Marina said:
Like I said, the issue is the command timeout. So you need to set it on
the SqlCommand object. This is not related to IIS or anything like that, I
would expect the same behavior in a windows app.

So you should be able to retrieve 1 million records. But you probably want
to re-examine your requirement to get 1 million records into memory all at
once.

sam said:
I had set timeout at following :
- web.config
- ISS 6.0
- ASP.Net page

But it is seem un-retrievable for millions record....

I also notice that MS SQL Server 2000 (Standard Edition) is capable to
store over millions but unable to retrieval via stored procedure or
ADO.Net...

Could it possible that MS SQL Server 2000 (Standard Edition) have
constraint of retrieval millions record and only MS SQL Server 2000
(Enterprise Edition) could do so...

Please advise...

Marina said:
The default timeout for a command is 30 seconds. You need to set it to a
larger timeout, if you need more time to execute your query.

However, do you really need to retrieve 1 million records all at once?

When I use ASP.Net via ADO.Net to retrieve data from MS SQL Server
2000, it is always prompt error message "Timeout" if the number record
is larger than 1 million.

May I know what is the best practise for pool large records for my
case, please?

Or it is limitation on ADO.Net or ASP.Net or .Net framework 1.1?


ASP.Net Coding
-----------------
Server.ScriptTimeout = 9000

Dim constr_GL_3a_2 As String = "server='SQLSVR'; user id='sa';
password='sa'; Database='ERP_ARCHIVAL';Connect Timeout=1200"
Dim sqlcon_GL_3a_2 As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr_GL_3a_2)
Dim sqlcmd_GL_3a_2 As System.Data.SqlClient.SqlCommand = New
SqlCommand()
Dim sqlda_GL_3a_2 As New system.Data.SqlClient.sqldataAdapter()
Dim sqlprm1_GL_3a_2 As New system.Data.SqlClient.SqlParameter()
Dim sqlprm2_GL_3a_2 As New system.Data.SqlClient.SqlParameter()

Try
sqlcmd_GL_3a_2 = sqlcon_GL_3a_2.CreateCommand

sqlcmd_GL_3a_2.CommandText = "[General_Ledger]"

sqlcmd_GL_3a_2.CommandType = commandtype.StoredProcedure

sqlprm1_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateFrom",SqlDbType.Int,6)
sqlprm1_GL_3a_2.value = FlagFrDate

sqlprm2_GL_3a_2 =
sqlcmd_GL_3a_2.Parameters.Add("@DateTo",SqlDbType.Int,6)
sqlprm2_GL_3a_2.value = FlagToDate

sqlda_GL_3a_2.SelectCommand = sqlcmd_GL_3a_2
sqlcon_GL_3a_2.Open
sqlcmd_GL_3a_2.ExecuteNonQuery
Finally
sqlcon_GL_3a_2.Close()
sqlcon_GL_3a_2.Dispose()
End Try

constr_GL_3a_2 = Nothing
sqlcon_GL_3a_2 = Nothing
sqlcmd_GL_3a_2 = Nothing
sqlda_GL_3a_2 = Nothing
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running Stored Procedure by Parameters 4
dataadapter.fill doesnt update database 6
cannot connect sql server with sqlDataAdapter 1
Update Not Working 2
sqlAdapter caching data? 3
Get 1st record in ADO.Net 3
SqlCommand Disposing Connection Object 16
Sum Total in ADO.Net? 2

Top