David Emerson
davide at dalyn.co.nz
Tue Jan 20 16:03:19 CST 2004
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