By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,980 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,980 IT Pros & Developers. It's quick & easy.

Timeout while executing stored procedure from VB.net

P: n/a
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("@prev2Be gin", prev2Begin)

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

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

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

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

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

runBoardPostings.Parameters.AddWithValue("@curStar t", 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!

Feb 21 '07 #1
Share this Question
Share on Google+
2 Replies


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

Robin S.
-------------------------
<dr****@nelsonwatson.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...
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("@prev2Be gin", prev2Begin)

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

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

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

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

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

runBoardPostings.Parameters.AddWithValue("@curStar t", 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!


Feb 21 '07 #2

P: n/a
<dr****@nelsonwatson.comschrieb
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

Feb 21 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.