Posted by vassone on 10/05/16 11:26

Hi Simon,

Thanks for coming back to me.

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

If I remove the output variable I don't get the error?

Many thanks


"Simon Hayes" <sql@hayes.ch> wrote in message
news:43258bd2$1_3@news.bluewin.ch...
> vassone wrote:
>> Dear All, I read through all the post and flipped through the books but I
>> still can't find the answer to my problem.
>>
>> I'm inserting a new record via a stored procedure and want to return the
>> id via scope_identity, which I thought would be preety straight forward.
>>
>> The code I'm using is below and this keeps giving me "Multiple-step OLE
>> DB operation generated errors. Check each OLE DB status value, if
>> available. No work was done."
>>
>> How do I pick the returning id value and could anyone see were I'm going
>> wrong below.
>>
>> Many thanks for any help you can offer.
>>
>> CREATE PROCEDURE [sp_insert_address]
>>
>>
>> @ADDR_NAME_2 [char](70),
>> @ADDR_NO_3 [char](10),
>> @ADDR_ROAD_4 [char](50),
>> @ADDR_DISTRICT_5 [char](50),
>> @ADDR_TOWN_6 [char](50),
>> @ADDR_BOROUGH_7 [char](50),
>> @ADDR_PCODE_8 [char](12),
>> @addr_id [int] OUTPUT
>>
>>
>> AS INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
>> (
>> [ADDR_NAME],
>> [ADDR_NO],
>> [ADDR_ROAD],
>> [ADDR_DISTRICT],
>> [ADDR_TOWN],
>> [ADDR_BOROUGH],
>> [ADDR_PCODE])
>>
>>
>> VALUES
>> (
>> @ADDR_NAME_2,
>> @ADDR_NO_3,
>> @ADDR_ROAD_4,
>> @ADDR_DISTRICT_5,
>> @ADDR_TOWN_6,
>> @ADDR_BOROUGH_7,
>> @ADDR_PCODE_8)
>>
>> set @addr_id = scope_identity()
>> GO
>>
>>
>
> First, don't use the sp_ prefix for your procedure - it's reserved for
> system procs, and MSSQL will look for it in the master database before the
> current database.
>
> Apart from that, your code looks basically correct - are you asking how to
> use output parameters? If so, then this should work:
>
> declare @new_id int
>
> exec insert_address
> @addr_name2 = 'Something',
> -- etc.
> @addr_id = @new_id OUTPUT
>
> select @new_id
>
> Simon

[Back to original message]