"Frank Dean" wrote:

>
> I'm accessing a SQL Server 2000 stored procedure with ADO and I get the
> error message:
>
> "Operation not allowed when the object is closed"
>
> I know to add "SET NOCOUNT ON" to the procedure (that usually solves the
> problem) but in this case it didn't.
>
> Any suggestions as to other things to look at?
>
> Thanks in advance,
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

"SQL novice" <bal***@gmail.com> wrote in message
news:1131690530.320032.72500@g47g2000cwa.googlegroups.com...
> And to Add  to it  ...
>
> SET NOCOUNT ON is not required for that purpose.
> It is to avoid showing the counts of records affected by the different
> SQLs that you have used in your stored procedure. By doing so any
> client application using the stored procedure will not get these
> unwanted mesages.
>

"Frank Dean" <fpd***@yahoo.com> wrote in message news:%23FWCvLt5FHA.3880@TK2MSFTNGP12.phx.gbl...
>
>
> Thanks for the information. It's helped me understand the NOCOUNT
> setting but I still can't solve the problem.
>
> Here is the stored procedure (it a long piece of code so I took out some
> of the detail):
>
> DROP Procedure z_fpd_VolunteerAssignment
> GO
> CREATE Procedure z_fpd_VolunteerAssignment
> @Committee varchar(1000),
> @StartDate datetime,
> @EndDate datetime,
> @RatingLow int,
> @RatingHigh int,
> @CommitteeReviewDate datetime
> AS
>
> SET NOCOUNT ON
>
> CREATE TABLE #Result (
> [CommitteeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ProspectName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ProspectSortName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ProspectAddressee] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProspectID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [IncludeProspectInSolicitationListing] [bit],
> [RecordID] [int] NULL,
> [Phone] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveCategory] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AskAmount] [numeric](30, 6) NULL ,
> [Rating] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionID] [int] NULL,
> [Notes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VolunteerID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Volunteer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VolunteerNameFriendly] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [VolunteerRecordID] [int] NULL,
> [RelationshipType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AllVolunteers] [varchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherVolunteers] [varchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AllSolicitors] [varchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherSolicitors] [varchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [SolicitorName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CountOfPrimarySolicitors] [int] NULL ,
> [CountOfActiveProposals] [int] NULL ,
> [PreferredAddress] [varchar] (1000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL,
> [GiftTotal] [money],
> [LastGiftID] [int],
> [LastGiftAmount] [money],
> [LastGiftDate] [datetime],
> [ReviewActionID] [int],
> [SelectedForCommitteeReview] [bit],
> [CommitteeReviewNote] [varchar] (500),
> [UniqueID] [int] IDENTITY(1,1),
> [DeleteRecord] [bit]
> ) ON [PRIMARY]
>
> Insert into #Result
> SELECT dbo.JOB.NAME AS CommitteeName,
> dbo.BuildDataEntryName(aProspect.CONSTIT_ID, 0) AS ProspectName,
>                      dbo.z_fpd_SortName(aProspect.CONSTIT_ID) AS
> ProspectSortName, dbo.z_fpd_Addressee(aProspect.CONSTIT_ID, 1, 'Report
> Format', '')
>                      AS ProspectAddressee,
> dbo.BuildDataEntryName(aProspect.CONSTIT_ID, 2) AS ProspectID, NULL AS
> IncludeProspectInSolicitationListing, aProspect.CONSTIT_ID AS RecordID,
> NULL AS Phone, NULL AS MoveCategory, NULL AS AskAmount, NULL AS Rating,
> NULL AS ActionID, '' AS Notes,
> dbo.BuildDataEntryName(aVolunteer.CONSTIT_ID, 2) AS VolunteerID,
> dbo.BuildDataEntryName(aVolunteer.CONSTIT_ID, 0) AS Volunteer,
> dbo.BuildDataEntryName(aVolunteer.CONSTIT_ID, 1) AS
> VolunteerNameFriendly, aVolunteer.Constit_ID as VolunteerRecordID,
> TABLEENTRIES_2.LONGDESCRIPTION AS RelationshipType,
>                      '' AS AllVolunteers, NULL AS OtherVolunteers, ''
> AS AllSolicitors, NULL AS OtherSolicitors, NULL AS SolicitorName, NULL
>                      AS CountOfPrimarySolicitors, NULL AS
> CountOfActiveProposals, NULL AS PreferredAddress, 0 AS GiftTotal, null
> as LastGiftID, null as LastGiftAmount, null as LastGiftDate, null as
> [ReviewActionID], null as SelectedForCommitteeReview, null as
> CommitteeReviewNote, 0 AS DeleteRecord
> FROM         dbo.TABLEENTRIES TABLEENTRIES_2 FULL OUTER JOIN
>                      dbo.CONSTIT_RELATIONSHIPS aProspect INNER JOIN
>                      dbo.RECORDS ON aProspect.CONSTIT_ID =
> dbo.RECORDS.ID FULL OUTER JOIN
>                      dbo.VOL_ASSIGNMENT INNER JOIN
>                      dbo.VOLUNTEER ON dbo.VOL_ASSIGNMENT.VOLUNTEER_ID =
> dbo.VOLUNTEER.ID INNER JOIN
>                      dbo.JOB ON dbo.VOL_ASSIGNMENT.JOB_ID = dbo.JOB.ID
> FULL OUTER JOIN
>                      dbo.CONSTIT_RELATIONSHIPS aVolunteer ON
> dbo.VOLUNTEER.RECORD_ID = aVolunteer.CONSTIT_ID ON
>                      aProspect.ID = aVolunteer.RECIPROCAL_ID ON
> TABLEENTRIES_2.TABLEENTRIESID = aVolunteer.RECIP_RELATION_CODE
> WHERE     (TABLEENTRIES_2.LONGDESCRIPTION = 'cc2001 Volunteer' OR
>                      TABLEENTRIES_2.LONGDESCRIPTION = 'cc2001
> Solicitor') AND (dbo.VOL_ASSIGNMENT.TO_DATE IS NULL OR
>                      dbo.VOL_ASSIGNMENT.TO_DATE >= GETDATE()) AND
> (dbo.RECORDS.DECEASED = 0) AND dbo.JOB.NAME = @Committee
>
> <lots of update statements>
>
> DECLARE @SQLText varchar(5000)
>
> Declare @ProspectID varchar(255)
> Declare @UniqueID int
> Declare @VolTemp varchar(500)
>
> Declare @LastProspectID varchar(255)
> Declare @LastUniqueID int
> Declare @LastVolTemp varchar(500)
>
> Declare cResultDupes Cursor for Select ProspectID, UniqueID, Volunteer
> from #Result Order By ProspectID, UniqueID
>
> <work with this cursor to identify duplicate records>
>
> Delete From #Result where DeleteRecord=1
>
> <Some update statements>
>
>
> Declare @RecID int
> Declare @CumTotal money
> Declare @GiftTotal money
>
> Declare cResult Cursor for Select RecordID from #Result
>
> <work with the cursor>
>
> <lots of update statements>
>
> select * from #Result
>
> GO
>
>
> Here is the VBCode:
>
> Dim DB As ADODB.Connection
> Dim CMD As ADODB.Command
> Dim RS As ADODB.Recordset
>
> Set DB = New ADODB.Connection
> Set CMD = New ADODB.Command
>
> DB.Open "PROVIDER='SQLOLEDB';DATA
> SOURCE='<SERVER>';DATABASE=<DBNAME>;Uid=<USERNAME>;PWD=<PASSWORD>;"
> DB.CursorLocation = adUseServer
>
>
> CMD.ActiveConnection = DB
> CMD.CommandType = adCmdStoredProc
> CMD.CommandText = "z_fpd_VolunteerAssignment"
> DB.CommandTimeout = 1000
>
> CMD.Parameters.Append CMD.CreateParameter("Committee", adVarChar,
> adParamInput, 1000, "Leadership Gifts Committee")
> CMD.Parameters.Append CMD.CreateParameter("StartDate", adDate,
> adParamInput, , #12/1/2001#)
> CMD.Parameters.Append CMD.CreateParameter("EndDate", adDate,
> adParamInput, , #12/1/2005#)
> CMD.Parameters.Append CMD.CreateParameter("RatingLow", adInteger,
> adParamInput, , 0)
> CMD.Parameters.Append CMD.CreateParameter("RatingHigh", adInteger,
> adParamInput, , 0)
> CMD.Parameters.Append CMD.CreateParameter("CommitteeReviewDate", adDate,
> adParamInput, , Null)
>
> Set RS = CMD.Execute
>
> Debug.Print RS.RecordCount
>
> When run I get the "Operation not allowed when the object is closed"
> error.
>
> Any suggestions would be helpful.
>
> Thanks,
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***