Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Bug
ID: 642177
Opened: 2/9/2011 7:46:23 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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)
Details
Sign in to post a comment.
Posted by Microsoft on 3/15/2011 at 12:04 PM
Hello,
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
Hi,
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.
Sign in to post a workaround.