October 16, 2018 at 6:53 pm
Hi all,
I am attempting to use a stored procedure to delete duplicate records, and return the number of records deleted.
the Stored Procedure looks like this:USE [MY_DB]
GO
/****** Object: StoredProcedure [dbo].[SP_DeleteDuplicatesFromWorkflow] Script Date: 10/17/2018 11:16:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_DeleteDuplicatesFromWorkflow]
-- Add the parameters for the stored procedure here
@intResult INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON; <-Turned off
/* Procedure
Use Partition By to record the instances of duplicates
This procedure is run from ExecuteDeleteStoredProcedures() in Mod_SQL in the Access Frontend
*/
WITH TempWorkflow (DuplicateCount,[Due Date], [DMSClient], [DMSEmailSummary], ClientID, ActivityID, [WorkItemType], [WorkItemStatus])
AS
(
SELECT ROW_NUMBER() OVER(PARTITION by [Due Date], [DMSClient], [DMSEmailSummary] ORDER BY [Due Date]), [Due Date], [DMSClient], [DMSEmailSummary], ClientID, ActivityID, [WorkItemType], [WorkItemStatus]
AS DuplicateCount
FROM dbo.Tbl_Workflow_New
WHERE (WorkItemType='Covenant')
)
--Select * from TempWorkflow
--WHERE DuplicateCount > 1
--Delete Duplicate Records
DELETE FROM TempWorkflow
WHERE DuplicateCount > 1
--<-Something not right here.
SET @intResult = @@ROWCOUNT
END
GO
My VBA Code looks like this
Sub ExecuteStoredProcedure(ByVal strStoredProcedureName As Variant)
Dim ADOConn As ADODB.Connection
Dim ADORS As ADODB.Recordset
Dim ADOCmd As ADODB.Command
Dim intResult As Integer
'Set the Connection object
Set ADOConn = New ADODB.Connection
'Open the connection to the applicable Db
ADOConn.Open strProductionConnection
'Set the command Object
Set ADOCmd = New ADODB.Command
'Assign the connection to the applicable Procedure Name
ADOCmd.ActiveConnection = ADOConn
ADOCmd.CommandText = strStoredProcedureName
ADOCmd.CommandType = adCmdStoredProc
ADOCmd.Parameters.Append ADOCmd.CreateParameter("intResult", adInteger, adParamInput, 6, intResult)
'Execute the command and supply the parameters to the recordset
'On Error Resume Next ' becuase the SP does not house the parameter
Set ADORS = ADOCmd.Execute
intResult = ADOCmd.Parameters("@intResult") '<--I thought this should return it, but nope.
On Error GoTo 0
Set ADORS = Nothing
Set ADOConn = Nothing
Set ADOCmd = Nothing
End Sub
Can someone please tell me where I'm going wrong. It seems the SP is not even returning a value of any kind.
October 16, 2018 at 10:10 pm
barry.nielson - Tuesday, October 16, 2018 6:53 PM
ADOCmd.Parameters.Append ADOCmd.CreateParameter("intResult", adInteger, adParamInput, 6, intResult)Can someone please tell me where I'm going wrong. It seems the SP is not even returning a value of any kind.
NOTE: I haven't touched any code outside the DB in 15 years, so I may be off the mark.
That said, it appears that the issue is that you are telling ADO that your parameter is an input parameter.
You need to change the direction to adParamInputOutput or adParamOutput
October 16, 2018 at 10:21 pm
Thank you for your response.
I had thought something like this, but am getting the error 'Invalid attribute/option identifier'
Wait a second! Stop everything! Hold the phone! The input name was wrong - D'oh!!!
Works! thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply