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

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:

Code:

---

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:

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

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

Code:

---

select 0 as ReturnValue -- failure

---

More specifically, it should look like this:

Code:

---

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

---

-tg

Why do you have this

Code:

---

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:

Code:

---

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'

---

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

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!

Quote:

---

Originally Posted by 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

---

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!

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.

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.

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.

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:

Code:

---

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:

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?

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

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

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.

Code:

---

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

---

Code:

---

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!