Francisco H Tapia
my.lists at verizon.net
Wed Jan 28 16:59:51 CST 2004
glad you got it going... did you stick the sprocs into a 3rd sproc, or just calling each individually now? David Emerson wrote: > 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 >> > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco