stored procedure times out after 10 minutes

  • Thread starter Thread starter vb4me
  • Start date Start date
V

vb4me

Hello.
Can anyone help with this one?
I've written an app that queries data on two linked servers (sql
server 2000).
The stored procedure runs fine when I 'exec' it in management studio,
but it times out when launched from VB2005.

The funny thing is it worked last week - I'm just not sure what has
changed since then.

Here's (some of) my vb source

Dim Conn As New
System.Data.SqlClient.SqlConnection(My.Settings.ModelstockConnString)
Dim Cmd As New System.Data.SqlClient.SqlCommand
Dim sqlstr As String

Cmd.CommandTimeout = 600
Cmd.CommandType = Data.CommandType.Text
Conn.Open()
Cmd.Connection = Conn
Dim dr As System.Data.SqlClient.SqlDataReader = Nothing
Dim dtms As New System.Data.DataTable

sqlstr = "exec MWLBringToModelstockQuery '" &
DataGridView1.Rows(0).Cells("cCustNo").ToString.Trim & "'"
Cmd.CommandText = sqlstr
dtms.Load(Cmd.ExecuteReader)


Here's my connection string
Data Source=TESTSERVER;Initial Catalog=TESTDB;Integrated Security=True

Here's my SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[MWLBringToModelstockQuery]
(
@custno nchar(10),
@itemno nchar(20) = '',
@storeno nchar(10) = ''
)
as
 
Here's the rest of the SP, my finger fumbled

BEGIN
set nocount on

select rtrim(custno) as custno, rtrim(itemno) as itemno,
rtrim(storeno) as storeno, onhand, onorder, shipped, adj, sold
into #postemp
from [LINKEDSERVER\LINKEDINST].[thedata].dbo.thetable
where enddt = (select max(enddt) from [LINKEDSERVER\LINKEDINST].
[thedata].dbo.thetable where custno = @custno)
AND custno = @custno

if @itemno <> '' and @storeno = ''
begin
select a.storeno as cStoreNo, a.itemno as cItemNo, (b.maxship -
(a.onorder + a.onhand + (case when a.adj <= a.shipped then a.shipped
else a.adj end))) as Qty, ' ' as Stat, ' ' as PONo, a.custno as
cCustNo
from #postemp a, [TESTSERVER].[TESTDB].[dbo].[testtable] b
where a.custno = b.custno and a.itemno = b.itemno and a.storeno =
b.storeno
-- only include specified custno & itemno
and a.custno = @custno
-- only include specifed itemno
and a.itemno = @itemno
-- only include specified storeno
-- and a.storeno = @storeno
-- only include items on POG
and b.maxship > 0
-- include items being reported by MIC in their 852
and (a.onhand + a.onorder + a.sold) > 0
-- only include non-zero adjustments in the output file
and (b.maxship - (a.onorder + a.onhand + (case when a.adj <=
a.shipped then a.shipped else a.adj end))) <> 0
order by a.storeno, a.itemno
end
 
vb4Me,

Would it not be nice to show us a cliened up Sproc instead something that
you added with all kind of uncommenting. If you do that yourself and bring
it first down to a smaller sproc then you will see probably the error easier
then we do. We don't have your tables you know.

Cor
 
Back
Top