[dba-SQLServer]Sproc not returning records

David, did you try both my and Francisco's advise together?
I believe you need the SET NOCOUNT OFF after your notes, 
but following your code it also looks like you end with either 
a choice of returning an error or a 0 (if no error). 

Try commenting out that whole block as such:

/*
If @@Error <> 0
     Begin
          Select 'Unexpected error occurred: ', @@Error
          Return @@Error
     End
else
    return 0
*/


David
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of David
Emerson
Sent: Tuesday, January 20, 2004 12:00 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Sproc not returning records


Still no joy.

David

At 20/01/2004, Francisco wrote:
>just a hunch but try remming these out...
> >>          else
> >>                  return 0
>--
>-Francisco
>
>David Emerson wrote:
>
>>Thanks for the response.  Tried it and made no difference.
>>David
>>At 19/01/2004, you wrote:
>>
>>>put
>>>
>>>SET NOCOUNT OFF
>>>
>>>  before your final select (after your note) which will return the data 
>>> to Access.
>>>
>>>HTH
>>>David McAfee
>>>-----Original Message-----
>>>From: dba-sqlserver-bounces at databaseadvisors.com
>>>[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of David
>>>Emerson
>>>Sent: Monday, January 19, 2004 5:43 PM
>>>To: dba-SQLServer at databaseadvisors.com
>>>Subject: [dba-SQLServer]Sproc not returning records
>>>
>>>AXP, SQL2000
>>>
>>>I have a sproc that calls a number of other sprocs to fill in a
>>>table.  Then it uses this table to retrieve records.  If I run it (by
>>>pressing the ! button in Access) then the table is filled in, but the grid
>>>that shows the retrieved records has two column headings Expr<1> and
>>>Expr<2> (three are expected with proper names).  There are the correct
>>>number of records but all the rows are blank.
>>>
>>>If however I run the code to fill in the table first.  Then when that has
>>>stopped run the code for retrieving the records I get a grid showing the
>>>correct column names and data.
>>>
>>>My next attempt was to have a second sproc that executes the first one
>>>(without the final select statement), then runs the select statement.
>>>This
>>>had the same effect as if it was one sproc.
>>>
>>>Does anyone have an idea why this is happening?  The code is below.
>>>
>>>Regards
>>>
>>>David Emerson
>>>Dalyn Software Ltd
>>>25 Cunliffe St, Churton Park
>>>Wellington, New Zealand
>>>Ph/Fax (877) 456-1205
>>>
>>>
>>>ALTER PROCEDURE spSalesTransactLoadSummary
>>>          (
>>>                  @dtmDate1 varchar(20),
>>>                  @intDate2 int
>>>          )
>>>AS
>>>          SET NOCOUNT ON
>>>
>>>          DELETE FROM dbo.ttmpLoadAllocateRpt
>>>
>>>          DECLARE @intyyyymm int, @charDate varchar(10)
>>>
>>>          WHILE (YEAR(@dtmDate1) * 100 + MONTH(@dtmDate1)) <= @intDate2
>>>          BEGIN
>>>                  SET @intyyyymm = YEAR(@dtmDate1) * 100 + MONTH(@dtmDate1)
>>>                  SET @charDate = CONVERT(nvarchar(20), DATEPART(mm,
>>>@dtmDate1)) + '/' +
>>>                          CONVERT(nvarchar(20), DATEPART(dd, @dtmDate1)) +
>>>'/' +
>>>                          CONVERT(nvarchar(20),DATEPART(yy, @dtmDate1))
>>>                          EXEC dbo.spLoadAllocat4 @intyyyymm
>>>                  EXEC dbo.spLoadAllocat6  @intyyyymm, @charDate
>>>                          EXEC dbo.spLoadAllocatOther @intyyyymm
>>>                  SET @dtmDate1 = DATEADD(Month, 1, @dtmDate1)
>>>          END
>>>
>>>--Note - up to here works fine.  Next statement not work if run at same
>>>time as above.
>>>
>>>          SELECT tblNetOwners.NetOwnerName, ttmpLoadAllocateRpt.YrMth,
>>>                  SUM(ttmpLoadAllocateRpt.GJ) as GJSum
>>>          FROM tblNetOwners INNER JOIN (tblCustomers INNER JOIN
>>>(tblGateStations INNER JOIN
>>>                  ttmpLoadAllocateRpt ON tblGateStations.GateID =
>>>ttmpLoadAllocateRpt.GateIDNo)
>>>                  ON tblCustomers.CustomerID =
>>>ttmpLoadAllocateRpt.CustomerID) ON
>>>                  tblNetOwners.NetOwnerID = tblGateStations.NetOwnIDNo
>>>          GROUP BY tblNetOwners.NetOwnerName, ttmpLoadAllocateRpt.YrMth
>>>          ORDER BY tblNetOwners.NetOwnerName, ttmpLoadAllocateRpt.YrMth
>>>
>>>          If @@Error <> 0
>>>                  Begin
>>>                          Select 'Unexpected error occurred: ', @@Error
>>>                          Return @@Error
>>>                  End
>>>          else
>>>                  return 0