Francisco H Tapia
my.lists at verizon.net
Tue Jan 20 03:36:09 CST 2004
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 >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com > > > Regards > > David Emerson