David Emerson
davide at dalyn.co.nz
Wed Jan 28 17:09:23 CST 2004
I call each one individually from within my vba code. David At 28/01/2004, Francisco wrote: >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