September 12, 2005 at 6:08 pm
I have a stored procedure that was hanging indefinitely, but used to execute correctly. The proc takes 3 input parameters, @Month integer, @Year Integer, @Version @integer. These params are used in a select query similar to "Select field1, field2, ...etc from A_Typical_View where Mo = @Month and Yr = @Year and Ver = @Version. The proc is run once a month, and last month was fine. Now it is hanging (like locked, but NOT!). If, in the proc, I replace the params with hardwired constants, like Mo = 8 and yr = 2005 and version = 1, it runs fine. Finally I checked the definitions of the 3 fields Mo, Yr, and Ver, and found that in the base table behind the view, they are defined as Smallint. When I changed the declarations in the proc to declare the 3 params as Smallint, IT EXECUTES FINE. If I had declared the params as Varchar or Binary, etc. I would assume the query would simply not return any results, but why would it hang infinitely just because my input params were declared as Integer rather than Smallint? The ONLY clue I can think of is that the server (SQL2K) has a Non-US locale defined?
September 13, 2005 at 2:18 pm
If your application is using .NET the system may be seeing Smallint as SByte type and it is not CLS-compliant, you are asked to use int16 instead of SByte.
Value types are not classes in C# so check under Structs in the FCL(framework class library). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
September 14, 2005 at 2:09 pm
Thanks for input. This problem is happening directly within EM/Isql - here is a snippet from the proc:
ALTER PROCEDURE fact_Penetration
@Month integer,
@Year integer,
@Version integer
AS
create table #SampleCounts
(
Version integer,
Yr integer,
Mo integer,
Category varchar(80),
Region varchar(30),
District varchar(30),
DistrictSites integer
)
/* initialize table with data from view v_sampleunicounts */
insert into #SampleCounts
(Version, Yr, Mo, Category,Region,District,DistrictSites)
select Version, Yr, Mo, 'Resellers', Region, District, 0
FROM v_sampleunicounts
WHERE Version = @Version and Mo = @Month and Yr = @Year
(the rest of the proc just repeats similar to above a few more
times, then updates numeric DistrictSites column)
Using Profiler, I can see the process hang right at the Insert statement. The view v_sampleunicounts returns only about 4000 rows.
Replacing the line
"WHERE Version = @Version and Mo = @Month and Yr = @Year"
with
"WHERE Version = 1 and Mo = 8 and Yr = 2005"
eliminates the problem and executes in < 1s!
Even if I simplify the proc by eliminating the #temp table, and just perform the "select" statement, it still hangs infinitely.
The table behind the view v_sampleunicounts is a "plain vanilla" table, no default constraints, no triggers, etc. but the fields Mo,Yr & Version are defined as smallint. The view itself does nothing unusual - no subqueries, nesting, etc., just a simple 2 table join.
I have seen this similar problems a few times in the past, but never made the "connection" to the data typing as the culprit.
September 14, 2005 at 2:21 pm
do you mind to write: SET NOCOUNT ON at the begining of the procedure ?
* Noel
September 14, 2005 at 2:52 pm
What's the attributes of tempdb ?
data/log size and growth for each.
Maybe you'r getting hung on a tempdb growth issue ? To test this remove the temp table and create a permanent one for a quick test.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 14, 2005 at 3:28 pm
Try to comment everything in SP but this statement and run it.
Looks like you look at wrong point.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply