[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!!

[Doogle]

I'm not well up on MSSQL but is there an '@' missing before 'ReturnValue' here:

select 0 as ReturnValue -- failure

[techgnome]

More specifically, it should look like this:

SET @ReturnValue = 0
Return
-- or
SELECT @ReturnValue = 0
Return

-tg

[szlamany]

Why do you have this

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

Shouldn't that be:

CREATE PROCEDURE [dbo].[SP_ExportData] 

    @ReturnValue int  OUTPUT

as

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

[techgnome]

Depends... the first way allows the other items to be passed, or set with default values if no values are specified... but at the same time... the OP isn't creating the parameters in code... which I think you still need to do... even if you don't set their values.

-tg

[manofsteel00]

Thanks for the great replies! I did not code the MSSQL statements, but I can tell you that techgnome's most recent statements concerning it are correct. The code is meant to be used dynamically.

My biggest concern was the VB6 code. I will make the proposed changes, and let you know if I have any issues.

Thanks again!

[manofsteel00]

Depends... the first way allows the other items to be passed, or set with default values if no values are specified... but at the same time... the OP isn't creating the parameters in code... which I think you still need to do... even if you don't set their values.

-tg

Can you tell me what you mean by "the OP isn't creating the parameters in code"? Does OP mean Output Parameter?

The error message that I am getting is "Formal parameter '@sql' was defined as OUTPUT but the actual parameter not declared OUTPUT". I do not understand why it keeps referencing @sql.

Any help would be great! Thanks again!

[szlamany]

You are the OP - the ORIGINAL POSTER.

Why do you have 3 parameters in the SPROC but you are only creating one on the VB side?

You either make the VB have three parameters or you move those parameters down to inside the SPROC.

They should match.

[szlamany]

Show the VB6 code with the 3 parameters please. Actually whenever you post that you changed something and you are still getting an error - please post the code.

[manofsteel00]

Well, again, I do not know MSSQL syntax very well, but when I do not pass a parameter, then I still receive the same error.

[manofsteel00]

Having updated the VB code to include the other parameters, I am still receiving the same error message at cmd.Execute.

Below is the update VB code with the additional parameters being passed:

Sub export()
    Dim res As Integer
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_ExportData2"
    cmd.Parameters.Append cmd.CreateParameter("@sql", adVarChar, adParamOutput, 1000)
    cmd.Parameters.Append cmd.CreateParameter("@fullFileName", adVarChar, adParamOutput, 1000)
    cmd.Parameters.Append cmd.CreateParameter("@ReturnValue", adInteger, adParamOutput)
    cmd.Execute
    res = cmd("@ReturnValue")
    If (res = 1) Then
        MsgBox "Exported Successfully"
    End If
    Set cmd.ActiveConnection = Nothing
End Sub

And below is the corresponding MSSQL code:

CREATE PROCEDURE [dbo].[SP_ExportData2] 

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

as

BEGIN

Could the problem be with the adVarChar?

[szlamany]

You have adParamOutputfor all three parameters in the VB.

But the SQL only has the third parameter as OUTPUT.

This needs to match - fix the VB side and remove the adParamOutput

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[manofsteel00]

This works correctly now. Below are the code fragments.

Since I wanted to pass a parameter to the stored procedure in MSSQL for verification purposes, and since the stored procedure was set up to accept three parameters, I had to send all three. The first two were only input parameters, and the last one was the output parameter that I test on. Hopefully, this will help those that experience similar problems.

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\Production\90_To_Be_Deleted\WIP_Report\WipReport.csv',
    @ReturnValue int = 1 OUTPUT

as



BEGIN

Sub export()
    Dim res, i As Integer
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_ExportData"
    cmd.Parameters.Append cmd.CreateParameter("@sql", adVarChar, adParamInput, 5000)
    cmd.Parameters.Append cmd.CreateParameter("@fullFileName", adVarChar, adParamInput, 1000)
    cmd.Parameters.Append cmd.CreateParameter("@ReturnValue", adInteger, adParamOutput)
    cmd.Execute
    res = cmd("@ReturnValue")
    If (res = 1) Then
        i = MsgBox("The CSV file is available at the following folder location: " & vbCrLf & vbCrLf & "\\na.ina.com\fortmill\DATA\NZ-IBC-Z\Projects\02_BAM\Production\90_To_Be_Deleted\WIP_Report\", vbInformation, "Process Complete")
    Else
        i = MsgBox("The CSV file was NOT created successfully.  Contact the P & I department.", vbCritical, "Process Failed")
    End If
    Set cmd.ActiveConnection = Nothing
End Sub

Thanks for everyone who contributed! It was greatly appreciated!