Posted by Ben on 06/05/07 02:04

I timed the execution: 1 minute. Then it gives me the overflow error.

I double checked the code especially the declarations, and they seem to
okay. The "TotalItems" var in the calling method uses "long" as its data
type. The "@TotalInStock" OUTPUT var in the stored procedure is declared as
"int". They seem to be okay.

Is there an issue between the number of open connections to the database?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9945EF2273D83Yazorman@127.0.0.1...
> Ben (pillars4@sbcglobal.net) writes:
>> .Execute ' This is where it hangs up...
>>
>> TotalItems = .Parameters("@TotalInStock")
>> TotalCost = .Parameters("@TotalCost")
>>
>> End With
>>
>> Set com = Nothing
>>
>> and the store procedure is:
>>
>> CREATE PROCEDURE DBO.sp_Recalculate
>
> Do not use the sp_ prefix in your procedures. This prefix is reserved
> for system procedures, and SQL Server will first look for these in master.
> I don't think this explains why your process hangs, but I nevertheless
> wanted to point it out.
>
>> @ItemNumber nvarchar(50),
>> @TotalInStock int = 0,
>> @TotalCost money = 0
>> AS
>
> Judging from the code, the parameters @TotalInStock and @TotalCost
> should be declared as OUTPUT. Right now your procedure is only a no-op.
>
> Also, I can't see in you code that you create these parameters when you
> call the procedure. You need to do that; you cannot just refer the
> parameters after the call.
>
>> BEGIN
>> SET @TotalInStock = (
>> SELECT Sum([Quantity in Stock])
>> FROM [Inventory Products]
>> WHERE [Item Number] = @ItemNumber)
>>
>> SET @TotalCost = (
>> SELECT Sum([Cost] * [Quantity in Stock])
>> FROM [Inventory Products]
>> WHERE [Item Number] = @ItemNumber)
>>
>> END
>
> Rewrite as
>
> SELECT @TotalInStock = Sum([Quantity in Stock]),
> @TotalCost = Sum([Cost] * [Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @ItemNumber
>
> That will slash the execution time in half.
>
>> When the process goes to the ".Execute" line, it hangs up for a long time
>> then gives me an error message "Everflow". I have been trying to solve
>> this issue but do not have an idea for now of the cause.
>
> I guess you mean "Overflow"? That sounds like VB message to me, which
> would indicate that you are using the wrong data type for TotalItems.
> (Check that you did not mistakenly declare it as Integer.) Then again,
> it does not seem that you would get anything back from the procedure
> at all. But maybe that is the problem? You get some unintialised junk?
>
>> Below is my finding:
>> a. When I run the stored procedure in the SQL analyzer, it works just
>> fine.
>
> And it completes in how long time?
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]