David Emerson
davide at dalyn.co.nz
Tue Jan 20 15:16:42 CST 2004
Tried all permutations of the suggestions including your one below. Still no luck. It seems strange that the number of records is correct, just the number of columns and column headings are wrong. David At 20/01/2004, you wrote: >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