Why does an Execute SQL Task runtime error occur when two parameters are executed?
I am using SQL Server Data Tools (SSDT) 2010. I am getting the following error while executing a SQL Execute 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 used are of the String and Int32 data types.
If I edit the Execute SQL Task so that it only uses one of these parameters, it works fine. Any of them works great. But trying to use both failures with the error above.
In manual mode, similar commands in SQL Server Management Studio work fine.
Does anyone know why?
source to share
Modify your query to remove the single quotes ( '
) around the first parameter placeholder. Change it from to just '?'
?
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', ?);
The cause of the problem:
When you enclose the first space holder in single quotes, it is literally treated as a string value. I am assuming that you have defined the parameters in the order below in the tab Parameter Mapping
:
-
String
, in other wordsVARCHAR
-
Long
data type parameter
Now for the first question mark to be treated as a literal string value. The execution of the SQL Task tries to pass a string value to the second question mark, which is actually an integer field. This crashes Execute SQL Task
as it cannot insert a string into an integer field.