SQL Connection timeout while trying to fill a dataset

[Robert Brown]

Hi All..

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.

I have seen a lot of posts mentioning to set the commandtimeout
property, but how do you do that on an SQLAdapter? I setting a
connection timeout on the SQL Connector, but that didn't help.

Here is my code.. Can anyone help???? BTW: I am returning about 1
million records (hope the dataset can handle it...

Dim SQLLink As String =
"server=(local);Trusted_Connection=Yes;Database=FTO;Connection
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(SQLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems.Ordernum = tbl_OrderLog.FOrdernum "
sSql += "where tbl_OrderLog.StartTD <= '" & DateRange1 & "' order by
tbl_orderlog.Ordernum"

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.Fill(dsOrderItems, "t1")


Thanks,
Robert

 

[Paul Hatcher]

Robert

You can explicitly create and assign the SelectCommand to the DataAdapter
rather than using SQL in the constructor, or this should work...

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.SelectCommand.CommandTimeout = 120
daSQL.Fill(dsOrderItems, "t1")

though I haven't tested it

Regards

Paul

 

[Cor]

Hi Robert,

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.

\\\watch typos not checked
dim cmd as new sqlcommand(sSql, SQLLink)
cmd.CommandTimeout = 60
daSQL = New SqlDataAdapter(cmd)

daSQL.Fill(dsOrderItems, "t1")

///
I hope this helps,

Cor

 

[Samuel Hon]

Hi All..

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.

I have seen a lot of posts mentioning to set the commandtimeout
property, but how do you do that on an SQLAdapter? I setting a
connection timeout on the SQL Connector, but that didn't help.

Here is my code.. Can anyone help???? BTW: I am returning about 1
million records (hope the dataset can handle it...

Dim SQLLink As String =
"server=(local);Trusted_Connection=Yes;Database=FTO;Connection
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(SQLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems.Ordernum = tbl_OrderLog.FOrdernum "
sSql += "where tbl_OrderLog.StartTD <= '" & DateRange1 & "' order by
tbl_orderlog.Ordernum"

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.Fill(dsOrderItems, "t1")


Thanks,
Robert

Hey there Robert

Dare I ask why you are retrieving so many records? There is probably
another way to acheive your goal

Sam

 

[Robert Brown]

Thanks Paul.. It worked perfectly..

Thanks to the others that replied as well..

Robert

 

[Robert Brown]

Hi Samuel.

I wish there is a better way. What I am doing is archiving at the end
of 3 months records from one database to an identical database, but
keeping what is in the second database as ther archival records. This
is all part of a VB.NET program written for a client.

If someone can suggest an easier way, then I would love to hear about
it..

Robert

 

[Samuel Hon]

Hi Samuel.

I wish there is a better way. What I am doing is archiving at the end
of 3 months records from one database to an identical database, but
keeping what is in the second database as ther archival records. This
is all part of a VB.NET program written for a client.

If someone can suggest an easier way, then I would love to hear about
it..

Robert

If its just a data copy process in SQL server, then I think there is
no need to use VB to do anything. I'm no DBA so you might want to post
in here:
comp.databases.ms-sqlserver

Good luck

Sam