Output-parameter via VBA cmd.Parameters always returns null

hi,
still trouble with StoredProcs and VBA, using SQLServer 2012 and Access2010

First my StoredProc:

/****** Object:  StoredProcedure [dbo].[SP_CLEAR]    Script Date: 30.09.2016 00:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
--
--
-- =============================================
CREATE PROCEDURE [dbo].[SP_CLEAR] 
	-- Add the parameters for the stored procedure here
	@ParPRINTER_NAME					AS nvarchar(100)	= NULL
	, @ParAppEnv						AS integer 				= 0
	, @ParOUT_NumberRowsDELETED			AS integer OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

	-- !!!!!!!!!!!!!   !!!!!!!!!!!!!   !!!!!!!!!!!!!   !!!!!!!!!!!!!
	-- IMPORTANT for CRUD-operations to return the number of affected rows 
	SET NOCOUNT OFF;
	-- !!!!!!!!!!!!!   !!!!!!!!!!!!!   !!!!!!!!!!!!!   !!!!!!!!!!!!!

	-- 1. DECLARATIONS
	DECLARE @RowsDeleted				AS int				= 0
	-- ...

		-- 2. do some CURSOR handling
		OPEN DS_cursor;
		-- ...
		-- ...
		SET @RowsDeleted = @RowsDeleted + 1
		-- ...

		-- FINIS
		CLOSE DS_cursor;
		DEALLOCATE DS_cursor;

	SET @ParOUT_NumberRowsDELETED = @RowsDeleted
	SELECT @ParOUT_NumberRowsDELETED AS MyParOut

END

Open in new window


and this in VBA to read the output-parameter. The code is adapted from Access 2000 Developer's Handbook Vol.2
Public Function fctCallSP_CLEAR(ByVal INstrPRTNameLang As String) As Integer
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Dim lngReturnRowsAffected As Long

   On Error GoTo fctCallSP_CLEAR_Error

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    Call sSQLServerVerbinden(cnn, 3, gstrDatenbank)
    
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "SP_CLEAR"
    cmd.CommandType = adCmdStoredProc
	
    Set prm = cmd.CreateParameter("PRINTER_NAME", adVarWChar, adParamInput, 100, INstrPRTNameLang)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("APPENV", adInteger, adParamInput, , glngAPP_ENVIRONMENT)
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("NumberRowsDELETED", adInteger, adParamOutput)
    cmd.Parameters.Append prm	
	
    cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords
	
	fctCallSP_CLEAR = Nz(cmd.Parameters("NumberRowsDELETED"), 0)

ExitHere:
	On Error Resume Next
		
		'-- FINIS
		Set prm = Nothing
		Set cmd = Nothing
		
		If cnn.State = adStateOpen Then
			cnn.Close
			Set cnn = Nothing
		End If
    Exit Function

fctCallSP_CLEAR_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fctCallSP_CLEAR_Error of Klassenmodul clsPSIpenta_Tables_CRUD"

End Function

Open in new window


and yes, I tried to name the parameter the same as in the StoredProc (tried with and without @).
The output parameter always returns null and I don't know why. [w00t]

When I create a similar but very simple StoredProc I can read the output parameter correctly.
What drives me crazy if I debug the StoredProc in SSMS all is fine
Any ideas?
candideSoftware developerAsked:
Who is Participating?
 
candideConnect With a Mentor Software developerAuthor Commented:
hi folks,
solution found: permissions / db access was lost because of a linked server problem which the db admin fixed. Now all is fine :-)
0
 
Rgonzo1971Commented:
Hi,

maybe
Set prm = cmd.CreateParameter("ParOUT_NumberRowsDELETED", adInteger, adParamOutput)
cmd.Parameters.Append prm	
	
cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords
	
fctCallSP_CLEAR = Nz(cmd.Parameters("ParOUT_NumberRowsDELETED"), 0)

Open in new window


or

[code]Set prm = cmd.CreateParameter("RowsDELETED", adInteger, adParamOutput)
cmd.Parameters.Append prm	
	
cmd.Execute RecordsAffected:=lngReturnRowsAffected, Options:=adExecuteNoRecords
	
fctCallSP_CLEAR = Nz(cmd.Parameters("RowsDELETED"), 0)

Open in new window

[/code]Regards
0
 
candideSoftware developerAuthor Commented:
hi rgonzo1971,
as I wrote I've also tried to rename the parameters in the CreateParameter method as you wrote, no success.
Here a screenshot of the vba-debug window:
vba debug window after cmd.ExecuteAs you can see here the first two parameters (input) can be read, the third parameter (item3; output) always returns empty. btw lngReturnRowsAffected has the value -1.
0
 
candideSoftware developerAuthor Commented:
hi folks,
what I now found out is that the above StoredProc SP_CLEAR is calling another StoredProc2. The Try-Catch block is not listed in my SP-code example above for simplicity.

When testing StoredProc2 it returns an
ERROR_NUMBER(): 7314    :-(
which means:


"The OLE DB provider "%ls" for linked server "%ls" does not contain the table "%ls". The table either does not exist or the current user does not have permissions on that table."

Someone has changed my permissions on tables - ouch! I first have to get these permissions which were existing some weeks ago...
Please wait until thursday when I get a call with the db-admin
thx
0
 
candideSoftware developerAuthor Commented:
the issue was not the code it was a permission problem on the SQL Server linked server which could not be resolved by any code
0
All Courses

From novice to tech pro — start learning today.