[dba-SQLServer]Sproc not returning records

The error handling specified will not work correctly if the actual error
occurs.  Also, leave SET NOCOUNT ON in the main procedure and add the
same statement to the procedures that are being called within the loop.
Actually this behavior is typical of a connection object not accounting
for the presence of action queries while being used for data retrieval.
It is a trivial task for a front-end dev. Tool like VB or C#, but
becomes a challenge for something like Access due to the nature of the
underlying architecture of the Jet engine.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
McAfee
Sent: Tuesday, January 20, 2004 2:54 PM
To: 'dba-sqlserver at databaseadvisors.com'
Cc: 'David Emerson'
Subject: RE: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com