Timeout while executing stored procedure from VB.net

[drolfe]

See below the code for details about the scenario.
------------------------------------------------------------------------------------

Public WithEvents conADS As New SqlConnection("Data
Source=server;Initial Catalog=db;Persist Security Info=True;User
ID=username;Password=password;Connection Timeout=0;")

Public runBoardPostings As New SqlCommand("EXEC [dbo].
[nwinsight_all_teams_commission_report_summary] @PREV2_PERIOD_START =
@prev2Begin, @PREV2_PERIOD_END = @prev2End, @PREV2_CUTOFF_DATE =
@prev2Cut, @PREV_PERIOD_START = @prevBegin, @PREV_PERIOD_END =
@prevEnd, @PREV_CUTOFF_DATE = @prevCut, @CUR_PERIOD_START = @curStart,
@CUR_PERIOD_END = @curEnd, @CUR_CUTOFF_DATE = @curCut", conADS)

Public BoardPostingsDR As SqlDataReader

conADS.Open()
conReports.Open()


getReportInfo.Parameters.Clear()
getReportInfo.Parameters.AddWithValue("@thisReport",
reportID)
ReportInfoDR = getReportInfo.ExecuteReader
With ReportInfoDR
Try
.Read()
curBegin = .GetValue(0) : curEnd = .GetValue(1) :
curCut = .GetValue(2)
prevBegin = .GetValue(3) : prevEnd
= .GetValue(4) : prevCut = .GetValue(5)
prev2Begin = .GetValue(6) : prev2End
= .GetValue(7) : prev2Cut = .GetValue(8)
ReportInfoDR.Close()
runBoardPostings.Parameters.Clear()

runBoardPostings.Parameters.AddWithValue("@prev2Begin", prev2Begin)

runBoardPostings.Parameters.AddWithValue("@prev2End", prev2Begin)

runBoardPostings.Parameters.AddWithValue("@prev2Cut", prev2Begin)

runBoardPostings.Parameters.AddWithValue("@prevBegin", prevBegin)

runBoardPostings.Parameters.AddWithValue("@prevEnd", prevEnd)

runBoardPostings.Parameters.AddWithValue("@prevCut", prevCut)

runBoardPostings.Parameters.AddWithValue("@curStart", curBegin)

runBoardPostings.Parameters.AddWithValue("@curEnd", curEnd)

runBoardPostings.Parameters.AddWithValue("@curCut", curCut)
-----> BoardPostingsDR = runBoardPostings.ExecuteReader

GoTo step2
Catch ex As Exception
Exit Sub
End Try

The arrow marks the spot where the exception occurs.

The stored procedure takes about 5 minutes to run, it querys 15-20
tables and compiles the data into a single table for return. I am
trying to take this table and write it into another database on a
different server, along with some other values that have been pre-
determined. Would anybody be kind enough to help me out with this
dillemma?? I thank you in advance for your anticipated help!

 

[Armin Zingler]

See below the code for details about the scenario.
------------------------------------------------------------------------------------

Public WithEvents conADS As New SqlConnection("Data
Source=server;Initial Catalog=db;Persist Security Info=True;User
ID=username;Password=password;Connection Timeout=0;")

Public runBoardPostings As New SqlCommand("EXEC [dbo].
[nwinsight_all_teams_commission_report_summary] @PREV2_PERIOD_START
= @prev2Begin, @PREV2_PERIOD_END = @prev2End, @PREV2_CUTOFF_DATE =
@prev2Cut, @PREV_PERIOD_START = @prevBegin, @PREV_PERIOD_END =
@prevEnd, @PREV_CUTOFF_DATE = @prevCut, @CUR_PERIOD_START =
@curStart, @CUR_PERIOD_END = @curEnd, @CUR_CUTOFF_DATE = @curCut",
conADS)

runBoardPostings.CommandTimeout = <whatever>


Armin

 

[RobinS]

In addition to the timeout property on the connection, there is a timeout
property on the command object. Give that a try.

Robin S.