Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the following SQL code against the AdventureWorks Database:

begin transaction;

drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
    add LineNumber int null;
go

with sod as (
    select
        SalesOrderID, 
            /* other cols */
        LineNumber,
        NewLineNumber = row_number() over 
            (partition by SalesOrderID order by SalesOrderDetailID) + 1
    from Sales.SalesOrderDetail
    where SalesOrderID <= 44000)
update sod
set LineNumber = NewLineNumber,
    ModifiedDate = getdate()
output
    inserted.SalesOrderID, 
    /* other cols */
    inserted.LineNumber;

select
    SalesOrderID, 
    /* other cols */
    LineNumber
from Sales.SalesOrderDetail
where SalesOrderID <= 44000;

rollback transaction;

When running this code on SQL Server 2008R2, it runs fine; that is to say, it updates the data in Sales.SalesOrderDetail and outputs the results, which match the data from the following SELECT statement.

However, when running this code on SQL Server 2012, I get the message:

Msg 4196, Level 16, State 1, Line 2
Column 'deleted.NewLineNumber' cannot be referenced in the OUTPUT clause because the column definition contains an expression using a window function.

This is odd because I am not referencing deleted.NewLineNumber in any way. I have attempted removing various OUTPUT columns and get the same result. The only two ways I have found to stop the error have been to

  • make the NewLineNumber column be exactly the result of the row_number() (that is to say: NewLineNumber = row_number() over (...)); or
  • remove the OUTPUT clause altogether.

Is this possibly a bug in how SQL Server is processing the expressions in the CTE? Has anyone else run into this?

I'm working on possible workarounds, but I am curious about this.

share|improve this question
    
I presume something to do with the fix here connect.microsoft.com/SQLServer/feedback/details/682645/… –  Martin Smith Feb 13 '13 at 12:51
    
Looks like someone has now raised this issue here connect.microsoft.com/SQLServer/feedback/details/779077/… –  Martin Smith Feb 13 '13 at 18:08
    
Yes - I raised the issue at MS connect after hearing from Aaron below and from other people at MS TechNet. –  Stuart Turner Feb 13 '13 at 21:22
    
As for the other ticket, I believe that it is related. I would presume that when they fixed the problem listed in that ticket, they created the problem I'm running into. I agree that the problem given by the original ticket is a valid bug and should be fixed. However, I think they may have been a little overzealous in preventing it, as I see no reason why this one should not work, especially seeing as it works fine with the +1 in the set clause. –  Stuart Turner Feb 13 '13 at 21:24
add comment

1 Answer

up vote 7 down vote accepted

This is a bug in SQL Server 2012, and will be fixed in SP2:

http://connect.microsoft.com/SQLServer/feedback/details/779077/output-clause-fails-when-doing-update-using-cte

A workaround is to remove the + 1 from the ROW_NUMBER() calculation; this spurious error message goes away. Rewrite this portion:

  NewLineNumber = row_number() over 
    (partition by SalesOrderID order by SalesOrderDetailID)
    ---------------- remove the +1 here --------------------^^^
  from Sales.SalesOrderDetail
  where SalesOrderID <= 44000)
update sod 
  set LineNumber = NewLineNumber + 1,
  ------- and put it here -------^^^
share|improve this answer
    
Hmm... very interesting. All the more reason to think this might be a bug in the query engine. I've put in a note at the tech forums to see if maybe the MS people know anything more. This is probably enough for me to jerry-rig a workaround for my project though. Thanks! –  Stuart Turner Feb 12 '13 at 21:31
    
@Stuart I have some contacts that I'm pinging as well. I don't think this is a bug per se, I just think the error message is slightly misleading (it shouldn't talk about deleted). But I would like to get an explanation about why the workaround works simply by moving the location of the addition. –  Aaron Bertrand Feb 12 '13 at 21:40
    
@Stuart it is in fact a bug, and will be fixed in 2012 SP2 (ETA unknown): connect.microsoft.com/SQLServer/feedback/details/779077/… The workaround is trivial so it is unlikely pestering PSS will get you a hot fix any faster. –  Aaron Bertrand Feb 18 '13 at 18:14
    
Thanks Aaron. This is the feedback I posted, and I was grateful to hear from them that they had already fixed it. Yes, the workaround is easy, but will require a bit of ugly code in my project. Right or wrong, I am building a SQL string in my project on the fly (though with parameters for user provided data; thankfully I'm not a moron). The original pattern is easy to abstract as "provide the definition of sod and we'll update from there". The new pattern requires I provide the update statement as well. –  Stuart Turner Feb 18 '13 at 21:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.