David Emerson
davide at dalyn.co.nz
Tue Jan 20 14:00:05 CST 2004
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