[dba-SQLServer]Sproc not returning records

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