I want to execute a stored procedure in an OLE DB source in a Data Flow Task.
The stored procedure has a parameter.
When I put in an SQL command as :
EXEC sp_readcustomers 1
(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.
The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :
EXEC sp_readcustomers ?
In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.
@.par_company_id has been declared as int, var_company_id as Int16
When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :
Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Preview : No value given for one or more required parameters (Microsoft SQL Native Client)
I have already installed SQL 2005 SP2 and VS2005 SP1.
I have tried everything I know, please help ?!?!
Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||I have tried that.
I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.
Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.
When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?
|||
Ronald Dirkx wrote:
I have tried that.
I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.
Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.
When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?
To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.
No comments:
Post a Comment