Oledbcommand not updating funny quotes about internet dating
While designing a package last week with our consultant, Latha Chathri (twitter), I came across a need to call a stored procedure drom within a Data Flow Task.
I have never needed to do this before and while the call is simple there are a few quirks that I found and would like to share with you.
I've created a sample procedure with a single input parameter (@incoming) and a single output parameter (@outgoing).
While the component does know there are parameters, it makes no assumption as to how you are going to access or organize them, so it complains.
Instead, I recommend you build a staging table on your destination database server, and populate that table with your “update” data.
Then, in the control flow, after the data flow successfully executes, you can issue an Execute SQL Task to perform a batch update.
The way the architecture works with the Data Adapter is that you can make any changes you want to the filled Data Set, and it won't affect the database until you call the Update method.
When Update is called, the Data Adapter will attempt to execute each query (UPDATE, INSERT, DELETE) on every row of the Data Set that has been updated, inserted, and deleted.
The procedure used in this example is rather trivial and many real-world situations call for more complex procedures.Sometimes though, the developer of the package does not even understand which component is taking so long to process say, thousands of records in their data flow, so it is understandable why this component may proliferate in many shops.(Sometimes it is needed, of course, but you need to always ask the question – do I need the updates to happen RIGHT NOW?There are two ways in which you can call a stored procedure: 'Pass-by-Position' or 'Pass-by-Parameter'.'Pass-by-Parameter' is my favorite method of calling a stored procedure.You list out the parameter names and pass them values.
Ole Db Command("ALTER TABLE [Parent] ADD [" & Column Date & "] int", con) obj Cmd = New Ole Db.