Updating using Execute SQL Task

Question

Updating using Execute SQL Task

stricknyn

SSCertifiable

Points: 6560
More actions
December 8, 2008 at 2:35 pm

#383535

Hi all,
I have an variable in my package called ContractID. This Variable is set to 11258. I just want to update a contractID column in my execute SQL Task with whats in my ContractID variable. Seems like it should be simple but I'm having some issues.
In my execute SQL task, under the Parameter Mapping tab I have "variable name" set to my variable (ContractID) and the "Parameter Name" named ID.
On the General tab, I have the SQL Statement as:
UPDATE dbo.PROVIDER_PARTICIPATIONS
SET CONTRACT_ID = ?
FROM MyTable
WHERE "MYWHERE CLAUSE"
I've also tried replacing the ? with @ID (what I named it in the parameter mapping tab) and I get the same error message for both. That error message is:
"Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Can anyone see what I'm doing wrong?
Thanks,
Strick

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris-121516 SSC Enthusiast Points: 152 More actions · Answer 1

try renaming the parameter to a numeric. The first being 0 and subsequent ones increasing by 1 each time. Reference by ? in the SQL shoudl work fine

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Have you really got two tables in this update, dbo.PROVIDER_PARTICIPATIONS and Mytable?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Hi, yes I am only using the listed tables. I'm trying to figure out how to do update with a Execute SQL Task based on how I have my variables set.

Thanks,

Strick

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

stricknyn (12/9/2008)
Hi, yes I am only using the listed tables. I'm trying to figure out how to do update with a Execute SQL Task based on how I have my variables set.
Thanks,
Strick

What's the join condition between the two tables referenced in the UPDATE?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden