[RESOLVED] Formal parameter was defined as OUTPUT but the actual parameter not declared OUTPUT.

[manofsteel00]

Hello,

I am trying to call a stored procedure in MSSQL from VB6; however, I am getting an error message: "Formal parameter was defined as OUTPUT but the actual parameter not declared OUTPUT."

I have looked at examples online and within these threads, but I cannot see what is incorrect in the code. I am hoping someone can help me out?


Below is the code in MSSQL for the Stored Procedure:

CREATE PROCEDURE [dbo].[SP_ExportData] 

    @sql varchar(5000) = 'Select * from vw_WIP_Report',        
    @fullFileName varchar(1000) = '\\na.ina.com\fortmill\DATA\NZ-IBC-Z\Projects\02_BAM\Eng_Dev\90_To_Be_Deleted\WIP_Report\WipReport.csv',
    @ReturnValue int = 1 OUTPUT

as

BEGIN
   	if @sql = '' or @fullFileName = ''
	begin
		select 0 as ReturnValue -- failure
		return
	end 


	if object_id('##TempExportData') is not null
		drop table #TempExportData
	if object_id('##TempExportData2') is not null
		drop table #TempExportData2


	-- insert data into a global temp table
	declare @columnNames varchar(8000)
          , @columnNamesBrackets varchar(8000)
          , @columnNamesQuotes varchar(8000)
          , @columnConvert varchar(8000)
          , @tempSQL varchar(8000)
          ,@ReturnCode int
          ,@ErrorMessage varchar(1000)


	select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + 
		 substring(@sql, charindex('from', @sql)-1, len(@sql))
     --/* Debugging Statement    
    print @tempSQL
    --*/
  
	exec(@tempSQL)

    /* Debugging Statement    
    select * from ##TempExportData
    --*/

	if @@error > 0
	begin
		select @ReturnValue = 0  -- failure
		return
	end 


	-- build 2 lists
	-- 1. column names
	-- 2. columns converted to nvarchar
	SELECT  @columnNamesBrackets = COALESCE( @columnNamesBrackets  + ',', '') + '[' + column_name + ']',
			@columnNamesQuotes = COALESCE( @columnNamesQuotes  + ',', '') + '''' + column_name + '''',
			@columnConvert = COALESCE( @columnConvert  + ',', '') + ' convert(nvarchar(4000),' 
			+ ' [' + column_name + ']' + case when data_type in ('datetime', 'smalldatetime') then ',121'
								 when data_type in ('numeric', 'decimal') then ',128'
								 when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
								 when data_type in ('datetime', 'smalldatetime') then ',120'
								 else ''
							end + ') as ' + '''' + column_name + ''''
	FROM    tempdb.INFORMATION_SCHEMA.Columns
	WHERE    table_name = '##TempExportData'


    /* Debugging Statement    
    print @columnNames
     --*/

	-- execute select query to insert data and column names into new temp table
	SELECT    @sql = 'select ' + @columnNamesBrackets + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]        
              from ##TempExportData union all select ' + replace(@columnNamesQuotes, ', ', ', ') + ', ''1'') t order by [temp##SortID]'

    /* Debugging Statement    
    print @sql
    --*/


	exec (@sql)


    --/* Debugging Statement    
    Select * from ##TempExportData2
    --*/

	-- build full BCP query
	select    @sql = 'bcp "select * from ##TempExportData2" queryout "' + @fullFileName + '" -T -c -t, '
    print @sql

    /* Debugging Statement
    select * from ##TempExportData2
    --*/
    
	--Create temp table to hold result
    CREATE TABLE #temp (SomeCol VARCHAR(500))
    CREATE TABLE #ErrorTable ( rownumber int Identity(1,1)
                             , ErrorMsg varchar(8000))
	-- execute BCP
	INSERT #temp
    Exec @ReturnCode = master..xp_cmdshell @sql

	IF @ReturnCode <> 0
	BEGIN
		SELECT @ErrorMessage = @ErrorMessage 
		FROM #temp

		--Display error message and return code
	  INSERT into #ErrorTable
      SELECT SomeCol from #temp where SomeCol like 'Error =%' 
	 
      Select * from #ErrorTable
	END

	if @@error > 0
	begin 
		select @ReturnValue = 0 -- failure
		return
	end

	drop table #temp
	drop table #ErrorTable
	drop table ##TempExportData
	drop table ##TempExportData2
	select  @ReturnValue = 1 -- success

END

GO

And below is the VB6 code:

Sub export()
    Dim res As Integer
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_ExportData"
    cmd.Parameters.Append cmd.CreateParameter("@ReturnValue", adInteger, adParamOutput)
    cmd.Execute ' This is where the error is thrown.
    res = cmd("@ReturnValue")
    If (res = 1) Then
        MsgBox "Exported Successfully"
    End If
    Set cmd.ActiveConnection = Nothing
End Sub

Any help I can get would be much appreciated! Thanks again!!