Why does the Execute SQL Task fail while using two parameters?

Question

I am using SQL server Data Tools (SSDT) 2010. I am getting following error in an Execute SQL Task:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, 
if available. No work was done

The SQL Statement is set to:

INSERT INTO DerivedSoftware
           (Name, VendorID, RuleID, GroupID, SourceEnumID, DeviceID)
    VALUES ('?', '1', '1', '1', '1', ?)

The parameters being used are of data types String and Int32.

If I edit the Execute SQL Task so that it only uses one of these parameters, it works fine. Either one works fine. But attempting to use both fails with above error.

Manually running similar commands in SQL Server Management Studio works fine.

Anyone know why?

VALUES ('?', '1', '1', '1', '1', ?) On one ? you have single qoutes (') on one you don't. — grepsedawk
– grepsedawk, Commented Feb 18, 2013 at 18:23

score 3 · Accepted Answer · 2013-02-18 18:37:48Z

Change your query statement to remove the single quotes (') around the first parameter placeholder. Change it from '?' to simply ?

From:

INSERT INTO DerivedSoftware
(Name, VendorID, RuleID, GroupID, SourceEnumID, DeviceID)
VALUES ('?', '1', '1', '1', '1', ?);

To:

INSERT INTO DerivedSoftware
(Name, VendorID, RuleID, GroupID, SourceEnumID, DeviceID)
VALUES (?, '1', '1', '1', '1', ?);

Reason for the issue:

When you enclose the first parameter place holder within single quotes, it is literally treated as string value. I assume that you have defined parameters in the below mentioned order on the Parameter Mapping tab:

String data type, in other words VARCHAR
Long data type parameter

Now, that the first question mark is treated as literal string value. Execute SQL Task is trying to pass the string value to the second question mark, which is actually an integer field. This is causing the Execute SQL Task to fail because it is unable to insert a string into integer field.

Collectives™ on Stack Overflow

Why does the Execute SQL Task fail while using two parameters?

1 Answer 1

From:

To:

Reason for the issue:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Reason for the issue:

Comments

Your Answer

Sign up or log in

Post as a guest

Related