Stored Procedure runs in SQL Server Management Studio but not in VB

28 Apr 2006 7:37 PM

Crazy Cat

I'm calling several SQL Server 2005 stored procedures from Visual Basic
2005. I've got one stored procedure that locks up at the same point
everytime, but if I copy the stored procedure from the SQL Profiler
trace and paste it into SQL Server Management Studio it runs fine -- I
even tried setting the timeout in Visual Studio to unlimited to see if
the procedure would ever complete and it doesn't. What the hell is
going on? Here is the portion of the stored procedure where it appears
to lock up --

insert @temp(MSC_KEY, [Calculated T1s])
        select    MSC_KEY,
                sum([Calculated T1s]) as [Calculated T1s]
        from dbo.port_stats
        where process_id LIKE @process_id
        group by MSC_KEY

@temp is a temporary table, and the table that is being aggregated over
has about 330 rows.

This procedure has worked fine up until today and I can't recall what
changed about it since it ran fine. Please help.

29 Apr 2006 8:06 AM

Cor Ligthert [MVP]

Crazy Cat,

If this procedure has forever run until today, than you would have to ask
your DBADMIN in my idea if he has done some changes. The last wonder was
when there a star was falling in Bethlehem.

Cor

Show quoteHide quote

"Crazy Cat" <danbr***@hotmail.com> schreef in bericht
news:1146253040.334975.298100@u72g2000cwu.googlegroups.com...
> I'm calling several SQL Server 2005 stored procedures from Visual Basic
> 2005. I've got one stored procedure that locks up at the same point
> everytime, but if I copy the stored procedure from the SQL Profiler
> trace and paste it into SQL Server Management Studio it runs fine -- I
> even tried setting the timeout in Visual Studio to unlimited to see if
> the procedure would ever complete and it doesn't. What the hell is
> going on? Here is the portion of the stored procedure where it appears
> to lock up --
>
> insert @temp(MSC_KEY, [Calculated T1s])
> select MSC_KEY,
> sum([Calculated T1s]) as [Calculated T1s]
> from dbo.port_stats
> where process_id LIKE @process_id
> group by MSC_KEY
>
> @temp is a temporary table, and the table that is being aggregated over
> has about 330 rows.
>
>
> This procedure has worked fine up until today and I can't recall what
> changed about it since it ran fine. Please help.
>