How to fix error : Multiple-step OLE DB operation generated errors. Check each OLE DB status value

1
votes

I am passing variable value as input parameter to a script in execute sql task. Its a string variable in package having value C:/Archive

The input parameter data type is varchar in parameter mapping in execute sql task.

script in execute sql task is 

Declare @body1 nvarchar(max), @b nvarchar(max)

set @body1 = 'The softdollar error files are located at '
set @b =  @body1 + ?

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='PWO Notification',
@recipients='[email protected]',
@subject= 'Soft Dollar Package',
@body= @b

Package throws error :

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

2
ssis

2 Answers

1
votes

If you change set @b = @body1 + ? to set @b = @body1 + N'?' does it work?

My assumption is that the statement is not concatenating as one might expect. Specifically, had you done declare @c nvarchar(max); SET @c =?; SET @b = @body1 + @c; I suspect things would be escaped and substituted in the correct, escaped, manner.

If not, report back and I'll delete this answer.

0
votes

I fixed it by creating a new variable and having the script as an expression in that variable. called the variable in Execute sql task. Put the variable from parameter mapping in script as @[user::variable] where question mark is.