January 8, 2010 at 2:43 pm
I can execute the following SQL statement at my database:
Declare @EnDate Date
Set @EnDate = '2005-01-02'
Update data.Table1
Set EndDate = @EnDate
Where ID = 1
However, when I try to change it into my Command Statement in OLE DB Command as follows:
Declare @EnDate Date
Set @EnDate = ?
Update data.Table1
Set EndDate = @EnDate
Where ID = ?
The 'Command Debugger' gives me the 'non-specific, permission, or other' error. There may be some key in the fact that I had to put a single quote around the Date value ('2005-01-02' works, but 2005-01-02 does not) in the original SQl query. I've attempted a few variations of CAST and CONVERT on the first ? to no avail.
Ideas?
January 8, 2010 at 2:55 pm
Why not lose the variable declaration and pass in the 2 parameters like normal?
Update data.Table1
Set EndDate = ?
Where ID = ?
January 8, 2010 at 3:07 pm
By the way, this would entail you getting your data value into the pipeline as a column in the dataset. You could easily do this using the Derived Column transformation.
Alternatively, you could write the UPDATE as a stored procedure, handle your date logic there, and pass in the other value as a parameter to the SP.
January 8, 2010 at 3:11 pm
John is right. Bring the @enddate as column in pipeline and use it on column mappings of OLEDB command and map it to parameter and use JOHN command in ur update SQL code.
January 8, 2010 at 11:13 pm
Gkhadka (1/8/2010)
... and use JOHN command in ur update SQL code.
Wow, did Mr Rowan get his own command? 😎
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2010 at 3:51 pm
Phil Parkin (1/8/2010)
Gkhadka (1/8/2010)
... and use JOHN command in ur update SQL code.Wow, did Mr Rowan get his own command? 😎
Whooo Hoooo!
January 10, 2010 at 3:54 pm
You know, that does give me an idea. I think I may have to create a user defined funtion in our DB and let some of our 'less informed' users know about it.
January 11, 2010 at 7:12 am
It was originally supposed to be part of a larger query, more like a If existing EndDate is older than the new EndDate then update. I didn't include that part of the code, and I was trying to simplify the problem before I posted. I ended up just adding another Data Flow object to compare the dates instead of using a query.
Now I must test it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply