Passing parameters to a stored procedure with a sql task

Question

I'm trying to pass variables to the stored procedure i have set up, i have them mapped in the Parameter Mapping tab. I think i may have the syntax incorrectly but i cant find a solution.

    EXEC [dbo].[proc_GetQuarterlyEmailId] 
    @employeeNumber = ?, 
    @employeeFirstName = ?, 
    @employeeLastName = ?, 
    @employeeEmail = ?

The error i am getting is:

Executing the query "EXEC [dbo].[proc_GetQuarterlyEmailId] @employeeNu..." failed with the following error: "Could not find stored procedure ''.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If i remove the parameters from the stored procedure and remove the parameter mapping it runs perfectly fine, so i know it can find it. I am using a ADO.NET connection.

Žiga Prajs Žiga Prajs · Accepted Answer · 2017-01-30T14:13:15

Try to execute SP without parameters listed:

EXEC proc_GetQuarterlyEmailId ?, ?, ?, ?

I always use the syntax like this... and never have had problems...

And if procedure returns some rows when parameters are supplied, then you have to change the property "Reslut Set" to tomenthing else than "None"...

Mapping of parameters differs on which conection type you are using. If you are using OLE DB you have to use question marks for parameters, as I mentioned yesterday. And for mapping in "Parameter Name" column you have to use numbers 0, 1, 2, ... I'm attaching sample pics for "Result Set" = "None", as I have a SP that does not return eny resultset.

Check also this link: https://msdn.microsoft.com/en-us/library/ms140355(v=sql.105).aspx

Passing parameters to a stored procedure with a sql task

2 Answers