OUTPUT Clause Not Working On SQL 2008/2008R2 As mentioned, But working On 2005: When Insert Fails OUTPUT has Nothing - by Shah Anup

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 642177 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/9/2011 7:46:23 AM
Access Restriction Public


I see the Intended behaviour of OUTPUT Clause should be:
Even If Insert/Update/Delete/Merge Fails OUTPUT Clause will return result nad hence user should be care full while using that result set.

But I am not able to replicate this on sql 2008R2. my insert fails and OUTPUT Clause retuns nothing.

I am using following SQL Server Version:
    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1
    Developer Edition(Build 2600: Service Pack 3)

I have also Old SQL 2005 Instance I Upgraded from. 
O that Instance this was workin fine and returning first 2 rows as expected.
My 2005 insatnce is following 
Microsoft SQL Server 2005 - 9.00.3310.00 
Enterprise Edition (Build 3790: Service Pack 1) 
Sign in to post a comment.
Posted by Microsoft on 3/15/2011 at 12:04 PM
Upon further investigation the behavior you are seeing is by design. If the DML statement throws error then the OUTPUT clause may or may not return rows. It can also return different set of rows depending on the error or query processing. So as such you should ignore the rows returned if there is any error in the DML statement. Hope this helps explain the behavior.

Umachandar, SQL Programmability Team
Posted by Microsoft on 3/2/2011 at 4:15 PM
Thanks for reporting the issue. We will investigate the problem and let you know what we find.

Umachandar, SQL Programmability Team
Posted by Shah Anup on 2/11/2011 at 7:54 AM
Hi, I found the error. this was not error in OUTPUT Clause. but in UNION ALL clause.

in 2005 instance you can perform below select w/o any error

select 1 union all

select 2 union all

select 'a'

but you can not do it in sql 2008 becaus in first 2 select stmt i am select integer and then in 3rd select stmt i am selecting char data.

we mke sure in our etl process we are selcting same type of data and use cast/convert to make identical if required and everything is fine now.

but this makes one more thing clear is that SQL Server do first whole SELECT and then insert one row at a time. in my case SELECT itself was failing so process was not reaching to even OUTPUT clause.