Topic
1 reply Latest Post - ‏2006-05-08T18:25:34Z by SystemAdmin
SystemAdmin
SystemAdmin
1215 Posts
ACCEPTED ANSWER

Pinned topic SQL Stored Procedure not returning result set

‏2006-05-08T17:40:31Z |
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so:
SELECT region_code,
OperGroup_Code,
country_name,
MajorGroup_Name,
RptgEntity_Name,
shell_code,
CRC,
' ' as right_type
INTO #tmpTShell FROM TShell WHERE 1=0

But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.

Has anyone ever experienced anthying like this? Any help would be greatly appreciated!

Thanks.
Updated on 2006-05-08T18:25:34Z at 2006-05-08T18:25:34Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1215 Posts
    ACCEPTED ANSWER

    Re: SQL Stored Procedure not returning result set

    ‏2006-05-08T18:25:34Z  in response to SystemAdmin
    figured it out... need to SET NOCOUNT ON in my stored procedure.