David Emerson
davide at dalyn.co.nz
Wed Jan 28 16:31:48 CST 2004
Finally solved problem by splitting up the sproc into two separate ones - the first executing the other sprocs, the second selecting the records from the table. Thanks for all the suggestions. David At 21/01/2004, you wrote: >I have checked all the related sprocs and all have SET NOCOUNT ON. Any >other suggestions? > >David > >At 20/01/2004, you wrote: >>The error handling specified will not work correctly if the actual error >>occurs. Also, leave SET NOCOUNT ON in the main procedure and add the >>same statement to the procedures that are being called within the loop. >>Actually this behavior is typical of a connection object not accounting >>for the presence of action queries while being used for data retrieval. >>It is a trivial task for a front-end dev. Tool like VB or C#, but >>becomes a challenge for something like Access due to the nature of the >>underlying architecture of the Jet engine. >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David >>McAfee >>Sent: Tuesday, January 20, 2004 2:54 PM >>To: 'dba-sqlserver at databaseadvisors.com' >>Cc: 'David Emerson' >>Subject: RE: [dba-SQLServer]Sproc not returning records >> >> >>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