David Emerson
davide at dalyn.co.nz
Mon Jan 19 20:59:05 CST 2004
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 >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com Regards David Emerson Dalyn Software Ltd 25 Cunliffe St, Churton Park Wellington, New Zealand Ph/Fax 0064 4 478-7456